Why MySQL Query Cache Slowed My Production Database and How We Fixed It

Background

Business users reported intermittent database latency while slow‑query logs showed many statements.

Investigation Steps

Checked monitoring charts – CPU, I/O, network, memory were normal.

Examined processlist – no long‑running connections or transactions.

Confirmed no triggers, stored procedures or scheduled jobs.

Observed bursts of slow queries at random timestamps, all sharing the same timestamp.

Most of the burst queries were DML (INSERT, UPDATE) statements.

Analysis

Initial hypothesis pointed to lock wait due to REPEATABLE READ isolation, but testing showed no lock contention. Using mysqldumpsslow on ~300 slow queries revealed only ~10 % were DML; the majority were SELECTs. EXPLAIN showed most SELECTs were const, indicating no obvious inefficiency.

Approximately 70 % of the SELECTs accessed the same tables that were being modified by DML, and their execution time was consistently 2.78‑2.80 s.

Two suspicions arose:

The DML on those tables might be blocking many reads.

Large query cache size could be causing contention.

MySQL 5.5 documentation shows query_cache_type defaults to ON but query_cache_size defaults to 0, effectively disabling it. In this environment the size was set to 256 M, enabling the cache.

Because the cache was large, each DML caused the cache to invalidate entries for the affected tables, forcing subsequent SELECTs to rebuild cache entries while other DMLs were still running, leading to mutex contention and long query times.

Evidence

Qcache status from the production server showed about 120 million cache inserts but only ~8.7 million hits, confirming poor cache effectiveness.

Resolution

After discussing with operations, the query cache was disabled during a maintenance window. Subsequent monitoring showed the latency disappeared and slow‑query logs stopped generating the burst entries.

Takeaway

In environments with frequent DML, keeping the MySQL query cache enabled can degrade performance; disabling it is advisable unless the workload is almost read‑only.