Enable Query Cache vs. Rely on InnoDB Buffer Pool for Read‑Heavy Workloads in MariaDB 10.3–10.5

Answer the Exact Question First

In a 90 % read / 10 % write workload on MariaDB 10.3–10.5, enabling the query cache reduces the average latency of repeated identical SELECTs from roughly 3–5 ms (buffer‑pool read) to sub‑1 ms when the hit ratio is high. However, it introduces stale‑read risk and invalidation overhead that can offset the latency benefit if writes are frequent or queries vary.

1. Latency Comparison

With query_cache_type=1 and a modest query_cache_size (e.g., 64 MB), a hot SELECT that is literally identical on every request is served from the cache in ~0.5 ms on a typical 10‑core server. When the cache is disabled, the same SELECT is fetched from the InnoDB buffer pool in ~3–5 ms, depending on page size and OS page‑cache. The latency gap widens when the cache hit ratio exceeds 90 %.

2. Data Freshness Impact

The query cache ignores transaction isolation. After a write, any SELECT that touches the affected table may return data from the old version until the cache entry is invalidated. In a 90 % read / 10 % write mix, stale reads can persist for up to a few seconds if the write is followed by a burst of SELECTs on the same query. If the application requires read‑your‑writes or strict consistency, relying solely on the buffer pool is safer.

3. Invalidation Overhead

Each INSERT/UPDATE/DELETE on a table causes the server to lock the global query‑cache mutex and drop all cached rows that reference that table. With 10 % writes on a hot table, this can consume ~5–10 % of CPU time on a busy server, especially under high concurrency. The overhead grows linearly with the number of distinct tables affected.

Practical Verification Steps

1. Set up a representative benchmark that mimics your workload. For example:

   sysbench oltp_read_only --threads=20 --time=60 run
   sysbench oltp_write_only --threads=2 --time=60 run
   

2. Run the benchmark twice: once with the query cache enabled:

   SET GLOBAL query_cache_type=1;
   SET GLOBAL query_cache_size=65536;
   

   and once with it disabled:

   SET GLOBAL query_cache_type=0;
   

3. Collect metrics:

   SHOW STATUS LIKE 'Qcache_%';
   SHOW ENGINE INNODB STATUS;
   

4. Compare average SELECT latency from the benchmark output. A 3–5× latency drop on hot queries indicates a useful cache.

Recommendation

• If latency is critical and your SELECTs are *exactly identical* (same literals, no parameterized variations), and you can tolerate occasional stale reads, enable the query cache with a modest size (e.g., 64 MB). Monitor Qcache_hits and Qcache_inserts to keep the hit ratio above 80 %.
• If consistency matters or you observe high invalidation counts or low cache hit ratios due to query variation, rely solely on the InnoDB buffer pool. The buffer pool will keep data pages in memory and provide consistent reads without the extra lock overhead.
• Disable the query cache if you see Qcache_lowmem_prunes climbing, which indicates the cache is thrashing.

Missing Diagnostic Detail

To fine‑tune the decision, could you share the typical SELECT statement patterns: are they parameterized or do they contain literal values that change often? This will affect the cache hit ratio and thus the benefit of the query cache.