$ guides / proxysql / proxysql-query-rule-cpu-overload ▌

Operations Guides

ProxySQL query rule CPU overload: expensive regex saturating the worker threads

ProxySQL host CPU is pegged. Client query latency is climbing. But the MySQL backends are idle: ConnFree is greater than zero across the pool, backend ping latency is normal, and there are no slow queries on the database side. The proxy itself is the bottleneck.

When the proxy’s worker threads are saturated, every query slows down uniformly regardless of which backend it targets or how complex the SQL is. The symptom looks like a backend problem from the application’s perspective, but the databases are fine.

The root cause is almost always an expensive regular expression in match_pattern or replace_pattern on a high-traffic mysql_query_rules entry. Every query passes through the rule chain sequentially. A regex that takes 100 microseconds to evaluate costs 100 microseconds on every query, every time. At thousands of queries per second across four worker threads (the mysql-threads default), the CPU budget is consumed by pattern matching, leaving no cycles for query routing and result forwarding.

Query Processor mechanics

ProxySQL’s Query Processor parses every incoming SQL statement and walks it through the ordered mysql_query_rules table. Each rule can match on match_pattern (regex against the full query text), match_digest (regex against the normalized digest), schemaname, username, client_addr, or flagIN/flagOUT chains. Rules are evaluated in rule_id order. The first matching rule with apply=1 terminates evaluation. Rules without apply=1 allow evaluation to continue.

The regex engine is configurable via mysql-query_processor_regex. The default is PCRE (value 1). RE2 (value 2) is also available and guarantees linear-time matching, meaning it cannot exhibit catastrophic backtracking. However, RE2 cannot apply both CASELESS and GLOBAL modifiers simultaneously via re_modifiers, which limits some use cases.

Worker threads (mysql-threads, default 4, maximum 255) handle all client connections and query processing via non-blocking event loops. Each thread manages many connections, but if a thread blocks on CPU-intensive regex evaluation, every connection on that thread stalls. Because mysql-threads is a startup parameter, changing it requires LOAD MYSQL VARIABLES TO RUNTIME, SAVE MYSQL VARIABLES TO DISK, and a full ProxySQL restart.

The aggregate time spent inside the Query Processor is tracked as Query_Processor_time_nsec in stats_mysql_global. This counter is only populated when mysql-stats_time_query_processor is set to true. It is disabled by default because the timing measurement itself adds approximately 0.3 microseconds of latency per query. If you have never enabled it, Query_Processor_time_nsec will be zero regardless of actual processing cost.

Per-rule timing is not available in upstream ProxySQL. You must infer the culprit by correlating rule ordering, hit counts, and pattern complexity.

The diagnostic triad:

ProxySQL process CPU is saturated (all worker threads near 100%)
Backend pool has free connections (ConnFree > 0, backends not saturated)
Client latency is uniformly elevated (not specific to one hostgroup or digest)

flowchart TD
    A[High client latency] --> B{ConnFree > 0?}
    B -->|Yes, backends idle| C{ProxySQL CPU near 100%?}
    B -->|No, pool empty| D[Backend pool starvation]
    C -->|Yes| E{Query_Processor_time_nsec elevated?}
    C -->|No| F[Check TLS, connection churn, or network]
    E -->|Yes| G[Query rule CPU overload]
    E -->|Not tracked| H[Enable mysql-stats_time_query_processor]

Common causes

Cause	What it looks like	First thing to check
Catastrophic backtracking in PCRE regex	CPU spikes after a query pattern change; latency is proportional to query text length	Review `match_pattern` for nested quantifiers like `(a+)+` or `(a\|a)*`
Broad `match_pattern` on high-traffic queries	All queries slow down; hits on the suspect rule are very high	Check `stats_mysql_query_rules.hits` against rule ordering
Long rule chain without early termination	Many rules with `apply=0`; every query walks most of the chain	Count active rules and check which have `apply=1`
`replace_pattern` with complex substitution	CPU spike correlates with a rule that rewrites query text	Review rules where `replace_pattern` is non-null
New rule deployed via config change	Latency spike correlates with a recent `LOAD MYSQL QUERY RULES TO RUNTIME`	Check deployment history and rule table version

Quick checks

These are read-only queries against the admin interface (default port 6032). They do not modify configuration.

# Check ProxySQL process CPU
ps -p $(pidof proxysql) -o pid,pcpu,pmem,rss

# Per-thread CPU to detect worker thread saturation
ps -L -p $(pidof proxysql) -o pid,lwp,pcpu

# Check worker thread count and key 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 IN ('MySQL_Thread_Workers','Questions','Query_Processor_time_nsec','Active_Transactions');"

# Verify backends are idle (ConnFree > 0 confirms not a pool issue)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, Latency_us FROM stats_mysql_connection_pool;"

# Check which rules are matching and how often (hits reset on rule reload)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;"

# Review active regex engine and key variables
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT variable_name, variable_value FROM global_variables \
      WHERE variable_name IN ('mysql-query_processor_regex','mysql-threads','mysql-stats_time_query_processor');"

# Review all runtime query rules for pattern complexity
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, active, match_pattern, match_digest, replace_pattern, destination_hostgroup, apply \
      FROM runtime_mysql_query_rules ORDER BY rule_id;"

How to diagnose it

Confirm the pattern. Verify that ProxySQL process CPU is near 100% across all worker threads (not just one), backend connections are free (ConnFree > 0), and client latency is elevated. This distinguishes query rule CPU overload from backend connection pool exhaustion, backend query slowness, or network latency.
Check whether query processor timing is tracked. If Query_Processor_time_nsec is zero and you have never set mysql-stats_time_query_processor=true, the counter is simply not populated. Enable it to get a baseline. Note the 0.3 microsecond per-query overhead.
List all active rules and identify hot paths. Pull stats_mysql_query_rules.hits to see which rules are evaluated most frequently. A rule with millions of hits is on the hot path. Any regex cost on that rule is multiplied by the hit count.
Review regex patterns for complexity. Look for long patterns, nested quantifiers, alternation with overlap, and patterns that match against match_pattern (full query text) rather than match_digest (normalized digest text, which is shorter and faster to match).
Binary search by disabling rules in groups. Since per-rule timing is not available, disable the bottom half of the rule chain (active=0), load to runtime, and observe whether CPU drops. Repeat until you isolate the specific rule. Warning: disabling rules changes routing behavior. Test off-hours or on a staging instance first.
Test the suspect regex in isolation. Extract the match_pattern from the suspect rule and run it against representative query strings using a PCRE test harness. Measure evaluation time. A pattern that takes more than a few microseconds on a typical query string is the problem.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
ProxySQL process CPU (per-thread)	Worker thread CPU is the hard ceiling on throughput	All worker threads near 100% sustained
`Query_Processor_time_nsec`	Aggregate time spent in the Query Processor	Rate increasing disproportionate to `Questions` rate
`ConnFree` per backend	Distinguishes proxy-side CPU from backend pool exhaustion	`ConnFree > 0` while latency is high = proxy bottleneck
`stats_mysql_query_rules.hits`	Shows which rules are on the hot path	High-hit rule with complex `match_pattern`
`MySQL_Thread_Workers`	Confirms the hard parallelism ceiling	CPU saturated with default 4 threads
Client query latency (from histogram)	Uniform latency increase signals proxy-side delay	`stats_mysql_commands_counters` histogram shifts toward higher buckets across all command types

Fixes

Emergency: disable the suspect rule

If the proxy is causing visible client impact, disable the rule immediately and load to runtime.

# Disable the suspect rule and activate immediately
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "UPDATE mysql_query_rules SET active=0 WHERE rule_id=<rule_id>; LOAD MYSQL QUERY RULES TO RUNTIME;"

This is safe and reversible. If CPU drops immediately, you have confirmed the rule was the cause. Save to disk only after confirming the fix.

Switch regex engine from PCRE to RE2

RE2 guarantees linear-time matching and cannot exhibit catastrophic backtracking. If your re_modifiers do not require both CASELESS and GLOBAL simultaneously, switching is low-risk.

# Switch to RE2 regex engine
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SET mysql-query_processor_regex=2; LOAD MYSQL VARIABLES TO RUNTIME;"

Test in a staging environment first. RE2 does not support backreferences or lookahead. If any existing rule uses unsupported syntax, that rule will stop matching after the switch. Check ProxySQL logs for regex compilation errors.

Replace `match_pattern` with `match_digest`

match_digest matches against the normalized query digest, where literal values are replaced with ?. For routing rules that do not need to inspect literal values, this is significantly faster because the digest text is shorter and more predictable than the full query.

# Example: rewrite a rule to use match_digest instead of match_pattern
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "UPDATE mysql_query_rules SET match_pattern=NULL, match_digest='^SELECT.*FROM users' WHERE rule_id=<rule_id>; LOAD MYSQL QUERY RULES TO RUNTIME;"

Rules that need to match specific literal values cannot use match_digest, since WHERE id = 42 normalizes to WHERE id = ?.

Restructure the rule chain with flagIN/flagOUT

Every query walks the chain from rule_id 1 until it hits a match with apply=1. Use flagIN/flagOUT chaining to create rule pipelines: a query matched by a rule with flagOUT=N only evaluates rules with flagIN=N next. This skips irrelevant rules entirely.

For static routing by username and schemaname (no regex needed), consider mysql_query_rules_fast_routing. This table provides hash-based O(1) routing without any regex evaluation.

Increase `mysql-threads` (requires restart)

If the rule chain is already optimized and the proxy is still CPU-bound, increasing mysql-threads raises the parallelism ceiling.

# Increase thread count (requires restart to take effect)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SET mysql-threads=8; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;"
# Then restart ProxySQL

mysql-threads above 16 can degrade throughput on high-core-count systems due to context switching overhead. Test before committing to high thread counts in production. This is a capacity fix, not a rule-complexity fix: if the regex itself is pathological, more threads only delays the saturation point.

Prevention

Review every new query rule for regex complexity before loading to runtime. Test the pattern against representative query strings and measure evaluation time.
Prefer match_digest over match_pattern unless the rule specifically needs to inspect literal values in the query text.
Keep the rule chain short. Every query walks from rule_id 1 until it hits apply=1. Use apply=1 aggressively to terminate evaluation early.
Enable mysql-stats_time_query_processor=true permanently on production instances. The 0.3 microsecond per-query overhead is negligible compared to the cost of flying blind during a CPU overload incident.
Consider RE2 as the default regex engine (mysql-query_processor_regex=2) if no rules require PCRE-specific features.
Audit rule changes as configuration events. Track LOAD MYSQL QUERY RULES TO RUNTIME in your change management system. A rule addition is the most common trigger for this failure pattern.

How Netdata helps

Per-second CPU metrics on the ProxySQL host reveal thread saturation as it develops, not minutes after. Per-core breakdown shows whether all worker threads are pegged or only one is hot-spotting.
Query_Processor_time_nsec trend correlates directly with rule complexity. A step change after a rule deployment is the clearest signal that a new regex is expensive.
ConnFree per backend distinguishes proxy-side CPU saturation from backend pool exhaustion in a single dashboard. Free backends combined with high proxy CPU points to this pattern.
stats_mysql_query_rules.hits collected over time shows which rules are on the hot path, so you can prioritize optimization toward high-traffic rules rather than guessing.