教學課程:監視和調整「適用於 PostgreSQL 的 Azure 資料庫 - 單一伺服器」Tutorial: Monitor and tune Azure Database for PostgreSQL - Single Server

適用於 PostgreSQL 的 Azure 資料庫具有能協助您了解並改善伺服器效能的功能。Azure Database for PostgreSQL has features that help you understand and improve your server performance. 在本教學課程中,您將了解如何:In this tutorial you will learn how to:

  • 啟用查詢及等候統計資料的收集Enable query and wait statistics collection
  • 存取並運用所收集的資料Access and utilize the data collected
  • 檢視查詢效能與隨時間變化的等候統計資料View query performance and wait statistics over time
  • 分析資料庫以取得效能建議Analyze a database to get performance recommendations
  • 套用效能建議Apply performance recommendations

開始之前Before you begin

您需要有 PostgreSQL 9.6 版或 10 版的適用於 PostgreSQL 的 Azure 資料庫伺服器。You need an Azure Database for PostgreSQL server with PostgreSQL version 9.6 or 10. 您可以依照建立教學課程中的步驟來建立伺服器。You can follow the steps in the Create tutorial to create a server.

重要

查詢存放區查詢效能深入解析以及效能建議都處於公開預覽狀態。Query Store, Query Performance Insight, and Performance Recommendation are in Public Preview.

啟用資料收集Enabling data collection

查詢存放區能擷取伺服器上查詢及等候統計資料的歷程記錄,並將其儲存在您伺服器上的 azure_sys 資料庫中。The Query Store captures a history of queries and wait statistics on your server and stores it in the azure_sys database on your server. 它是選擇加入的功能。It is an opt-in feature. 若要啟用它:To enable it:

  1. 開啟 Azure 入口網站。Open the Azure portal.

  2. 選取適用於 PostgreSQL 的 Azure 資料庫伺服器。Select your Azure Database for PostgreSQL server.

  3. 在左側功能表的 [設定] 區段中,選取 [伺服器參數] 。Select Server parameters which is in the Settings section of the menu on the left.

  4. 將 [pg_qs.query_capture_mode] 設定為 [TOP] 以開始收集查詢效能資料。Set pg_qs.query_capture_mode to TOP to start collecting query performance data. 將 [pgms_wait_sampling.query_capture_mode] 設定為 [ALL] 以開始收集等候統計資料。Set pgms_wait_sampling.query_capture_mode to ALL to start collecting wait statistics. 儲存。Save.

    查詢存放區伺服器參數

  5. 請等候 20 分鐘,以讓第一批資料保存在 azure_sys 資料庫中。Allow up to 20 minutes for the first batch of data to persist in the azure_sys database.

效能深入解析Performance insights

Azure 入口網站中的查詢效能深入解析檢視會以視覺效果呈現來自查詢存放區的重要資訊。The Query Performance Insight view in the Azure portal will surface visualizations on key information from Query Store.

  1. 在適用於 PostgreSQL 的 Azure 資料庫伺服器的入口網站頁面中,在左側功能表的 [支援與疑難排解] 區段下,選取 [查詢效能深入解析] 。In the portal page of your Azure Database for PostgreSQL server, select Query performance Insight under the Support + troubleshooting section of the menu on the left.

  2. [長時間執行的查詢] 索引標籤會每隔 15 分鐘彙總一次,依每次執行的平均持續時間,顯示前 5 個查詢。The Long running queries tab shows the top 5 queries by average duration per execution, aggregated in 15 minute intervals.

    [查詢效能深入解析] 登陸頁面

    您可以從 [查詢數目] 下拉式清單中選取,以檢視更多查詢。You can view more queries by selecting from the Number of Queries drop down. 當您這樣做時,特定查詢識別碼的圖表色彩可能會有所變更。The chart colors may change for a specific Query ID when you do this.

  3. 您可以在圖表中按一下並拖曳來縮小到特定時間範圍。You can click and drag in the chart to narrow down to a specific time window.

  4. 使用放大和縮小圖示來分別檢視一段較短或較長的時間。Use the zoom in and out icons to view a smaller or larger period of time respectively.

  5. 檢視圖表下方的資料表以了解該時間範圍內長時間執行之查詢的詳細資訊。View the table below the chart to learn more details about the long-running queries in that time window.

  6. 選取 [等候統計資料] 索引標籤,以檢視伺服器中等候的對應視覺效果。Select the Wait Statistics tab to view the corresponding visualizations on waits in the server.

    查詢效能深入解析等候統計資料

權限Permissions

需要擁有者參與者權限,才能檢視查詢效能深入解析中的查詢文字。Owner or Contributor permissions required to view the text of the queries in Query Performance Insight. 讀者可以檢視圖表與資料表,但無法檢視查詢文字。Reader can view charts and tables but not query text.

效能建議Performance recommendations

效能建議功能可分析整部伺服器的工作負載,來找出可能可以改善效能的索引。The Performance Recommendations feature analyzes workloads across your server to identify indexes with the potential to improve performance.

  1. 在 PostgreSQL 伺服器的 Azure 入口網站頁面上,從功能表列的 [支援與疑難排解] 區段開啟 [效能建議] 。Open Performance Recommendations from the Support + troubleshooting section of the menu bar on the Azure portal page for your PostgreSQL server.

    [效能建議] 登陸頁面

  2. 選取 [分析] 並選擇資料庫。Select Analyze and choose a database. 隨即開始分析。This will begin the analysis.

  3. 根據您的工作負載,這可能需要幾分鐘才能完成。Depending on your workload, this may take several minutes to complete. 分析完成後,入口網站中會有通知。Once the analysis is done, there will be a notification in the portal.

  4. [效能建議] 視窗會以清單顯示所找到的任何建議。The Performance Recommendations window will show a list of recommendations if any were found.

  5. 建議會顯示 [資料庫] 、[資料表] 、[資料行] 與 [索引大小] 等相關資訊。A recommendation will show information about the relevant Database, Table, Column, and Index Size.

    效能建議結果

  6. 若要實作建議,請複製查詢文字並從您選擇的用戶端中執行該文字。To implement the recommendation, copy the query text and run it from your client of choice.

權限Permissions

需要擁有者參與者權限,才能使用 [效能建議] 功能執行分析。Owner or Contributor permissions required to run analysis using the Performance Recommendations feature.

後續步驟Next steps