$ guides / proxysql / proxysql-query-cache-stampede ▌

Operations Guides

ProxySQL query cache stampede: a hot entry expires and the herd hits the backend

Backend connection pool usage spikes suddenly. Backend latency climbs. Query cache hit rate drops to near zero for a brief window, then recovers minutes later. If this pattern repeats at regular intervals, a cache stampede is the likely cause.

ProxySQL’s query cache is stale-read, keyed by query digest, user, and schema. It uses TTL-based expiration only. There is no request coalescing, no lock-based regeneration, and no mechanism to ensure only one request repopulates the cache after a miss. When a hot entry expires, every concurrent request for that query independently hits the backend MySQL.

What this means

Entries are created when a query matched by a rule with cache_ttl > 0 executes against a backend. The result set is stored in ProxySQL’s process memory and expires when the TTL elapses. There is no write-triggered invalidation: if the underlying data changes, cached entries are not invalidated until their TTL expires. Applications must tolerate stale reads.

When a hot entry expires, ProxySQL does not hold subsequent requests while one request regenerates the entry. Each concurrent request independently discovers the cache miss and routes to the backend. If the query normally runs hundreds of times per second with a 60-second TTL, a single expiry event can send dozens of near-simultaneous identical queries to the backend within milliseconds. Once the first response is cached, subsequent requests resume hitting the cache and pressure subsides until the next TTL cycle.

flowchart TD
    A["Hot entry served from cache\nGET_OK rate high"] -->|"cache_ttl expires"| B["All concurrent requests miss"]
    B --> C["Thundering herd hits backend\nConnUsed spikes"]
    C --> D["Backend pool saturates\nLatency increases"]
    D --> E["First response cached\nHit rate recovers"]
    E --> F["Backend pressure subsides\nuntil next TTL expiry"]

The key diagnostic signature is periodicity. Backend pressure spikes at regular intervals matching your cache_ttl values point to a stampede.

Common causes

Cause	What it looks like	First thing to check
Single hot query with short TTL	One digest dominates `stats_mysql_query_digest`. Spike interval matches the TTL.	Top digests by `count_star`
TTL aligned across query classes	Multiple digests expire simultaneously, amplifying the herd.	Whether multiple rules share the same `cache_ttl` value
Cache too small for working set	`Query_Cache_Purged` rate is high. Hit ratio is low even outside stampede windows.	`Query_Cache_Memory_bytes` vs `mysql-query_cache_size_MB`
Backend already under pressure	Stampede tips an already loaded backend over the edge.	Backend CPU, I/O, and connection counts at the time of the spike

Quick checks

Read-only queries against the ProxySQL admin interface (default port 6032). Safe to run during an incident.

# Check query cache metrics - look for GET_OK rate drop
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';"

# Find the hot digest - look for disproportionate count_star
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, schemaname, username, digest_text, count_star, sum_time, ROUND(sum_time/count_star) AS avg_time_us FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 20;"

# Check which rules have cache_ttl set
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, match_digest, destination_hostgroup, cache_ttl FROM runtime_mysql_query_rules WHERE cache_ttl > 0 ORDER BY rule_id;"

# Check backend pool usage - ConnUsed spike during stampede
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnOK, ConnERR FROM stats_mysql_connection_pool;"

# Cached queries show hostgroup -1 (served from cache)
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, digest_text, count_star FROM stats_mysql_query_digest WHERE hostgroup = -1 ORDER BY count_star DESC LIMIT 10;"

# Check backend connection pool failures
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name IN ('ConnPool_get_conn_success','ConnPool_get_conn_failure','Server_Connections_delayed');"

# Check cache memory usage vs configured limit
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name IN ('Query_Cache_Memory_bytes','Query_Cache_Entries','Query_Cache_Purged');"

How to diagnose it

Confirm the periodic pattern. Compare the interval between backend pressure spikes against the cache_ttl values in your query rules. If a rule has cache_ttl=60000 (60 seconds) and you see spikes roughly every 60 seconds, that rule is the culprit.
Identify the hot query. Sort stats_mysql_query_digest by count_star descending. A single digest with a count orders of magnitude higher than all others is the stampeding query. Cross-reference the digest text against your query rules to find which cache_ttl applies.
Verify cache miss behavior. During a stampede, the hot digest shows up with its real backend hostgroup (not -1) because cache misses route to the backend. Outside the stampede window, the same digest shows hostgroup -1 (cache hit). Two readings, one during and one outside the spike, confirm the diagnosis via the hostgroup shift.
Check backend impact. Look at ConnUsed on the target hostgroup during the spike. If ConnUsed approaches max_connections for any backend, the stampede is causing real resource pressure. Check ConnPool_get_conn_failure and Server_Connections_delayed for evidence of pool exhaustion.
Rule out other causes. If backend latency is elevated continuously, the problem is not purely a stampede. Check backend MySQL process list for concurrent issues outside the stampede window.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`Query_Cache_count_GET_OK` rate	Measures cache hits directly. Drops during stampede.	Sudden rate drop to near zero in a narrow window
`Query_Cache_count_GET` rate	Total cache lookups. Stays stable during stampede (same traffic volume).	Confirms traffic is constant while hit rate drops
Backend `ConnUsed` per backend	Shows backend pool pressure during stampede.	Spike synchronized with cache hit rate drop
`ConnPool_get_conn_failure`	Most direct pool starvation signal.	Any sustained increase during stampede window
`stats_mysql_query_digest` top digest	Identifies the stampeding query.	Single digest with disproportionate `count_star`
`Query_Cache_Purged` rate	Entries removed from cache. May spike if many entries expire together.	Spike synchronized with hit rate drop
Backend `Latency_us` per backend	Backend ping latency.	Spike during stampede window
`Slow_queries` rate	Queries exceeding `mysql-long_query_time`.	Increase during stampede as backend contends

Fixes

Increase the cache_ttl on the hot query

The most direct fix. If the hot query tolerates stale reads, lengthening the TTL reduces how often the stampede recurs.

# Increase cache_ttl for the matching rule (value in milliseconds)
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "UPDATE mysql_query_rules SET cache_ttl=300000 WHERE rule_id=<rule_id>; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;"

Tradeoff: Longer TTL means staler data. Evaluate the application’s tolerance for stale reads before increasing TTL aggressively.

Stagger TTL values across query classes

If multiple hot queries share the same cache_ttl, they expire simultaneously, compounding the herd. Assign slightly different TTL values to different rules so expirations spread over time.

Tradeoff: Adds operational complexity. You must track which TTL applies to which query class and ensure staggering is maintained when rules change.

Reduce query cardinality

If the hot query produces many unique cache keys (for example, queries parameterized by user ID or timestamp before normalization), each key is a separate cache entry with its own TTL. More unique keys means more independent expiry events, which blurs the stampede pattern but also means the cache is less effective overall.

Consider whether the query can be rewritten to reduce cardinality, or whether caching is appropriate for this query at all.

Flush the query cache for immediate relief

If you need a clean cache state (for example, after changing cache_ttl on a pre-v2.0.1 ProxySQL where rule changes do not purge entries), flush the entire query cache:

# Immediately free all query cache memory - all entries are lost
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "PROXYSQL FLUSH QUERY CACHE;"

Warning: This purges all cached entries, not just the hot one. Every cached query will miss on its next request. Use only when you need a clean cache state, not as a routine fix.

Disable caching for the problematic query

If the query does not benefit from caching (high cardinality, low repeat rate, or freshness requirements that make stale reads unacceptable), remove cache_ttl from the matching rule:

# Disable caching for a specific rule
mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "UPDATE mysql_query_rules SET cache_ttl=0 WHERE rule_id=<rule_id>; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;"

Tradeoff: Removing caching increases backend load for that query. Only do this if the query is not cacheable or the stampede cost exceeds the caching benefit.

Prevention

Audit cache_ttl values regularly. Track which rules have caching enabled and what TTL each uses. Look for queries whose TTL is shorter than their access frequency warrants.
Monitor hit rate as a trend, not a snapshot. A periodic dip in hit rate is the stampede signature. Alert on periodic patterns, not just absolute thresholds.
Verify prepared statements are not expected to use the cache. ProxySQL’s query cache does not work with prepared statements. If your application uses prepared statements (common with ORMs like Laravel or Doctrine), the cache will never be hit regardless of rule configuration.
Watch for TTL alignment. If you add caching to multiple query rules, avoid giving them all the same TTL. Stagger values to prevent synchronized expiry events.
Track cache memory pressure. Query_Cache_Memory_bytes approaching mysql-query_cache_size_MB causes increased purging. A cache under memory pressure has a lower effective hit rate, which means more backend traffic even outside stampede windows.
Reset digest stats periodically. stats_mysql_query_digest accumulates over time and resets on restart. Use SELECT * FROM stats_mysql_query_digest_reset to read and clear the table, keeping the data current and preventing unbounded memory growth from digest storage.

How Netdata helps

Per-second metric resolution shows the exact shape of the cache hit rate drop and recovery, making it easy to distinguish a stampede (sharp V-shaped dip with periodic recurrence) from gradual degradation.
Correlate Query_Cache_count_GET_OK with backend ConnUsed and ConnPool_get_conn_failure on the same timeline to confirm cache misses are driving backend pool pressure.
ML anomaly detection on query cache hit rate flags the periodic dip pattern even when the average hit rate looks acceptable.
Digest-level visibility through stats_mysql_query_digest collection identifies which specific query is stampeding, not just that a stampede is occurring.
Backend latency correlation across hostgroups shows whether the stampede is causing real user-facing impact or is absorbed by backend headroom.