If you are facing Performance problems with Azure Database for MySQL, these can often be resolved by doing basic troubleshooting steps to understand the cause of the bottleneck and then explore the options to fix it.
Server Level Troubleshooting
1- Resource Utilization: It's very important to start by making sure that you are not maxing out any of your resource limits, reaching high levels of resource utilization will cause a performance issue this can
be avoided by monitoring server's resource utilization on the Azure Portal.
In Azure Database for MySQL resource limits determined by the service tier that you are using and the number of vCores and Storage size that you provision all this information is documented here and here, you can navigate to your Azure Database for MySQL and use the Metrics blade to check if you are maxing out any of your resource limits.
2- Consider ANALYZE and OPTIMIZE all database tables frequently
Analyze: Performs a key distribution analysis and stores the distribution for the named table or tables, clears table statistics from the "INFORMATION_SCHEMA.INNODB_TABLESTATS" table and sets the "STATS_INITIALIZED" column to Uninitialized. Statistics are collected again the next time the table is accessed, this will help pushing a better execution plans for your queries if the data in the table was modified recently.
reference: ANALYZE TABLE
Optimize: Once your data reaches a stable size, or a growing table has increased by tens or some hundreds of megabytes, consider using the OPTIMIZE TABLE statement to reorganize the table and compact any wasted space. The reorganized tables require less disk I/O to perform full table scans. This is a straightforward technique that can improve performance when other techniques such as improving index usage or tuning application code are not practical.In short this will reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.
reference: Optimizing Storage Layout for InnoDB Tables
3- Make sure you are applying Application side best practices
a) Connection pooling: This significantly reduces connection latency by reusing existing connections and enables higher database throughput (transactions per second) on the server.
b) 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.
c) Application and Database should be in the same Data Center.
4- Tune server parameters to better serve your application workload
Azure Database for MySQL allows you to configure server parameters for the values that better serve your workload, check this documentation to learn how. Each application has best practices for those parameter, please review those in order to get the best out of the database server. Some of the parameters that you might need to check is:
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.
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 about it's advantages here.
The amount of memory allocated for caching query results. by default this is turned OFF, read more about this here.
The server logs all statements that change data to the binary log, which is used for replication, If you don't have replication enabled please make sure the you have this parameter turned OFF.
e- query_store_capture_mode= NONE
Query store capture can help you identify your top consumers and wit statistics but this also adds overhead in terms of performance as the background process will be be collecting data all the time.
f- slow_query_log= OFF
Turning slow query log off will save data collection overhead which contributes to better performance, if you don't need low query log turn off and you can enable it later if needed.
g- log_queries_not_using_indexes= OFF
This parameter helps you determine missing indexes, while its awesome to learn about missing indexes but keeping this parameter enabled all the time will drop query performance significantly, rule of thumb here is to use it when you need it.
Query Level Troubleshooting
Use Microsoft Intelligent Performance tools to explore queries that are considered top consumers and also learn about missing indexes along with recommendation and full analysis for your workload. Intelligent Performance for Azure Database for MySQL includes (QPI, Performance Recommendations, Slow query log):
1- Leverage Query Performance Insight (QPI)
Query Performance Insight helps you to quickly identify what your longest running queries are, how they change over time, and what waits are affecting them, this can help you determine problematic queries. Tutorial on how to use QPI can be found here.
2- Use Performance Recommendations feature
The Performance Recommendations feature analyzes your databases to create customized suggestions for improved performance. To produce the recommendations, the analysis looks at various database characteristics including schema. you can read more about this here.
Please consider also to read
Azure brings intelligence and high performance to Azure Database for MySQL
3- Enable Slow Query Log: Slow query log can be used to identify performance bottlenecks for troubleshooting, this will drill issue down into the query level to show how long each query took to execute and this will help to identify an action lan to resolve the issue, to enable this please view this documentation.
4- Query Store: The Query Store feature in Azure Database for MySQL provides a way to track query performance over time. Query Store simplifies performance troubleshooting by helping you quickly find the longest running and most resource-intensive queries. Query Store automatically captures a history of queries and runtime statistics, and it retains them for your review. It separates data by time windows so that you can see database usage patterns. Data for all users, databases, and queries is stored in the mysql schema database in the Azure Database for MySQL instance.
5- Use the EXPLAIN command
EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query. This can help understand the bottleneck in a specific query.
Please view the following tutorial on EXPLAIN to profile query performance in Azure Database for MySQL.
6- Use the SHOW PROFILE command
SHOW PROFILE and SHOW PROFILES statements display profiling information that indicates resource usage for statements executed during the course of the current session. Profiling is enabled per session. When a session ends, its profiling information is lost.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.