mysql慢查询call_慢查询日志 - Azure Database for MySQL | Microsoft Docs

您现在访问的是微软AZURE全球版技术文档网站，若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站，请访问 https://docs.azure.cn.

Azure Database for MySQL 中的慢查询日志Slow query logs in Azure Database for MySQL

11/6/2020

本文内容

在 Azure Database for MySQL 中，慢查询日志可供用户使用。In Azure Database for MySQL, the slow query log is available to users. 不支持访问事务日志。Access to the transaction log is not supported. 可以使用慢查询日志来查明性能瓶颈以进行故障排除。The slow query log can be used to identify performance bottlenecks for troubleshooting.

有关 MySQL 慢查询日志的详细信息，请参阅 MySQL 参考手册中的慢查询日志部分。For more information about the MySQL slow query log, see the MySQL reference manual's slow query log section.

在服务器上启用 查询存储 时，可能会看到类似于 "" 的查询 CALL mysql.az_procedure_collect_wait_stats (900, 30); 记录在慢速查询日志中。When Query Store is enabled on your server, you may see the queries like "CALL mysql.az_procedure_collect_wait_stats (900, 30);" logged in your slow query logs. 此行为是预期的行为，查询存储功能将收集有关查询的统计信息。This behavior is expected as the Query Store feature collects statistics about your queries.

配置慢查询日志记录Configure slow query logging

默认情况下，慢查询日志被禁用。By default the slow query log is disabled. 若要启用它，请将 slow_query_log 设置为 ON。To enable it, set slow_query_log to ON. 可以使用 Azure 门户或 Azure CLI 启用此功能。This can be enabled using the Azure portal or Azure CLI.

可以调整的其他参数包括：Other parameters you can adjust include:

long_query_time ：如果某个查询花费的时间超过了 long_query_time(以秒为单位)，则会记录该查询。long_query_time : if a query takes longer than long_query_time (in seconds) that query is logged. 默认为 10 秒。The default is 10 seconds.

log_slow_admin_statements ：如果为 ON，则会在写入到 slow_query_log 的语句中包括管理性语句，例如 ALTER_TABLE 和 ANALYZE_TABLE。log_slow_admin_statements : if ON includes administrative statements like ALTER_TABLE and ANALYZE_TABLE in the statements written to the slow_query_log.

log_queries_not_using_indexes ：确定是否将未使用索引的查询记录到 slow_query_log 中log_queries_not_using_indexes : determines whether queries that do not use indexes are logged to the slow_query_log

log_throttle_queries_not_using_indexes ：此参数限制可以写入到慢查询日志的非索引查询的数目。log_throttle_queries_not_using_indexes : This parameter limits the number of non-index queries that can be written to the slow query log. 当 log_queries_not_using_indexes 设置为 ON 时，此参数生效。This parameter takes effect when log_queries_not_using_indexes is set to ON.

log_output ：如果设置为“文件”，则允许将慢查询日志写入本地服务器存储和 Azure Monitor 诊断日志。log_output : if "File", allows the slow query log to be written to both the local server storage and to Azure Monitor Diagnostic Logs. 如果设置为“无”，则仅将慢查询日志写入 Azure Monitor 诊断日志。If "None", the slow query log will only be written to Azure Monitor Diagnostics Logs.

重要

如果表未编制索引，则将 log_queries_not_using_indexes 和 log_throttle_queries_not_using_indexes 参数设置为 ON 可能会影响 MySQL 性能，因为对这些非索引表运行的所有查询都将写入到慢速查询日志。If your tables are not indexed, setting the log_queries_not_using_indexes and log_throttle_queries_not_using_indexes parameters to ON may affect MySQL performance since all queries running against these non-indexed tables will be written to the slow query log.

如果计划在较长一段时间内记录慢速查询，建议将 log_output 设置为“None”。If you plan on logging slow queries for an extended period of time, it is recommended to set log_output to "None". 如果设置为“File”，则这些日志将写入到本地服务器存储，并且可能会影响 MySQL 性能。If set to "File", these logs are written to the local server storage and can affect MySQL performance.

有关慢查询日志参数的完整说明，请参阅 MySQL 慢查询日志文档。See the MySQL slow query log documentation for full descriptions of the slow query log parameters.

访问慢查询日志Access slow query logs

可以通过两种方法访问 Azure Database for MySQL 中的慢查询日志：本地服务器存储或 Azure Monitor 诊断日志。There are two options for accessing slow query logs in Azure Database for MySQL: local server storage or Azure Monitor Diagnostic Logs. 此项使用 log_output 参数进行设置。This is set using the log_output parameter.

对于本地服务器存储，可以使用 Azure 门户或 Azure CLI 列出并下载慢查询日志。For local server storage, you can list and download slow query logs using the Azure portal or the Azure CLI. 在 Azure 门户中导航到你的服务器。In the Azure portal, navigate to your server in the Azure portal. 在“监视”标题下，选择“服务器日志”页面。Under the Monitoring heading, select the Server Logs page. 有关 Azure CLI 的详细信息，请参阅使用 Azure CLI 配置和访问慢查询日志。

使用 Azure Monitor 诊断日志可以通过管道将慢查询日志传输到 Azure Monitor 日志 (Log Analytics)、Azure 存储或事件中心。Azure Monitor Diagnostic Logs allows you to pipe slow query logs to Azure Monitor Logs (Log Analytics), Azure Storage, or Event Hubs. 有关详细信息，请参阅下文。See below for more information.

本地服务器存储日志保留期Local server storage log retention

将日志记录到服务器的本地存储时，日志在创建后的七天内可用。When logging to the server's local storage, logs are available for up to seven days from their creation. 如果可用日志的总大小超过了 7 GB，则会删除最旧的文件，直到有空间可用。If the total size of the available logs exceeds 7 GB, then the oldest files are deleted until space is available.

日志每 24 小时或每 7 GB 轮换一次(以先达到的条件为准)。Logs are rotated every 24 hours or 7 GB, whichever comes first.

备注

上述日志保留期不适用于使用 Azure Monitor 诊断日志通过管道传输的日志。The above log retention does not apply to logs that are piped using Azure Monitor Diagnostic Logs. 可以更改日志所发送到的数据接收器(例如You can change the retention period for the data sinks being emitted to (ex. Azure 存储)的保留期。Azure Storage).

诊断日志Diagnostic logs

Azure Database for MySQL 集成了 Azure Monitor 诊断日志。Azure Database for MySQL is integrated with Azure Monitor Diagnostic Logs. 在 MySQL 服务器上启用慢查询日志后，可以选择将它们发送到 Azure Monitor 日志、事件中心或 Azure 存储。Once you have enabled slow query logs on your MySQL server, you can choose to have them emitted to Azure Monitor logs, Event Hubs, or Azure Storage. 若要详细了解如何启用诊断日志，请参阅诊断日志文档中的操作说明部分。To learn more about how to enable diagnostic logs, see the how to section of the diagnostic logs documentation.

下表介绍了每个日志中的内容。The following table describes what's in each log. 根据输出方法，包含的字段以及这些字段出现的顺序可能会有所不同。Depending on the output method, the fields included and the order in which they appear may vary.

属性Property

说明Description

TenantId

租户 IDYour tenant ID

SourceSystem

Azure

TimeGenerated [UTC]TimeGenerated [UTC]

记录日志时的时间戳 (UTC)Time stamp when the log was recorded in UTC

Type

日志的类型。Type of the log. 始终是 AzureDiagnosticsAlways AzureDiagnostics

SubscriptionId

服务器所属的订阅的 GUIDGUID for the subscription that the server belongs to

ResourceGroup

服务器所属的资源组的名称Name of the resource group the server belongs to

ResourceProvider

资源提供程序的名称。Name of the resource provider. 始终是 MICROSOFT.DBFORMYSQLAlways MICROSOFT.DBFORMYSQL

ResourceType

Servers

ResourceId

资源 URIResource URI

Resource

服务器的名称Name of the server

Category

MySqlSlowLogs

OperationName

LogEvent

Logical_server_name_s

服务器的名称Name of the server

start_time_t [UTC]start_time_t [UTC]

查询开始时间Time the query began

query_time_s

执行查询所需的总时间(秒)Total time in seconds the query took to execute

lock_time_s

锁定查询的总时间(秒)Total time in seconds the query was locked

user_host_s

用户名Username

rows_sent_s

发送的行数Number of rows sent

rows_examined_s

检查的行数Number of rows examined

insert_id_s

插入 IDInsert ID

sql_text_s

完整查询Full query

server_id_s

服务器 IDThe server's ID

thread_id_s

线程 IDThread ID

\_ResourceId

资源 URIResource URI

备注

对于 sql_text，如果日志超过 2048 个字符，则会截断日志。For sql_text, log will be truncated if it exceeds 2048 characters.

分析 Azure Monitor 日志中的日志Analyze logs in Azure Monitor Logs

通过诊断日志将慢速查询日志传输到 Azure Monitor 日志后，可以进一步分析慢速查询。Once your slow query logs are piped to Azure Monitor Logs through Diagnostic Logs, you can perform further analysis of your slow queries. 下面是一些可帮助你入门的示例查询。Below are some sample queries to help you get started. 请确保使用你的服务器名称更新下面的内容。Make sure to update the below with your server name.

特定服务器上超过 10 秒的查询Queries longer than 10 seconds on a particular server

AzureDiagnostics

| where LogicalServerName_s == ''

| where Category == 'MySqlSlowLogs'

| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s

| where query_time_d > 10

列出特定服务器上最长的 5 个查询List top 5 longest queries on a particular server

AzureDiagnostics

| where LogicalServerName_s == ''

| where Category == 'MySqlSlowLogs'

| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s

| order by query_time_d desc

| take 5

按最小、最大、平均和标准偏差查询时间汇总特定服务器上的慢速查询Summarize slow queries by minimum, maximum, average, and standard deviation query time on a particular server

AzureDiagnostics

| where LogicalServerName_s == ''

| where Category == 'MySqlSlowLogs'

| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s

| summarize count(), min(query_time_d), max(query_time_d), avg(query_time_d), stdev(query_time_d), percentile(query_time_d, 95) by LogicalServerName_s

绘制特定服务器上的慢速查询的分布图Graph the slow query distribution on a particular server

AzureDiagnostics

| where LogicalServerName_s == ''

| where Category == 'MySqlSlowLogs'

| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s

| summarize count() by LogicalServerName_s, bin(TimeGenerated, 5m)

| render timechart

显示启用了诊断日志的所有 MySQL 服务器上超过 10 秒的查询Display queries longer than 10 seconds across all MySQL servers with Diagnostic Logs enabled

AzureDiagnostics

| where Category == 'MySqlSlowLogs'

| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s

| where query_time_d > 10

后续步骤Next Steps