How ProxySQL actually works in production: a mental model for operators

When ProxySQL fails in production, the layer where it fails determines what you see: connection exhaustion at the frontend pool, mis-routing in the query processor, pool starvation from multiplexing collapse, or false health decisions in the monitor module. Understanding these layers is prerequisite to debugging any of them.

This article covers the request path from client connection to backend response, the components that make routing and pooling decisions, and the failure modes characteristic of each. The ProxySQL runbooks in this section build on the terminology and component relationships described here.

What it is and why it matters

ProxySQL fully parses the MySQL wire protocol on the frontend, understands every query, and makes per-query decisions about routing, caching, and connection management. It is not a TCP relay. It speaks the MySQL wire protocol only and does not support PostgreSQL.

This is what enables:

• Connection multiplexing: N application connections served by M backend connections, where M is much smaller than N. The primary reason most teams deploy ProxySQL.
• Query routing: queries sent to different hostgroups based on rule matching. Read/write splitting is the most common application.
• Query caching: result sets cached in process memory with TTL-based expiration. No invalidation on data change; it is a time-bounded stale-read cache.
• Backend health monitoring: automatic detection of backend failures, replication lag, and role changes via background probe threads.

Each has characteristic failure modes: multiplexing collapses when application behavior pins connections, query rules mis-route writes to read replicas, the cache churns with high-cardinality queries, and the monitor module makes false decisions with aggressive thresholds. The internal structure is what lets you tell these apart under pressure.

How it works

The diagram shows the path a query takes through ProxySQL, from client connection to result return. The monitor module operates independently, probing backends in the background.

flowchart TD
    C[Client] -->|port 6033| FP[Frontend pool]
    FP -->|auth: mysql_users| QP[Query processor + rules]
    QP -->|cache hit| QC[Query cache, TTL-based]
    QP -->|miss or no cache rule| MX[Multiplexing engine]
    QC -->|serve result| C
    MX -->|borrow connection| BP[Backend pool, per hostgroup]
    BP -->|route query| BE[MySQL backends]
    BE -->|result set| BP
    BP -->|return connection| MX
    MX -->|return result| C
    MON[Monitor module] -.->|health probes| BE

Frontend connection pool

Client connections arrive on the data-plane port (default 6033). Each client establishes a MySQL session with ProxySQL itself, not with any backend. ProxySQL authenticates the client against its own mysql_users table, independently of backend credentials. The session carries state: user, schema, transaction status, session variables, autocommit mode.

The frontend pool has a global connection limit (mysql-max_connections, default 2048). Per-user limits can also be set in mysql_users. When either limit is reached, new connections are rejected.

Query processor and mysql_query_rules

Every incoming query is parsed and normalized into a digest. The query processor evaluates the query against mysql_query_rules, an ordered list matched by rule_id in ascending order. Rules can match on regex patterns (match_digest on the normalized digest, or match_pattern on the full query text), schema, user, client address, and flagIN/flagOUT chains.

The first matching rule with apply=1 terminates evaluation and determines: target hostgroup, whether to cache (cache_ttl), whether to mirror, query timeout, delay, and query rewriting via replace_pattern. If no rule matches, the query falls to the user’s default_hostgroup.

Complex regex in match_pattern is evaluated on every matching query and is more expensive than match_digest, which operates on the shorter normalized form. A poorly designed rule chain is a latency tax on every query; under sufficient load, it can saturate worker threads.

TTL query cache

If a matching query rule sets cache_ttl > 0, ProxySQL checks its in-memory query cache before routing to a backend. The cache is keyed by query digest, user, and schema. A cache hit returns the result immediately without touching any backend.

Limitations: invalidation is TTL-based only; there is no API to purge entries when underlying data changes. Cache size is controlled by mysql-query_cache_size_MB (default 256 MB), with LRU eviction when full. The cache may not work correctly with prepared statements depending on ProxySQL version and configuration.

Multiplexing engine

This is the critical abstraction that makes ProxySQL valuable. When a client sends a query that is not cached, the multiplexing engine borrows a backend connection from the pool, routes the query, receives the result, returns it to the client, and returns the backend connection to the pool for reuse by another client.

Multiplexing breaks when session state exists on the connection. Conditions that disable multiplexing include:

• Open transactions (BEGIN, START TRANSACTION, until COMMIT/ROLLBACK)
• SET commands that change session variables
• Temporary tables
• LOCK TABLES
• User-defined variables (@var)
• GET_LOCK()
• Prepared statements

When multiplexing is disabled for a session, a backend connection is pinned to that client for the duration. This is visible as Client_Connections_hostgroup_locked in the stats. If many sessions are pinned, the multiplexing ratio degrades toward 1:1, eliminating the pooling benefit and potentially exhausting backend connections.

Backend connection pool, per hostgroup

Backend connections are organized by hostgroup. Each hostgroup is a set of MySQL backends that serve the same class of queries. In a read/write split deployment, a writer hostgroup contains the primary and a reader hostgroup contains replicas.

Each backend entry in mysql_servers has a max_connections limit: ProxySQL’s self-imposed ceiling on connections to that backend. This is independent of MySQL’s own max_connections setting. The two interact: the sum of ProxySQL’s max_connections across all ProxySQL instances, plus connections from other consumers (direct admin connections, monitoring tools, replication threads), must not exceed the backend MySQL’s actual max_connections.

Backend server status is tracked per hostgroup:

Monitor module

The monitor module runs on its own background threads and continuously probes backends:

• Connect checks: can ProxySQL establish a TCP connection to the backend?
• Ping checks: is the backend responsive to mysql_ping()?
• Read-only checks: is @@read_only set? Used for automatic read/write role detection.
• Replication lag checks: how far behind is this replica? When lag exceeds max_replication_lag, the backend is shunned.
• Group Replication and Galera checks: node state for InnoDB Cluster, Group Replication, and Galera/PXC topologies.

Monitor results drive backend status transitions. The monitor uses its own credentials (mysql-monitor_username, mysql-monitor_password), separate from application credentials. If monitor credentials expire, health checks fail and ProxySQL shuns backends it cannot verify.

Admin interface and three-layer config

ProxySQL exposes a MySQL-protocol admin interface (default port 6032) for configuration and stats retrieval. This is the control plane, separate from the data plane.

Configuration exists in three layers:

• MEMORY: staging area. Changes made via the admin interface land here first.
• RUNTIME: active configuration. LOAD ... TO RUNTIME activates MEMORY changes.
• DISK: persistent SQLite storage. SAVE ... TO DISK persists changes across restarts.

A change is not live until loaded to RUNTIME. A change is not durable until saved to DISK. On restart, ProxySQL loads from DISK. Config drift between layers is a common operational hazard: an operator changes config, applies it to RUNTIME, forgets to SAVE to DISK, and the next restart reverts the fix.

Thread pool

Worker threads (mysql-threads, default 4) handle all client connections and query processing using non-blocking I/O (epoll). Each thread manages many connections via an event loop. The thread count is a hard ceiling on parallelism, set at startup and cannot be changed at runtime without restart.

If worker threads saturate (complex regex evaluation, high connection count, TLS overhead), queries queue in the event loop. Latency increases uniformly across all queries regardless of backend. Adding more backends does not help when the proxy itself is the bottleneck.

Where it shows up in production

Deployment patterns and their operational concerns:

• Standalone: single instance. Simple but a single point of failure unless a VIP is managed externally.
• ProxySQL Cluster: multiple instances synchronizing configuration via proxysql_servers. Cluster sync is tracked in stats_proxysql_servers_checksums. Introduces split-brain risk during network partitions.
• Sidecar: ProxySQL runs on the same host as the application. Reduces network latency but creates resource contention (CPU, memory, file descriptors).
• Galera or Group Replication-aware: special hostgroup tables (mysql_galera_hostgroups, mysql_group_replication_hostgroups) automate writer/reader role routing based on cluster state.

Tradeoffs and when to use it

ProxySQL adds a stateful component to your database path. The benefits come with costs.

Added latency: every query passes through parsing, rule matching, and connection borrowing. ProxySQL’s overhead is typically under 1 ms per query, but complex regex rules or large result sets increase this. Under worker thread saturation, latency is added uniformly to every query.

Configuration complexity: the three-layer config model, the query rule chain, and the multiplexing engine all introduce state that must be understood. Config drift, rule mis-routing, and multiplexing collapse are failure modes that do not exist with direct connections.

Multiplexing assumptions: capacity models for ProxySQL typically assume a healthy multiplexing ratio (10:1 or better). If application behavior silently disables multiplexing (ORMs setting session variables, prepared statements, long transactions), the proxy degrades to a 1:1 mapping with overhead. The capacity plan becomes fiction.

Connection storms after restart: the backend connection pool starts empty. The first burst of queries creates backend connections synchronously, which can overwhelm backend MySQL’s connection handling.

Signals to watch in production

How Netdata helps

• Per-second collection of stats_mysql_global counters reveals multiplexing degradation (locked connections rising relative to Client_Connections_connected) before it becomes a backend connection outage. Polling at minute granularity misses the transition.
• Correlating backend status transitions with monitor check failure rates (connect, ping, read-only, replication lag) distinguishes real backend failures from monitor false positives. A backend flapping between ONLINE and SHUNNED with healthy direct connections points to monitor threshold tuning, not a backend problem.
• Per-backend pool metrics (ConnUsed, ConnFree, ConnERR from stats_mysql_connection_pool) show pool pressure building on specific backends before ConnPool_get_conn_failure starts climbing.
• Memory subsystem metrics (jemalloc_resident, jemalloc_allocated, query_digest_memory) catch slow memory growth that leads to OOM kills, and distinguish useful allocation from fragmentation.
• Process-level signals (per-core CPU, file descriptor counts) catch thread saturation and FD exhaustion that ProxySQL does not expose in its own stats tables. FD exhaustion is a binary cliff: every connection (client plus backend plus monitor) consumes one FD, and exhaustion causes immediate total failure.

Related guides

• ProxySQL monitoring checklist: the signals every production proxy needs
• ProxySQL monitoring maturity model: from survival to expert
• ProxySQL backend SHUNNED: why a healthy backend gets pulled out of rotation
• ProxySQL zero ONLINE backends in a hostgroup: total outage for that traffic class
• ProxySQL OFFLINE_SOFT vs OFFLINE_HARD vs SHUNNED: what each backend status means
• ProxySQL backend flapping between ONLINE and SHUNNED: monitor-induced oscillation
• ProxySQL monitor check failures: connect, ping, read-only, and replication-lag probes failing
• ProxySQL MySQL_Monitor_Workers is zero: health checks stopped and status is stale
• ProxySQL backend connection pool exhausted: queries queuing for a free connection
• ProxySQL error 1040 Too many connections: the backend MySQL rejecting the pool
• ProxySQL error 9001 Max connect timeout reached while reaching hostgroup
• ProxySQL ConnPool_get_conn_failure rising: the most direct pool-starvation signal