使用查询存储监视 Azure Database for MariaDB 的性能Monitor Azure Database for MariaDB performance with Query Store

适用于: Azure Database for MariaDB 10.2Applies to: Azure Database for MariaDB 10.2

使用 Azure Database for Mariadb 中的查询存储功能可以跟踪一段时间内的查询性能。The Query Store feature in Azure Database for Mariadb 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 MariaDB 实例的 mysql 架构数据库中。Data for all users, databases, and queries is stored in the mysql schema database in the Azure Database for MariaDB instance.

使用查询存储的常见场景Common scenarios for using Query Store

可以在许多场景中使用查询存储,包括:Query store can be used in a number of scenarios, including the following:

  • 检测回归查询Detecting regressed queries
  • 确定在给定时间范围内执行查询的次数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

启用查询存储Enabling Query Store

查询存储是一项选择加入功能,因此默认情况下它在服务器上未处于活动状态。Query Store is an opt-in feature, so it isn't active by default on a server. 对于给定服务器上的所有数据库,查询存储处于全局启用或禁用状态,且无法为每个数据库打开或关闭它。The query 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 MariaDB 服务器。Sign in to the Azure portal and select your Azure Database for MariaDB server.
  2. 在菜单的“设置”部分中选择“服务器参数” 。Select Server Parameters in the Settings section of the menu.
  3. 搜索 query_store_capture_mode 参数。Search for the query_store_capture_mode parameter.
  4. 将值设置为 ALL,然后保存Set the value to ALL and Save.

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

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

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

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

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

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

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

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

SELECT * FROM mysql.query_store;

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

SELECT * FROM mysql.query_store_wait_stats;

查找等待查询Finding wait queries

备注

不应该在工作负荷高峰时段启用等待统计信息,或者对于敏感的工作负荷无限期地启用等待统计信息。Wait statistics should not be enabled during peak workload hours or be turned on indefinitely for sensitive workloads.
对于在 CPU 利用率较高的情况下运行的工作负荷,或在配置了较低 vCore 的服务器上运行的工作负荷,启用等待统计信息时请格外小心。For workloads running with high CPU utilization or on servers configured with lower vCores, use caution when enabling wait statistics. 不应无限期地启用等待统计信息。It should not be turned on indefinitely.

等待事件类型按相似性将不同的等待事件组合到存储桶中。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, 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
query_store_capture_modequery_store_capture_mode 根据值打开/关闭查询存储功能。Turn the query store feature ON/OFF based on the value. 注意:如果 performance_schema 为 OFF,则打开 query_store_capture_mode 会打开 performance_schema,以及此功能所需的一部分性能架构检测。Note: If performance_schema is OFF, turning on query_store_capture_mode will turn on performance_schema and a subset of performance schema instruments required for this feature. ALLALL NONE、ALLNONE, ALL
query_store_capture_intervalquery_store_capture_interval 查询存储捕获时间间隔,以分钟为单位。The query store capture interval in minutes. 允许指定聚合查询指标的时间间隔Allows specifying the interval in which the query metrics are aggregated 1515 5 - 605 - 60
query_store_capture_utility_queriesquery_store_capture_utility_queries 打开或关闭捕获系统中正在执行的所有实用工具查询的功能。Turning ON or OFF to capture all the utility queries that is executing in the system. NO YES、NOYES, NO
query_store_retention_period_in_daysquery_store_retention_period_in_days 在查询存储中保留数据的时限,以天为单位。Time window in days to retain the data in the query store. 77 1 - 301 - 30

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

参数Parameter 说明Description 默认Default 范围Range
query_store_wait_sampling_capture_modequery_store_wait_sampling_capture_mode 允许打开/关闭等待统计信息。Allows turning ON / OFF the wait statistics. NONE NONE、ALLNONE, ALL
query_store_wait_sampling_frequencyquery_store_wait_sampling_frequency 更改等待采样的频率,以秒为单位。Alters frequency of wait-sampling in seconds. 5 到 300 秒。5 to 300 seconds. 3030 5-3005-300

备注

目前,query_store_capture_mode 将取代此配置,这意味着,query_store_capture_modequery_store_wait_sampling_capture_mode 都必须启用 ALL,才能正常查询等待统计信息。Currently query_store_capture_mode supersedes this configuration, meaning both query_store_capture_mode and query_store_wait_sampling_capture_mode have to be enabled to ALL for wait statistics to work. 如果关闭 query_store_capture_mode,则等待统计信息也会关闭,因为等待统计信息利用已启用的 performance_schema,以及查询存储捕获的 query_text。If query_store_capture_mode is turned off, then wait statistics is turned off as well since wait statistics utilizes the performance_schema enabled, and the query_text captured by query store.

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

视图和函数Views and functions

使用以下视图和函数查看并管理查询存储。View and manage Query Store using the following views and functions. 选择特权公共角色中的任何人都可使用这些视图来查看查询存储中的数据。Anyone in the select privilege public role can use these views to see the data in Query Store. 这些视图仅在 mysql 数据库中提供。These views are only available in the mysql 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.

mysql.query_storemysql.query_store

此视图返回查询存储中的所有数据。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 数据类型Data Type IS_NULLABLEIS_NULLABLE 说明Description
schema_name varchar(64)varchar(64) NO 架构名称Name of the schema
query_id bigint(20)bigint(20) NO 为特定查询生成的唯一 ID,如果在不同的架构中执行同一个查询,将生成新的 IDUnique ID generated for the specific query, if the same query executes in different schema, a new ID will be generated
timestamp_id timestamptimestamp NO 执行查询时的时间戳。Timestamp in which the query is executed. 此值基于 query_store_interval 配置This is based on the query_store_interval configuration
query_digest_text longtextlongtext NO 删除所有文本后的规范化查询文本The normalized query text after removing all the literals
query_sample_text longtextlongtext NO 首次出现的包含文本的实际查询First appearance of the actual query with literals
query_digest_truncated bitbit YES 查询文本是否已截断。Whether the query text has been truncated. 如果查询超过 1 KB,则值为 YesValue will be Yes if the query is longer than 1 KB
execution_count bigint(20)bigint(20) NO 针对此时间戳 ID/在配置的间隔时间段内执行该查询的次数The number of times the query got executed for this timestamp ID / during the configured interval period
warning_count bigint(20)bigint(20) NO 此查询在该时间间隔内生成的警告数Number of warnings this query generated during the internal
error_count bigint(20)bigint(20) NO 此查询在该时间间隔内生成的错误数Number of errors this query generated during the interval
sum_timer_wait Doubledouble YES 此查询在该时间间隔内的总执行时间Total execution time of this query during the interval
avg_timer_wait Doubledouble YES 此查询在该时间间隔内的平均执行时间Average execution time for this query during the interval
min_timer_wait Doubledouble YES 此查询的最小执行时间Minimum execution time for this query
max_timer_wait Doubledouble YES 最大执行时间Maximum execution time
sum_lock_time bigint(20)bigint(20) NO 在此时间范围内对此查询执行的所有锁花费的总时间Total amount of time spent for all the locks for this query execution during this time window
sum_rows_affected bigint(20)bigint(20) NO 受影响的行数Number of rows affected
sum_rows_sent bigint(20)bigint(20) NO 发送到客户端的行数Number of rows sent to client
sum_rows_examined bigint(20)bigint(20) NO 检查的行数Number of rows examined
sum_select_full_join bigint(20)bigint(20) NO 完整联接的数目Number of full joins
sum_select_scan bigint(20)bigint(20) NO select 扫描数Number of select scans
sum_sort_rows bigint(20)bigint(20) NO 排序的行数Number of rows sorted
sum_no_index_used bigint(20)bigint(20) NO 查询未使用任何索引的次数Number of times when the query did not use any indexes
sum_no_good_index_used bigint(20)bigint(20) NO 查询执行引擎未使用任何适当索引的次数Number of times when the query execution engine did not use any good indexes
sum_created_tmp_tables bigint(20)bigint(20) NO 创建的临时表总数Total number of temp tables created
sum_created_tmp_disk_tables bigint(20)bigint(20) NO 在磁盘中创建的临时表总数(生成 I/O)Total number of temp tables created in disk (generates I/O)
first_seen timestamptimestamp NO 在聚合时段发生第一次查询的时间 (UTC)The first occurrence (UTC) of the query during the aggregation window
last_seen timestamptimestamp NO 在此聚合时段发生最后一次查询的时间 (UTC)The last occurrence (UTC) of the query during this aggregation window

mysql.query_store_wait_statsmysql.query_store_wait_stats

此视图返回查询存储中的等待事件数据。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 数据类型Data Type IS_NULLABLEIS_NULLABLE 说明Description
interval_start timestamptimestamp NO 间隔开始时间(15 分钟增量)Start of the interval (15-minute increment)
interval_end timestamptimestamp NO 间隔结束时间(15 分钟增量)End of the interval (15-minute increment)
query_id bigint(20)bigint(20) NO 针对规范化查询生成的唯一 ID(从查询存储)Generated unique ID on the normalized query (from query store)
query_digest_id varchar(32)varchar(32) NO 删除所有文本后的规范化查询文本(从查询存储)The normalized query text after removing all the literals (from query store)
query_digest_text longtextlongtext NO 首次出现的包含文本的实际查询(从查询存储)First appearance of the actual query with literals (from query store)
event_type varchar(32)varchar(32) NO 等待事件的类别Category of the wait event
event_name varchar(128)varchar(128) NO 等待事件的名称Name of the wait event
count_star bigint(20)bigint(20) NO 在查询间隔内采样的等待事件数Number of wait events sampled during the interval for the query
sum_timer_wait_ms Doubledouble NO 此查询在该时间间隔内的总等待时间(以毫秒为单位)Total wait time (in milliseconds) of this query during the interval

函数Functions

名称Name 说明Description
mysql.az_purge_querystore_data(TIMESTAMP) 清除给定时间戳之前的所有查询存储数据Purges all query store data before the given time stamp
mysql.az_procedure_purge_querystore_event(TIMESTAMP) 清除给定时间戳之前的所有等待事件数据Purges all wait event data before the given time stamp
mysql.az_procedure_purge_recommendation(TIMESTAMP) 清除其过期时间在给定时间戳之前的建议Purges recommendations whose expiration is before the given time stamp

限制和已知问题Limitations and known issues

  • 如果 MariaDB 服务器启用了参数 default_transaction_read_only,查询存储将无法捕获数据。If a MariaDB 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).
  • 等待统计信息的保留期为 24 小时。The retention period for wait statistics is 24 hours.
  • 等待统计信息使用样本来捕获一部分事件。Wait statistics uses sample ti capture a fraction of events. 可以使用参数 query_store_wait_sampling_frequency 修改频率。The frequency can be modified using the parameter query_store_wait_sampling_frequency.

后续步骤Next steps