ProxySQL Statistics Overview
ProxySQL exposes rich real-time statistics through the stats database in the Admin interface. These tables give you visibility into query performance, connection pool health, query cache effectiveness, and overall throughput.
stats Database Tables
| Table | Description |
|---|---|
| stats_mysql_global | Global counters — connections, queries, bytes |
| stats_mysql_connection_pool | Per-server connection pool stats |
| stats_mysql_query_digest | Per-query-type statistics (most useful!) |
| stats_mysql_query_digest_reset | Same as above but resets counters after read |
| stats_mysql_query_rules | Hit counts per query rule |
| stats_mysql_processlist | Active connections/queries |
| stats_mysql_users | Per-user frontend connection counts |
| stats_mysql_free_connections | Free connections in pool |
| stats_mysql_prepared_statements_info | Prepared statement stats |
| stats_proxysql_servers_checksums | ProxySQL cluster checksums |
| stats_proxysql_servers_metrics | ProxySQL cluster node metrics |
stats_mysql_global — Key Counters
SQL — Global Stats
SELECT variable_name, variable_value
FROM stats.stats_mysql_global
ORDER BY variable_name;
-- Most important counters:
SELECT variable_name, variable_value
FROM stats.stats_mysql_global
WHERE variable_name IN (
'Active_Transactions',
'Client_Connections_aborted',
'Client_Connections_connected',
'Client_Connections_created',
'Com_autocommit',
'Com_autocommit_filtered',
'Com_commit',
'Com_commit_filtered',
'Com_rollback',
'Com_rollback_filtered',
'ConnPool_get_conn_failure',
'ConnPool_get_conn_success',
'MySQL_Monitor_Workers',
'Questions',
'Server_Connections_aborted',
'Server_Connections_connected',
'Server_Connections_created',
'Slow_queries'
); stats_mysql_query_digest — Query Analysis
This is the most powerful stats table. It shows normalized queries, which hostgroup they went to, execution counts and timing.
SQL — Query Digest Analysis
-- Top 20 queries by execution count
SELECT hostgroup, schemaname, username,
digest_text,
count_star AS executions,
sum_time AS total_time_us,
min_time AS min_us,
avg_time AS avg_us,
max_time AS max_us,
first_seen, last_seen
FROM stats.stats_mysql_query_digest
ORDER BY count_star DESC
LIMIT 20;
-- Slowest queries by average time
SELECT hostgroup, digest_text,
count_star, avg_time, max_time
FROM stats.stats_mysql_query_digest
ORDER BY avg_time DESC
LIMIT 20;
-- Queries going to wrong hostgroup (debug routing)
SELECT hostgroup, digest_text, count_star
FROM stats.stats_mysql_query_digest
WHERE hostgroup = 10
AND digest_text LIKE 'SELECT%'
ORDER BY count_star DESC;
-- Reset digest stats
SELECT * FROM stats.stats_mysql_query_digest_reset LIMIT 1; stats_mysql_users — Per User Stats
SQL — User Stats
-- Frontend connections per user
SELECT username, frontend_connections, frontend_max_connections
FROM stats.stats_mysql_users
ORDER BY frontend_connections DESC; stats_mysql_processlist — Active Queries
SQL — Active Processlist
-- See all active queries right now
SELECT ThreadID, SessionID, user, db, cli_host, cli_port,
hostgroup, srv_host, srv_port, command, time_ms, info
FROM stats.stats_mysql_processlist
ORDER BY time_ms DESC;
-- Find long-running queries (> 5 seconds)
SELECT ThreadID, user, hostgroup, srv_host, time_ms, info
FROM stats.stats_mysql_processlist
WHERE time_ms > 5000
ORDER BY time_ms DESC; stats_mysql_query_rules — Rule Hit Counts
SQL — Rule Hit Counts
-- See which query rules are being hit
SELECT rule_id, hits, match_pattern, destination_hostgroup, comment
FROM stats.stats_mysql_query_rules
ORDER BY hits DESC;
-- Rules with zero hits (possibly unused or misconfigured)
SELECT rule_id, match_pattern, comment
FROM stats.stats_mysql_query_rules
WHERE hits = 0; Resetting Statistics
SQL — Reset Stats
-- Reset all global counters
SELECT * FROM stats.stats_mysql_global;
-- Reset query digest (returns current data then clears)
SELECT * FROM stats.stats_mysql_query_digest_reset;
-- Flush ProxySQL logs to disk
PROXYSQL FLUSH LOGS;