MySQL performance issues rarely happen overnight. Queries that used to execute in milliseconds gradually slow down, CPU usage quietly climbs, and replication lag builds up unnoticed. By the time alerts fire, the problem has usually been festering for weeks. Spotting the early warning signs is what prevents a minor issue from turning into a full outage.
1. Slow Query Logs Are Going Unread
When the same problematic query patterns reappear in your slow query log week after week, the real issue isn't the queries — it's the lack of a structured process to act on them. Percona Toolkit's pt-query-digest can consolidate slow query entries into ranked summaries that show which query types are consuming the most cumulative time. Common culprits include absent composite indexes, type mismatches in WHERE clauses, and functions wrapped around indexed columns that block the optimizer from using those indexes.
2. InnoDB Buffer Pool Hit Rate Drops Below 99%
The InnoDB buffer pool is MySQL's most consequential memory component. When it's sized correctly, frequently accessed data stays in RAM; when it's not, every cache miss forces a disk read — the quickest route to throughput collapse. A hit rate under 99% means disk reads are happening regularly. The fix isn't always adding more RAM — it could mean identifying which tables or indexes are pushing hot pages out, adjusting innodb_buffer_pool_size, or enabling multiple buffer pool instances to ease latch contention.
3. Replication Lag Keeps Climbing With No Obvious Reason
Replication lag has several distinct causes, and each demands a different remedy. Broadly attributing it to a "slow replica" wastes time. The most frequent causes include a single-threaded replica applier serializing writes from the source — resolved by enabling replica_parallel_workers — long-running transactions on the source that the replica must replay one at a time, missing indexes on the replica causing full table scans during row-based replication, network saturation between source and replica (addressable via binary log compression in MySQL 8.0.20+), and replica storage that simply can't sustain the apply rate.
4. Table-Level Locks Appearing in a High-Concurrency Setup
InnoDB is built around row-level locking, so table-level locks signal that something has gone wrong upstream. Possible triggers include DDL statements run without ALGORITHM=INPLACE, unclosed LOCK TABLES calls left in application code, or queries scanning entire tables due to missing indexes, which forces an implicit table lock. Persistent lock waits point to architectural problems, and the fix may involve adding indexes, restructuring transaction order, or shifting schema changes to online tools like pt-online-schema-change or gh-ost.
5. Thread Count and Mutex Waits Increase Under Normal Load
When thread counts rise without a corresponding spike in query volume, it's a sign of resource contention — not a capacity problem. Threads are waiting on locks, buffer pool latches, or I/O instead of doing actual work. The Performance Schema's wait event data can zero in on the exact bottleneck, such as buffer pool mutex contention (resolved by raising innodb_buffer_pool_instances) or slow storage I/O. Applications without a connection pool also suffer at scale from the overhead of spawning and destroying threads per request — configuring thread_cache_size or adding a proxy layer like ProxySQL addresses this.
6. ibdata1 or Undo Tablespace Growing Without Bound
When storage consumption keeps rising even though the actual data volume is stable, a bloated shared system tablespace (ibdata1) or swelling undo tablespace is often responsible. This fragments InnoDB's write path and degrades performance. The most common trigger is long-running transactions holding open a read view, which prevents InnoDB's purge thread from reclaiming undo records. A history list length consistently above 10,000 is a sign the purge thread is behind. The lasting fix is migrating to separate, truncatable undo tablespaces and addressing the long-running transactions causing the backlog.
7. The Query Optimizer Switches Execution Plans Unpredictably
When EXPLAIN output for the same query produces different plans across executions — sometimes using an index, sometimes not — optimizer statistics are likely stale or skewed. This leads to intermittent latency spikes that are hard to reliably reproduce. The fix involves running ANALYZE TABLE to refresh statistics, raising innodb_stats_persistent_sample_pages well above the default of 20 for large tables, adding histograms for non-indexed columns in WHERE clauses, and using optimizer hints to enforce a stable index for critical queries while the data distribution is examined.
The Takeaway
All seven of these problems — slow query neglect, low buffer pool hit rates, replication lag, lock contention, thread pile-ups, tablespace bloat, and unpredictable execution plans — have concrete, targeted solutions. But in real production systems, they rarely appear in isolation. Fixing one without understanding how the others interact leads to repetitive, inefficient troubleshooting. Effective MySQL tuning demands a holistic view: workload patterns, index design, memory settings, storage behavior, replication topology, and connection management all considered together.
Sign in to leave a comment.