How to optimize query cache in MariaDB or MySQL

Optimizing the query cache can reduce response time for repeated, identical SELECT statements in read-heavy workloads by serving results from memory instead of re-executing the query. For workloads that repeatedly fetch the same reference data or page fragments, a stable cache can also lower CPU utilization and smooth latency spikes.

When enabled, the cache stores complete result sets keyed by the exact SQL text and serves them until an invalidation event occurs. Invalidation is table-based, so any data change to a table can invalidate cached entries that reference it, which is why write-heavy tables often produce low hit rates and high cache churn.

The feature is not available in MySQL 8.0 and may be disabled or removed in some builds, so confirming support is the first gate. Oversizing the cache can increase mutex contention and trigger frequent pruning, so tuning should start conservative and be validated using Qcache status counters rather than guesswork.

1. Confirm that the running server supports the query cache.

   mysql> SHOW VARIABLES LIKE 'query_cache%';
   +------------------------------+----------+
   | Variable_name                | Value    |
   +------------------------------+----------+
   | query_cache_limit            | 1048576  |
   | query_cache_min_res_unit     | 4096     |
   | query_cache_size             | 67108864 |
   | query_cache_type             | ON       |
   | query_cache_wlock_invalidate | OFF      |
   +------------------------------+----------+
   5 rows in set (0.00 sec)

   No rows returned commonly indicates MySQL 8.0 or a server build with query cache removed.

2. Review query cache effectiveness counters.

   mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Qcache_hits','Qcache_inserts','Qcache_not_cached','Qcache_lowmem_prunes','Qcache_free_memory','Qcache_queries_in_cache','Qcache_free_blocks','Qcache_total_blocks');
   +-------------------------+---------+
   | Variable_name           | Value   |
   +-------------------------+---------+
   | Qcache_free_blocks      | 48      |
   | Qcache_free_memory      | 1048576 |
   | Qcache_hits             | 9812212 |
   | Qcache_inserts          | 312981  |
   | Qcache_lowmem_prunes    | 421     |
   | Qcache_not_cached       | 18212   |
   | Qcache_queries_in_cache | 1821    |
   | Qcache_total_blocks     | 3890    |
   +-------------------------+---------+
   8 rows in set (0.00 sec)

   Rising Qcache_lowmem_prunes indicates eviction pressure, while a high Qcache_free_blocks value relative to Qcache_total_blocks can indicate fragmentation.

3. Set conservative query cache size settings under the [mysqld] section of the server configuration.

   [mysqld]
   query_cache_type = ON
   query_cache_size = 64M
   query_cache_limit = 1M

   query_cache_type values: ON caches every cacheable statement, DEMAND caches only queries that include SQL_CACHE, and OFF disables caching; freeing the memory typically uses query_cache_size = 0.

   Oversized query caches can increase contention and hurt performance on busy write-heavy systems.

4. Restart the database service to apply the new query cache settings.

   $ sudo systemctl restart mariadb

   Replace the unit name with mysql on installations that use mysql.service.

5. Verify the active query cache settings.

   mysql> SHOW VARIABLES WHERE Variable_name IN ('query_cache_type','query_cache_size','query_cache_limit','query_cache_min_res_unit');
   +--------------------------+----------+
   | Variable_name            | Value    |
   +--------------------------+----------+
   | query_cache_limit        | 1048576  |
   | query_cache_min_res_unit | 4096     |
   | query_cache_size         | 67108864 |
   | query_cache_type         | ON       |
   +--------------------------+----------+
   4 rows in set (0.00 sec)

6. Re-check Qcache counters during representative load to confirm improvement.

   mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Qcache_hits','Qcache_inserts','Qcache_lowmem_prunes','Qcache_free_memory');
   +----------------------+---------+
   | Variable_name        | Value   |
   +----------------------+---------+
   | Qcache_free_memory   | 7340032 |
   | Qcache_hits          | 9823412 |
   | Qcache_inserts       | 313104  |
   | Qcache_lowmem_prunes | 421     |
   +----------------------+---------+
   4 rows in set (0.00 sec)

   Increasing Qcache_hits with stable Qcache_lowmem_prunes usually indicates useful caching, while continuously rising pruning with flat hits is a strong sign to switch to DEMAND or disable the cache.