Tutorial: Query Performance Insight for Azure Database for MySQL - Flexible Server

APPLIES TO: Azure Database for MySQL - Flexible Server

Query Performance Insight proposes to provide intelligent query analysis for databases. The most preferred insights are the workload patterns and the longer-running queries. Understanding these insights can help you find which queries to optimize to improve overall performance and to use your available resources efficiently.

Query Performance Insight is designed to help you spend less time troubleshooting database performance by providing such information as:

• Top N long-running queries and their trends.
• The query details: view the history of execution with minimum, maximum, average, and standard deviation query time.
• The resource utilizations (CPU, memory, and storage).

This article discusses how to use Azure Database for MySQL flexible server slow query logs, the Log Analytics tool, and workbooks templates to visualize Query Performance Insight for Azure Database for MySQL flexible server.

In this tutorial, you'll learn how to:

• Configure slow query logs by using the Azure portal or the Azure CLI
• Set up diagnostics
• View slow query logs by using Log Analytics
• View slow query logs by using workbooks

Prerequisites

• Create an Azure Database for MySQL flexible server instance.
• Create a Log Analytics workspace.

Configure slow query logs by using the Azure portal

1. Sign in to the Azure portal.

2. Select your Azure Database for MySQL flexible server instance.

3. On the left pane, under Settings, select Server parameters.

   

4. For the slow_query_log parameter, select ON.

   

5. For the other parameters, such as long_query_time and log_slow_admin_statements, refer to the slow query logs documentation.

   

6. Select Save.

   

You can return to the list of logs by closing the Server parameters page.

Configure slow query logs by using the Azure CLI

Alternatively, you can enable and configure slow query logs for your Azure Database for MySQL flexible server instance from the Azure CLI by running the following command:

Important

To ensure that your Azure Database for MySQL flexible server instance's performance is not heavily affected, we recommend that you log only the event types and users that are required for your auditing purposes.

• Enable slow query logs.

az mysql flexible-server parameter set \
--name slow_query_log \
--resource-group myresourcegroup \
--server-name mydemoserver \
--value ON

• Set long_query_time time to 10 seconds. This setting will log all queries that execute for more than 10 seconds. Adjust this threshold based on your definition for slow queries.

az mysql server configuration set \
--name long_query_time \
--resource-group myresourcegroup \
--server mydemoserver \
--value 10

Set up diagnostics

Slow query logs are integrated with Azure Monitor diagnostic settings to allow you to pipe your logs to any of three data sinks:

• A Log Analytics workspace
• An event hub
• A storage account

Note

You should create your data sinks before you configure the diagnostics settings. You can access the slow query logs in the data sinks you've configured. It can take up to 10 minutes for the logs to appear.

1. On the left pane, under Monitoring, select Diagnostic settings.

2. On the Diagnostics settings pane, select Add diagnostic setting.

   

3. In the Name box, enter a name for the diagnostics setting.

4. Specify which destinations (Log Analytics workspace, an event hub, or a storage account) to send the slow query logs to by selecting their corresponding checkboxes.

   Note

   For this tutorial, you'll send the slow query logs to a Log Analytics workspace.

5. Under Log, for the log type, select the MySqlSlowLogs checkbox.

   

6. After you've configured the data sinks to pipe the slow query logs to, select Save.

   

View query insights by using Log Analytics

1. In Log Analytics, on the left pane, under Monitoring, select Logs.

2. Close the Queries window that opens.

   

3. In the query window, you can write the query to be executed. To find queries longer than 10 seconds on a particular server, we've used the following code:

   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
   

4. Select the Time range, and then run the query. The results are displayed in the following image:

   

View query insights by using workbooks

1. In the Azure portal, on the left pane, under Monitoring for your Azure Database for MySQL flexible server instance, select Workbooks.

2. Select the Query Performance Insight template.

   

In the workbook, you can view the following visualizations:

• Query Load
• Total Active Connections
• Slow Query Trend (>10 Sec Query Time)
• Slow Query Details
• List top 5 longest queries
• Summarize slow queries by minimum, maximum, average, and standard deviation query time

Note

• To view resource utilization, you can use the Overview template.
• You can also edit these templates and customize them according to your requirements. For more information, see Azure Workbooks.
• For a quick view, you can also pin the workbooks or Log Analytics query to your Dashboard. For more information, see Create a dashboard in the Azure portal.

In Query Performance Insight, two metrics that can help you find potential bottlenecks are duration and execution count. Long-running queries have the greatest potential for locking resources longer, blocking other users, and limiting scalability.

In some cases, a high execution count can lead to more network round trips. Round trips affect performance. They're subject to network latency and downstream server latency. So execution count can help to find frequently executed ("chatty") queries. These queries are the best candidates for optimization.

Next steps

• Learn more about Azure Monitor workbooks and their rich visualization options.
• Learn more about slow query logs.