Performance diagnostics for Azure Database for MySQL – Flexible Server

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Database monitoring and troubleshooting is critical for any data driven application to detect issues faster and mitigate them as quickly as possible. The most difficult area to troubleshoot is being able to identify and address MySQL database performance issues. To help database administrators and developers to identify performance issues bottlenecks, in the Azure portal, there is a new Performance diagnostics feature designed to help manage Azure Database for MySQL servers. This feature takes advantage of MySQL’s information_schema and performance_schema databases to provide a comprehensive view of the system's internal operations. The data from performance schema helps to diagnose performance issues and identify bottlenecks.


  • To access the functionality, in the Azure portal, navigate to an Azure Database for MySQL flexible server, and then, under Help, select Performance diagnostics:

View performance diagnostics in left navigation menu under Help.View performance diagnostics in left navigation menu under Help.


Identifying performance issues

The performance diagnostics feature includes three tabs: Processes and transactions, Expensive queries, and Indexes. Additional information about these pages is provided in the following sections.


The Processes and transactions tab

This tab shows you queries that can help you identify long running processes and transactions. You can get this information from the following two tables:

  • INFORMATION_SCHEMA.PROCESSLIST table: You can get the server’s process list from this table.
  • INFORMATION_SCHEMA.INNODB_TRX: You can get InnoDB’s transaction metadata from this table.

An accurate description of the connection and transaction state requires using information from both sources, for example, because the process list doesn’t indicate whether or not there’s an open transaction associated with any the session, while the transaction metadata doesn’t show session state and time spent in that state.

Identify long running processes and open transactionsIdentify long running processes and open transactions

To mitigate or debug further, consider the following recommendations:

  • Avoid large or long-running transactions by breaking them into smaller transactions.
  • Create an alert for “Host CPU Percent” so that you get notifications if the system exceeds any of the specified thresholds.
  • Use Query Performance Insights to identify and then optimize any problematic or slowly running queries.


The Expensive queries tab

This tab shows all the expensive queries that are running on your MySQL flexible server instance. There could be a lot of queries from your application being executed on the MySQL server. Therefore, you can always create a digest so that the most relevant information for the expensive queries with the highest execution time.

View Expensive queries for MySQL flexible serverView Expensive queries for MySQL flexible server

To mitigate or debug further, consider the following recommendations:


The Indexes tab

This tab shows queries that can help you identify unused indexes. When unused indexes are present, you may see symptoms of high I/O usage on your server metrics. These unused indexes consume storage and can have a negative impact on performance as they consume disk space, cache, and slow write operations (INSERT / DELETE / UPDATE). As table data is modified, the indexes also need to be updated.

View indexes used for your queriesView indexes used for your queries

Remove unused indexes after verifying that the index is not being used. Be sure to avoid inadvertently removing an index that might be critical for a query that runs only quarterly or annually. Also, be sure to consider that some indexes are used to enforce uniqueness or ordering.


Running queries in performance diagnostics

Based on your networking configuration, you can run the queries using the Azure cloud shell and/or any MySQL client tool.

  • For a MySQL flexible server with public access, select Connect to run the query in the portal using Azure cloud shell. You can also run the queries in Azure data studio, MySQL workbench, or MySQL command line tool.

Connect with Azure Cloud Shell to run performance diagnostic queriesConnect with Azure Cloud Shell to run performance diagnostic queries

  • For MySQL flexible servers with private access servers using VNET injections, Azure cloud shell is not supported.  Hence you would need to copy and run these queries using a MySQL command line tool. Remember to select ‘performance schema’ as your database before running the query.


Run your queries in Azure data studioRun your queries in Azure data studio



Using these queries, MySQL developers and DBAs can gather information to help pinpoint performance bottlenecks, optimize queries, and enhance the overall efficiency of their database applications. Regularly analyzing and interpreting the data from the performance schema contributes to proactive performance management and ensures that your MySQL database continues to function optimally.


If you have any suggestions for improving the performance diagnostics experience for MySQL flexible servers, please leave a comment below or email us at


To learn more about what's new with Flexible Server, see What's new in Azure Database for MySQL - Flexible Server. Also be sure tune in for all of our updates and announcements by following us on social media: YouTube | LinkedIn | Twitter.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.