ProxySQL Statistics and Metrics Tables

This document provides a comprehensive analysis of ProxySQL statistics and metrics table schemas. ProxySQL exposes statistics through its admin interface (default port 6032) backed by in-memory SQLite tables.

Table of Contents

• 1. MySQL Statistics Tables
• 2. PostgreSQL Statistics Tables
• 3. Cluster Statistics Tables
• 4. Historical Statistics Tables
• 5. System Statistics Tables
• 6. Table Reset Pattern
• 7. Database Architecture
• 8. Data Characteristics and Example Queries
• 9. Common Notes
• 10. File References

1. MySQL Statistics Tables

1.1 stats_mysql_query_rules

Tracks hit counts for query rules.

CREATE TABLE stats_mysql_query_rules (
    rule_id INTEGER PRIMARY KEY,
    hits INT NOT NULL
)

Columns:

• rule_id - Reference to the query rule in mysql_query_rules
• hits - Number of times this rule was matched

1.2 stats_mysql_users

Monitors frontend user connections and limits.

CREATE TABLE stats_mysql_users (
    username VARCHAR PRIMARY KEY,
    frontend_connections INT NOT NULL,
    frontend_max_connections INT NOT NULL
)

Columns:

• username - MySQL username
• frontend_connections - Current number of active frontend connections
• frontend_max_connections - Maximum allowed connections (from mysql_users config)

1.3 stats_mysql_commands_counters

Aggregates command execution statistics with latency distribution.

CREATE TABLE stats_mysql_commands_counters (
    Command VARCHAR NOT NULL PRIMARY KEY,
    Total_Time_us INT NOT NULL,
    Total_cnt INT NOT NULL,
    cnt_100us INT NOT NULL,
    cnt_500us INT NOT NULL,
    cnt_1ms INT NOT NULL,
    cnt_5ms INT NOT NULL,
    cnt_10ms INT NOT NULL,
    cnt_50ms INT NOT NULL,
    cnt_100ms INT NOT NULL,
    cnt_500ms INT NOT NULL,
    cnt_1s INT NOT NULL,
    cnt_5s INT NOT NULL,
    cnt_10s INT NOT NULL,
    cnt_INFs INT NOT NULL
)

Columns:

• Command - MySQL command type (SELECT, INSERT, etc.)
• Total_Time_us - Total execution time in microseconds
• Total_cnt - Total number of executions
• cnt_100us - Count of queries with latency ≤100μs
• cnt_500us - Count of queries with latency ≤500μs
• cnt_1ms - Count of queries with latency ≤1ms
• cnt_5ms - Count of queries with latency ≤5ms
• cnt_10ms - Count of queries with latency ≤10ms
• cnt_50ms - Count of queries with latency ≤50ms
• cnt_100ms - Count of queries with latency ≤100ms
• cnt_500ms - Count of queries with latency ≤500ms
• cnt_1s - Count of queries with latency ≤1s
• cnt_5s - Count of queries with latency ≤5s
• cnt_10s - Count of queries with latency ≤10s
• cnt_INFs - Count of queries with unknown/undefined latency

1.4 stats_mysql_processlist

Shows active MySQL sessions, similar to MySQL's PROCESSLIST.

CREATE TABLE stats_mysql_processlist (
    ThreadID INT NOT NULL,
    SessionID INTEGER PRIMARY KEY,
    user VARCHAR,
    db VARCHAR,
    cli_host VARCHAR,
    cli_port INT,
    hostgroup INT,
    l_srv_host VARCHAR,
    l_srv_port INT,
    srv_host VARCHAR,
    srv_port INT,
    command VARCHAR,
    time_ms INT NOT NULL,
    info VARCHAR,
    status_flags INT,
    extended_info VARCHAR
)

Columns:

• ThreadID - Internal thread identifier
• SessionID - Unique session identifier (primary key)
• user - Username
• db - Database/schema name
• cli_host - Client host address
• cli_port - Client port
• hostgroup - Target hostgroup
• l_srv_host - Local (proxysql) server hostname
• l_srv_port - Local server port
• srv_host - Backend MySQL server hostname
• srv_port - Backend MySQL server port
• command - Current command type
• time_ms - Query execution time in milliseconds
• info - Query text or status message
• status_flags - Internal status flags
• extended_info - Extended session information (JSON)

1.5 stats_mysql_connection_pool

Backend connection pool metrics per server.

CREATE TABLE stats_mysql_connection_pool (
    hostgroup INT,
    srv_host VARCHAR,
    srv_port INT,
    status VARCHAR,
    ConnUsed INT,
    ConnFree INT,
    ConnOK INT,
    ConnERR INT,
    MaxConnUsed INT,
    Queries INT,
    Queries_GTID_sync INT,
    Bytes_data_sent INT,
    Bytes_data_recv INT,
    Latency_us INT
)

Columns:

• hostgroup - Hostgroup ID
• srv_host - Backend server hostname
• srv_port - Backend server port
• status - Server status (ONLINE, SHUNNED, OFFLINE_SOFT, OFFLINE_HARD)
• ConnUsed - Currently used connections
• ConnFree - Free/available connections in pool
• ConnOK - Total successful connections
• ConnERR - Total connection errors
• MaxConnUsed - Maximum concurrent connections used
• Queries - Total queries sent to this server
• Queries_GTID_sync - Queries synchronized with GTID
• Bytes_data_sent - Total bytes sent to server
• Bytes_data_recv - Total bytes received from server
• Latency_us - Ping latency in microseconds

stats_mysql_connection_pool_reset - Identical schema to stats_mysql_connection_pool but allows resetting stats.

1.6 stats_mysql_free_connections

Details about idle connections available in the pool.

CREATE TABLE stats_mysql_free_connections (
    fd INT NOT NULL,
    hostgroup INT NOT NULL,
    srv_host VARCHAR NOT NULL,
    srv_port INT NOT NULL,
    user VARCHAR NOT NULL,
    schema VARCHAR,
    init_connect VARCHAR,
    time_zone VARCHAR,
    sql_mode VARCHAR,
    autocommit VARCHAR,
    idle_ms INT,
    statistics VARCHAR,
    mysql_info VARCHAR
)

Columns:

• fd - File descriptor for the connection
• hostgroup - Hostgroup ID
• srv_host - Backend server hostname
• srv_port - Backend server port
• user - Backend username
• schema - Default schema
• init_connect - INIT_CONNECT string
• time_zone - Timezone setting
• sql_mode - SQL mode
• autocommit - Autocommit status
• idle_ms - Idle time in milliseconds
• statistics - Connection statistics (JSON)
• mysql_info - Additional MySQL-specific info (JSON)

1.7 stats_mysql_query_digest

Aggregated query performance statistics.

CREATE TABLE stats_mysql_query_digest (
    hostgroup INT,
    schemaname VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    digest_text VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    sum_time INTEGER NOT NULL,
    min_time INTEGER NOT NULL,
    max_time INTEGER NOT NULL,
    sum_rows_affected INTEGER NOT NULL,
    sum_rows_sent INTEGER NOT NULL,
    PRIMARY KEY(hostgroup, schemaname, username, client_address, digest)
)

Columns:

• hostgroup - Hostgroup where query was executed
• schemaname - Database/schema name
• username - Username who executed query
• client_address - Client IP address
• digest - Query digest hash
• digest_text - Representative query text (normalized)
• count_star - Total executions
• first_seen - Unix timestamp of first execution
• last_seen - Unix timestamp of last execution
• sum_time - Total execution time in microseconds
• min_time - Minimum execution time in microseconds
• max_time - Maximum execution time in microseconds
• sum_rows_affected - Total rows affected
• sum_rows_sent - Total rows sent to client

stats_mysql_query_digest_reset - Identical schema to stats_mysql_query_digest for resetting stats.

1.8 stats_mysql_global

Global ProxySQL metrics.

CREATE TABLE stats_mysql_global (
    Variable_Name VARCHAR NOT NULL PRIMARY KEY,
    Variable_Value VARCHAR NOT NULL
)

Complete Variable List:

This table uses a key-value format where each row is a different metric. The variables are populated from multiple sources in the codebase (see Source References below).

System / Uptime

Client Connection Metrics

Server Connection Metrics

Memory Buffer Metrics

Command Counters (Com_*)

Prepared Statement Counters

Query Metrics

Connection Pool Operation Counters

Backend Health Counters

Hostgroup Locking Counters

Unexpected Frontend Counters

Max Lag Throttling Counters

Security Counters

Error and Miscellaneous Counters

Mirror Metrics

Miscellaneous Status

Access Denied Counters

MySQL Monitor Metrics

Memory Metrics

Prepared Statement Metrics

Query Cache Metrics

MySQL Logger Metrics

Source References

Variables are aggregated from these code locations:

• MySQL_Threads_Handler::SQL3_GlobalStatus() in lib/MySQL_Thread.cpp
• MySQL_HostGroups_Manager::SQL3_Get_ConnPool_Stats() in lib/MySQL_HostGroups_Manager.cpp
• ProxySQL_Admin::stats___mysql_global() in lib/ProxySQL_Admin_Stats.cpp
• Query_Cache::SQL3_getStats() in lib/Query_Cache.cpp
• MySQL_Logger::getAllMetrics() in lib/MySQL_Logger.cpp

1.9 stats_memory_metrics

Memory usage statistics.

CREATE TABLE stats_memory_metrics (
    Variable_Name VARCHAR NOT NULL PRIMARY KEY,
    Variable_Value VARCHAR NOT NULL
)

Complete Variable List:

This table uses a key-value format. Variables are populated from lib/ProxySQL_Admin_Stats.cpp.

Core Memory

jemalloc Memory (requires jemalloc build)

Module Memory

Prepared Statement Memory

Firewall Memory

Stack Memory

1.10 stats_mysql_gtid_executed

GTID (Global Transaction ID) information.

CREATE TABLE stats_mysql_gtid_executed (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    gtid_executed VARCHAR,
    events INT NOT NULL
)

Columns:

• hostname - Backend server hostname
• port - Backend server port
• gtid_executed - GTID set string
• events - Number of GTID events

1.11 stats_mysql_errors

MySQL error tracking.

CREATE TABLE stats_mysql_errors (
    hostgroup INT NOT NULL,
    hostname VARCHAR NOT NULL,
    port INT NOT NULL,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    schemaname VARCHAR NOT NULL,
    errno INT NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    last_error VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup, hostname, port, username, schemaname, errno)
)

Columns:

• hostgroup - Hostgroup ID
• hostname - Backend server hostname
• port - Backend server port
• username - Username
• client_address - Client IP address
• schemaname - Database/schema name
• errno - MySQL error number
• count_star - Error count
• first_seen - Unix timestamp of first occurrence
• last_seen - Unix timestamp of last occurrence
• last_error - Error message

stats_mysql_errors_reset - Identical schema to stats_mysql_errors for resetting stats.

1.12 stats_mysql_client_host_cache

Client host error tracking for connection throttling.

CREATE TABLE stats_mysql_client_host_cache (
    client_address VARCHAR NOT NULL,
    error_count INT NOT NULL,
    last_updated BIGINT NOT NULL
)

Columns:

• client_address - Client IP address
• error_count - Number of recent errors from this host
• last_updated - Unix timestamp of last update

stats_mysql_client_host_cache_reset - Identical schema to stats_mysql_client_host_cache for resetting stats.

1.13 stats_mysql_prepared_statements_info

Prepared statement statistics.

CREATE TABLE stats_mysql_prepared_statements_info (
    global_stmt_id INT NOT NULL,
    schemaname VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    ref_count_client INT NOT NULL,
    ref_count_server INT NOT NULL,
    num_columns INT NOT NULL,
    num_params INT NOT NULL,
    query VARCHAR NOT NULL
)

Columns:

• global_stmt_id - Global statement ID
• schemaname - Database schema
• username - Username
• digest - Query digest
• ref_count_client - Reference count from client
• ref_count_server - Reference count from server
• num_columns - Number of result columns
• num_params - Number of parameters
• query - Query text

1.14 stats_mysql_query_events

Query event log (when query logging is enabled).

CREATE TABLE stats_mysql_query_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    thread_id INTEGER,
    username TEXT,
    schemaname TEXT,
    start_time INTEGER,
    end_time INTEGER,
    query_digest TEXT,
    query TEXT,
    server TEXT,
    client TEXT,
    event_type INTEGER,
    hid INTEGER,
    extra_info TEXT,
    affected_rows INTEGER,
    last_insert_id INTEGER,
    rows_sent INTEGER,
    client_stmt_id INTEGER,
    gtid TEXT,
    errno INT,
    error TEXT
)

Columns:

• id - Auto-increment ID
• thread_id - Thread ID
• username - Username
• schemaname - Database schema
• start_time - Start timestamp
• end_time - End timestamp
• query_digest - Query digest
• query - Full query text
• server - Server address
• client - Client address
• event_type - Event type
• hid - Hostgroup ID
• extra_info - Extra information
• affected_rows - Rows affected
• last_insert_id - Last insert ID
• rows_sent - Rows sent
• client_stmt_id - Client statement ID
• gtid - GTID information
• errno - Error number
• error - Error message

2. PostgreSQL Statistics Tables

All PostgreSQL stats tables mirror their MySQL counterparts with appropriate name changes (e.g., schemaname instead of db, sqlstate instead of errno).

2.1 stats_pgsql_query_rules

CREATE TABLE stats_pgsql_query_rules (
    rule_id INTEGER PRIMARY KEY,
    hits INT NOT NULL
)

2.2 stats_pgsql_users

CREATE TABLE stats_pgsql_users (
    username VARCHAR PRIMARY KEY,
    frontend_connections INT NOT NULL,
    frontend_max_connections INT NOT NULL
)

2.3 stats_pgsql_commands_counters

CREATE TABLE stats_pgsql_commands_counters (
    Command VARCHAR NOT NULL PRIMARY KEY,
    Total_Time_us INT NOT NULL,
    Total_cnt INT NOT NULL,
    cnt_100us INT NOT NULL,
    cnt_500us INT NOT NULL,
    cnt_1ms INT NOT NULL,
    cnt_5ms INT NOT NULL,
    cnt_10ms INT NOT NULL,
    cnt_50ms INT NOT NULL,
    cnt_100ms INT NOT NULL,
    cnt_500ms INT NOT NULL,
    cnt_1s INT NOT NULL,
    cnt_5s INT NOT NULL,
    cnt_10s INT NOT NULL,
    cnt_INFs INT NOT NULL
)

2.4 stats_pgsql_processlist

CREATE TABLE stats_pgsql_processlist (
    ThreadID INT NOT NULL,
    SessionID INTEGER PRIMARY KEY,
    user VARCHAR,
    database VARCHAR,
    cli_host VARCHAR,
    cli_port INT,
    hostgroup INT,
    l_srv_host VARCHAR,
    l_srv_port INT,
    srv_host VARCHAR,
    srv_port INT,
    backend_pid INT,
    backend_state VARCHAR,
    command VARCHAR,
    time_ms INT NOT NULL,
    info VARCHAR,
    status_flags INT,
    extended_info VARCHAR
)

Note: This table includes database, backend_pid, and backend_state columns specific to PostgreSQL.

2.5 stats_pgsql_stat_activity

A PostgreSQL-compatible VIEW (not a table) that mirrors pg_stat_activity.

CREATE VIEW stats_pgsql_stat_activity AS
SELECT
    ThreadID AS thread_id,
    database AS datname,
    SessionID AS pid,
    user AS usename,
    cli_host AS client_addr,
    cli_port AS client_port,
    hostgroup,
    l_srv_host,
    l_srv_port,
    srv_host,
    srv_port,
    backend_pid,
    backend_state AS state,
    command,
    time_ms AS duration_ms,
    info as query,
    status_flags,
    extended_info
FROM stats_pgsql_processlist

2.6 stats_pgsql_connection_pool

CREATE TABLE stats_pgsql_connection_pool (
    hostgroup INT,
    srv_host VARCHAR,
    srv_port INT,
    status VARCHAR,
    ConnUsed INT,
    ConnFree INT,
    ConnOK INT,
    ConnERR INT,
    MaxConnUsed INT,
    Queries INT,
    Bytes_data_sent INT,
    Bytes_data_recv INT,
    Latency_us INT
)

2.7 stats_pgsql_free_connections

CREATE TABLE stats_pgsql_free_connections (
    fd INT NOT NULL,
    hostgroup INT NOT NULL,
    srv_host VARCHAR NOT NULL,
    srv_port INT NOT NULL,
    user VARCHAR NOT NULL,
    database VARCHAR,
    init_connect VARCHAR,
    time_zone VARCHAR,
    sql_mode VARCHAR,
    idle_ms INT,
    statistics VARCHAR,
    pgsql_info VARCHAR
)

2.8 stats_pgsql_query_digest

CREATE TABLE stats_pgsql_query_digest (
    hostgroup INT,
    database VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    digest_text VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    sum_time INTEGER NOT NULL,
    min_time INTEGER NOT NULL,
    max_time INTEGER NOT NULL,
    sum_rows_affected INTEGER NOT NULL,
    sum_rows_sent INTEGER NOT NULL,
    PRIMARY KEY(hostgroup, database, username, client_address, digest)
)

Note: This table uses database column, unlike MySQL which uses schemaname. However, the historical table history_pgsql_query_digest uses schemaname for consistency with MySQL history tables.

2.9 stats_pgsql_prepared_statements_info

CREATE TABLE stats_pgsql_prepared_statements_info (
    global_stmt_id INT NOT NULL,
    database VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    ref_count_client INT NOT NULL,
    ref_count_server INT NOT NULL,
    num_param_types INT NOT NULL,
    query VARCHAR NOT NULL
)

2.10 stats_pgsql_global

CREATE TABLE stats_pgsql_global (
    Variable_Name VARCHAR NOT NULL PRIMARY KEY,
    Variable_Value VARCHAR NOT NULL
)

Complete Variable List:

This table uses a key-value format, similar to stats_mysql_global. Variables are populated from PgSQL_Threads_Handler::SQL3_GlobalStatus() in lib/PgSQL_Thread.cpp and other sources.

Note: The PgSQL global stats are less mature than MySQL. Many counter/gauge arrays present in the MySQL path are commented out in the PgSQL code. The active variables are listed below.

System / Uptime

Client Connection Metrics

Server Connection Metrics

Memory Buffer Metrics

Transaction Command Counters

Backend Command Counters

Mirror Metrics

Miscellaneous Status

Access Denied Counters

PgSQL Monitor Metrics

Connection Pool Operation Counters

Memory Metrics

Prepared Statement Metrics

Query Cache Metrics

Query Processor Metrics

Source References

Variables are aggregated from these code locations:

• PgSQL_Threads_Handler::SQL3_GlobalStatus() in lib/PgSQL_Thread.cpp
• PgSQL_HostGroups_Manager::SQL3_Get_ConnPool_Stats() in lib/PgSQL_HostGroups_Manager.cpp
• ProxySQL_Admin::stats___pgsql_global() in lib/ProxySQL_Admin_Stats.cpp
• Query_Cache::SQL3_getStats() in lib/Query_Cache.cpp

2.11 stats_pgsql_errors

CREATE TABLE stats_pgsql_errors (
    hostgroup INT NOT NULL,
    hostname VARCHAR NOT NULL,
    port INT NOT NULL,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    database VARCHAR NOT NULL,
    sqlstate VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    last_error VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup, hostname, port, username, database, sqlstate)
)

2.12 stats_pgsql_client_host_cache

CREATE TABLE stats_pgsql_client_host_cache (
    client_address VARCHAR NOT NULL,
    error_count INT NOT NULL,
    last_updated BIGINT NOT NULL
)

2.13 MySQL vs PostgreSQL Column Naming

ProxySQL uses different column names for the same concept between MySQL and PostgreSQL tables:

Note on History Tables: The history_pgsql_query_digest table uses schemaname (matching MySQL convention) rather than database, creating an inconsistency with the live stats_pgsql_query_digest table.

3. Cluster Statistics Tables

Tables for monitoring ProxySQL cluster nodes.

3.1 stats_proxysql_servers_clients_status

CREATE TABLE stats_proxysql_servers_clients_status (
    uuid VARCHAR NOT NULL,
    hostname VARCHAR NOT NULL,
    port INT NOT NULL,
    admin_mysql_ifaces VARCHAR NOT NULL,
    last_seen_at INT NOT NULL,
    PRIMARY KEY (uuid, hostname, port)
)

3.2 stats_proxysql_servers_status

CREATE TABLE stats_proxysql_servers_status (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 6032,
    weight INT CHECK (weight >= 0) NOT NULL DEFAULT 0,
    master VARCHAR NOT NULL,
    global_version INT NOT NULL,
    check_age_us INT NOT NULL,
    ping_time_us INT NOT NULL,
    checks_OK INT NOT NULL,
    checks_ERR INT NOT NULL,
    PRIMARY KEY (hostname, port)
)

3.3 stats_proxysql_servers_metrics

CREATE TABLE stats_proxysql_servers_metrics (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 6032,
    weight INT CHECK (weight >= 0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    response_time_ms INT NOT NULL,
    Uptime_s INT NOT NULL,
    last_check_ms INT NOT NULL,
    Queries INT NOT NULL,
    Client_Connections_connected INT NOT NULL,
    Client_Connections_created INT NOT NULL,
    PRIMARY KEY (hostname, port)
)

3.4 stats_proxysql_servers_checksums

CREATE TABLE stats_proxysql_servers_checksums (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 6032,
    name VARCHAR NOT NULL,
    version INT NOT NULL,
    epoch INT NOT NULL,
    checksum VARCHAR NOT NULL,
    changed_at INT NOT NULL,
    updated_at INT NOT NULL,
    diff_check INT NOT NULL,
    PRIMARY KEY (hostname, port, name)
)

3.5 stats_proxysql_message_metrics

Message/metric tracking for ProxySQL internal use.

CREATE TABLE stats_proxysql_message_metrics (
    message_id VARCHAR NOT NULL,
    filename VARCHAR NOT NULL,
    line INT CHECK (line >= 0) NOT NULL DEFAULT 0,
    func VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    PRIMARY KEY (filename, line, func)
)

4. Historical Statistics Tables

Tables stored in the persistent statsdb_disk database with time-series data.

4.1 Connection Metrics

4.1.1 mysql_connections

Connection statistics by timestamp.

CREATE TABLE mysql_connections (
    timestamp INT NOT NULL,
    Client_Connections_aborted INT NOT NULL,
    Client_Connections_connected INT NOT NULL,
    Client_Connections_created INT NOT NULL,
    Server_Connections_aborted INT NOT NULL,
    Server_Connections_connected INT NOT NULL,
    Server_Connections_created INT NOT NULL,
    ConnPool_get_conn_failure INT NOT NULL,
    ConnPool_get_conn_immediate INT NOT NULL,
    ConnPool_get_conn_success INT NOT NULL,
    Questions INT NOT NULL,
    Slow_queries INT NOT NULL,
    GTID_consistent_queries INT NOT NULL,
    PRIMARY KEY (timestamp)
)

4.1.2 mysql_connections_hour

Hourly aggregated connection metrics.

4.1.3 mysql_connections_day

Daily aggregated connection metrics.

4.2 Connection Pool History

4.2.1 history_stats_mysql_connection_pool

Historical connection pool metrics by timestamp.

CREATE TABLE history_stats_mysql_connection_pool (
    timestamp INT NOT NULL,
    hostgroup INT,
    srv_host VARCHAR,
    srv_port INT,
    status VARCHAR,
    ConnUsed INT,
    ConnFree INT,
    ConnOK INT,
    ConnERR INT,
    MaxConnUsed INT,
    Queries INT,
    Queries_GTID_sync INT,
    Bytes_data_sent INT,
    Bytes_data_recv INT,
    Latency_us INT,
    PRIMARY KEY (timestamp, hostgroup, srv_host, srv_port)
)

4.3 MyHGM (MySQL Host Group Manager) Metrics

4.3.1 myhgm_connections

MyHGM internal connection metrics.

CREATE TABLE myhgm_connections (
    timestamp INT NOT NULL,
    MyHGM_myconnpoll_destroy INT NOT NULL,
    MyHGM_myconnpoll_get INT NOT NULL,
    MyHGM_myconnpoll_get_ok INT NOT NULL,
    MyHGM_myconnpoll_push INT NOT NULL,
    MyHGM_myconnpoll_reset INT NOT NULL,
    PRIMARY KEY (timestamp)
)

4.4 MySQL Status Variables History

4.4.1 history_mysql_status_variables

Time-series data for MySQL status variables.

CREATE TABLE history_mysql_status_variables (
    timestamp INT NOT NULL,
    variable_id INT NOT NULL,
    variable_value VARCHAR NOT NULL,
    PRIMARY KEY (timestamp, variable_id)
)

4.4.2 history_mysql_status_variables_lookup

Mapping table for variable names to IDs.

CREATE TABLE history_mysql_status_variables_lookup (
    variable_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    variable_name VARCHAR NOT NULL,
    UNIQUE (variable_name)
)

4.4.3 history_pgsql_status_variables

PostgreSQL status variables history.

4.4.4 history_pgsql_status_variables_lookup

PostgreSQL variable name to ID mapping.

4.5 Query Digest History

4.5.1 history_mysql_query_digest

Historical query digest snapshots.

CREATE TABLE history_mysql_query_digest (
    dump_time INT,
    hostgroup INT,
    schemaname VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    digest_text VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    sum_time INTEGER NOT NULL,
    min_time INTEGER NOT NULL,
    max_time INTEGER NOT NULL,
    sum_rows_affected INTEGER NOT NULL,
    sum_rows_sent INTEGER NOT NULL
)

4.5.2 history_pgsql_query_digest

PostgreSQL query digest history.

4.6 Query Events History

4.6.1 history_mysql_query_events

Historical query event log.

CREATE TABLE history_mysql_query_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    thread_id INTEGER,
    username TEXT,
    schemaname TEXT,
    start_time INTEGER,
    end_time INTEGER,
    query_digest TEXT,
    query TEXT,
    server TEXT,
    client TEXT,
    event_type INTEGER,
    hid INTEGER,
    extra_info TEXT,
    affected_rows INTEGER,
    last_insert_id INTEGER,
    rows_sent INTEGER,
    client_stmt_id INTEGER,
    gtid TEXT,
    errno INT,
    error TEXT
)

5. System Statistics Tables

5.1 CPU Metrics

5.1.1 system_cpu

CPU usage statistics.

CREATE TABLE system_cpu (
    timestamp INT NOT NULL,
    tms_utime INT NOT NULL,
    tms_stime INT NOT NULL,
    PRIMARY KEY (timestamp)
)

5.1.2 system_cpu_hour

Hourly aggregated CPU metrics.

5.1.3 system_cpu_day

Daily aggregated CPU metrics.

5.2 Memory Metrics

5.2.1 system_memory

Memory usage statistics (requires jemalloc, excluded with NOJEM).

CREATE TABLE system_memory (
    timestamp INT NOT NULL,
    allocated INT NOT NULL,
    resident INT NOT NULL,
    active INT NOT NULL,
    mapped INT NOT NULL,
    metadata INT NOT NULL,
    retained INT NOT NULL,
    PRIMARY KEY (timestamp)
)

5.2.2 system_memory_hour

Hourly aggregated memory metrics.

5.2.3 system_memory_day

Daily aggregated memory metrics.

5.3 Query Cache Metrics

5.3.1 mysql_query_cache

Query cache statistics.

CREATE TABLE mysql_query_cache (
    timestamp INT NOT NULL,
    count_GET INT NOT NULL,
    count_GET_OK INT NOT NULL,
    count_SET INT NOT NULL,
    bytes_IN INT NOT NULL,
    bytes_OUT INT NOT NULL,
    Entries_Purged INT NOT NULL,
    Entries_In_Cache INT NOT NULL,
    Memory_Bytes INT NOT NULL,
    PRIMARY KEY (timestamp)
)

5.3.2 mysql_query_cache_hour

Hourly aggregated query cache metrics.

5.3.3 mysql_query_cache_day

Daily aggregated query cache metrics.

6. Table Reset Pattern

Many stats tables have a *_reset counterpart:

• stats_mysql_connection_pool_reset
• stats_mysql_query_digest_reset
• stats_mysql_errors_reset
• stats_mysql_client_host_cache_reset
• stats_pgsql_connection_pool_reset
• stats_pgsql_query_digest_reset
• stats_pgsql_errors_reset
• stats_pgsql_client_host_cache_reset
• stats_proxysql_message_metrics_reset

Purpose: Allow preserving current stats before resetting. Reset is typically done by:

1. Reading from the regular table
2. Inserting into the _reset table
3. Resetting the regular table counters

7. Database Architecture

7.1 stats (In-Memory Database)

Contains real-time statistics tables prefixed with stats_*. This database is attached to the main admin database as the stats schema.

7.2 statsdb_disk (Persistent Database)

Contains historical tables with time-series data. Attached as stats_history schema. Includes:

• Hourly aggregated tables (suffixed with _hour)
• Daily aggregated tables (suffixed with _day)
• Full-resolution history tables (prefixed with history_)

7.3 statsdb_mem (Internal In-Memory)

Internal statistics database used by ProxySQL for metrics collection and aggregation.

8. Data Characteristics and Example Queries

ProxySQL stats tables fall into a few distinct categories based on how they store data.

8.1 Key-Value Tables

These tables store metrics as (Variable_Name, Variable_Value) rows, where each row is a different metric.

Example - Reading individual metrics:

SELECT Variable_Value FROM stats_mysql_global
WHERE Variable_Name = 'Client_Connections_connected';

Example - Pivoting multiple metrics into a single row:

SELECT
  MAX(CASE WHEN Variable_Name = 'Client_Connections_connected' THEN Variable_Value END) AS connected,
  MAX(CASE WHEN Variable_Name = 'Client_Connections_created' THEN Variable_Value END) AS created,
  MAX(CASE WHEN Variable_Name = 'Questions' THEN Variable_Value END) AS questions
FROM stats_mysql_global;

8.2 Per-Entity Tables

Each row represents one instance of an entity (a backend server, a query pattern, a user, etc.), with numeric columns sharing the same meaning across rows.

Example - Connection pool summary by hostgroup:

SELECT
  hostgroup,
  SUM(ConnUsed) AS total_used,
  SUM(ConnFree) AS total_free,
  SUM(Queries) AS total_queries,
  AVG(Latency_us) AS avg_latency_us
FROM stats_mysql_connection_pool
GROUP BY hostgroup;

Example - Top 10 slowest query digests:

SELECT digest_text, count_star,
  sum_time / count_star AS avg_time_us,
  max_time AS max_time_us
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 10;

Example - Error distribution by hostgroup:

SELECT hostgroup, errno, SUM(count_star) AS total_errors
FROM stats_mysql_errors
GROUP BY hostgroup, errno
ORDER BY total_errors DESC;

8.3 Snapshot / Event Tables

These tables capture live state or individual events. Their contents change constantly and are primarily useful for real-time debugging.

8.4 Historical / Time-Series Tables

Stored in the persistent statsdb_disk database, these tables record timestamped snapshots at different granularities for trend analysis.

Example - Connection trends over the last hour:

SELECT timestamp, Questions, Client_Connections_connected
FROM mysql_connections
WHERE timestamp > strftime('%s','now','-1 hour');

8.5 Data Collection Triggers

Different tables are populated through different mechanisms:

Timer-Based Collection

Historical time-series tables are populated by periodic timers in the admin thread. Each metric type has its own configurable interval:

Setting a variable to 0 disables collection for that metric type.

Allowed Discrete Intervals: Values are rounded to the nearest allowed interval: 0, 1, 5, 10, 30, 60, 120, 300, or 600 seconds.

Per-Query Updates

These tables are updated in real-time as queries execute:

On-Demand / Manual Collection

8.6 Retention Policies

Historical tables in statsdb_disk have automatic retention management:

Note: The *_day tables are defined in the schema but are never populated by the current codebase. Only raw and hourly granularities contain data.

Retention cleanup happens automatically after each data insertion. For example, after inserting a new row into system_cpu, rows older than 7 days are deleted, and then the hourly aggregation is checked and performed if needed.

8.7 Aggregation Methods for Hourly Tables

When raw data is aggregated into _hour tables, different columns use different aggregation functions based on their metric type:

system_cpu_hour

system_memory_hour

mysql_connections_hour

mysql_query_cache_hour

myhgm_connections_hour

Aggregation Timing: Hourly aggregation is performed lazily. Each time a new raw row is inserted, the system checks if the current time is at least 3600 seconds past the last hourly entry. If so, it runs the aggregation INSERT for the completed hour(s).

8.8 Buffer and Flush Mechanics

Some tables require explicit flush operations to make data visible.

Query Events Buffer

Query events are first collected into a circular buffer in memory, not directly into SQLite tables.

Data Flow:

Query executes
    ↓
MySQL_Logger::log_request() adds event to circular buffer (MyLogCB)
    ↓
Buffer accumulates events (size controlled by mysql_eventslog_table_memory_size)
    ↓
Manual DUMP command drains buffer to SQLite tables

Flush Commands:

Important: The get_all_events() operation drains the buffer. Once flushed, events are removed from the buffer. There is no way to peek at buffer contents without draining.

Retention Differences:

• stats_mysql_query_events: Capped to eventslog_table_memory_size entries; oldest rows evicted when full
• history_mysql_query_events: Append-only, no automatic eviction

Query Digest Snapshot

Query digest statistics are maintained in an in-memory hash map (digest_umap), not in SQLite. The map is updated in real-time as queries complete.

Reading Live Data (Non-Destructive):

When you SELECT FROM stats_mysql_query_digest, ProxySQL:

1. Briefly swaps the live map with an empty map
2. Serializes the swapped data to an in-memory SQLite table
3. Merges any new entries accumulated during serialization back into the map
4. Executes your query against the SQLite table

This is a non-destructive read — the data is preserved.

Saving to History (Destructive):

When you run SAVE MYSQL DIGEST TO DISK (or the periodic timer triggers):

1. The live map is atomically swapped with an empty map
2. The swapped data is written to history_mysql_query_digest with a dump_time column
3. The swapped data is deleted — the live map is now empty
4. New queries immediately start accumulating in the fresh empty map

Snapshot Characteristics:

• Each SAVE produces a batch of rows with the same dump_time
• Each snapshot represents stats accumulated since the previous save
• The same digest can appear in multiple snapshots with different counts
• first_seen/last_seen timestamps are converted from monotonic to wall-clock time

8.9 PostgreSQL Table Coverage

Not all tables have PostgreSQL equivalents. Here is the coverage matrix:

Live Statistics Tables

Historical Tables

Shared Tables (Database-Agnostic)

These tables are shared between MySQL and PostgreSQL as they track ProxySQL itself:

• stats_proxysql_servers_status
• stats_proxysql_servers_metrics
• stats_proxysql_servers_checksums
• stats_proxysql_servers_clients_status
• stats_memory_metrics
• system_cpu / system_cpu_hour
• system_memory / system_memory_hour

9. Common Notes

1. Schema Versioning: Tables have version-specific definitions; current versions are aliased without version suffixes.

2. Reset Capability: Most counters support reset via *_reset tables.

3. Time Units:

   • *_us = microseconds
   • *_ms = milliseconds
   • *_s = seconds
   • Unqualified timestamps are Unix epoch (seconds)

4. Primary Keys: Most tables use composite keys for efficient data aggregation.

5. Nullable Columns: Some columns may be NULL depending on configuration (e.g., status, port, hostgroup).

6. Views: Some tables like stats_pgsql_stat_activity are actually views and cannot be dropped or modified directly.

7. Performance Considerations: Historical tables can grow large; ProxySQL manages retention through hourly/daily aggregation tables.

10. File References

Schema definitions are located in:

• include/ProxySQL_Admin_Tables_Definitions.h - Stats table definitions
• include/ProxySQL_Statistics.hpp - Historical/Time-series table definitions
• lib/ProxySQL_Statistics.cpp - Statistics initialization and management
• lib/Admin_Bootstrap.cpp - Stats table registration
• lib/ProxySQL_Admin_Stats.cpp - Stats table population logic