MySQL Performance Tuning

This page is original PmaControl documentation. It keeps a practical index structure for MySQL optimization work, without copying third-party content or depending on an external product.

Objective

MySQL tuning reduces latency, stabilizes throughput, and avoids repeated saturation. The work must start from real measurements rather than generic values copied from one server to another.

Recommended Work Order

1. Capture the real workload: QPS, latency, errors, connections, disk I/O, memory, and CPU.
2. Identify dominant queries with performance_schema, the slow log, or a digest tool.
3. Check the schema: missing indexes, redundant indexes, oversized types, and tables without a primary key.
4. Tune InnoDB: buffer pool, redo log, flush, I/O threads, and durability strategy.
5. Review server limits: connections, open files, table caches, and temporary table sizes.
6. Test during a controlled window, with a documented rollback.

Useful Commands

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Key Point

Good MySQL tuning is incremental. A variable changed without before/after measurement becomes operational debt.

Build a comparable baseline

Record the exact server version, host resources, dataset size, application release, traffic mix and observation window. Separate client response time from SQL execution time: connection-pool queues, network round trips and application work can dominate a request even when individual queries are fast. Use the same workload and concurrency before and after a change.

SELECT VERSION() AS server_version, @@version_comment AS distribution;
SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Uptime', 'Questions', 'Threads_connected', 'Threads_running',
  'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
  'Innodb_log_waits', 'Created_tmp_tables', 'Created_tmp_disk_tables'
);

Take two snapshots over a representative interval. Questions is cumulative: client statement rate = ΔQuestions / elapsed seconds, not the raw counter. A restart or counter reset invalidates the interval. Record p50, p95 and p99 request latency alongside completed work and errors; a lower average alone can hide a worse tail.

Find the queries that consume the budget

SELECT SCHEMA_NAME, LEFT(DIGEST_TEXT, 140) AS digest,
       COUNT_STAR AS executions,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_seconds,
       ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS average_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

This MySQL Performance Schema query ranks accumulated execution time, not a recent-window p95. Timers are expressed in picoseconds. Check instrumentation and reset history; an empty result does not prove an idle server. Frequent short queries can cost more overall than one slow query. Compare rows examined with rows returned, then inspect representative parameters with EXPLAIN FORMAT=JSON. EXPLAIN ANALYZE executes the statement: use a controlled environment and a supported version.

Match the intervention to the bottleneck

Validate one change at a time

Write down the hypothesis, previous setting or schema, expected benefit and rollback trigger. Warm caches consistently, repeat representative trials, and retain raw results. Compare latency, throughput, errors, CPU, memory, I/O and replica lag together. Reject a faster query if correctness, durability or other important workloads regress. Recheck during peak traffic and a full business cycle before keeping the change.

Continue the investigation

• Health checks establish availability and capacity.
• Schema checks and performance parameters cover targeted interventions.

Official references

• MySQL Performance Schema
• MySQL EXPLAIN