Why Do MySQL Queries Slow Down? Understanding Write/Read Bottlenecks

Background

Slow SQL execution is a frequent interview question and a critical performance concern for backend developers. In production environments, a sluggish query can indicate serious issues that need to be identified and mitigated.

Write Operations

During a write, MySQL (InnoDB) records changes in the redo log and updates the memory buffer. The redo log is written sequentially, which is fast, but when the circular redo log fills up under high concurrency, dirty pages accumulate.

When the redo log is full, InnoDB must flush dirty pages to disk, pausing further writes and causing query latency. Additionally, write‑side locks can block a transaction until the lock is released, or lead to deadlocks that require manual intervention.

To inspect current locks, run the statements shown in the diagram (e.g., querying information_schema.innodb_lock_waits and related tables). If a lock persists too long, you can release it with kill <thread_id>, where trx_mysql_thread_id identifies the thread.

Read Operations

Read‑side slowdowns are often caused by two main factors:

Missing indexes, which force a full table scan.

Dirty‑page flushing during reads when the buffer pool evicts a dirty page to make room for a needed page.

InnoDB keeps frequently accessed data and index pages in the buffer pool. When the pool reaches its limit, the least‑recently‑used pages are evicted; if the evicted page is dirty, it must be written to disk before reuse, adding latency.

Diagnosing with EXPLAIN

Use EXPLAIN <SQL> to view the execution plan. Important columns include:

type : access method (ALL, index, range, ref, eq_ref, const, system, NULL) – performance improves from left to right.

possible_keys : indexes that could be used.

key : index actually used.

rows : estimated rows examined.

Extra : additional actions such as Using index , Using where , Using temporary , Using filesort .

Slow Query Log

The slow query log records statements that exceed a configurable execution time. It is disabled by default and must be enabled manually.

Check the current status: SHOW VARIABLES LIKE 'slow_query_log'; Enable temporarily: SET GLOBAL slow_query_log = ON; Note: This setting is lost after a MySQL restart; to make it permanent, edit my.cnf.

Prevention and Optimization

To avoid or mitigate slow queries:

Ensure queries use appropriate indexes; regularly run EXPLAIN to verify.

Monitor and limit the dirty‑page ratio (keep it well below 75%).

Adjust innodb_io_capacity to match disk performance and control redo‑log flush speed.

Summary

Write‑side slowdowns arise from redo‑log saturation leading to dirty‑page flushing and from lock contention or deadlocks.

Read‑side slowdowns are typically due to missing indexes (full scans) or dirty‑page eviction during buffer‑pool replacement.

Use the slow query log and EXPLAIN to pinpoint problems, and tune InnoDB parameters to keep the system responsive.