At 2:40 AM, a fintech team's on-call engineer was staring at elevated p99 latencies on their MySQL cluster with no obvious culprit in MySQL's own slow query log. The application reported timeouts, but SHOW PROCESSLIST showed nothing obviously stuck. It was only when the engineer pulled SELECT * FROM stats.stats_mysql_query_digest directly from ProxySQL that the issue became clear: a single query pattern — an ORM-generated SELECT with a missing index — was executing 4,200 times per minute, each taking 680 ms on average, and consuming 87% of all backend connection time. MySQL's slow query log had missed it because the per-execution time sat just under the long_query_time threshold. ProxySQL saw the aggregate. That kind of visibility — invisible to MySQL itself — is exactly what ProxySQL's built-in statistics schema delivers, and most teams using ProxySQL have never fully explored it.
- ProxySQL maintains a rich internal
statsschema that tracks query digest performance, connection pool utilization, backend health, and global counters — all queryable with standard SQL. stats_mysql_query_digestis the single most powerful monitoring table in ProxySQL: it shows query frequency, total and average latency, error rates, and which hostgroup each query was routed to.stats_mysql_connection_poolexposes per-hostgroup connection states — use it to detect pool saturation before it causes client timeouts.- The
monitorschema records every backend health check result, giving you a timestamped audit trail of replication lag alerts and connect failures. - Pair ProxySQL's stats with prometheus-proxysql-exporter and Grafana to build dashboards that alert on latency regression, pool starvation, and backend failures in real time.
ProxySQL's Stats Schema Overview
ProxySQL exposes monitoring data through several internal SQLite-backed schemas accessible on the admin interface (default port 6032). Unlike MySQL's performance_schema, these tables are designed specifically for operational monitoring of the proxy layer itself — routing decisions, connection pooling behavior, and backend health. You query them just like regular MySQL tables, using any MySQL-compatible client connected to the ProxySQL admin port.
# Connect to ProxySQL admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL Admin> 'The schemas you will interact with most frequently are:
- stats — real-time and cumulative counters for queries, connections, memory, and command types. This is the primary monitoring schema.
- monitor — timestamped results of every backend health check: ping latency, replication lag measurements, connect test results, and read-only flag checks.
- main — the configuration schema. Contains hostgroups, servers, query rules, and users. Read-only for monitoring purposes.
- disk — the persisted configuration store. Rarely queried directly during monitoring.
ProxySQL's stats counters are cumulative since the last reset (or since ProxySQL started). Use SELECT * FROM stats.stats_mysql_global WHERE Variable_Name = 'ProxySQL_Uptime'; to determine the accumulation window, then divide cumulative values by uptime seconds to derive per-second rates for alerting. You can also reset digests without restarting by running SELECT * FROM stats_mysql_query_digest_reset; — this atomically returns current data and clears the table.
Query Performance Monitoring (stats_mysql_query_digest)
stats_mysql_query_digest is the cornerstone of ProxySQL monitoring. ProxySQL normalizes every query it processes — stripping literal values and replacing them with placeholders — and aggregates execution statistics per unique query pattern. The result is a table that tells you exactly which query shapes are consuming the most time, how frequently they run, which backends they route to, and how often they fail. This works even for queries that complete too quickly to appear in MySQL's own slow query log.
Finding Your Top Latency Offenders
-- Top 20 queries by total execution time (the real load drivers)
SELECT
hostgroup,
schemaname,
username,
digest_text,
count_star AS executions,
ROUND(sum_time / 1000000, 2) AS total_time_sec,
ROUND(sum_time / count_star / 1000, 2) AS avg_latency_ms,
ROUND(min_time / 1000, 2) AS min_latency_ms,
ROUND(max_time / 1000, 2) AS max_latency_ms,
sum_errors AS errors
FROM stats.stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 20;The sum_time column is in microseconds. Dividing by count_star gives average latency per execution. Multiplying count_star by the monitoring interval gives queries-per-second. Sort by sum_time DESC rather than avg_latency_ms DESC to find queries that are actually contributing the most load — a query averaging 50 ms but executing 10,000 times per minute matters far more than a query averaging 500 ms that runs once per hour.
Identifying High-Frequency Low-Latency Queries
-- High-frequency queries that may be good candidates for query caching or batching
SELECT
digest_text,
count_star AS executions,
ROUND(sum_time / count_star / 1000, 2) AS avg_latency_ms,
hostgroup,
first_seen,
last_seen
FROM stats.stats_mysql_query_digest
WHERE count_star > 10000
AND sum_time / count_star < 5000 -- Under 5ms average
ORDER BY count_star DESC
LIMIT 15;Routing Verification: Are Reads Going to Replicas?
-- Verify read/write split routing is working correctly
-- Assumes hostgroup 10 = writer, hostgroup 20 = readers
SELECT
hostgroup,
SUM(count_star) AS total_queries,
ROUND(SUM(count_star) * 100.0 /
SUM(SUM(count_star)) OVER (), 2) AS pct_of_traffic,
ROUND(SUM(sum_time) / SUM(count_star) / 1000, 2) AS avg_latency_ms
FROM stats.stats_mysql_query_digest
GROUP BY hostgroup
ORDER BY hostgroup;Detecting Query Errors at the Proxy Layer
-- Queries with non-trivial error rates
SELECT
digest_text,
count_star AS executions,
sum_errors AS total_errors,
ROUND(sum_errors * 100.0 / count_star, 2) AS error_rate_pct,
hostgroup
FROM stats.stats_mysql_query_digest
WHERE sum_errors > 0
ORDER BY sum_errors DESC
LIMIT 20;ProxySQL's query digest normalizes literals but preserves query structure. A query like SELECT * FROM orders WHERE id IN (1,2,3) and SELECT * FROM orders WHERE id IN (4,5,6,7,8) will produce different digests because the IN-list length differs. If your ORM generates variable-length IN lists, expect many digest entries for what is logically the same query pattern. Consider enforcing a fixed IN-list size in your application or using a staging table approach for large IN queries.
Connection Pool Monitoring (stats_mysql_connection_pool)
ProxySQL manages separate connection pools for each (hostgroup, backend server) pair. stats_mysql_connection_pool gives you a live view of connection states across all pools: how many connections are free, how many are in use, how many have been created versus destroyed, and how long backends are taking to process queries. This is your first-line diagnostic for connection starvation and backend overload.
Current Pool State
-- Current connection pool state for all backends
SELECT
hostgroup,
srv_host,
srv_port,
status,
ConnUsed AS connections_in_use,
ConnFree AS connections_free,
ConnOK AS connections_ok_total,
ConnERR AS connections_error_total,
MaxConnUsed AS peak_connections_used,
Queries AS queries_routed,
ROUND(Bytes_data_sent / 1048576, 2) AS data_sent_mb,
ROUND(Bytes_data_recv / 1048576, 2) AS data_recv_mb,
ROUND(Latency_us / 1000, 2) AS avg_latency_ms
FROM stats.stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;The status column reports the backend's current state: ONLINE, SHUNNED, OFFLINE_SOFT, or OFFLINE_HARD. A backend in SHUNNED state has been temporarily removed from rotation due to too many connection errors — ProxySQL will retry it after shun_recovery_time_sec seconds. A backend in OFFLINE_HARD receives no new connections at all.
Connection Utilization Ratio
-- Connection utilization ratio per backend — alert if > 80%
SELECT
hostgroup,
srv_host,
ConnUsed AS in_use,
ConnFree AS free,
ConnUsed + ConnFree AS total_connections,
ROUND(ConnUsed * 100.0 / NULLIF(ConnUsed + ConnFree, 0), 1) AS utilization_pct,
MaxConnUsed AS peak_used
FROM stats.stats_mysql_connection_pool
WHERE status = 'ONLINE'
ORDER BY utilization_pct DESC;If utilization_pct stays above 80% on a backend, ProxySQL is approaching its max_connections limit for that backend. Before adding more connections, check whether the high utilization is caused by slow queries holding connections rather than genuine throughput demand. A 5-minute spike in avg_latency_ms on the same backend is a strong signal that query slowness — not connection shortage — is the root cause.
Backend Query Throughput Over Time
-- Compare query routing and latency across all hostgroup backends
SELECT
hostgroup,
srv_host,
Queries AS total_queries_routed,
ROUND(Latency_us / 1000, 2) AS current_avg_latency_ms,
ConnERR AS connection_errors,
status
FROM stats.stats_mysql_connection_pool
ORDER BY Queries DESC;Backend Server Health (stats_mysql_global and the monitor Schema)
ProxySQL's monitor module continuously tests each backend server: it pings them, checks replication lag, and verifies the read-only flag. Every result is logged in the monitor schema. This gives you a complete health history — not just the current state, but a timestamped trail of when backends went unhealthy, how long they stayed unhealthy, and what error they reported.
Global Stats Counters
-- ProxySQL global performance counters — key operational metrics
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',
'Server_Connections_connected',
'Server_Connections_aborted',
'Questions',
'Slow_queries',
'Query_Cache_Memory_bytes',
'Query_Cache_count_GET_OK',
'Query_Cache_count_SET',
'MySQL_Thread_Workers',
'Backend_query_time_nsec',
'ProxySQL_Uptime'
)
ORDER BY Variable_Name;The Client_Connections_aborted counter is particularly important: it increments whenever a client disconnects before ProxySQL finishes routing its query, which indicates application-side timeouts. Rising values here should trigger investigation of query latency and connection pool sizing simultaneously.
Replication Lag Monitoring History
-- Recent replication lag checks — last 50 results per backend
SELECT
hostname,
port,
time_start_us,
repl_lag,
error
FROM monitor.mysql_server_replication_lag_log
ORDER BY time_start_us DESC
LIMIT 50;-- Backends currently exceeding replication lag threshold
-- Useful for alerting when replicas fall behind
SELECT
hostname,
port,
MAX(repl_lag) AS max_lag_sec,
AVG(repl_lag) AS avg_lag_sec,
COUNT(CASE WHEN error IS NOT NULL THEN 1 END) AS error_count
FROM monitor.mysql_server_replication_lag_log
WHERE time_start_us > (UNIX_TIMESTAMP() - 300) * 1000000 -- Last 5 minutes
GROUP BY hostname, port
ORDER BY max_lag_sec DESC;Backend Connect Health Log
-- Recent connection test results — detect intermittent backend failures
SELECT
hostname,
port,
time_start_us,
connect_success,
connect_error
FROM monitor.mysql_server_connect_log
WHERE connect_success = 0
ORDER BY time_start_us DESC
LIMIT 30;Read-Only Flag Verification Log
-- Monitor check log for read_only flag changes — critical for read/write split
SELECT
hostname,
port,
time_start_us,
success_time_us,
read_only,
error
FROM monitor.mysql_server_read_only_log
ORDER BY time_start_us DESC
LIMIT 40;If your monitor logs show intermittent connect_success = 0 results for a backend that otherwise appears healthy, investigate network-level instability between the ProxySQL host and the MySQL backend before adjusting connect_retries_on_failure. ProxySQL will shun a backend after connect_retries_on_failure consecutive errors — if intermittent network drops are causing spurious shunning, the fix is network reliability, not more retries.
Setting Up Prometheus Metrics with proxysql-exporter
ProxySQL does not expose a native Prometheus endpoint, but the open-source proxysql-exporter (originally from Percona, now maintained at github.com/percona/proxysql_exporter) bridges ProxySQL's admin SQL interface to Prometheus-compatible metrics. It connects to the ProxySQL admin port, queries the stats tables on a configurable scrape interval, and exposes the results on an HTTP endpoint that Prometheus can scrape.
Installing the Exporter
# Download the latest release from GitHub
curl -Lo proxysql_exporter.tar.gz \
https://github.com/percona/proxysql_exporter/releases/download/v1.1.1/proxysql_exporter-1.1.1.linux-amd64.tar.gz
tar xvf proxysql_exporter.tar.gz
sudo mv proxysql_exporter-1.1.1.linux-amd64/proxysql_exporter /usr/local/bin/
# Verify the binary
proxysql_exporter --versionRunning the Exporter
# Run with ProxySQL admin credentials
# DATA_SOURCE_NAME format: user:password@tcp(host:port)/
export DATA_SOURCE_NAME='stats:stats@tcp(127.0.0.1:6032)/'
proxysql_exporter \
--web.listen-address=:42004 \
--web.telemetry-path=/metrics \
--collect.mysql_connection_pool=true \
--collect.mysql_connection_list=true \
--collect.mysql_status=truePrometheus Scrape Configuration
# prometheus.yml scrape config for ProxySQL exporter
scrape_configs:
- job_name: 'proxysql'
static_configs:
- targets: ['proxysql-host-1:42004', 'proxysql-host-2:42004']
scrape_interval: 15s
scrape_timeout: 10s
relabel_configs:
- source_labels: [__address__]
target_label: instance
regex: '([^:]+):\d+'
replacement: '$1'Key Prometheus Metrics to Alert On
# Prometheus alerting rules for ProxySQL
groups:
- name: proxysql_alerts
rules:
# Alert when backend connection errors exceed threshold
- alert: ProxySQLBackendConnectionErrors
expr: rate(proxysql_connection_pool_conn_err_total[5m]) > 0.5
for: 2m
labels:
severity: warning
annotations:
summary: "ProxySQL backend connection errors on {{ $labels.hostgroup }}/{{ $labels.endpoint }}"
description: "{{ $value }} connection errors/sec for the past 5 minutes"
# Alert when query latency exceeds baseline
- alert: ProxySQLHighQueryLatency
expr: >
rate(proxysql_query_digest_sum_time_total[5m]) /
rate(proxysql_query_digest_count_total[5m]) / 1000 > 200
for: 3m
labels:
severity: warning
annotations:
summary: "ProxySQL average query latency above 200ms"
# Alert when a backend is shunned
- alert: ProxySQLBackendShunned
expr: proxysql_connection_pool_status == 3
for: 30s
labels:
severity: critical
annotations:
summary: "ProxySQL backend {{ $labels.endpoint }} is SHUNNED"Grafana Dashboard Essentials for ProxySQL
With Prometheus ingesting ProxySQL metrics, Grafana can visualize the full operational picture of your MySQL proxy tier. The most effective ProxySQL Grafana dashboards focus on five panels that cover the scenarios most likely to precede an incident.
Panel 1: Query Throughput by Hostgroup
PromQL:
sum by (hostgroup) (
rate(proxysql_connection_pool_queries_total[1m])
)
Visualization: Stacked time-series graph
Purpose: Detect traffic imbalance between writer (HG10) and reader (HG20) pools,
and observe overall QPS trends that precede capacity incidents.Panel 2: Connection Pool Utilization Heat Map
PromQL (utilization ratio per backend):
proxysql_connection_pool_conn_used
/
(proxysql_connection_pool_conn_used + proxysql_connection_pool_conn_free)
Visualization: Gauge or heat map per backend endpoint
Alert threshold: > 0.80 (80% utilized)
Purpose: Catch pool saturation before it queues client connections.Panel 3: Replication Lag per Replica
PromQL:
proxysql_mysql_server_replication_lag_seconds
Visualization: Time-series with threshold line at lag SLA (e.g., 5 seconds)
Purpose: Correlate reader latency spikes with replica lag — when a replica falls
behind, ProxySQL should automatically shunt it to a degraded hostgroup if
mysql-monitor_replication_lag_group_by_slave is configured correctly.Panel 4: Top Query Digests Table
-- Supplement Grafana with a direct ProxySQL admin query for digest analysis
-- Run this periodically and export results to a time-series store or spreadsheet
SELECT
SUBSTRING(digest_text, 1, 80) AS query_pattern,
hostgroup,
count_star AS executions,
ROUND(sum_time / 1000000, 1) AS total_sec,
ROUND(sum_time / count_star / 1000, 1) AS avg_ms,
ROUND(max_time / 1000, 1) AS max_ms,
sum_errors AS errors
FROM stats.stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 10;Panel 5: Client vs Server Connection Counts
PromQL:
proxysql_mysql_status_client_connections_connected # application-side connections
proxysql_mysql_status_server_connections_connected # backend MySQL connections
Visualization: Dual-axis time-series
Purpose: Visualize the connection multiplexing ratio — the gap between client
connections and server connections shows how much compression ProxySQL is
achieving. A shrinking ratio under load may indicate connection pool misconfiguration.Import Grafana dashboard ID 11323 ("ProxySQL Overview") from Grafana Labs as a starting point. It covers the most common metrics out of the box and is compatible with the standard proxysql-exporter metric names. You will still need to customize alerting thresholds for your specific workload — the default thresholds are rarely appropriate for production without tuning.
Querying Query Digest Stats Programmatically for Trend Tracking
-- Snapshot query digest stats at regular intervals (e.g., via cron + MySQL client)
-- to build a historical trend without a full TSDB
SELECT
NOW() AS snapshot_time,
digest,
SUBSTRING(digest_text, 1, 120) AS query_pattern,
hostgroup,
count_star,
sum_time,
sum_errors,
first_seen,
last_seen
FROM stats.stats_mysql_query_digest
WHERE count_star > 100
ORDER BY sum_time DESC
LIMIT 50;- ProxySQL's
stats_mysql_query_digestsurfaces aggregate query performance data that MySQL's slow query log misses — especially high-frequency queries that individually fall underlong_query_timebut collectively dominate backend load. - Sort query digests by
sum_time DESC, notavg_latency_ms DESC, to find the queries actually driving the most database load. - Monitor
stats_mysql_connection_poolfor utilization ratios above 80% per backend — sustained high utilization is a leading indicator of connection starvation and query queuing, not just slow queries. - The
monitorschema gives you a timestamped audit trail of replication lag readings and connect failures — use it to prove or disprove whether a backend health event preceded a latency spike. - Deploy
proxysql_exporterto bridge ProxySQL stats into Prometheus and configure alerting rules on backend errors, connection utilization, and replication lag — these fire before users notice. - Use
SELECT * FROM stats_mysql_query_digest_reset;to atomically snapshot and clear the digest table during incident investigation, giving you a clean baseline after resolving a query regression. - Grafana dashboard ID 11323 provides a solid starting point, but production alerting thresholds must be tuned to your actual query latency baselines and connection pool sizing.
Working with JusDB on ProxySQL and MySQL
JusDB deploys and manages ProxySQL for MySQL teams who need connection pooling, read/write splitting, and deep query visibility. Our DBAs configure monitoring pipelines, Grafana dashboards, and alerting rules so you see MySQL performance issues before users do.
Explore JusDB MySQL Management → | Talk to a DBA
Related reading: