How to optimize query cache in MariaDB or legacy MySQL

Tuning the query cache can cut latency for repeated, identical SELECT statements in read-heavy workloads that keep reusing the same stable result sets. When it fits the workload, the server can return those results from memory instead of re-executing the statement, which reduces CPU work and smooths response time for hot lookups.

The cache matches on the exact SQL text and invalidates cached results when a referenced table changes, so it works best for read-mostly tables and predictable queries. Current MariaDB releases still expose the feature, while current MySQL releases do not, which makes a support check the first gate before any tuning.

The feature is removed in MySQL 8.0 and MySQL 8.4, and it was already deprecated late in the MySQL 5.7 line. On systems that still support it, keep the cache small, prefer DEMAND mode so only statements marked with SQL_CACHE use it, and watch Qcache counters for churn, pruning, and misses instead of assuming a larger cache will help.

1. Confirm that the running server still exposes query cache support before tuning it.

   $ mysql --table -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('have_query_cache','query_cache_type','query_cache_size');"
   +------------------+---------+
   | Variable_name    | Value   |
   +------------------+---------+
   | have_query_cache | YES     |
   | query_cache_size | 1048576 |
   | query_cache_type | OFF     |
   +------------------+---------+
   
   $ mysql --table -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('have_query_cache','query_cache_type','query_cache_size');"
   +------------------+-------+
   | Variable_name    | Value |
   +------------------+-------+
   | have_query_cache | NO    |
   +------------------+-------+

   Stop here on MySQL 8.0 or MySQL 8.4 because those releases removed the query cache; continue only on MariaDB or older MySQL builds that still return have_query_cache = YES and query-cache variables.

2. Baseline the current query cache settings and counters before changing them.

   $ mysql --table -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('query_cache_type','query_cache_size','query_cache_limit','query_cache_min_res_unit'); SHOW GLOBAL STATUS WHERE Variable_name IN ('Qcache_hits','Qcache_inserts','Qcache_not_cached','Qcache_lowmem_prunes','Qcache_queries_in_cache');"
   +--------------------------+---------+
   | Variable_name            | Value   |
   +--------------------------+---------+
   | query_cache_limit        | 1048576 |
   | query_cache_min_res_unit | 4096    |
   | query_cache_size         | 1048576 |
   | query_cache_type         | OFF     |
   +--------------------------+---------+
   +-------------------------+-------+
   | Variable_name           | Value |
   +-------------------------+-------+
   | Qcache_hits             | 0     |
   | Qcache_inserts          | 0     |
   | Qcache_lowmem_prunes    | 0     |
   | Qcache_not_cached       | 0     |
   | Qcache_queries_in_cache | 0     |
   +-------------------------+-------+

   Rising Qcache_not_cached or flat Qcache_hits under real load usually means the workload is a poor fit for query caching.

3. Add conservative query cache settings under [mysqld] in the server option file.

   [mysqld]
   query_cache_type = DEMAND
   query_cache_size = 32M
   query_cache_limit = 1M

   DEMAND caches only statements marked with SQL_CACHE, which avoids forcing every cacheable SELECT into the query cache.

   Keep the cache small because larger query caches increase prune cost and mutex contention on busy write-heavy systems.

   Related: How to locate and modify MySQL and MariaDB configuration files

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

   $ sudo systemctl restart mariadb

   Use the installed unit name such as mysql, mariadb, or mysqld.

   Related: How to manage MySQL or MariaDB service with systemctl in Linux

5. Verify the active query cache settings from a new client session.

   $ mysql --table -u root -p -e "SHOW VARIABLES WHERE Variable_name IN ('query_cache_type','query_cache_size','query_cache_limit');"
   +-------------------+----------+
   | Variable_name     | Value    |
   +-------------------+----------+
   | query_cache_limit | 1048576  |
   | query_cache_size  | 33554432 |
   | query_cache_type  | DEMAND   |
   +-------------------+----------+

   The reported size can be rounded to the nearest internal allocation block.

6. Run the same cacheable SELECT twice with SQL_CACHE against a stable read-mostly table.

   $ mysql --table -u root -p appdb -e "SELECT SQL_CACHE value_text FROM lookup WHERE id = 1; SELECT SQL_CACHE value_text FROM lookup WHERE id = 1; SHOW GLOBAL STATUS WHERE Variable_name IN ('Qcache_hits','Qcache_inserts','Qcache_queries_in_cache');"
   +------------+
   | value_text |
   +------------+
   | alpha      |
   +------------+
   +------------+
   | value_text |
   +------------+
   | alpha      |
   +------------+
   +-------------------------+-------+
   | Variable_name           | Value |
   +-------------------------+-------+
   | Qcache_hits             | 1     |
   | Qcache_inserts          | 1     |
   | Qcache_queries_in_cache | 1     |
   +-------------------------+-------+

   If Qcache_hits stays at 0, the statement may be uncacheable, the table may be changing between runs, or the session may still be using OFF because a live SET GLOBAL change does not update an already-open client session.

7. Re-check the query cache counters during representative load and disable the cache if the workload does not benefit from it.

   $ mysql --table -u root -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Qcache_hits','Qcache_inserts','Qcache_not_cached','Qcache_lowmem_prunes','Qcache_free_blocks','Qcache_total_blocks');"
   +--------------------+-------+
   | Variable_name      | Value |
   +--------------------+-------+
   | Qcache_free_blocks | 1     |
   | Qcache_hits        | 1     |
   | Qcache_inserts     | 1     |
   | Qcache_lowmem_prunes | 0   |
   | Qcache_not_cached  | 0     |
   | Qcache_total_blocks | 4    |
   +--------------------+-------+

   A useful cache usually shows rising Qcache_hits with controlled Qcache_lowmem_prunes; if inserts and invalidations dominate, switch the cache fully off with query_cache_type = 0 and query_cache_size = 0 instead of continuing to tune it.