Hostgroup connection-pool statistics

Use hostgroup connection-pool statistics when an application request is slow but its SQL runs quickly on an otherwise available database connection. These statistics show whether requests wait for a backend connection before the database can execute their work.

The tables are available in ProxySQL 3.1.12 and 4.0.12 for both MySQL and PostgreSQL. They are not available in ProxySQL 3.0. No additional collection setting is required. Query them through ProxySQL Admin, normally on port 6032.

Read the hostgroup your application uses

For MySQL traffic routed to hostgroup 20:

SELECT hostgroup, acquisitions_total, waits_total,
       wait_time_us_total, waiters
FROM stats_mysql_hostgroup_connection_pool
WHERE hostgroup=20;

For PostgreSQL:

SELECT hostgroup, acquisitions_total, waits_total,
       wait_time_us_total, waiters
FROM stats_pgsql_hostgroup_connection_pool
WHERE hostgroup=20;

Replace 20 with the actual destination hostgroup. Routing rules and active transactions can affect that destination, so check the group serving the request. A group number does not identify a single physical database: one server can appear in several groups, and a group can contain several servers.

An acquisition is not necessarily a new TCP connection, a SQL statement, or an application request. A backend retained for a transaction can execute several statements without another acquisition. A wait also does not prove the configured connection limit is too low: server availability, routing constraints, and occupied connections can all affect acquisition.

Compare two observations

Save the result with a timestamp, then repeat the same query during the incident, for example after 60 seconds. For the same hostgroup, subtract the first counter values from the second:

Keep waiters alongside those changes. Do not subtract it to count waits: a queue can grow and empty repeatedly between samples. A hostgroup can have zero waiters now after substantial waiting earlier, or many waiters whose final durations have not yet been added to the time counter.

Wait time is recorded when a wait finishes. Waits crossing a sample boundary therefore make change in wait time / change in waits unsuitable as an exact average for one set of requests. Treat it as an indication of pressure, not a request-latency measurement. Concurrent waits can accumulate more than one second of wait time per second of wall-clock time.

Follow occupied connections

Compare the hostgroup queue with the per-server pool view:

SELECT hostgroup, srv_host, srv_port, status,
       ConnUsed, ConnFree, MaxConnUsed, ConnERR, Queries
FROM stats_mysql_connection_pool
WHERE hostgroup=20
ORDER BY srv_host, srv_port;

Use stats_pgsql_connection_pool for PostgreSQL; these selected columns have the same names. ConnUsed and ConnFree describe current pool usage, while MaxConnUsed records peak usage and ConnERR accumulates connection failures. See the MySQL statistics reference or PostgreSQL statistics reference for the complete tables.

If waiting persists, inspect long transactions, slow backend queries, sessions that retain a backend, and servers that are unavailable or shunned. Compare configured max_connections in runtime_mysql_servers or runtime_pgsql_servers with actual usage. Raise limits only when the databases have room for the added concurrency; otherwise, additional simultaneous work can prolong the delay.

These counters do not measure database lock waits, network latency, or waiting inside the application’s own connection pool. Continue investigating those layers if the hostgroup is not queueing during the slowdown.

Coordinate resets and monitoring

The normal tables do not clear counters. The corresponding reset tables are:

SELECT * FROM stats_mysql_hostgroup_connection_pool_reset;
SELECT * FROM stats_pgsql_hostgroup_connection_pool_reset;

Reading a reset table returns the current counter window and starts a new Admin window for that protocol’s hostgroups. It does not clear live waiters. Filtering a reset query to one hostgroup does not make the reset exclusive to that row. Prefer the normal tables for shared monitoring. If another observer resets the window, or ProxySQL restarts, discard the old baseline and begin a new interval instead of reporting negative activity.

Prometheus exposes proxysql_connpool_acquisitions_total, proxysql_connpool_waits_total, proxysql_connpool_wait_time_seconds_total, and proxysql_connpool_waiters, with hostgroup and protocol labels. Protocol values are mysql and pgsql. Use rates for counters and the current gauge for waiters. Prometheus wait time is in seconds, whereas the Admin table uses microseconds. Reading an Admin reset table does not reset Prometheus counters.