Checklist for troubleshooting and improving MySQL database performance

[Guest Jim Toland]

Introduction

 

 

Database performance is a hot topic among database users and practitioners. Users are always looking for the best performance, preferably out of the box, with no need for additional tuning, and at the most affordable price point. While Azure Database for MySQL is already tuned for the best perfromance out of the box, workloads vary. As a result, specific workloads may require some user intervention to boost and get the most out of a database server's performance. It's always a good idea to benchmark a MySQL server using sysbench to assess the capabilities of the server SKU it is uses.

 

 

 

This blog post provides a checklist to help improve MySQL database performance by troubleshooting and resolving issues that can commonly occur.

 

 

 

Checklist

 

 

1. Database performance is limited by physics. In the case of server performance, the three main hardware resources: CPU, Memory, and IOPS. Test if your workload is maxing out one of these three resources. How? Check this blog post to troubleshoot basic MySQL issues.

 

• Is it high CPU utilization? Troubleshoot high CPU utilization in Azure Database for MySQL from here. Keep in mind that Business Critical service tier is ideal for Tier 1 production workloads which require low latency, high concurrency, fast failover, and high scalability.
  
• Is it high memory utilization? Monitor the current memory usage and troubleshoot the causes of high memory utilization and then consider switching to the Business Critical Tier which offers generously memory sized SKUs for Tier-1 workloads.
  
• Is it high IOPS utilization? Consider switching to auto-scale IOPS which allow you to enjoy worry free IO management in Azure Database for MySQL - Flexible Server because the server scales IOPs up or down automatically depending on workload needs. Otherwise, consider provisioning additional IOPS to accommodate for the increased workload need.
  

 

2. Understand your workload split between reads and writes. Do you happen to have a read intensive workload? You have a few alternatives to improve performance:

 

• Consider using a thread pool which allows you to support higher connections, extract more throughput, and overcommit the number of vCores available on the server to maximize performance, assuming the server has enough memory.
  
• Consider scaling your server horizontally using read replicas. Now you can load balance read replicas using ProxySQL in Azure Database for MySQL.
  
• Consider dedicated caching tier such as Azure Cache for Redis.
  

 

3. Database version matters. Cosnider testing your workload on both supported versions of Azure Database for MySQL. It is known that different workloads run differently on v5.7 and v8.0. Learn more about Supported MySQL versions from here.

 

 

 

4. Monitor Azure Database for MySQL flexible server by using Azure Monitor workbooks or using Percona Monitoring and Management (PMM) and benefit from the customizable dashboards and real-time alerting.

 

 

 

5. Troubleshoot at the query level. Is it a subset of queries which is running slow? In that case, you have aaew alternatives to improve your database performance.

 

• Optimally tuning your workload on Azure Database for MySQL
  
• Enable slow query logs to find queries that take a long time to execute and are therefore candidates for optimization
  
• Consider ANALYZE and OPTIMIZE all database tables frequently. Check ANALYZE TABLE and Optimizing Storage Layout for InnoDB Tables.
  
• Enable log_queries_not_using_indexes: This parameter helps you determine missing indexes, this will allow you determine missing indexes. As you all know, indexes enhance data access speed, this is a critical step for tuned database performance.
  
• Troubleshoot query performance in Azure Database for MySQL using logging and profiling.
  
• Profile query performance in Azure Database for MySQL using EXPLAIN
  

 

6. Tune server parameters to better server your workload needs. This is a complex topic and may require pages to discuss all the aspects of it. But in short, the value of some parameters could potentially change your server performance significantly. To accommodate this, Azure Database for MySQL allows you to configure server parameters. Here are the top server parameters which you can consider adjusting while troubleshooting performance related issues:

 

• innodb_buffer_pool_size: The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. If you have large chunks of data, increase this value.
  
• innodb_file_per_table: By default, InnoDB tables are stored in the system tablespace. As an alternative, you can store each InnoDB table in its own data file. This feature is called “file-per-table tablespaces” because each table has its own tablespace data file (.ibd file). Learn more from here.
  
• log_bin: The server logs all statements that change data to the binary log, which is used for replication. If you don't have replication enabled, make sure you have this parameter turned OFF.
  
• innodb_io_capacity – This variable sets the rate for input/output from your storage device. In short, this sets an upper limit on I/O activity performed by InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer. You can adjust this value to better match your hardware
  
• slow_query_log: Turning slow_query \_log off will save data collection overhead which contributes to better performance. If you don't need slow query log, turn it off and enable it only when needed.
  
• log_queries_not_using_indexes: This parameter helps you determine missing indexes. While it's awesome to learn about missing indexes, keeping this parameter enabled all the time will drop query performance significantly. The rule of thumb here is to use it when you need it.
  

 

In addition to the above, consider reviewing and applying the following best practices for optimal performance of your Azure Database for MySQL flexible server:

 

• Physical proximity: Make sure you deploy an application and the database in the same availability zone and the same azure region to reduce network latencies to the minimum.
  
• Connect efficiently with connection pooling: Establishing a new connection is always an expensive and time-consuming task. When an application requests a database connection, it prioritizes the allocation of existing idle database connections rather than creating a new one. Consider Using ProxySQL, which provides built-in connection pooling and load balance your workload to multiple read-replicas as required on demand with any changes in application code.
  
• Partitioning: When your production workload uses extremely large tables, partitioning is a great method to improve database performance and ease maintenance. Partitioning makes it easier to manage large tables, this approach allows you to add and drop partitions to manage large tables effectively. Partitioning can also help scale the engine by alleviating internal structure contention such as internal locks per table or per index (e.g., consider the btr_search_latch in InnoDB). Read more here.
  
• Write scaling and sharding: This option should be considered for larger database sizes, find best practices here.
  

 

 

 

Summary

 

 

We hope this checklist helps you to mitigate your existing performance bottlenecks and improves the overall performance of your database server and application workload!

 

 

If you have any questions, feedback, or suggestions on what else you’d like us to write about, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!

 

Continue reading...