查詢存放區的最佳做法Best practices for Query Store

適用範圍: Azure Database for PostgreSQL-9.6] 和 [10 的單一伺服器Applies to: Azure Database for PostgreSQL - Single Server 9.6 and 10

此文章概述在適用於 PostgreSQL 的 Azure 資料庫中使用查詢存放區的最佳做法。This article outlines best practices for using Query Store in Azure Database for PostgreSQL.

設定最佳查詢擷取模式Set the optimal query capture mode

讓查詢存放區擷取對您而言重要的資料。Let Query Store capture the data that matters to you.

pg_qs.query_capture_modepg_qs.query_capture_mode 案例Scenario
所有All 根據您的所有查詢與其執行頻率與其他統計資料徹底分析您的工作負載。Analyze your workload thoroughly in terms of all queries and their execution frequencies and other statistics. 識別您工作負載中的新查詢。Identify new queries in your workload. 偵測到如果臨機操作查詢用來識別使用者或自動參數化的機會。Detect if ad hoc queries are used to identify opportunities for user or auto parameterization. _全都_有增加的資源耗用成本。All comes with an increased resource consumption cost.
前幾個Top 專注在前幾個查詢 - 那些由客戶發出的查詢。Focus your attention on top queries - those issued by clients.
None 您已擷取查詢集與一段時間,您想要在這段時間內調查並減少其他查詢可能會產生之令人困惑之事。You've already captured a query set and time window that you want to investigate and you want to eliminate the distractions that other queries may introduce. _無_適用於測試及效能評定環境。None is suitable for testing and bench-marking environments. _無_應該謹慎使用,因為您可能可能會錯過追蹤及最佳化重要新查詢的機會。None should be used with caution as you might miss the opportunity to track and optimize important new queries. 我們無法復原過去時段內的資料。You can't recover data on those past time windows.

查詢存放區也包含等候統計資料的存放區。Query Store also includes a store for wait statistics. 有一個額外的擷取模式查詢,此查詢規範等候統計資料:pgms_wait_sampling.query_capture_mode 可設定為 noneallThere is an additional capture mode query that governs wait statistics: pgms_wait_sampling.query_capture_mode can be set to none or all.

注意

pg_qs.query_capture_mode 已取代 pgms_wait_sampling.query_capture_modepg_qs.query_capture_mode supersedes pgms_wait_sampling.query_capture_mode. 若 pg_qs.query_capture_mode 是 none,則 pgms_wait_sampling.query_capture_mode 設定沒有影響。If pg_qs.query_capture_mode is none, the pgms_wait_sampling.query_capture_mode setting has no effect.

保留您所需的資料Keep the data you need

pg_qs.retention_period_in_days 參數可指定查詢存放區的資料保留期間 (天)。The pg_qs.retention_period_in_days parameter specifies in days the data retention period for Query Store. 較舊的查詢與統計資料會被刪除。Older query and statistics data is deleted. 根據預設,查詢存放區是設定為將資料保留 7 天。By default, Query Store is configured to retain the data for 7 days. 避免保留您不打算使用的歷史資料。Avoid keeping historical data you do not plan to use. 若需要保留資料較長的時間,請增加該值。Increase the value if you need to keep data longer.

設定等候統計資料取樣的頻率Set the frequency of wait stats sampling

pgms_wait_sampling.history_period 參數可指定等候事件取樣頻率 (毫秒)。The pgms_wait_sampling.history_period parameter specifies how often (in milliseconds) wait events are sampled. 期間越短,取樣頻率就越頻繁。The shorter the period, the more frequent the sampling. 會擷取更多資訊,但會也產生較高的資源耗用成本。More information is retrieved, but that comes with the cost of greater resource consumption. 若伺服器負載不足或您不需要精細度,您可以增加此期間長度Increase this period if the server is under load or you don't need the granularity

取得查詢存放區的快速見解Get quick insights into Query Store

您可以使用 Azure入口網站中的查詢效能深入解析來取得查詢存放區資料的快速見解。You can use Query Performance Insight in the Azure portal to get quick insights into the data in Query Store. 視覺效果會呈現一段時間內執行時間最長的查詢,以及最長的等候時間。The visualizations surface the longest running queries and longest wait events over time.

後續步驟Next steps