MySQL Connection Pooling, Multiplexing & Caching

This page explains how MySQL prepared statements interact with ProxySQL’s connection pool, why holding open prepared statements affects connection sharing, how the global statement cache is bounded and cleaned up, and how to monitor all of these behaviours through the admin interface.

Connection Pooling Interaction

Statements live on specific backend connections

When a client prepares a statement, ProxySQL sends COM_STMT_PREPARE to a backend connection and receives a numeric statement ID in return. That ID is assigned by the MySQL server and is meaningful only within the context of that single backend connection. A different backend connection — even one in the same hostgroup — knows nothing about it.

ProxySQL maintains a mapping table that links each process-wide global statement ID to the per-connection backend numeric ID for every backend connection that has been asked to prepare that statement. This means the same logical statement can exist on multiple backend connections simultaneously, each with its own local numeric ID, all pointing at the same global entry.

Pooled connections retain their prepared statements

When a backend connection is returned to the pool after serving a client request, any prepared statements on that connection are left in place. ProxySQL does not tear them down. The next client that borrows the same backend connection may reuse those already-prepared statements without sending a new COM_STMT_PREPARE to the backend, provided the global-to-backend ID mapping is still valid for that connection.

This lazy retention improves throughput for workloads where the same statements are prepared repeatedly. It does, however, mean that backend connections accumulate prepared statement state over time.

Connection destruction and cleanup

When a backend connection is destroyed — due to a backend error, a connection limit being reached, or an idle timeout — ProxySQL walks the global-to-backend ID mappings associated with that connection and decrements the backend reference count on each global entry. If both the client reference count and the backend reference count on a global entry reach zero, that entry becomes a candidate for eviction from the global cache. The actual removal is performed by a background purge cycle rather than inline with the connection teardown, so the global entry may persist for a short period after all references are gone.

Multiplexing Constraints

Why prepared statements disable multiplexing

ProxySQL’s multiplexing feature allows a single backend connection to be shared among multiple client sessions: a client borrows the connection, sends a query, and returns it to the pool so another client can use it. This only works when the backend connection is stateless between requests.

Prepared statements break that assumption. A prepared statement numeric ID is scoped to the backend connection where it was created. If a client holds an open prepared statement and ProxySQL were to route that client’s next COM_STMT_EXECUTE to a different backend connection, the backend numeric ID would be wrong, and the backend would return an error. To avoid this, ProxySQL disables multiplexing for any client session that has at least one open prepared statement.

In practical terms: once a client prepares a statement via the binary protocol, ProxySQL assigns that client a dedicated backend connection. Other client sessions cannot borrow that connection until all of the client’s prepared statements are closed.

When multiplexing resumes

Multiplexing is re-enabled for a client session when the client explicitly closes all of its open prepared statements using COM_STMT_CLOSE. At that point ProxySQL returns the backend connection to the shared pool.

There is no automatic timeout that forces statements closed. If an application prepares a statement and never closes it, the associated backend connection remains pinned to that client session for its entire lifetime.

Text-protocol PREPARE is a permanent lock

When a client prepares a statement using the SQL text command PREPARE stmt_name FROM '...' rather than the binary protocol COM_STMT_PREPARE, ProxySQL also disables multiplexing. However, for text-protocol prepared statements there is no close mechanism that re-enables multiplexing. Once a session executes a text PREPARE, multiplexing is permanently disabled for that session regardless of whether the application later runs DEALLOCATE PREPARE.

This is a known limitation described in more detail on the Multiplexing page.

Impact on connection efficiency

Long-lived prepared statements pin backend connections. In a deployment with many client sessions each holding open statements for extended periods, the effective connection pool shrinks because pinned connections cannot be shared. This can lead to backend connection exhaustion before the configured maximum is reached.

Best practice: close prepared statements as soon as they are no longer needed. In ORM and driver frameworks this usually means using scoped or short-lived prepared statement handles rather than caching them across the full lifetime of a database connection object.

Statement Cache Management

The global statement map

ProxySQL maintains a process-wide cache of MySQL prepared statement metadata. Every unique statement that has been prepared at least once is recorded in this global map, keyed by a digest computed from the SQL text, the connected username, and the current schema.

Each entry stores:

• The original SQL text
• Parameter and result-column metadata received from the MySQL server at prepare time
• A globally unique numeric ID used to cross-reference client and backend maps
• Client and backend reference counts

The global map is shared across all client sessions and all backend connections. When two clients prepare the same statement under the same user and schema, they share one global entry rather than creating two independent records.

Cache size limit

The size of the global map is bounded by the mysql-max_stmts_cache admin variable. When the number of cached entries reaches the configured limit, ProxySQL will not add new entries until space has been reclaimed by the background eviction process. The variable can be inspected and updated through the admin interface:

-- Check current limit
SELECT variable_name, variable_value
FROM global_variables
WHERE variable_name = 'mysql-max_stmts_cache';

-- Update the limit
SET mysql-max_stmts_cache = 20000;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Eviction heuristics

The background eviction process applies several conditions before removing a global entry:

1. Both reference counts must be zero. An entry with any active client or backend references is never evicted.
2. Minimum unused fraction threshold. Eviction only runs when the proportion of zero-reference entries exceeds a configured fraction of the total cache. This avoids unnecessary purge overhead when the cache is mostly active.
3. Rate-limited purge cycles. The purge loop runs on a timer, not on every statement close. This prevents bursts of COM_STMT_CLOSE operations from triggering continuous purge work.
4. ID recycling. When an entry is evicted its global numeric ID is returned to a free-list and can be reused by a future statement, keeping the ID space compact.

Normal cache lifecycle

During application startup the global cache grows steadily as new statements are prepared for the first time via COM_STMT_PREPARE. Once all distinct statements in the application have been prepared at least once, the cache stabilises. Unused entries — statements that belonged to connections that have since closed and are not actively referenced — accumulate gradually and are removed by the periodic purge cycle. Under normal conditions the cache reaches a steady state with a small fraction of zero-reference entries waiting for the next purge pass.

Memory Tracking

ProxySQL accounts for the memory consumed by MySQL prepared statement state at several levels:

• Query text: the raw SQL string stored in each global map entry
• Parameter metadata: type and nullability descriptors for each bind parameter
• Column metadata: result-set column descriptors returned by the MySQL server at prepare time
• Backend handles: per-connection bookkeeping for each global ID that has been prepared on that connection

Aggregate memory figures for these categories are exposed through the admin interface in the stats_memory_metrics table. Querying this table regularly is useful when diagnosing memory growth in long-running deployments:

SELECT *
FROM stats_memory_metrics
WHERE variable_name LIKE '%stmt%';

A steadily growing prepared-statement memory figure usually indicates one of two patterns:

• The application generates dynamic SQL with varying literals rather than parameterised queries, causing each unique statement text to be treated as a separate cache entry and triggering a COM_STMT_PREPARE to the backend for each one.
• Prepared statements are being accumulated but never closed via COM_STMT_CLOSE, so their reference counts never reach zero and eviction cannot reclaim them.

Both patterns are identifiable by combining memory metrics with the statement count and reference-count columns in the stats tables described in the next section.

Monitoring and Observability

Stats table

ProxySQL exposes MySQL prepared statement cache state through the stats_mysql_prepared_statements_info admin table. It presents one row per cached global statement and includes the SQL text, reference counts, associated user and schema context, and memory consumption figures.

-- View all cached MySQL prepared statements ordered by active client usage
SELECT *
FROM stats_mysql_prepared_statements_info
ORDER BY ref_count_client DESC;

-- View statements with no active references (eviction candidates)
SELECT global_statement_id, query, ref_count_client, ref_count_backend
FROM stats_mysql_prepared_statements_info
WHERE ref_count_client = 0 AND ref_count_backend = 0;

-- Count total cached statements and how many are unreferenced
SELECT
    COUNT(*) AS total_cached,
    SUM(CASE WHEN ref_count_client = 0 AND ref_count_backend = 0 THEN 1 ELSE 0 END) AS zero_ref
FROM stats_mysql_prepared_statements_info;

Key metrics to watch

Related Documentation

• MySQL Prepared Statements Architecture
• MySQL Prepared Statement Lifecycle
• Multiplexing