Slow query logs in Azure Database for MariaDB

In Azure Database for MariaDB, 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.

For more information about the slow query log, see the MariaDB documentation for slow query log.

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. To enable it, set slow_query_log to ON. This can be enabled using the Azure portal or Azure CLI.

Other parameters you can adjust include:

• long_query_time: if a query takes longer than long_query_time (in seconds) that query is logged. The default is 10 seconds.
• 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: determines whether queries that do not use indexes are logged to the slow_query_log
• log_throttle_queries_not_using_indexes: This parameter limits the number of non-index queries that can be written to the slow query log. This parameter takes effect when log_queries_not_using_indexes is set to ON.
• log_output: if "File", allows the slow query log to be written to both the local server storage and to Azure Monitor Diagnostic Logs. If "None", the slow query log will only be written to Azure Monitor Diagnostics Logs.

See the MariaDB slow query log documentation for full descriptions of the slow query log parameters.

Access slow query logs

There are two options for accessing slow query logs in Azure Database for MariaDB: local server storage or Azure Monitor Diagnostic Logs. This is set using the log_output parameter.

For local server storage, you can list and download slow query logs using the Azure portal or the Azure CLI. In the Azure portal, navigate to your server in the Azure portal. Under the Monitoring heading, select the Server Logs page. For more information on Azure CLI, see Configure and access server logs using Azure CLI.

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. If the total size of the available logs exceeds 7 GB, then the oldest files are deleted until space is available. The 7 GB storage limit for the server logs is available free of cost and cannot be extended.

Logs are rotated every 24 hours or 7 GB, whichever comes first.

Diagnostic logs

Azure Database for MariaDB is integrated with Azure Monitor Diagnostic Logs. Once you have enabled slow query logs on your MariaDB 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.

Analyze logs in Azure Monitor Logs

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.

• Queries longer than 10 seconds on a particular server

  AzureDiagnostics
  | where LogicalServerName_s == '<your server name>'
  | where Category == 'MySqlSlowLogs'
  | project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s 
  | where query_time_d > 10
  

• List top 5 longest queries on a particular server

  AzureDiagnostics
  | where LogicalServerName_s == '<your server name>'
  | 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 == '<your server name>'
  | 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 == '<your server name>'
  | 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
  

• Display queries longer than 10 seconds across all MariaDB 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

• How to configure slow query logs from the Azure portal
• How to configure slow query logs from the Azure CLI