Azure Database for MySQL - Checklist for Performance

Introduction:

Do you want to know which checklist needs to be followed when you are working with Azure Database for MySQL? Please follow the below-given information to pro-actively and re-actively make your server better. If you are facing Performance problems with Azure Database for MySQL, these can often be resolved by doing basic troubleshooting and by setting best practices in advance.

Optimize connection between the application and Azure Database for MySQL:

• Physical Proximity: You can significantly increase the application throughput by creating the application server and database service in the same region. Make sure you deploy an application and the database in the same region. A quick check before starting any performance benchmarking run is to determine the network latency between the client and database using a simple SELECT 1 query. We have seen customers report significant improvement in throughput when the client and Azure Database for MySQL is in the same region.
• Accelerated Networking: Use accelerated networking for the application server/client machine, wherever it is applicable. Accelerated Networking enables single root I/O virtualization (SR-IOV) to a VM, greatly improving its networking performance. This high-performance path bypasses the host from the datapath, reducing latency, jitter, and CPU utilization, for use with the most demanding network workloads on supported VM types. For more information please refer to create a virtual machine with accelerated networking.
  
• Connecting Efficiently: Establishing a new connection is always an expensive and time-consuming task, reduce the number of attempts to create new connections by implementing the following approaches.
  
  • Connection Pooling: Is a mechanism that manages the creation and allocation of database connections. When a program/application requests a database connection, it prioritizes the allocation of existing idle database connections rather than creating a new one. For connection pooling, you can leverage ProxySQL, an open source, high performance proxy which provide built-in connection pooling and further allows you to scale out and load balance your workload to multiple read replicas as required on demand with any changes in application code. Alternatively, you can also leverage Heimdall Data Proxy, a vendor-neutral proprietary proxy solution. It supports query caching and read/write split with replication lag detection. You can also refer to how to accelerate MySQL Performance with the Heimdall proxy.  
  • Persistent or Long-Lived Connection: If your application has short transactions/queries typically with execution time < 5-10 ms then replace short connections with persistent connections. Replace short connections with persistent connections requires only minor changes to the code, but it has a major effect in terms of improving performance in many typical application scenarios. By using this approach you can avoid opening a new request for each connection instead reuse the existing connection to send more requests. Please make sure to set the timeout or close connection when it is done.

Basic Monitoring - What, How & When:

Keep a track of the resource limit by using Azure Monitor, by setting alerts on Metrics and by using an Azure Advisor. Please find below the common performance scenarios and their solutions: 

• CPU usage consistently hitting upper limits then increase the number of cores. Also, find out resource-intensive queries using Query Performance Insights and take corrective action. Use Query Store, Query Performance Recommendation to identify queries with the longest duration, most executed. Explore opportunities to optimize.
• Reaching maximum IOPS then increase storage provisioning to align with IOPS requirements. IOPS are dependent on storage (3 IOPS per GB). When storage hits the upper limit the server will be marked as read-only. You can configure auto grow to increase storage by 5% if you are close to limit. You can also find out costly queries using Query Performance Insights and take corrective action by implementing an Indexing strategy. Use Query Store, Performance Recommendation to identify queries with the longest duration, most executed and explore the opportunities to optimize it.
• When there is a low cache hit ratio issue, increase memory by moving to the higher SKU of the same tier or move to memory-optimized SKU. Review your query plan and leverage the Query Performance Insights. Use Query Store, Performance Recommendation to identify queries with the longest duration, most executed. Explore opportunities to optimize.
  
• When CPU & Memory is a constraint for high read-heavy workloads then Add a read replica. You can increase the performance by deploying read replicas and isolating read and write workloads. This brings your applications closer to the users around the world by deploying read replicas to other Azure regions. Add a read replica is ideal for read-heavy workloads including BI reporting, social media, web apps, and analytics. For MySQL, you can load balance read replicas using ProxySQL and also scale your MySQL workload running on Kubernetes with read replicas and ProxySQL.
• When failing to connect to the database due to no availability of the connection then use the recommendations explain above in connection tuning section and/or move to higher SKU.

To drill a little more in MySQL Performance checklist, I request you to please refer Azure Database for MySQL Performance Troubleshooting Basic, this will help you to understand the cause of the performance bottleneck, how to do basic troubleshooting and what options are available to fix it. 

If you are still having a performance issue and need assistance, you have the following options:

• Open a support incident with Microsoft support from azure portal.
• Post a question on our MSDN forums for MySQL
  
• Send a tweet to @AzureDBforMySQL
  

Enjoy and Happy Learning!