- Article
- 7 minutes to read
APPLIES TO: 
Azure Database for MySQL: Single server
Important
Azure Database for MySQL: Single server is being retired. We recommend upgrading to Azure Database for MySQL - Flexible Server. For more information on migrating to Azure Database for MySQL - Flexible Server, seeWhat's happening with Azure Database for MySQL single server?
Applies to:Azure Database for MySQL 5.7, 8.0
The Azure Database for MySQL Query Store feature provides a way to track query performance over time. Query Store simplifies performance troubleshooting by helping you quickly find the most time-consuming and resource-intensive queries. Query Store automatically captures query history and runtime statistics and preserves them for your review. It separates data by time window so you can see database usage patterns. Data from all users, databases and queries are stored inMySQLSchema database on instance of Azure Database for MySQL.
Common scenarios for using the Query Store
Query Store can be used in a variety of scenarios, including the following:
- reverse query detection
- Determine how many times a query was executed in a given time window
- Compare the average execution time of a query across time windows to see large deltas
Enable query storage
Query Store is an optional feature, so by default it is not active on a server. Query Store is enabled or disabled globally for all databases on a given server and cannot be enabled or disabled on a per-database basis.
Enable Query Store through the Azure portal
- Sign in to the Azure portal and select your Azure Database for MySQL server.
- Chooseserver parameterinside sidethe settingsmenu section.
- Look for the query_store_capture_mode parameter.
- Set the value to ALL andsave not computer.
To enable wait statistics on your query store:
- Look for the query_store_wait_sampling_capture_mode parameter.
- Set the value to ALL andsave not computer.
Allow up to 20 minutes for the first batch of data to be stored in the MySQL database.
Information in the query repository
The query store has two repositories:
- A runtime statistics store that stores information about query execution statistics.
- Wait statistics store to store wait statistics information.
To minimize memory usage, runtime runtime statistics are aggregated in the runtime statistics store over a fixed, configurable window of time. Information in these stores is visible by querying the Query Store views.
The following query returns information about the queries in the query store:
SELECT * FROM mysql.query_store;Or this wait statistics query:
AUSWAHL * DESDE mysql.query_store_wait_stats;query query search
Use
Wait statistics should not be enabled during peak workload periods or indefinitely for sensitive workloads.
Use caution when enabling wait statistics on workloads with high CPU utilization or on servers configured with fewer virtual cores. It shouldn't be on all the time.
Wait event types combine different wait events into buckets according to similarity. Query Store provides the type of wait event, the specific wait event name, and the query in question. The ability to correlate this wait information with query execution time statistics means you can gain a deeper understanding of what contributes to query performance.
Here are some examples of how you can use Query Store wait statistics to learn more about your workload:
| monitoring | action |
|---|---|
| highlock awaits | Review the query texts for the affected queries and identify the target entities. Search the Query Store for other queries that modify the same entity, run frequently, and/or have a long duration. Once you identify these queries, you should change your application logic to improve concurrency or use a less restrictive isolation level. |
| High buffer I/O wait | Find queries with a large number of physical reads in the query store. If they match queries with high I/O latencies, consider introducing an index on the underlying entity to perform lookups instead of checks. This would minimize query I/O overhead. Check theperformance recommendationsto your server on the portal to see if there are any index recommendations for that server that optimize queries. |
| high standby memory | Find the most memory intensive queries in Query Store. These queries are likely to delay the progress of the affected queries. Check theperformance recommendationsto your server on the portal to see if there are any index recommendations that optimize these queries. |
configuration options
When Query Store is enabled, it stores 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 | Pattern | To reach |
|---|---|---|---|
| query_store_capture_mode | Enable or disable the Query Store feature based on value. Note: If performance_schema is disabled, enabling query_store_capture_mode enables performance_schema and a subset of the performance schema tools required for this feature. | NO | NONE, ALL |
| query_store_capture_interval | The Query Store polling interval in minutes. Lets you specify the interval over which query metrics are aggregated | fifteen | 5 - 60 |
| query_store_capture_utility_consultas | Enable or disable to capture all utility queries running on the system. | NOT | BUT |
| query_store_retention_period_in_days | Time window in days to keep data in Query Store. | 7 | 1 - 30 |
The following options are specific to wait statistics.
| Parameter | Description | Pattern | To reach |
|---|---|---|---|
| query_store_wait_sampling_capture_mode | Allows you to enable/disable wait statistics. | NO | NONE, ALL |
| query_store_wait_sampling_frequency | Changes the hold sample rate in seconds. 5 to 300 seconds. | 30 | 5-300 |
Use
nowquery_store_capture_modeoverrides this setting, which means that bothquery_store_capture_modejquery_store_wait_sampling_capture_modeMust be enabled for ALL for wait stats to work. Yesquery_store_capture_modeis disabled, wait statistics are also disabled because wait statistics uses the enabled performance schema and the query text captured by Query Store.
Use oblue portalÖCLI do Azureto get or set a different value for a parameter.
views and features
View and manage Query Store using the following views and features. someone inSelect the privileged public roleYou can use these views to display the data in the Query Store. These views are only available inMySQLData base.
Queries are normalized by looking at their structure after removing literals and constants. If two queries are identical except for the literal values, they will have the same hash.
mysql.query_store
This view returns all data in the query store. There is a row for each unique database ID, user ID, and query ID.
| Name | data type | IS_NULLABLE | Description |
|---|---|---|---|
scheme name | varchar(64) | NOT | scheme name |
query_id | grande (20) | NOT | Unique ID generated for the specific query. If the same query is run against a different schema, a new ID will be generated |
datetime_id | time stamp | NOT | The timestamp when the query was executed. This is based on the query_store_interval setting |
query_summary_text | bigger text | NOT | Normalized query text after removing all literals |
query_example_text | bigger text | NOT | First occurrence of actual query with literals |
query_digest_truncated | a little | E | Whether the query text was truncated. Value is Yes if the query is greater than 1KB |
run_count | grande (20) | NOT | The number of times the query was executed for this timestamp ID/during the configured interval period |
account_warning | grande (20) | NOT | Number of alerts generated by this query during internal processing |
error_count | grande (20) | NOT | Number of errors this query generated during the interval |
sum_wait_timer | Double | E | Total execution time of this query during the interval |
avg_timer_wait | Double | E | Average execution time of this query during the interval |
min_timer_wait | Double | E | Minimum execution time for this query |
max_timer_wait | Double | E | Maximum execution time |
sum_lock_time | grande (20) | NOT | Total time spent running this query on all locks during this time window |
sum_affected_rows | grande (20) | NOT | Number of rows affected |
sum_rows_sent | grande (20) | NOT | Number of lines sent to the client |
sum_rows_examined | grande (20) | NOT | Number of lines examined |
sum_select_full_join | grande (20) | NOT | Number of complete unions |
sum_select_scan | grande (20) | NOT | Number of scans selected |
sum_sort_rows | grande (20) | NOT | Number of sorted rows |
sum_no_index_used | grande (20) | NOT | Number of times the query did not use an index |
sum_no_good_index_used | grande (20) | NOT | Number of times the query execution engine did not use good indexes |
sum_created_tmp_tables | grande (20) | NOT | Total number of temporary tables created |
sum_created_tmp_disk_tables | grande (20) | NOT | Total number of temp tables created on disk (generating I/O) |
seen for the first time | time stamp | NOT | The first occurrence (UTC) of the query during the aggregation window |
last seen | time stamp | NOT | The last occurrence (UTC) of the query during this aggregation window |
mysql.query_store_wait_stats
This view returns data from wait events in Query Store. There is a row for each unique database ID, user ID, query ID, and event.
| Name | data type | IS_NULLABLE | Description |
|---|---|---|---|
starting range | time stamp | NOT | Start of the break (15-minute increment) |
end_interval | time stamp | NOT | End of break (15-minute increment) |
query_id | grande (20) | NOT | Unique ID generated in normalized query (from Query Store) |
query_digest_id | varchar(32) | NOT | Normalized query text after removing all literals (from Query Store) |
query_summary_text | bigger text | NOT | First hit of actual query with literals (from Query Store) |
event type | varchar(32) | NOT | Wait for the event category |
event name | varchar(128) | NOT | Wait event name |
star_account | grande (20) | NOT | Number of wait events collected during the polling interval |
sum_timer_wait_ms | Double | NOT | Total wait time (in milliseconds) for this query during the interval |
functions
| Name | Description |
|---|---|
mysql.az_purge_querystore_data(TIMESTAMP) | Deletes all Query Store data before the specified timestamp |
mysql.az_procedure_purge_querystore_event(TIMESTAMP) | Clears all wait event data before the specified timestamp |
mysql.az_procedure_purge_recommendation(TIMESTAMP) | Deletes recommendations that expire before the specified timestamp |
Limitations and known issues
- If a MySQL server has the parameter
only readingEnabled, Query Store cannot retrieve data. - Query Store functionality may break when long Unicode queries (>= 6000 bytes) are encountered.
- The retention period for wait statistics is 24 hours.
- Wait statistics use sampling to capture a subset of events. The frequency can be changed with the parameter
query_store_wait_sampling_frequency.
Next steps
- learn more aboutQuery performance information