MySQL Performance Tuning: Diagnose Before Changing Settings
A task-focused diagnostic workflow for MySQL 8.0 and 8.4, from the slow operation to a reviewed change request.
Written By ROMAN AGABEKOV | JUL 05, 2023
Last update | SEP 10, 2026
Start MySQL performance tuning with the slow operation. Identify its queries, then correlate their behavior with connections, memory, temporary work, and storage activity before choosing settings to change.
This read-only workflow covers MySQL 8.0 and 8.4 with InnoDB, not MariaDB. Use an authorized account; managed services may restrict diagnostics. The queries do not change configuration or application data, but consume resources. Run relevant checks selectively, not in a polling loop.
This read-only workflow covers MySQL 8.0 and 8.4 with InnoDB, not MariaDB. Use an authorized account; managed services may restrict diagnostics. The queries do not change configuration or application data, but consume resources. Run relevant checks selectively, not in a polling loop.
- Establish the Workload and Baseline
- Find Statement Patterns Contributing to Work
- Review the Query Plan and Index Use
- Separate Connections from Lock Contention
- Check Memory and InnoDB Read Activity
- Investigate Temporary Work and Storage Delays
- Turn the Evidence into One Testable Change Request
- Verify the Outcome Against the Same Workload
1. Establish the Workload and Baseline
Record the slow operation and incident window. From existing monitoring, capture response time, request volume, errors, CPU, memory, and storage latency for that period.
Confirm the server version and observation time:
Confirm the server version and observation time:
SELECT VERSION(), CURRENT_TIMESTAMP();
SHOW GLOBAL STATUS LIKE 'Uptime';Uptime is server uptime in seconds, not a required waiting period. Investigate incidents immediately using a representative workload window, without an arbitrary 24-hour minimum. Record the timestamp's time zone.
2. Find Statement Patterns Contributing to Work
With existing digest collection and SELECT access to this Performance Schema table, inspect:
SELECT SCHEMA_NAME,
DIGEST,
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT,
SUM_ROWS_EXAMINED,
SUM_ROWS_SENT,
SUM_CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;This ranks statement patterns by accumulated time. COUNT_STAR counts executions; SUM_TIMER_WAIT is instrumented time in picoseconds, not CPU usage. Frequent short queries can contribute substantial total time.
Totals cover the collection period, not automatically your incident window. Confirm relevance with existing time-windowed monitoring. Collection settings and resets affect coverage; empty results or zero timing do not prove queries are inexpensive. Do not reset summaries or enable instrumentation.
DIGEST_TEXT retains identifiers and query structure: keep it private. LIMIT 10 bounds output, not the work of examining and sorting rows.
Totals cover the collection period, not automatically your incident window. Confirm relevance with existing time-windowed monitoring. Collection settings and resets affect coverage; empty results or zero timing do not prove queries are inexpensive. Do not reset summaries or enable instrumentation.
DIGEST_TEXT retains identifiers and query structure: keep it private. LIMIT 10 bounds output, not the work of examining and sorting rows.
3. Review the Query Plan and Index Use
Review an existing plan with the query owner: access method, index, join order, estimated rows, and filtering. Many examined rows relative to returned rows warrant investigation, not an automatic index change.
Ordinary EXPLAIN describes the optimizer's plan; EXPLAIN ANALYZE executes the statement. Obtain new plans through your approved diagnostic procedure, particularly for expensive statements or stored functions.
Query rewrites and index changes need correctness tests and DBA review. Consult the MySQL performance parameters reference when findings point to a setting.
Ordinary EXPLAIN describes the optimizer's plan; EXPLAIN ANALYZE executes the statement. Obtain new plans through your approved diagnostic procedure, particularly for expensive statements or stored functions.
Query rewrites and index changes need correctness tests and DBA review. Consult the MySQL performance parameters reference when findings point to a setting.
4. Separate Connections from Lock Contention
Inspect existing connection indicators:
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS
WHERE Variable_name IN (
'Threads_connected',
'Threads_running',
'Max_used_connections',
'Connection_errors_max_connections'
);Threads_connected counts open connections; Threads_running counts non-sleeping threads, including waiters, not CPU activity. Max_used_connections is a historical peak. Connection_errors_max_connections counts refusals caused by reaching the limit.
Correlate these with connection-pool behavior; a high peak alone does not justify increasing the limit. For blocked transactions, the MySQL deadlock detection guide separates current waits from the latest deadlock.
Correlate these with connection-pool behavior; a high peak alone does not justify increasing the limit. For blocked transactions, the MySQL deadlock detection guide separates current waits from the latest deadlock.
5. Check Memory and InnoDB Read Activity
Inspect the configured buffer pool and read counters:
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS
WHERE Variable_name IN (
'Innodb_buffer_pool_read_requests',
'Innodb_buffer_pool_reads'
);The buffer pool caches InnoDB data and index pages. These counters distinguish logical read requests from reads requiring disk access.
Compare read activity with latency, host memory, and storage measurements. Nonzero disk reads do not prove the pool is undersized. Its size is not total MySQL memory use; avoid universal host-RAM percentages.
MySQL 8.0 and 8.4 do not have the old query cache. query_cache_size, query_cache_type, and query-cache fragmentation are not tuning targets for these versions.
Compare read activity with latency, host memory, and storage measurements. Nonzero disk reads do not prove the pool is undersized. Its size is not total MySQL memory use; avoid universal host-RAM percentages.
MySQL 8.0 and 8.4 do not have the old query cache. query_cache_size, query_cache_type, and query-cache fragmentation are not tuning targets for these versions.
6. Investigate Temporary Work and Storage Delays
Inspect these existing status counters:
SHOW GLOBAL STATUS
WHERE Variable_name IN (
'Created_tmp_tables',
'Created_tmp_disk_tables',
'Sort_merge_passes',
'Innodb_log_waits'
);Correlate temporary-table and sort activity with step 2's query patterns. These counters are not bytes or memory-setting prescriptions. Created_tmp_disk_tables excludes memory-mapped temporary files where used, so it does not cover all temporary disk activity.
Innodb_log_waits counts waits for an undersized log buffer to be flushed, not general redo-capacity pressure. Correlate with existing storage-latency and write-activity charts.
Counters are accumulated observations, not current rates. Account for restarts and resets. Do not reset counters, move log files, or change durability settings.
Innodb_log_waits counts waits for an undersized log buffer to be flushed, not general redo-capacity pressure. Correlate with existing storage-latency and write-activity charts.
Counters are accumulated observations, not current rates. Account for restarts and resets. Do not reset counters, move log files, or change durability settings.
7. Turn the Evidence into One Testable Change Request
Bring the DBA the operation, window, digest, plan findings, matching resource evidence, and remaining unknowns. Tool recommendations are hypotheses, not approvals.
Configuration changes, generated recommendations, calculations, query rewrites, and indexes need human DBA review. Agree on tests, success measures, correctness checks, and rollback first. This workflow does not edit configuration, restart MySQL, or apply generated SQL.
Configuration changes, generated recommendations, calculations, query rewrites, and indexes need human DBA review. Agree on tests, success measures, correctness checks, and rollback first. This workflow does not edit configuration, restart MySQL, or apply generated SQL.
8. Verify the Outcome Against the Same Workload
After an approved change, compare equivalent workloads: response time, throughput, errors, resources, and replication health where relevant. Lighter traffic alone can explain faster responses.
Keep both observation windows. Report absent improvement or regressions to the change owner before adding adjustments.
For Releem resources and product options, visit the MySQL Optimization Center.
Keep both observation windows. Report absent improvement or regressions to the change owner before adding adjustments.
For Releem resources and product options, visit the MySQL Optimization Center.
Article by
- Founder & CEORoman Agabekov has 17 years of experience managing and optimizing MySQL and MariaDB in high-load environments. He founded Releem to automate routine database management tasks like performance monitoring, tuning, and query optimization. His articles share practical insights to help others maintain and improve their databases.
Turn MySQL Pain Into Performance.
Get expert insights, real benchmarks, and practical tuning tips — straight from the team behind Releem.
Monthly newsletter. Zero fluff. All signal.