使用查询存储监视性能Monitor performance with the Query Store

适用于: Azure Database for PostgreSQL(单一服务器)9.6 及更高版本Applies to: Azure Database for PostgreSQL - Single Server versions 9.6 and above

Azure Database for PostgreSQL 中的查询存储功能提供了一种一段时间内跟踪查询性能的方法。The Query Store feature in Azure Database for PostgreSQL provides a way to track query performance over time. 通过帮助快速查找运行时间最长且资源最密集的查询,查询存储可简化性能故障排除。Query Store simplifies performance troubleshooting by helping you quickly find the longest running and most resource-intensive queries. 查询存储自动捕获查询和运行时统计信息的历史记录,并保留它们以供查看。Query Store automatically captures a history of queries and runtime statistics, and it retains them for your review. 它按时间范围分隔数据,以便可以查看数据库使用模式。It separates data by time windows so that you can see database usage patterns. 所有用户、数据库和查询的数据都存储在 Azure Database for PostgreSQL 实例中的名为 azure_sys 的数据库中。Data for all users, databases, and queries is stored in a database named azure_sys in the Azure Database for PostgreSQL instance.

重要

请勿修改 azure_sys 数据库或其架构。Do not modify the azure_sys database or its schemas. 执行此操作将阻止查询存储和相关的性能功能正常运行。Doing so will prevent Query Store and related performance features from functioning correctly.

启用查询存储Enabling Query Store

查询存储是一项选择加入功能,因此默认情况下它在服务器上未处于活动状态。Query Store is an opt-in feature, so it isn't active by default on a server. 对于给定服务器上的所有数据库,该存储处于全局启用或禁用状态,且无法为每个数据库打开或关闭存储。The store is enabled or disabled globally for all the databases on a given server and cannot be turned on or off per database.

使用 Azure 门户启用查询存储Enable Query Store using the Azure portal

  1. 登录到 Azure 门户,选择 Azure Database for PostgreSQL 服务器。Sign in to the Azure portal and select your Azure Database for PostgreSQL server.
  2. 在菜单的“设置”部分中选择“服务器参数” 。Select Server Parameters in the Settings section of the menu.
  3. 搜索 pg_qs.query_capture_mode 参数。Search for the pg_qs.query_capture_mode parameter.
  4. 将值设置为 TOP保存Set the value to TOP and Save.

若要在查询存储中启用等待统计信息,请执行以下操作:To enable wait statistics in your Query Store:

  1. 搜索 pgms_wait_sampling.query_capture_mode 参数。Search for the pgms_wait_sampling.query_capture_mode parameter.
  2. 将值设置为 ALL保存Set the value to ALL and Save.

或者,可使用 Azure CLI 设置这些参数。Alternatively you can set these parameters using the Azure CLI.

az postgres server configuration set --name pg_qs.query_capture_mode --resource-group myresourcegroup --server mydemoserver --value TOP
az postgres server configuration set --name pgms_wait_sampling.query_capture_mode --resource-group myresourcegroup --server mydemoserver --value ALL

允许第一批数据在 azure_sys 数据库中最多保留 20 分钟。Allow up to 20 minutes for the first batch of data to persist in the azure_sys database.

查询存储中的信息Information in Query Store

查询存储有两个存储:Query Store has two stores:

  • 用于保存查询执行统计信息的运行时统计信息存储。A runtime stats store for persisting the query execution statistics information.
  • 用于保存等待统计信息的等待统计信息存储。A wait stats store for persisting wait statistics information.

使用查询存储的常见方案包括:Common scenarios for using Query Store include:

  • 确定在给定时间范围内执行查询的次数Determining the number of times a query was executed in a given time window
  • 比较跨时间范围查询的平均执行时间,以查看较大的增量Comparing the average execution time of a query across time windows to see large deltas
  • 标识过去 X 小时内运行时间最长的查询Identifying longest running queries in the past X hours
  • 标识正在等待资源的前 N 个查询Identifying top N queries that are waiting on resources
  • 了解特定查询的等待性质Understanding wait nature for a particular query

为尽量减少空间使用量,运行时统计信息存储中的运行时执行统计信息在一个固定的、可配置的时间范围内聚合。To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed, configurable time window. 通过查询这些查询存储视图,这些存储中的信息是可见的。The information in these stores is visible by querying the query store views.

访问查询存储信息Access Query Store information

查询存储数据存储在 Postgres 服务器上的 azure_sys 数据库中。Query Store data is stored in the azure_sys database on your Postgres server.

以下查询返回有关查询存储中的查询的信息:The following query returns information about queries in Query Store:

SELECT * FROM query_store.qs_view; 

或此等待统计信息的查询:Or this query for wait stats:

SELECT * FROM query_store.pgms_wait_sampling_view;

查找等待查询Finding wait queries

等待事件类型按相似性将不同的等待事件组合到存储桶中。Wait event types combine different wait events into buckets by similarity. 查询存储提供等待事件类型、特定等待事件名称和有争议的查询。Query Store provides the wait event type, specific wait event name, and the query in question. 能将此等待信息与查询运行时统计信息相关联,意味着可更深入地了解有助于查询性能特征的因素。Being able to correlate this wait information with the query runtime statistics means you can gain a deeper understanding of what contributes to query performance characteristics.

以下是一些示例,说明如何使用查询存储中的等待统计信息获得有关工作负载的更多见解:Here are some examples of how you can gain more insights into your workload using the wait statistics in Query Store:

观测Observation 操作Action
高锁定等待High Lock waits 检查受影响查询的查询文本,并确定目标实体。Check the query texts for the affected queries and identify the target entities. 在查询存储中查找修改同一实体的其他查询,这些查询经常执行和/或持续很长时间。Look in Query Store for other queries modifying the same entity, which is executed frequently and/or have high duration. 确定这些查询后,请考虑更改应用程序逻辑以提高并发性,或使用限制较少的隔离级别。After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
高缓冲 IO 等待High Buffer IO waits 在查询存储中查找具有大量物理读取的查询。Find the queries with a high number of physical reads in Query Store. 如果它们匹配具有高 IO 等待的查询,考虑在基础实体上引入索引,以便进行搜索而不是扫描。If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans. 这将最小化查询的 IO 开销。This would minimize the IO overhead of the queries. 检查门户中服务器的“性能建议”,以查看是否存在可优化查询的此服务器的索引建议。Check the Performance Recommendations for your server in the portal to see if there are index recommendations for this server that would optimize the queries.
高内存等待High Memory waits 在查询存储中查找消耗内存最多的查询。Find the top memory consuming queries in Query Store. 这些查询可能会延迟受影响查询的进度。These queries are probably delaying further progress of the affected queries. 检查门户中服务器的“性能建议”,以查看是否存在可优化这些查询的索引建议。Check the Performance Recommendations for your server in the portal to see if there are index recommendations that would optimize these queries.

配置选项Configuration options

启用查询存储时,它会在 15 分钟的聚合时段内保存数据,每个时段最多可存储 500 个不同查询。When Query Store is enabled it saves data in 15-minute aggregation windows, up to 500 distinct queries per window.

以下选项可用于配置查询存储参数。The following options are available for configuring Query Store parameters.

参数Parameter 说明Description 默认Default 范围Range
pg_qs.query_capture_modepg_qs.query_capture_mode 设置跟踪哪些语句。Sets which statements are tracked. none none, top, allnone, top, all
pg_qs.max_query_text_lengthpg_qs.max_query_text_length 设置可保存的最大查询长度。Sets the maximum query length that can be saved. 将截断较长的查询。Longer queries will be truncated. 60006000 100 - 10K100 - 10K
pg_qs.retention_period_in_dayspg_qs.retention_period_in_days 设置保持期。Sets the retention period. 77 1 - 301 - 30
pg_qs.track_utilitypg_qs.track_utility 设置是否跟踪实用程序命令Sets whether utility commands are tracked onon on, offon, off

以下选项专用于等待统计信息。The following options apply specifically to wait statistics.

参数Parameter 说明Description 默认Default 范围Range
pgms_wait_sampling.query_capture_modepgms_wait_sampling.query_capture_mode 设置跟踪哪些语句以获取等待统计信息。Sets which statements are tracked for wait stats. none none, allnone, all
Pgms_wait_sampling.history_periodPgms_wait_sampling.history_period 设置等待事件采样的频率(以毫秒为单位)。Set the frequency, in milliseconds, at which wait events are sampled. 100100 1-6000001-600000

备注

将 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 为 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.

使用 Azure 门户Azure CLI 获取或设置参数的不同值。Use the Azure portal or Azure CLI to get or set a different value for a parameter.

视图和函数Views and functions

使用以下视图和函数查看并管理查询存储。View and manage Query Store using the following views and functions. PostgreSQL 公共角色中的任何人都可使用这些视图来查看查询存储中的数据。Anyone in the PostgreSQL public role can use these views to see the data in Query Store. 这些视图仅在 azure_sys 数据库中可用。These views are only available in the azure_sys database.

删除文本和常数后,通过查看查询的结构来规范化查询。Queries are normalized by looking at their structure after removing literals and constants. 如果除文本值之外两个查询相同,则它们将具有相同的哈希值。If two queries are identical except for literal values, they will have the same hash.

query_store.qs_viewquery_store.qs_view

此视图返回查询存储中的所有数据。This view returns all the data in Query Store. 每个不同的数据库 ID、用户 ID 和查询 ID 都有一行。There is one row for each distinct database ID, user ID, and query ID.

名称Name 类型Type 参考References 说明Description
runtime_stats_entry_idruntime_stats_entry_id bigintbigint runtime_stats_entries 表的 IDID from the runtime_stats_entries table
user_iduser_id oidoid pg_authid.oidpg_authid.oid 执行此语句的用户的 OIDOID of user who executed the statement
db_iddb_id oidoid pg_database.oidpg_database.oid 在其中执行语句的数据库的 OIDOID of database in which the statement was executed
query_idquery_id bigintbigint 根据语句的分析树计算的内部哈希代码Internal hash code, computed from the statement's parse tree
query_sql_textquery_sql_text Varchar(10000)Varchar(10000) 代表语句的文本。Text of a representative statement. 具有相同结构的不同查询聚集在一起;此文本是群集中第一个查询的文本。Different queries with the same structure are clustered together; this text is the text for the first of the queries in the cluster.
plan_idplan_id bigintbigint 与此查询对应的计划 ID,尚不可用ID of the plan corresponding to this query, not available yet
start_timestart_time timestamptimestamp 查询按时段聚合 - 默认情况下,存储桶的时间跨度为 15 分钟。Queries are aggregated by time buckets - the time span of a bucket is 15 minutes by default. 这是与此条目的时段相对应的开始时间。This is the start time corresponding to the time bucket for this entry.
end_timeend_time timestamptimestamp 对应于此条目的时段的结束时间。End time corresponding to the time bucket for this entry.
callscalls bigintbigint 执行查询的次数Number of times the query executed
total_timetotal_time 双精度double precision 总查询执行时间(毫秒)Total query execution time, in milliseconds
min_timemin_time 双精度double precision 最小查询执行时间(毫秒)Minimum query execution time, in milliseconds
max_timemax_time 双精度double precision 最大查询执行时间(毫秒)Maximum query execution time, in milliseconds
mean_timemean_time 双精度double precision 平均查询执行时间(毫秒)Mean query execution time, in milliseconds
stddev_timestddev_time 双精度double precision 查询执行时间的标准偏差(毫秒)Standard deviation of the query execution time, in milliseconds
rows bigintbigint 由语句检索或影响的总行数Total number of rows retrieved or affected by the statement
shared_blks_hitshared_blks_hit bigintbigint 语句的共享块缓存命中总数Total number of shared block cache hits by the statement
shared_blks_readshared_blks_read bigintbigint 由语句读取的共享块总数Total number of shared blocks read by the statement
shared_blks_dirtiedshared_blks_dirtied bigintbigint 由语句更新的共享块总数Total number of shared blocks dirtied by the statement
shared_blks_writtenshared_blks_written bigintbigint 由语句编写的共享块总数Total number of shared blocks written by the statement
local_blks_hitlocal_blks_hit bigintbigint 语句的本地块缓存命中总数Total number of local block cache hits by the statement
local_blks_readlocal_blks_read bigintbigint 由语句读取的本地块总数Total number of local blocks read by the statement
local_blks_dirtiedlocal_blks_dirtied bigintbigint 由语句更新的本地块总数Total number of local blocks dirtied by the statement
local_blks_writtenlocal_blks_written bigintbigint 由语句编写的本地块总数Total number of local blocks written by the statement
temp_blks_readtemp_blks_read bigintbigint 由语句读取的临时块总数Total number of temp blocks read by the statement
temp_blks_writtentemp_blks_written bigintbigint 由语句编写的临时块总数Total number of temp blocks written by the statement
blk_read_timeblk_read_time 双精度double precision 语句读取块所花费的总时间(以毫秒为单位)(如果启用了 track_io_timing,否则为零)Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timeblk_write_time 双精度double precision 语句编写块所花费的总时间(以毫秒为单位)(如果启用了 track_io_timing,否则为零)Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

query_store.query_texts_viewquery_store.query_texts_view

此视图返回查询存储中的查询文本数据。This view returns query text data in Query Store. 每个不同的 query_text 都有一行。There is one row for each distinct query_text.

名称Name 类型Type 说明Description
query_text_idquery_text_id bigintbigint query_texts 表的 IDID for the query_texts table
query_sql_textquery_sql_text Varchar(10000)Varchar(10000) 代表语句的文本。Text of a representative statement. 具有相同结构的不同查询聚集在一起;此文本是群集中第一个查询的文本。Different queries with the same structure are clustered together; this text is the text for the first of the queries in the cluster.

query_store.pgms_wait_sampling_viewquery_store.pgms_wait_sampling_view

此视图返回查询存储中的等待事件数据。This view returns wait events data in Query Store. 每个不同的数据库 ID、用户 ID、查询 ID 和事件都有一行。There is one row for each distinct database ID, user ID, query ID, and event.

名称Name 类型Type 参考References 说明Description
user_iduser_id oidoid pg_authid.oidpg_authid.oid 执行此语句的用户的 OIDOID of user who executed the statement
db_iddb_id oidoid pg_database.oidpg_database.oid 在其中执行语句的数据库的 OIDOID of database in which the statement was executed
query_idquery_id bigintbigint 根据语句的分析树计算的内部哈希代码Internal hash code, computed from the statement's parse tree
event_typeevent_type texttext 后端正在等待的事件类型The type of event for which the backend is waiting
eventevent texttext 后端当前正在等待的等待事件名称The wait event name if backend is currently waiting
callscalls IntegerInteger 捕获的相同事件的数量Number of the same event captured

函数Functions

Query_store.qs_reset() 返回无效值Query_store.qs_reset() returns void

qs_reset 丢弃查询存储迄今收集的所有统计信息。qs_reset discards all statistics gathered so far by Query Store. 只能由服务器管理员角色执行此函数。This function can only be executed by the server admin role.

Query_store.staging_data_reset() 返回无效值Query_store.staging_data_reset() returns void

staging_data_reset 丢弃查询存储在内存中收集的所有统计信息(即内存中尚未刷新到数据库的数据)。staging_data_reset discards all statistics gathered in memory by Query Store (that is, the data in memory that has not been flushed yet to the database). 只能由服务器管理员角色执行此函数。This function can only be executed by the server admin role.

Azure MonitorAzure Monitor

Azure Database for PostgreSQL 集成了 Azure Monitor 诊断设置Azure Database for PostgreSQL is integrated with Azure Monitor diagnostic settings. 使用诊断设置可将 JSON 格式的 Postgres 日志发送到 Azure Monitor 日志用于分析和警报、发送到事件中心进行流式处理,或者发送到 Azure 存储进行存档。Diagnostic settings allows you to send your Postgres logs in JSON format to Azure Monitor Logs for analytics and alerting, Event Hubs for streaming, and Azure Storage for archiving.

重要

此诊断功能仅适用于“常规用途”和“内存优化”定价层。This diagnostic feature for is only available in the General Purpose and Memory Optimized pricing tiers.

配置诊断设置Configure diagnostic settings

可以使用 Azure 门户、CLI、REST API 和 PowerShell 为 Postgres 服务器启用诊断设置。You can enable diagnostic settings for your Postgres server using the Azure portal, CLI, REST API, and PowerShell. 要配置的日志类别是 QueryStoreRuntimeStatisticsQueryStoreWaitStatisticsThe log categories to configure are QueryStoreRuntimeStatistics and QueryStoreWaitStatistics.

若要使用 Azure 门户启用资源日志:To enable resource logs using the Azure portal:

  1. 在门户上 Postgres 服务器的导航菜单中,转到“诊断设置”。In the portal, go to Diagnostic Settings in the navigation menu of your Postgres server.
  2. 选择“添加诊断设置”。Select Add Diagnostic Setting.
  3. 为此设置命名。Name this setting.
  4. 选择首选的终结点(存储帐户、事件中心、Log Analytics)。Select your preferred endpoint (storage account, event hub, log analytics).
  5. 选择日志类型“QueryStoreRuntimeStatistics”和“QueryStoreWaitStatistics”。Select the log types QueryStoreRuntimeStatistics and QueryStoreWaitStatistics.
  6. 保存设置。Save your setting.

若要使用 PowerShell、CLI 或 REST API 启用此设置,请访问诊断设置一文。To enable this setting using PowerShell, CLI, or REST API, visit the diagnostic settings article.

JSON 日志格式JSON log format

下表描述了两种日志类型的字段。The following tables describes the fields for the two log types. 包括的字段以及它们的出现顺序可能有所不同,具体取决于你选择的输出终结点。Depending on the output endpoint you choose, the fields included and the order in which they appear may vary.

QueryStoreRuntimeStatisticsQueryStoreRuntimeStatistics

字段Field 说明Description
TimeGenerated [UTC]TimeGenerated [UTC] 记录日志时的时间戳 (UTC)Time stamp when the log was recorded in UTC
ResourceIdResourceId Postgres 服务器的 Azure 资源 URIPostgres server's Azure resource URI
CategoryCategory QueryStoreRuntimeStatistics
OperationNameOperationName QueryStoreRuntimeStatisticsEvent
LogicalServerName_sLogicalServerName_s Postgres 服务器名称Postgres server name
runtime_stats_entry_id_sruntime_stats_entry_id_s runtime_stats_entries 表的 IDID from the runtime_stats_entries table
user_id_suser_id_s 执行此语句的用户的 OIDOID of user who executed the statement
db_id_sdb_id_s 在其中执行语句的数据库的 OIDOID of database in which the statement was executed
query_id_squery_id_s 根据语句的分析树计算的内部哈希代码Internal hash code, computed from the statement's parse tree
end_time_send_time_s 对应于此条目的时段的结束时间End time corresponding to the time bucket for this entry
calls_scalls_s 执行查询的次数Number of times the query executed
total_time_stotal_time_s 总查询执行时间(毫秒)Total query execution time, in milliseconds
min_time_smin_time_s 最小查询执行时间(毫秒)Minimum query execution time, in milliseconds
max_time_smax_time_s 最大查询执行时间(毫秒)Maximum query execution time, in milliseconds
mean_time_smean_time_s 平均查询执行时间(毫秒)Mean query execution time, in milliseconds
resourceGroupResourceGroup 资源组The resource group
SubscriptionIdSubscriptionId 订阅 IDYour subscription ID
ResourceProviderResourceProvider Microsoft.DBForPostgreSQL
资源Resource Postgres 服务器名称Postgres server name
ResourceTypeResourceType Servers

QueryStoreWaitStatisticsQueryStoreWaitStatistics

字段Field 说明Description
TimeGenerated [UTC]TimeGenerated [UTC] 记录日志时的时间戳 (UTC)Time stamp when the log was recorded in UTC
ResourceIdResourceId Postgres 服务器的 Azure 资源 URIPostgres server's Azure resource URI
CategoryCategory QueryStoreWaitStatistics
OperationNameOperationName QueryStoreWaitEvent
user_id_suser_id_s 执行此语句的用户的 OIDOID of user who executed the statement
db_id_sdb_id_s 在其中执行语句的数据库的 OIDOID of database in which the statement was executed
query_id_squery_id_s 查询的内部哈希代码Internal hash code of the query
calls_scalls_s 捕获的相同事件的数量Number of the same event captured
event_type_sevent_type_s 后端正在等待的事件类型The type of event for which the backend is waiting
event_sevent_s 后端当前正在等待的等待事件名称The wait event name if the backend is currently waiting
start_time_tstart_time_t 事件开始时间Event start time
end_time_send_time_s 事件结束时间Event end time
LogicalServerName_sLogicalServerName_s Postgres 服务器名称Postgres server name
resourceGroupResourceGroup 资源组The resource group
SubscriptionIdSubscriptionId 订阅 IDYour subscription ID
ResourceProviderResourceProvider Microsoft.DBForPostgreSQL
资源Resource Postgres 服务器名称Postgres server name
ResourceTypeResourceType Servers

限制和已知问题Limitations and known issues

  • 如果 PostgreSQL 服务器具有参数 default_transaction_read_only,则查询存储无法捕获数据。If a PostgreSQL server has the parameter default_transaction_read_only on, Query Store cannot capture data.
  • 如果遇到较长的 Unicode 查询(> = 6000 个字节),查询存储功能可能会中断。Query Store functionality can be interrupted if it encounters long Unicode queries (>= 6000 bytes).
  • 只读副本从主服务器复制查询存储数据。Read replicas replicate Query Store data from the master server. 这意味着只读副本的查询存储不提供有关在只读副本上运行的查询的统计信息。This means that a read replica's Query Store does not provide statistics about queries run on the read replica.

后续步骤Next steps