查询存储的最佳做法Best practices for Query Store

适用于: Azure Database for PostgreSQL - 单一服务器版本 9.6、10、11Applies to: Azure Database for PostgreSQL - Single Server versions 9.6, 10, 11

本文概述了在 Azure Database for PostgreSQL 中使用查询存储的最佳做法。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 设置为“无”或“全部” 。There is an additional capture mode query that governs wait statistics: pgms_wait_sampling.query_capture_mode can be set to none or all.

备注

将 pgms_wait_sampling.query_capture_mode 替代为 pg_qs.query_capture_mode 。pg_qs.query_capture_mode supersedes pgms_wait_sampling.query_capture_mode. 若 pg_qs.query_capture_mode 设置为“无”,则 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 门户中的 Query Performance Insight 来快速了解查询存储的数据。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