MariaDB query cache efficiency appears underreported: Com_select already includes Qcache_hits

[cisi80]

Environment:

• MariaDB 10.11.14-MariaDB-0ubuntu0.24.04.1
• Ubuntu 24.04
• MySQLTuner version: MySQLTuner 2.8.44
• query_cache_type=ON

Observed MySQLTuner output:
Query cache efficiency: ~45%

Observed server counters:
SHOW GLOBAL STATUS WHERE Variable_name IN (
'Com_select',
'Qcache_hits',
'Qcache_inserts',
'Qcache_not_cached',
'Qcache_lowmem_prunes'
);

Result:
Com_select = 454794
Qcache_hits = 383668
Qcache_inserts = 65644
Qcache_not_cached = 5474

The counters reconcile as:
Qcache_hits + Qcache_inserts + Qcache_not_cached
= 383668 + 65644 + 5474
= 454786

This is effectively equal to:
Com_select = 454794

This suggests that, at least on MariaDB 10.11, Com_select already includes query-cache hits.
Current/old formula appears to be:
Qcache_hits / (Com_select + Qcache_hits)
which gives:
383668 / (454794 + 383668) = 45.77%

But based on the MariaDB counters, the practical hit ratio appears to be:
Qcache_hits / Com_select
which gives:
383668 / 454794 = 84.36%

Impact:
For MariaDB, MySQLTuner may significantly underreport query cache efficiency. On hosting workloads this can make a highly effective query cache look mediocre.

Suggested behavior:
For MariaDB versions where:

Com_select ≈ Qcache_hits + Qcache_inserts + Qcache_not_cached

calculate query cache efficiency as:
Qcache_hits / Com_select

or show both metrics:

• Overall MySQL-style formula
• MariaDB observed query-cache hit ratio

Additional note:
Prepared statements are not material here:

Com_stmt_prepare = 18
Com_stmt_execute = 18

query_cache_type is ON globally and for the session.

[jmrenouard]

Hi @cisi80

Have a look at thi issue there is a lot of information on this topic
#923

[jmrenouard]

Hi @cisi80

PR delivered: #931

[cisi80]

Thanks!