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

Operations Guides

ProxySQL query digest memory growth: stats_mysql_query_digest growing unbounded

ProxySQL’s stats_mysql_query_digest table grows without bound. There is no built-in memory cap. On high-cardinality workloads where queries embed unique identifiers (timestamps, UUIDs, session tokens, savepoint names), the digest hash table can balloon to gigabytes, tracked as query_digest_memory in stats_memory_metrics.

The symptoms: ProxySQL runs normally for days or weeks, then develops periodic latency spikes that correlate with monitoring scrapes. RSS creeps upward. In extreme cases, the process is OOM-killed. The digest table is rarely inspected for size, only for query content, so the root cause stays hidden.

The fix: read from stats_mysql_query_digest_reset (returns the data and atomically clears the table), run TRUNCATE TABLE stats_mysql_query_digest, or issue PROXYSQL FLUSH QUERY DIGEST on a schedule. The harder problem is deciding how often to reset, whether to persist digest data externally, and which tuning variables to adjust to reduce cardinality at the source.

What this means

Each unique normalized query pattern generates one entry in stats_mysql_query_digest. ProxySQL normalizes literal values to ?, so SELECT * FROM users WHERE id=1 and SELECT * FROM users WHERE id=2 share a single digest. But when queries embed identifiers that ProxySQL does not normalize, each distinct value creates a separate digest entry.

The query_digest_memory column in stats_memory_metrics reports bytes consumed by this hash table. There is no configurable upper limit. Issue #2095 requested one; it was closed in 2025 without a cap after the maintainers cited digest normalization improvements, atomic TRUNCATE, and exporter observability.

flowchart TD
    A["High-cardinality queries
with embedded IDs"] --> B["Unique digest entries
accumulate"]
    B --> C["stats_mysql_query_digest
grows without bound"]
    C --> D["query_digest_memory
rises in stats_memory_metrics"]
    D --> E["Memory pressure:
RSS growth toward OOM"]
    D --> F["Traversal cost:
stats reads block inserts"]
    F --> G["Periodic latency spikes
matching scrape interval"]

When query_digest_memory reaches multiple gigabytes, two operational problems emerge:

Memory pressure: The digest hash table competes with connection buffers, query cache, and jemalloc overhead for process memory. On memory-constrained hosts, this leads to OOM kills or swap degradation.
Latency spikes during stats reads: Every time a monitoring system or a human queries stats_mysql_query_digest, stats_mysql_query_digest_reset, or stats_memory_metrics, ProxySQL traverses the hash table under a lock. On a multi-gigabyte table, this traversal blocks digest insertions and lookups, causing client-facing latency spikes that correlate with the scrape interval.

Stats also reset on restart. If ProxySQL restarts (planned upgrade, OOM kill, host reboot), all digest data is lost silently. External persistence is required for historical baselines.

Common causes

Cause	What it looks like	First thing to check
High-cardinality queries with embedded identifiers	`query_digest_memory` grows steadily; row count far exceeds distinct query types	Top-N digest query, look for near-duplicate entries differing only in numeric values
`mysql-query_digests_no_digits` disabled	Digest entries differ only in numeric values (IDs, timestamps, savepoint names)	Check `global_variables` for the variable value
Excessive `mysql-query_digests_max_query_length`	More of each query is stored, increasing per-entry memory cost	Compare variable value against your workload’s query lengths
No periodic reset scheduled	`query_digest_memory` grows monotonically since process start	Compare growth rate against `ProxySQL_Uptime`
Monitoring scrape causes latency spikes	Periodic latency spikes matching scrape interval, backends idle during spikes	Correlate spike timing with `stats_memory_metrics` collection

Quick checks

# Check query_digest_memory and overall memory breakdown
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT query_digest_memory, Auth_memory, SQLite3_memory_bytes, mysql_query_rules_memory, jemalloc_active, jemalloc_resident FROM stats_memory_metrics;"

# Count entries in the digest table
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT COUNT(*) AS digest_entries FROM stats_mysql_query_digest;"

# Check all digest-related 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_digests%';"

# Check uptime to contextualize growth rate
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';"

# Top 20 digests by execution count to spot cardinality bombs
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT digest_text, count_star FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 20;"

# Look for near-duplicate digests (sign of poor normalization).
# Adjust LEFT() length if the varying part appears earlier in the query.
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT LEFT(digest_text, 60) AS prefix, COUNT(*) AS near_dupes FROM stats_mysql_query_digest GROUP BY prefix HAVING near_dupes > 10 ORDER BY near_dupes DESC LIMIT 20;"

# OS-level RSS of the ProxySQL process
ps -p $(pidof proxysql) -o pid,rss,vsz,etime

How to diagnose it

Confirm the memory consumer. Query stats_memory_metrics and compare query_digest_memory against Auth_memory, SQLite3_memory_bytes, mysql_query_rules_memory, and jemalloc_active. If query_digest_memory is a large fraction of jemalloc_active, the digest table is the dominant consumer.
Check the digest row count. A high row count relative to your expected number of distinct query patterns indicates cardinality inflation. A workload with 50 distinct query types should not have hundreds of thousands of digest entries.
Inspect top digests for normalization gaps. Look at the top-N entries by count_star, then run the LEFT() grouping query to find near-duplicate prefixes. Many entries differing only in embedded numeric values or identifiers means normalization settings need adjustment.
Correlate latency with stats reads. If you see periodic latency spikes, check whether they align with your monitoring scrape interval. A monitoring system querying stats_memory_metrics or stats_mysql_query_digest triggers a full hash table traversal under lock. If disabling the scrape eliminates the spikes, digest table size is the cause.
Verify variable configuration. Check whether mysql-query_digests is enabled and whether normalization variables match your workload. A workload that generates savepoints with random numeric suffixes (common with Django) or queries with embedded timestamps produces massive digest cardinality unless mysql-query_digests_no_digits is enabled.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`query_digest_memory` (`stats_memory_metrics`)	Direct measure of digest hash table memory	Monotonic growth over hours or days with no reset
`COUNT(*)` of `stats_mysql_query_digest`	Number of unique tracked query patterns	Far exceeds expected distinct query count
`jemalloc_resident` (`stats_memory_metrics`)	Actual process memory footprint	Growing faster than expected from connections or cache
`jemalloc_active` minus `jemalloc_allocated`	Memory fragmentation indicator	Large and growing gap suggests fragmentation
`ProxySQL_Uptime` (`stats_mysql_global`)	Contextualizes growth duration	Long uptime plus high `query_digest_memory` means overdue reset
ProxySQL process RSS (OS-level)	Process memory from kernel perspective	Approaching system or cgroup limits

Fixes

Reset the digest table now

Three methods, each with different characteristics:

Read from stats_mysql_query_digest_reset. Returns all current digest data and atomically clears the table. Use this when you want to capture the data before clearing:

-- WARNING: this returns data AND clears the table atomically.
-- Capture the output if you need the data; it is gone after the SELECT completes.
SELECT hostgroup, schemaname, username, digest_text, count_star, sum_time
FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC LIMIT 100;

TRUNCATE TABLE stats_mysql_query_digest. The most efficient reset method. From 2.5.2, its large-table purge path runs without holding the digest lock:

-- Destructive: clears all digest entries. No output returned.
TRUNCATE TABLE stats_mysql_query_digest;

PURGE stats_mysql_query_digest TO <unix_timestamp>. A selective cleanup that removes entries whose last_seen precedes the timestamp:

-- Destructive and selective: replace 1700000000 with the desired epoch seconds.
PURGE stats_mysql_query_digest TO 1700000000;

After resetting, query_digest_memory drops in stats_memory_metrics, but jemalloc_resident may not decrease. This is expected jemalloc behavior. Memory is returned to the process heap for reuse, not necessarily released to the OS. The resident figure will decrease when jemalloc next needs to allocate and finds reusable arena space.

Reduce digest cardinality at the source

If your workload generates high-cardinality digests, tune normalization before scheduling resets:

Enable mysql-query_digests_no_digits. Normalizes all numeric sequences to ?, collapsing entries that differ only in numeric identifiers. The single most impactful change for workloads with embedded IDs, savepoint names with random suffixes, or timestamps:

SET mysql-query_digests_no_digits = 'true';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

This changes what digest entries look like going forward. It does not affect existing entries until the next reset.

Reduce mysql-query_digests_max_query_length. Controls how much of each original query is stored. A shorter value means less per-entry memory but may truncate the distinguishing part of complex queries.

Reduce mysql-query_digests_max_digest_length. Controls the maximum length of the normalized digest text. Smaller values reduce per-entry memory at the cost of digest precision.

Adjust mysql-query_digests_grouping_limit. Controls how many tokens are grouped in the digest normalization. A lower value produces more aggressive normalization, collapsing more query variants into a single digest.

Schedule periodic resets

Given the absence of a memory cap, periodic resets are the primary mitigation. Options:

External cron job. Schedule a read from stats_mysql_query_digest_reset on a fixed interval (hourly, daily, depending on growth rate). Capture the output to external storage if you need historical data:

# Example: hourly digest capture and reset. The _reset table both returns AND clears.
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT NOW() AS collected_at, hostgroup, schemaname, username, digest_text, count_star, sum_time FROM stats_mysql_query_digest_reset;" \
  >> /var/log/proxysql/digest_archive.tsv

Automated flush to disk. Current ProxySQL exposes admin-stats_mysql_query_digest_to_disk, SAVE MYSQL DIGEST TO DISK, and history_mysql_query_digest. If your version supports the stats history module, set admin-stats_mysql_query_digest_to_disk to a non-zero interval. This periodically performs SAVE MYSQL DIGEST TO DISK, which flushes digests to history_mysql_query_digest and resets the in-memory table:

-- Flush digests to disk every hour (3600 seconds)
SET admin-stats_mysql_query_digest_to_disk = 3600;
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

Note: repeated SAVE MYSQL DIGEST TO DISK operations may produce duplicate entries in history_mysql_query_digest for queries already recorded in a previous flush. Deduplicate during downstream processing if you use this for analytics.

Handle monitoring scrape interference

If your monitoring system queries stats_mysql_query_digest or stats_memory_metrics at a regular interval and causes latency spikes:

Reduce the digest table size first (reset plus cardinality tuning). Traversal cost is proportional to hash table size.
Reduce the scrape frequency for these specific tables if per-second resolution is not needed for digest analysis.
Use a top-N query with LIMIT rather than a full table scan if you only need the heaviest queries.

Prevention

Set a reset cadence based on observed growth rate. Measure query_digest_memory growth per hour for your workload. Schedule resets before it reaches a size that causes meaningful traversal latency on stats reads.
Enable mysql-query_digests_no_digits from the start. Prevents numeric-identifier bloat before it begins, especially for frameworks that generate savepoints or query identifiers with random numeric suffixes.
Persist digest data externally. Since stats reset on restart, historical analysis requires external storage. Use the _reset table read as your collection mechanism so you capture and clear in one operation.
Monitor query_digest_memory as a trend, not a threshold. Alert on sustained growth rate, not absolute value. The acceptable absolute value depends on your workload’s natural cardinality.
Include digest reset in ProxySQL runbooks. After restarts, upgrades, or configuration changes, verify the reset schedule is still active and the growth rate has not changed.

How Netdata helps

Netdata’s ProxySQL collector gathers stats_memory_metrics including query_digest_memory per second, making monotonic growth visible as a trend without manual admin queries.
Correlating query_digest_memory growth against jemalloc_resident and jemalloc_active distinguishes digest-driven growth from connection buffer growth or memory fragmentation.
Per-second collection of query processing time and client connection metrics can reveal periodic spikes caused by stats table traversal, even when spikes are short.
Anomaly detection on query_digest_memory surfaces growth rate changes (for example, a new deployment introducing high-cardinality queries) before the table reaches a size that impacts performance.
Since stats_mysql_query_digest resets on ProxySQL restart, Netdata’s persistent time-series storage retains historical baselines that are otherwise lost, enabling before-and-after comparisons across incidents.