$ guides / proxysql / proxysql-query-cache-hit-rate-dropped ▌

Operations Guides

ProxySQL query cache hit rate dropped: backend load about to surge

A query cache hit rate drop is a leading indicator. By the time backend CPU spikes or the connection pool saturates, the cache has already stopped absorbing read load.

ProxySQL’s query cache is a TTL-based in-memory result set cache with no invalidation on data change. When it works, it shields backends from repetitive read queries. When it stops, every previously-cached query becomes a real backend query, and backend load increases proportionally to the miss delta.

The hit rate formula: Query_Cache_count_GET_OK / Query_Cache_count_GET. A drop of more than 20 percentage points from your established baseline, with a stable Questions rate, means more queries are reaching backends. Backend overload follows unless the cache recovers or you intervene.

What this means

The query cache sits inside the query processor. When an incoming query matches a rule with cache_ttl > 0, ProxySQL performs a cache lookup keyed by the query text, user, and schema. If a valid, non-expired entry exists, it is returned without touching any backend. If not, the query routes to a backend, and the result set may be stored in cache with the configured TTL.

A hit rate drop means one of two things: fewer queries are eligible for caching (rules or query patterns changed), or eligible queries are not finding cached entries (entries expired, were evicted, or were never stored). The diagnostic goal is to determine which.

Two nuances frame everything that follows:

Absolute hit rate values are meaningless without a workload baseline. A 30% hit rate might be excellent for one workload and terrible for another. What matters is the relative change from steady state.
High Query_Cache_count_GET with near-zero Query_Cache_count_GET_OK is pure lookup overhead with no benefit. The cache is checked on every eligible query but never hits. This adds latency and is worse than having caching disabled entirely.

Common causes

Cause	What it looks like	First thing to check
Cache flush or ProxySQL restart	`Query_Cache_Entries` drops to near 0, `Memory_bytes` drops sharply, hit rate climbs back over 30-120 seconds	`ProxySQL_Uptime` in stats_mysql_global
TTL expiry stampede	Hit rate drops sharply at a predictable interval, single digest dominates backend query rate	`cache_ttl` values on hot query rules, check for identical TTLs across rules
Cache churn (working set too large)	`Query_Cache_Purged` rate high and rising, `Memory_bytes` at limit, hit rate declining steadily	`Query_Cache_Memory_bytes` vs `mysql-query_cache_size_MB`
cache_ttl accidentally removed or set to 0	`Query_Cache_count_GET` stops growing or drops, no new `SET` operations	`runtime_mysql_query_rules WHERE cache_ttl > 0`
Query pattern change from deployment	New digests appear in top queries, old cached queries no longer arrive	`stats_mysql_query_digest` sorted by `count_star DESC`, compare against baseline
Prepared statement adoption	GET rate flat, new `COM_STMT_EXECUTE` patterns in `stats_mysql_commands_counters`	Check for `COM_STMT_PREPARE`/`COM_STMT_EXECUTE` command counts

Quick checks

All commands connect to the ProxySQL admin interface on port 6032. These are read-only SELECT statements and are safe to run at any time.

# 1. Check cache hit rate (raw counters)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';"

# 2. Confirm Questions rate is stable (compare two readings 60s apart)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name = 'Questions';"

# 3. Check ProxySQL uptime (detect recent restart)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Value AS uptime_seconds FROM stats_mysql_global WHERE Variable_Name = 'ProxySQL_Uptime';"

# 4. Check which query rules have cache_ttl enabled
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, match_digest, match_pattern, cache_ttl, apply FROM runtime_mysql_query_rules WHERE cache_ttl > 0 ORDER BY rule_id;"

# 5. Check rule ordering around caching rules (apply=1 on a preceding rule stops evaluation)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, match_digest, destination_hostgroup, cache_ttl, apply FROM runtime_mysql_query_rules ORDER BY rule_id LIMIT 30;"

# 6. Top 20 queries by frequency (watch for new digests)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, schemaname, digest_text, count_star, sum_time FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 20;"

# 7. Check cache configuration variables
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT variable_name, variable_value FROM global_variables WHERE variable_name LIKE 'mysql-query_cache%' OR variable_name LIKE 'mysql-threshold_resultset%';"

# 8. Check per-command execution stats (look for COM_STMT_PREPARE/COM_STMT_EXECUTE)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Command, Total_cnt, Total_Time_us FROM stats_mysql_commands_counters WHERE Total_cnt > 0 ORDER BY Total_cnt DESC LIMIT 15;"

How to diagnose it

The diagnostic flow narrows from “hit rate dropped” to a specific cause by checking cache contents, eviction rates, and query patterns in sequence.

flowchart TD
    A["Hit rate dropped
20+ pts from baseline"] --> B{"Questions rate
stable?"}
    B -- "No, dropped" --> C["Traffic changed
not a cache issue"]
    B -- "Yes" --> D{"Cache_Entries
near zero?"}
    D -- "Yes" --> E{"Uptime under
120 seconds?"}
    E -- "Yes" --> F["Cold cache
normal warmup"]
    E -- "No" --> G["Flush or OOM
check proxysql.log"]
    D -- "No, entries exist" --> H{"Purge rate
spiking?"}
    H -- "Yes" --> I["Cache churn
working set too large"]
    H -- "No" --> J{"New digests in
top queries?"}
    J -- "Yes" --> K["Query pattern change
from deployment"]
    J -- "No" --> L["Check cache_ttl
and rule ordering"]

Step through the flow:

Confirm the Questions rate is stable. If Questions dropped alongside the hit rate, the workload itself changed. Look upstream at application traffic or ProxySQL connection acceptance.
Check if Query_Cache_Entries dropped to near zero. If entries disappeared, either ProxySQL restarted (check ProxySQL_Uptime) or the cache was flushed. Check proxysql.log for OOM kills or PROXYSQL FLUSH QUERY CACHE invocations. A cold cache warms in 30-120 seconds.
Check the Query_Cache_Purged rate. If purged entries accumulate rapidly while the cache is at its memory limit (Query_Cache_Memory_bytes near mysql-query_cache_size_MB), the working set exceeds cache capacity. Entries are evicted before reuse. Increase cache size or reduce what you cache.
Check whether cache_ttl was accidentally removed. Query runtime_mysql_query_rules WHERE cache_ttl > 0. If rules that previously had caching no longer appear, the configuration changed. Also check rule ordering: if a new rule with apply=1 was inserted before a caching rule, it terminates evaluation and the caching rule never executes.
Look for new query digests. Sort stats_mysql_query_digest by count_star DESC. If new digests dominate that were absent from your baseline, an application deployment changed query patterns. The new queries may not match any caching rule, or the query text may have changed (added comments, different formatting) producing different cache keys. ProxySQL includes the full query text in the cache key, so a trace comment like /* app=v2 */ makes the same logical query a different cache entry.
Check for prepared statement adoption. The query cache is incompatible with prepared statements. If the application switched to server-side prepared statements (common with ORM updates), those queries bypass the cache. Check stats_mysql_commands_counters for PREPARE and EXECUTE rows; for binary-protocol prepared statements, also check the Stmt_Client_Active_Total and Stmt_Client_Active_Unique gauges in stats_mysql_global.
Correlate with backend pool metrics. Check ConnUsed and ConnFree in stats_mysql_connection_pool. If these are degrading, the backend load surge has started. See ProxySQL ConnPool_get_conn_failure rising for the direct pool-starvation signal.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`Query_Cache_count_GET_OK / Query_Cache_count_GET`	The hit rate itself. Directly measures cache effectiveness.	Drop of 20+ percentage points from baseline sustained over 5 minutes
`Query_Cache_count_GET` rate	Cache lookup volume. If this drops to 0, caching rules stopped matching.	Sudden drop or flat when Questions rate is stable
`Query_Cache_Purged` rate	Eviction rate. High purge rate with declining hit rate means cache too small.	Purge rate approaching SET rate (total churn)
`Query_Cache_Memory_bytes`	Current cache memory usage vs configured limit.	Approaching `mysql-query_cache_size_MB` ceiling
`Query_Cache_Entries`	Current cached entry count. Sharp drop indicates flush or restart.	Drops to near 0 without a recent restart
`Questions` rate	Total query throughput. Must be stable for hit rate comparison.	Any significant deviation invalidates the baseline comparison
`ConnUsed` per backend	Backend connection utilization. Hit rate drop causes this to rise.	Rising proportionally with the miss rate increase
`ProxySQL_Uptime`	Process uptime. Detects restarts that reset the cache.	Recent reset relative to baseline warmup time

Fixes

Cache flush or restart

A cold cache is expected after restart and warms in 30-120 seconds. If restarts are frequent, check proxysql.log and kernel OOM killer logs. If Query_Cache_Memory_bytes was near the system memory limit before restart, reduce mysql-query_cache_size_MB to leave headroom for connection buffers and other ProxySQL subsystems.

TTL expiry stampede

When many entries share the same TTL, they expire simultaneously. The next request for each expired entry hits the backend, creating a synchronized burst.

Stagger TTL values. Avoid identical TTLs across rules. If three rules all use cache_ttl=60000, change them to 55000, 60000, and 65000 so they expire at different times.
Enable soft TTL refresh. Set mysql-query_cache_soft_ttl_pct to 1–99; values 0 and 100 disable the feature. Introduced in 2.5.0, it starts a backend refresh when an entry reaches the specified percentage of its TTL, the next query for that key goes to the backend, refreshing the entry and resetting the TTL. While that refresh runs, other queries continue using the old entry.
Increase TTL for hot queries. If stale reads are acceptable, a longer TTL reduces expiry frequency.

Cache churn (working set too large)

The cache is full but entries are evicted before reuse. Query_Cache_Purged is high and the hit rate is low despite a full cache.

Increase mysql-query_cache_size_MB. Default is 256 MB. This is a soft limit, not a hard cap. Ensure the system has memory headroom.
Stop caching high-cardinality queries. Queries with UUIDs, timestamps, or other high-variance parameters generate unique cache keys that are rarely reused. Remove cache_ttl from rules matching these patterns.
Disable empty result caching. Set mysql-query_cache_stores_empty_result to false if your workload produces many empty result sets that consume cache memory without benefit. The variable exists and defaults to true.
Immediate relief. PROXYSQL FLUSH QUERY CACHE; frees all cache memory. The cache rebuilds from scratch. This is disruptive: all cached entries are lost. Use it to break a churn cycle when the cache is stuck evicting faster than it can serve.

cache_ttl accidentally removed or rule reordered

If query rules were changed and caching stopped:

-- Verify the rule has cache_ttl set in runtime
SELECT rule_id, match_digest, cache_ttl, apply
  FROM runtime_mysql_query_rules WHERE rule_id = <ID>;

-- Restore the TTL and load to runtime
UPDATE mysql_query_rules SET cache_ttl = <milliseconds> WHERE rule_id = <ID>;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Also verify rule ordering. Rules are evaluated in rule_id order. If a rule with apply=1 precedes the caching rule and matches the same queries, the caching rule never executes. Either reorder (lower rule_id for caching) or set apply=0 on the preceding routing rule.

ProxySQL does not associate cached entries with the rule that produced them. Changing cache_ttl changes the TTL used for future cache lookups; it does not perform a rule-specific purge. Existing entries remain until they expire or are evicted. Use PROXYSQL FLUSH QUERY CACHE when you need an immediate cache-wide purge.

Query pattern change from deployment

If the application deployment changed query text, cache keys change. Queries that previously hit now miss because the text differs.

Check for added comments. A trace comment like /* traceparent=... */ added by a framework produces a different cache key. ProxySQL does not strip comments before computing the cache key.
Add caching rules for new query patterns. If the deployment introduced new queries that should be cached, add rules with appropriate cache_ttl.
Reset digest stats to establish a clean baseline. Query stats_mysql_query_digest_reset, but note that reading from this table resets all counters.

Prepared statement adoption

The query cache does not cache prepared statements. This is a documented ProxySQL limitation. If the application switched to server-side prepared statements, those queries bypass the cache.

Enable client-side emulation. For PDO-based applications, setting PDO::ATTR_EMULATE_PREPARES = true causes the driver to send text queries instead of using the binary prepared statement protocol, restoring cache eligibility.
Accept the loss. If prepared statements are required, the query cache will not help for those queries. Focus backend protection on connection pooling and query optimization.

Prevention

Baseline the hit rate. Record the normal hit rate per workload pattern (peak, off-peak, batch). Alert on relative changes, not absolute values.
Stagger cache_ttl values. Avoid identical TTLs across multiple rules to prevent synchronized expiry bursts.
Monitor purge rate alongside hit rate. A rising Query_Cache_Purged rate with a declining hit rate is the earliest sign that the cache is too small for the working set.
Review cache rules after every deployment. Query text changes (added comments, formatting, new queries) silently break cache key matching. Audit stats_mysql_query_rules hit counts before and after deploys.
Alert on GET rate collapse. If Query_Cache_count_GET drops to zero while Questions continues, caching rules stopped matching. This is often the first signal of an accidental rule change.
Avoid caching high-cardinality queries. Use digest analysis to identify which queries actually benefit from caching.

How Netdata helps

Netdata’s ProxySQL collector surfaces cache metrics at per-second resolution, which matters because hit rate drops can be step functions (cache flush) or gradual declines (churn).

Per-second Query_Cache_count_GET_OK and Query_Cache_count_GET rates show the exact moment the hit rate changed, not a minute-late average. Correlate the timing with deployments, restarts, or TTL boundaries.
Query_Cache_Purged rate alongside hit rate distinguishes “cache too small” from “cache rules changed.” When purge rate spikes and hit rate drops simultaneously, the cache is churning.
Backend pool correlation. ConnUsed, ConnFree, and ConnPool_get_conn_failure on the same dashboard show whether the backend load surge has already begun, so you can act before backends saturate.
ML anomaly detection on cache counters catches slow degradation that fixed thresholds miss, such as a gradual purge-rate increase over hours before the hit rate visibly drops.