Managed Service for MySQL® performance analysis and tuning
The following issues normally impact Managed Service for MySQL® cluster performance:
- High CPU load, frequent disk reads and writes, and network requests.
- Inefficient query execution in MySQL®.
- Locks.
Monitoring tools for MySQL® to troubleshoot performance issues in Managed Service for MySQL® clusters and special MySQL® queries will help detect these problems.
Getting started
- Select databases to troubleshoot.
- Enable statistics collection.
- Create a MySQL® user with the
PROCESS
privilege for these databases. Troubleshooting queries must run under this user's account.
Diagnosing resource shortages
Resource shortage is a likely cause of cluster performance degradation. Resource shortages become evident from cluster monitoring charts (CPU, disk I/O operations, network connections). If a continuously increasing resource utilization plot has leveled out, resource usage has reached its limit or exceeded the guaranteed service level.
Special queries can help determine the reasons for elevated resource utilization:
-
To estimate Disk IO utilization by different MySQL® threads, execute the query:
SELECT t.name AS thread_name, t.processlist_user AS user, t.processlist_info AS query, t.processlist_time AS time, io.bytes AS bytes FROM performance_schema.threads t JOIN ( SELECT thread_id, sum(number_of_bytes) AS bytes FROM performance_schema.events_waits_history_long WHERE object_type='FILE' GROUP BY thread_id) io ON t.thread_id = io.thread_id ORDER BY io.bytes DESC
The query returns a list of MySQL® file threads, ranked in descending order by the amount of memory used. Threads that serve replication and the InnoDB buffer
for caching tables and indexes are usually at the top of the list. -
To estimate network resource utilization by different MySQL® threads, execute the query:
SELECT t.name AS thread_name, t.processlist_user AS user, t.processlist_info AS query, t.processlist_time AS time, net.bytes/t.processlist_time AS avg_bytes, net.bytes AS total_bytes FROM performance_schema.threads t JOIN ( SELECT thread_id, Sum(variable_value) bytes FROM performance_schema.status_by_thread WHERE variable_name IN ('Bytes_sent', 'Bytes_received') GROUP BY thread_id ) net ON t.thread_id = net.thread_id WHERE t.processlist_time IS NOT NULL ORDER BY net.bytes DESC
This query returns statistics from thread launch, so long-lived connections (such as replication) will be returned in the top rows.
Note the read and write operations that handle a large number of rows. These can also cause increased network load. For writes, changes in WAL will be copied to replicas, which will further increase network load.
-
It is not possible to track CPU utilization with individual queries against MySQL®, but inefficient queries (see below) can be identified.
Diagnosing inefficient query execution
To identify problematic queries against MySQL®, execute the query:
SELECT *
FROM sys.statement_analysis
LIMIT 10
The query returns the 10 longest running queries in the entire server history.
You should pay attention to queries with high ROWS_EXAMINED
, ROWS_SORTED
values, or the FULL_SCAN
flag.
To learn more about the information in the output, see the MySQL®
Detecting locks
Cluster performance may degrade because of locks obtained when there are multiple simultaneous attempts to access the same DB resource (table, row).
To troubleshoot, check lock queues in queries:
-
Table-level lock queue:
SELECT * FROM sys.schema_table_lock_waits
-
Individual row-level lock queue:
SELECT * FROM sys.innodb_lock_waits
Troubleshooting
You can try and optimize the problematic queries you have diagnosed. There are several ways to approach such optimization:
-
Analyze the query plan using the
EXPLAIN
command and use the query optimization techniques from the MySQL® documentation . -
Optimize InnoDB tables
to reduce disk workload.
If you can't optimize the identified problem queries or manage without them, you can raise the host class.