$ guides / proxysql / proxysql-query-cache-memory-growth ▌

Operations Guides

ProxySQL query cache memory growth: high-cardinality caching toward OOM

Query_Cache_Memory_bytes sits at the mysql-query_cache_size_MB ceiling, the purge rate is rising, and hit rate is dropping despite a full cache. This is the high-cardinality caching pattern: the cache is churning through unique keys that never repeat, and the soft limit cannot keep RSS bounded because it only triggers eviction of expired entries.

The worst case is OOM kill. The kernel terminates ProxySQL, clients disconnect, and on restart the cache is empty, stats tables reset, and the cycle starts again. If you are mid-incident, run PROXYSQL FLUSH QUERY CACHE for immediate relief. Then fix the root cause before memory grows back.

What this means

The query cache stores result sets in ProxySQL’s process memory. Each unique combination of query, user, and schema becomes a separate cache entry. When cached queries include high-cardinality values (UUIDs, timestamps, session tokens, per-request identifiers), each invocation produces a cache entry that is written once and never read again.

mysql-query_cache_size_MB is a soft limit (default 256 MB). The purging thread uses it as a threshold to trigger eviction, but the cache only evicts entries whose TTL has expired. If cache_ttl is long and the query stream is diverse, entries accumulate faster than they expire. Memory grows beyond the configured ceiling.

The signature pattern is three signals moving together:

Query_Cache_Memory_bytes at or near mysql-query_cache_size_MB.
Query_Cache_Purged rate rising (entries being evicted).
Hit rate (Query_Cache_count_GET_OK / Query_Cache_count_GET) declining despite the cache being full.

The cache is doing work (writes, evictions, lookups) without providing value (hits). Each cache lookup adds overhead to every matched query, and RSS grows from entries the soft limit cannot reclaim.

flowchart TD
    A["cache_ttl set on query rule"] --> B["High-cardinality query matches"]
    B --> C["Each call creates unique cache key"]
    C --> D["Cache fills with write-once entries"]
    D --> E["mysql-query_cache_size_MB soft limit reached"]
    E --> F["Purge evicts only expired entries"]
    F --> G{"TTL still valid?"}
    G -->|Yes| H["Memory exceeds soft limit"]
    G -->|No| C
    H --> I["RSS grows, hit rate stays low"]
    I --> J["OOM kill"]

Common causes

Cause	What it looks like	First thing to check
High-cardinality queries cached	`Query_Cache_Entries` high, `Query_Cache_count_SET` rising fast, hit rate near 0%	`stats_mysql_query_digest` for queries matching cache rules that have many unique parameter sets
Excessively long `cache_ttl`	Memory growing steadily, purge rate low until the TTL window starts expiring	`runtime_mysql_query_rules WHERE cache_ttl > 0`
`mysql-query_cache_size_MB` too large for host memory	RSS close to system limit, other processes competing	`/proc/<pid>/status` `VmRSS` vs available system memory
Memory fragmentation amplifying cache growth	RSS growing faster than `Query_Cache_Memory_bytes`, `jemalloc_resident` well above `jemalloc_allocated`	`stats_memory_metrics` jemalloc columns

Quick checks

All commands connect to the admin interface (default port 6032). Replace credentials with your actual admin user and password.

# Check all query cache counters
mysql -u <admin_user> -p -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';"

# Check which query rules have caching enabled and their TTLs
mysql -u <admin_user> -p -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, match_digest, match_pattern, cache_ttl FROM runtime_mysql_query_rules WHERE cache_ttl > 0;"

# Check the configured soft limit
mysql -u <admin_user> -p -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM global_variables WHERE Variable_Name = 'mysql-query_cache_size_MB';"

# Check detailed memory breakdown including jemalloc fragmentation
mysql -u <admin_user> -p -h 127.0.0.1 -P 6032 \
  -e "SELECT * FROM stats_memory_metrics;"

# Check OS-level RSS, virtual size, and swap usage
cat /proc/$(pidof proxysql)/status | grep -E 'VmRSS|VmSize|VmSwap'

# Check top queries by frequency to spot high-cardinality patterns
mysql -u <admin_user> -p -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, schemaname, digest_text, count_star FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 20;"

How to diagnose it

Confirm cache saturation. Check Query_Cache_Memory_bytes against the configured limit. Calculate the utilization ratio: Query_Cache_Memory_bytes / (mysql-query_cache_size_MB * 1048576). If this ratio is at or above 1.0, the cache is at its soft limit.
Calculate the hit rate. Divide Query_Cache_count_GET_OK by Query_Cache_count_GET. A hit rate below 20% on a full cache means the cache is churning, not serving. Both counters are cumulative since ProxySQL start, so use deltas between two readings if the process has been running for a long time.
Check the purge rate. Query_Cache_Purged is a cumulative counter. If it is climbing fast relative to uptime, entries are being evicted at high volume. Compare the purge rate against Query_Cache_count_SET (writes). If writes and purges are both high but hits are low, the cache is a revolving door.
Identify cached query rules. Run the second quick check command to list all rules with cache_ttl > 0. Note the TTL value (in milliseconds) and the match_digest or match_pattern for each.
Cross-reference with query digests. For each cached rule, check stats_mysql_query_digest to see how many unique parameter sets match that rule. A query like SELECT * FROM sessions WHERE token = ? with count_star in the millions is a high-cardinality candidate. Each unique token value creates a separate cache entry.
Check RSS vs cache memory. Compare jemalloc_resident (or OS-level VmRSS) against Query_Cache_Memory_bytes. If RSS is significantly larger than cache memory, fragmentation or other memory consumers (connection buffers, prepared statement cache) are amplifying the problem. Check stats_memory_metrics for the full breakdown.
Assess OOM proximity. Check /proc/<pid>/status for VmRSS against available system memory. Also check VmSwap. If swap is non-zero, performance has already degraded and OOM is approaching.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`Query_Cache_Memory_bytes`	Current cache memory usage vs soft limit	Approaching or exceeding `mysql-query_cache_size_MB`
`Query_Cache_count_GET_OK / Query_Cache_count_GET`	Hit rate measures cache effectiveness	Declining despite cache being full indicates churn
`Query_Cache_Purged`	Cumulative count of evicted entries	Rising rate signals eviction pressure
`Query_Cache_Entries`	Current number of cached entries	Growing without proportional hit rate means high cardinality
`Query_Cache_count_SET`	Cache write rate	Much higher than GET_OK rate means write-once, never-read
`jemalloc_resident`	Actual process RSS from allocator	Growing faster than `Query_Cache_Memory_bytes` signals fragmentation
`ProxySQL_Uptime`	Context for cumulative counters	Recent restart resets all cumulative metrics, making ratios unreliable

Fixes

Immediate relief: flush the cache

PROXYSQL FLUSH QUERY CACHE;

This immediately frees all query cache memory. It is safe to run in production. The trade-off: the cache is cold after the flush, so hit rate drops to zero temporarily and backend load increases until the cache warms back up. If the underlying query rules are unchanged, memory will grow back. This buys time, not a permanent fix.

Shorten cache_ttl

Reduce the TTL on rules matching high-cardinality queries so entries expire sooner. This limits the working set size. For example, if a rule currently sets cache_ttl = 300000 (5 minutes), reducing it to 30000 (30 seconds) means entries are eligible for purging much sooner.

UPDATE mysql_query_rules SET cache_ttl = 30000 WHERE rule_id = <id>;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Trade-off: shorter TTL means more cache misses and more backend load for queries that do repeat. Use this when the query mix has some repeatable queries mixed with high-cardinality ones.

Stop caching high-cardinality queries entirely

Remove cache_ttl from rules that match queries with UUIDs, timestamps, session tokens, or other per-request unique values. These queries produce cache entries that are never reused. The cache lookup overhead is pure cost with no benefit.

UPDATE mysql_query_rules SET cache_ttl = NULL WHERE rule_id = <id>;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

This is the correct permanent fix when a cached query’s parameter space is effectively unbounded. Even with a short TTL, each second of traffic floods the cache with single-use entries.

Prevent caching empty results

If cached queries frequently return empty result sets, each empty result still consumes a cache entry. Setting cache_empty_result to 0 on the query rule prevents caching these entries. The column is present in ProxySQL v2.0+ mysql_query_rules schemas.

UPDATE mysql_query_rules SET cache_empty_result = 0 WHERE rule_id = <id>;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Useful when a high-cardinality query often returns no rows (for example, existence checks against a sparse table).

Reduce mysql-query_cache_size_MB

If the soft limit is set high relative to available system memory, lower it.

UPDATE global_variables SET Variable_Value = 128
  WHERE Variable_Name = 'mysql-query_cache_size_MB';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

This reduces the ceiling for cache growth. Trade-off: less cache capacity means more evictions and potentially lower hit rates for queries that do benefit from caching. This is a mitigation, not a root-cause fix.

Reset the query digest table separately

The digest table is a separate memory consumer from the query cache. If it is also growing (many unique query digests accumulating), it contributes to RSS independently.

SELECT * FROM stats_mysql_query_digest_reset;

Reading from stats_mysql_query_digest_reset returns the current contents and clears the table; this is the documented and version-portable reset path. Current ProxySQL source also handles TRUNCATE TABLE stats.stats_mysql_query_digest, but prefer _reset unless you have verified that form on your deployed version.

These two subsystems (query cache and query digests) are independent. Flushing one does not free memory from the other.

Prevention

Audit query rules before enabling cache_ttl. Before adding caching to a rule, check the cardinality of queries it matches. A query that includes a UUID, timestamp, or per-request token in its WHERE clause will produce a unique cache entry for every invocation.
Monitor hit rate as a ratio, not absolute memory. A full cache with a near-zero hit rate is worse than an empty cache. The cache adds lookup overhead to every matched query while providing no offload to backends.
Alert on the combination, not individual signals. Query_Cache_Memory_bytes near the limit alone is normal for a well-utilized cache. It becomes a problem only when combined with declining hit rate and rising purge rate. Alert on all three together.
Watch RSS divergence. Track jemalloc_resident against Query_Cache_Memory_bytes. If RSS grows much faster than cache memory, fragmentation is amplifying the problem. A restart clears fragmented memory but is not a sustainable fix.
Set mysql-query_cache_size_MB conservatively. The soft limit does not prevent RSS from exceeding it. Leave headroom for connection buffers, query digest storage, and other memory consumers in the same process.

How Netdata helps

Per-second collection of Query_Cache_Memory_bytes, Query_Cache_Entries, and Query_Cache_Purged makes cache growth and eviction rates visible at high resolution, catching the gradual RSS climb before it reaches OOM.
The hit rate ratio (Query_Cache_count_GET_OK / Query_Cache_count_GET) can be visualized alongside cache memory usage, making churn immediately visible: memory climbing while hit rate falls.
jemalloc_resident and jemalloc_allocated from stats_memory_metrics let you separate cache growth from allocator fragmentation. RSS diverging from Query_Cache_Memory_bytes signals fragmentation, not just cache pressure.
Correlating cache metrics with Questions rate and backend pool utilization shows whether cache churn is driving backend load spikes as misses pass through to MySQL.
ProxySQL_Uptime gating prevents false alerts during cold start, when cache counters reset and ratios are temporarily meaningless.