The MySQL stats tables in the stats schema expose runtime statistics collected by ProxySQL for all MySQL traffic, connection pools, and user activity. These tables provide visibility into query performance, backend health, error rates, and resource usage, making them the primary tool for monitoring and troubleshooting ProxySQL deployments.

Table of Contents

• stats_mysql_client_host_cache
• stats_mysql_commands_counters
• stats_mysql_connection_pool
• stats_mysql_errors
• stats_mysql_free_connections
• stats_mysql_global
• stats_mysql_gtid_executed
• stats_mysql_prepared_statements_info
• stats_mysql_processlist
• stats_mysql_query_digest
• stats_mysql_query_events
• stats_mysql_query_rules
• stats_mysql_users

stats_mysql_client_host_cache

Table stats_mysql_client_host_cache keeps records of all the failed connection attempts performed by clients when ‘client error limit’ feature is enabled, i.e. when mysql-client_host_cache_size is set to a value bigger than 0. Here is the statement used to create the stats_mysql_client_host_cache table:

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

The fields have the following semantics:

• client_address: the client address from which the connection failure was detected.
• error_count: the total number of consecutive connections errors originated from client address.
• last_updated: the time of the last connection error in microseconds.

Example:

Admin> SELECT * FROM stats_mysql_client_host_cache;
+----------------+-------------+--------------+
| client_address | error_count | last_updated |
+----------------+-------------+--------------+
| 10.200.1.2     | 1           | 104099605807 |
+----------------+-------------+--------------+
1 row in set (0.00 sec)

Related tables:

• mysql-client_host_cache_size global variable controls whether this feature is enabled.

stats_mysql_client_host_cache_reset

Querying the stats_mysql_client_host_cache_reset table is equivalent to querying stats_mysql_client_host_cache, with the only difference that the client host cache is cleared at the end of the SELECT statement.

stats_mysql_commands_counters

Table stats_mysql_commands_counters keeps records of all types of queries executed, and collects statistics based on their execution time, grouping them into buckets.

CREATE TABLE stats_mysql_commands_counters (
    Command VARCHAR NOT NULL,
    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,
    PRIMARY KEY(Command))

The fields have the following semantics:

• command : the type of SQL command that has been executed. Examples: FLUSH, INSERT, KILL, SELECT FOR UPDATE, etc.
• Total_Time_us : the total time spent executing commands of that type, in microseconds
• total_cnt : the total number of commands of that type executed
• cnt_100us,cnt_500us, ..., cnt_10s, cnt_INFs : the total number of commands of the given type which executed within the specified time limit and the previous one. For example, cnt_500us is the number of commands which executed within 500 microseconds, but more than 100 microseconds because there’s also a cnt_100us field. cnt_INFs is the number of commands whose execution exceeded 10 seconds.

Note: statistics for table stats_mysql_commands_counters are processed only if global variable mysql-commands_stats is set to true. This is the default, and used for other queries processing. It is recommended to NOT disable it.

Admin> SELECT * FROM stats_mysql_commands_counters ORDER BY Total_cnt DESC LIMIT 1G
*************************** 1. row ***************************
      Command: SELECT
Total_Time_us: 347608868191
    Total_cnt: 9246385
    cnt_100us: 1037
    cnt_500us: 2316761
      cnt_1ms: 2710036
      cnt_5ms: 2728904
     cnt_10ms: 457001
     cnt_50ms: 655136
    cnt_100ms: 146379
    cnt_500ms: 179698
       cnt_1s: 19157
       cnt_5s: 21705
      cnt_10s: 4663
     cnt_INFs: 5908
1 row in set (0.01 sec)

Related tables:

• stats_mysql_query_digest — per-query breakdown of execution time
• stats_mysql_global — global query counters including Questions and Slow_queries

stats_mysql_connection_pool

This table exports statistics on backend servers. Servers are identified based on their hostgroup, address and port, and the information available is related to connections, queries and traffic. Here is the statement used to create the stats_mysql_connection_pool table:

CREATE TABLE stats_mysql_connection_pool (
    hostgroup VARCHAR,
    srv_host VARCHAR,
    srv_port VARCHAR,
    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)

Each row represents a backend server within a hostgroup. The fields have the following semantics:

• hostgroup : the hostgroup in which the backend server belongs. Note that a single backend server can belong to more than one hostgroup
• srv_host, srv_port : the TCP endpoint on which the mysqld backend server is listening for connections
• status : the status of the backend server. Can be ONLINE, SHUNNED, OFFLINE_SOFT, OFFLINE_HARD. See the description of the mysql_servers table for more details about what each status means
• ConnUsed : how many connections are currently used by ProxySQL for sending queries to the backend server
• ConnFree : how many connections are currently free. They are kept open in order to minimize the time cost of sending a query to the backend server
• ConnOK : how many connections were established successfully.
• ConnERR : how many connections weren’t established successfully.
• MaxConnUsed : high water mark of connections used by ProxySQL for sending queries to the backend server
• Queries : the number of queries routed towards this particular backend server
• Queries_GTID_sync: the number of queries routed for GTID synchronization
• Bytes_data_sent : the amount of data sent to the backend. This does not include metadata (packets’ headers)
• Bytes_data_recv : the amount of data received from the backend. This does not include metadata (packets’ headers, OK/ERR packets, fields’ description, etc)
• Latency_us : the current ping time in microseconds, as reported from Monitor

In the following output, it is possible to note how efficient ProxySQL is by using few connections.

Admin> SELECT hostgroup hg, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR FROM stats_mysql_connection_pool WHERE ConnUsed+ConnFree > 0 ORDER BY hg, srv_host;
+----+-------------------+--------+----------+----------+--------+---------+
| hg | srv_host          | status | ConnUsed | ConnFree | ConnOK | ConnERR |
+----+-------------------+--------+----------+----------+--------+---------+
| 10 | back001-db-master | ONLINE | 69       | 423      | 524    | 0       |
| 11 | back001-db-master | ONLINE | 0        | 1        | 1      | 0       |
| 11 | back001-db-reader | ONLINE | 0        | 11       | 11     | 0       |
| 20 | back002-db-master | ONLINE | 9        | 188      | 197    | 2       |
| 21 | back002-db-reader | ONLINE | 0        | 1        | 1      | 0       |
| 31 | back003-db-master | ONLINE | 0        | 3        | 3      | 0       |
| 31 | back003-db-reader | ONLINE | 1        | 70       | 71     | 0       |
+----+-------------------+--------+----------+----------+--------+---------+
7 rows in set (0.00 sec)

Admin> SELECT hostgroup hg, srv_host, Queries, Bytes_data_sent, Bytes_data_recv, Latency_us FROM stats_mysql_connection_pool WHERE ConnUsed+ConnFree > 0 ORDER BY hg, srv_host;
+----+-------------------+---------+-----------------+-----------------+------------+
| hg | srv_host          | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+----+-------------------+---------+-----------------+-----------------+------------+
| 10 | back001-db-master | 8970367 | 9858463664      | 145193069937    | 17684      |
| 11 | back001-db-master | 69      | 187675          | 2903            | 17684      |
| 11 | back001-db-reader | 63488   | 163690013       | 4994101         | 113        |
| 20 | back002-db-master | 849461  | 1086994186      | 266034339       | 101981     |
| 21 | back002-db-reader | 8       | 6992            | 984             | 230        |
| 31 | back003-db-master | 3276    | 712803          | 81438709        | 231        |
| 31 | back003-db-reader | 2356904 | 411900849       | 115810708275    | 230        |
+----+-------------------+---------+-----------------+-----------------+------------+
7 rows in set (0.00 sec)

Related tables:

• mysql_servers — defines the backend servers that appear in this table
• stats_mysql_free_connections — detailed view of individual idle connections
• stats_mysql_global — global connection pool counters (ConnPool_get_conn_success, etc.)

stats_mysql_connection_pool_reset

Querying the stats_mysql_connection_pool_reset table is equivalent to querying stats_mysql_connection_pool, with the only difference that all statistics are reset to 0 at the end of the SELECT statement.

stats_mysql_errors

This table tracks errors reported by the backend servers during query execution. Servers are identified based on their hostgroup, address and port. Here is the statement used to create the stats_mysql_errors table:

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) )

Each row represents a backend server within a hostgroup. The fields have the following semantics:

• hostgroup : the hostgroup in which the backend server belongs. Note that a single backend server can belong to more than one hostgroup
• hostname, port : the TCP endpoint on which the mysqld backend server is listening for connections
• username : the username of the backend server user
• client_address : the frontend address connecting to ProxySQL
• schemaname : the schema the query was using when the error was generated
• errno : the error number generated by the backend server
• count_star : the number of times this error was seen for the user/schema/hostgroup/hostname/port combination since the last reset of mysql error statistics
• first_seen : when this entry was first seen since the last reset of mysql error statistics
• last_seen : when this entry was last seen
• last_error : exact error text for the last error of this entry

Admin> SELECT * FROM stats_mysql_errors LIMIT 2\G
*************************** 1. row ***************************
      hostgroup: 10
       hostname: back001-db-master
           port: 3306
       username: app_user
 client_address: 10.0.1.50
     schemaname: mydb
          errno: 1045
     count_star: 3
     first_seen: 1700000100
      last_seen: 1700000350
     last_error: Access denied for user 'app_user'@'10.0.1.50' (using password: YES)
*************************** 2. row ***************************
      hostgroup: 10
       hostname: back001-db-master
           port: 3306
       username: app_user
 client_address: 10.0.1.51
     schemaname: mydb
          errno: 1146
     count_star: 1
     first_seen: 1700000200
      last_seen: 1700000200
     last_error: Table 'mydb.missing_table' doesn't exist
2 rows in set (0.00 sec)

Related tables:

• stats_mysql_connection_pool — per-backend connection and query statistics
• mysql_servers — defines the backend servers referenced in this table

stats_mysql_errors_reset

Querying the stats_mysql_errors_reset table is equivalent to querying stats_mysql_errors, with the only difference that all statistics are reset to 0 at the end of the SELECT statement.

stats_mysql_free_connections

This table provides information about free connections in ProxySQL’s connection pool. Here is the statement used to create the table:

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)

Each row represents a connection to a backend server within a hostgroup. The fields have the following semantics:

• fd : the file descriptor of ProxySQL’s connection to the backend server
• hostgroup : the hostgroup in which the backend server belongs. Note that a single backend server can belong to more than one hostgroup
• srv_host, srv_port : the TCP endpoint to which mysqld backend server the connection is made
• user : the username of the backend server user
• schema : the schema the connection is using
• init_connect : init_connect instruction used when the connection was created, if any
• time_zone : the time_zone that was specified on connection, if any
• sql_mode : the current sql_mode of the connection
• autocommit : the current autocommit setting of the connection
• idle_ms : how long, in milliseconds, since the connection was used
• statistics : json object of statistics related to the connection, containing information of how much query traffic the connection has handled, how old the connection is, and how much usage it has received as part of the connection pool
• mysql_info : additional metadata about the connection to the backend, such as the server version and character set in use.

Admin> SELECT fd, hostgroup, srv_host, srv_port, user, schema, idle_ms FROM stats_mysql_free_connections LIMIT 3;
+----+-----------+-------------------+----------+----------+--------+---------+
| fd | hostgroup | srv_host          | srv_port | user     | schema | idle_ms |
+----+-----------+-------------------+----------+----------+--------+---------+
| 14 | 10        | back001-db-master | 3306     | app_user | mydb   | 312     |
| 17 | 10        | back001-db-master | 3306     | app_user | mydb   | 891     |
| 23 | 11        | back001-db-reader | 3306     | app_user | mydb   | 1452    |
+----+-----------+-------------------+----------+----------+--------+---------+
3 rows in set (0.00 sec)

Related tables:

• stats_mysql_connection_pool — aggregate connection pool statistics per backend server
• mysql_servers — defines the backend servers referenced in this table

stats_mysql_global

One of the most important tables in the stats schema is stats_mysql_global, which exports counters related to various ProxySQL internals. Here is the statement used to create the stats_mysql_global table:

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

Each row represents a global statistic at the proxy level related to MySQL including:

• Key Memory Usage
• Prepared Statements
• Query Cache
• Processing Time
• Global Connections
• Threads / Workers
• Connection Pooling
• Transactions
• SQL Statements

The same output is available using the SHOW MYSQL STATUS command. Example:

Admin> select * from stats.stats_mysql_global limit 5;
+------------------------------+----------------+
| Variable_Name                | Variable_Value |
+------------------------------+----------------+
| ProxySQL_Uptime              | 93382          |
| Active_Transactions          | 0              |
| Client_Connections_aborted   | 0              |
| Client_Connections_connected | 4              |
| Client_Connections_created   | 4              |
+------------------------------+----------------+

Variable description:

• ProxySQL_Uptime: the total uptime of ProxySQL in seconds
• Active_Transactions: provides a count of how many client connections are currently processing a transaction
• Client_Connections_aborted: client failed connections (or closed improperly)
• Client_Connections_connected: client connections that are currently connected
• Client_Connections_created: total number of client connections created
• Client_Connections_non_idle: number of client connections that are currently handled by the main worker threads. If ProxySQL isn’t running with “—idle-threads”, Client_Connections_non_idle is always equal to Client_Connections_connected.
• Client_Connections_hostgroup_locked: number of client connections locked to a specific hostgroup
• Server_Connections_aborted: backend failed connections (or closed improperly)
• Server_Connections_connected: backend connections that are currently connected
• Server_Connections_created: total number of backend connections created
• Server_Connections_delayed: number of backend connections that were delayed
• Servers_table_version: internal version counter for the servers table
• Backend_query_time_nsec: time spent making network calls to communicate with the backends
• Queries_backends_bytes_recv: total bytes received from backends
• Queries_backends_bytes_sent: total bytes sent to backends
• Queries_frontends_bytes_recv: total bytes received from frontends (clients)
• Queries_frontends_bytes_sent: total bytes sent to frontends (clients)
• backend_lagging_during_query: number of queries where the backend was detected as lagging
• backend_offline_during_query: number of queries where the backend went offline during execution
• mysql_backend_buffers_bytes: buffers related to backend connections if “fast_forward” is used (0 means fast_forward is not used)
• mysql_frontend_buffers_bytes: buffers related to frontend connections (read/write buffers and other queues)
• mysql_killed_backend_connections: number of backend connections that were killed
• mysql_killed_backend_queries: number of backend queries that were killed
• mysql_unexpected_frontend_com_quit: number of unexpected COM_QUIT packets from clients
• mysql_unexpected_frontend_packets: number of other unexpected packets from clients
• client_host_error_killed_connections: number of connections killed due to client host error limits
• hostgroup_locked_queries: number of queries processed while a hostgroup lock was held
• hostgroup_locked_set_commands: number of SET commands processed while a hostgroup lock was held
• max_connect_timeouts: number of connection attempts that timed out
• new_req_conns_count: number of new connection requests made to backends
• automatic_detected_sql_injection: number of automatically detected SQL injection attempts
• whitelisted_sqli_fingerprint: number of whitelisted SQL injection fingerprint matches
• generated_error_packets: number of error packets generated by ProxySQL (not the backend)
• Selects_for_update__autocommit0: number of SELECT FOR UPDATE queries in autocommit=0 sessions
• mysql_session_internal_bytes: other memory used by ProxySQL to handle MySQL Sessions
• Com_autocommit: number of autocommit commands
• Com_autocommit_filtered: number of autocommit commands that were filtered
• Com_backend_change_user: number of COM_CHANGE_USER commands sent to backends
• Com_backend_init_db: number of COM_INIT_DB commands sent to backends
• Com_backend_set_names: number of SET NAMES commands sent to backends
• Com_commit: number of COMMIT commands
• Com_commit_filtered: number of COMMIT commands that were filtered
• Com_frontend_init_db: number of COM_INIT_DB commands received from clients
• Com_frontend_set_names: number of SET NAMES commands received from clients
• Com_frontend_use_db: number of USE database commands received from clients
• Com_rollback: number of ROLLBACK commands
• Com_rollback_filtered: number of ROLLBACK commands that were filtered
• Com_frontend_stmt_prepare / Com_frontend_stmt_execute / Com_frontend_stmt_close: represent the number of “PREPARE / EXECUTE / CLOSE” executed by clients. It is common for clients to prepare a statement, execute the statement once, and then close it so these 3 metrics’ values are often almost identical.
• Com_backend_stmt_prepare / Com_backend_stmt_execute / Com_backend_stmt_close: represent the number of “PREPARE” / “EXECUTE” / “CLOSE” executed by ProxySQL against the backends. Com_backend_stmt_execute should roughly match Com_frontend_stmt_execute. ProxySQL tracks and re-uses prepared statements across connections where possible so Com_backend_stmt_prepare is generally much smaller than Com_frontend_stmt_prepare. Com_backend_stmt_close is always 0 in the current implementation as ProxySQL never closes prepared statements as it is inefficient (a network round trip would be wasted). Instead, when “mysql-max_stmts_per_connection” is reached in a backend connection and the connection returns to the connection pool and is reset (implicitly closing all prepared statements)
• MySQL_Thread_Workers: number of MySQL Thread workers i.e. “mysql-threads”
• MySQL_Monitor_Workers: The number of monitor threads. By default it is twice the number of worker threads, initially capped to 16 yet more threads will be created checks are being queued. Monitor threads perform blocking network operations and do not consume much CPU
• MySQL_Monitor_Workers_Aux: number of auxiliary monitor worker threads
• MySQL_Monitor_Workers_Started: total number of monitor worker threads that have been started
• MySQL_Monitor_connect_check_ERR: number of failed monitor connect checks
• MySQL_Monitor_connect_check_OK: number of successful monitor connect checks
• MySQL_Monitor_ping_check_ERR: number of failed monitor ping checks
• MySQL_Monitor_ping_check_OK: number of successful monitor ping checks
• MySQL_Monitor_read_only_check_ERR: number of failed monitor read-only checks
• MySQL_Monitor_read_only_check_OK: number of successful monitor read-only checks
• MySQL_Monitor_replication_lag_check_ERR: number of failed monitor replication lag checks
• MySQL_Monitor_replication_lag_check_OK: number of successful monitor replication lag checks
• ConnPool_get_conn_success: number of requests where a connection was already available in the connection pool
• ConnPool_get_conn_failure: number of requests where a connection was not available in the connection pool and either a new connection had to be created or no backend was available
• ConnPool_get_conn_immediate: number of connections that a MySQL Thread obtained from its own local connection pool cache. This value tends to be large only when there is high concurrency.
• ConnPool_get_conn_latency_awareness: number of connection pool requests that used latency-awareness routing
• Questions: the total number of client requests / statements executed
• Slow_queries: the total number of queries with an execution time greater than “mysql-long_query_time” milliseconds
• GTID_consistent_queries: number of queries that required GTID-consistent routing
• GTID_session_collected: number of GTID values collected during sessions
• Mirror_concurrency: current number of concurrently mirrored queries
• Mirror_que_length: current length of the mirror query queue
• queries_with_max_lag_ms: number of queries that had a max_lag_ms constraint applied
• queries_with_max_lag_ms__delayed: number of queries delayed due to max_lag_ms constraints
• queries_with_max_lag_ms__total_wait_time_us: total wait time in microseconds for max_lag_ms-delayed queries
• get_aws_aurora_replicas_skipped_during_query: number of Aurora replicas skipped during query routing
• Access_Denied_Max_Connections: number of connections denied due to max_connections limit
• Access_Denied_Max_User_Connections: number of connections denied due to per-user max_connections limit
• Access_Denied_Wrong_Password: number of connections denied due to wrong password
• MyHGM_myconnpoll_get: the number of requests made to the connection pool
• MyHGM_myconnpoll_get_ok: the number of successful requests to the connection pool (i.e. where a connection was available)
• MyHGM_myconnpoll_push: the number of connections returned to the connection pool
• MyHGM_myconnpoll_destroy: the number of connections considered unhealthy and therefore closed
• MyHGM_myconnpoll_reset: the number of connections that have been reset / re-initialized using “COM_CHANGE_USER”
• SQLite3_memory_bytes: memory used by SQLite
• ConnPool_memory_bytes: memory used by the connection pool to store connections metadata
• Stmt_Client_Active_Total: the total number of prepared statements that are in use by clients
• Stmt_Client_Active_Unique: this variable tracks the number of unique prepared statements currently in use by clients
• Stmt_Server_Active_Total: the total number of prepared statements currently available across all backend connections
• Stmt_Server_Active_Unique: the number of unique prepared statements currently available across all backend connections
• Stmt_Cached: this is the number of global prepared statements for which ProxySQL has metadata
• Stmt_Max_Stmt_id: when a new global prepared statement is created, a new “stmt_id” is used. Stmt_Max_Stmt_id represents the maximum “stmt_id” ever used. When metadata for a prepared statement is dropped, the “stmt_id” may be reused
• Query_Cache_Memory_bytes: memory currently used by the query cache
• Query_Cache_Entries: number of entries currently stored in the query cache
• Query_Cache_Purged: number of entries purged by the Query Cache due to TTL expiration
• Query_Cache_bytes_IN: number of bytes sent into the Query Cache
• Query_Cache_bytes_OUT: number of bytes read from the Query Cache
• Query_Cache_count_GET: number of read requests to the Query Cache
• Query_Cache_count_GET_OK: number of successful read requests to the Query Cache
• Query_Cache_count_SET: number of write requests to the Query Cache
• Query_Processor_time_nsec: the time spent inside the Query Processor to determine what action needs to be taken with the query (internal module)

Related tables:

• stats_mysql_connection_pool — per-backend connection statistics
• stats_mysql_commands_counters — per-command-type execution statistics

stats_mysql_gtid_executed

The stats_mysql_gtid_executed table provides statistics related to GTID tracking for consistent reads. The table shows the GTID sets and number of events executed on each backend node.

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

Each row represents a backend server being monitored for GTID execution. The fields have the following semantics:

• hostname : the hostname of the backend MySQL server being tracked
• port : the port of the backend MySQL server; defaults to 3306
• gtid_executed : the current GTID set executed on this backend node, as reported by the Monitor module. This value reflects the gtid_executed system variable from the backend server.
• events : the total number of GTID events executed on this backend node since ProxySQL last reset its internal GTID tracking counters

For example, here we can see a difference in GTID sets between the source (mysql1) and replicas (mysql2, mysql3):

Admin> select * from stats_mysql_gtid_executed where hostname='mysql1'G
*************************** 1. row ***************************
     hostname: mysql1
         port: 3306
gtid_executed: 85c17137-4258-11e8-8090-0242ac130002:1-65588
       events: 65581

# After a few moments...

Admin> select hostname,gtid_executed from stats_mysql_gtid_executed order by hostnameG
*************************** 1. row ***************************
     hostname: mysql1
gtid_executed: 85c17137-4258-11e8-8090-0242ac130002:1-146301
*************************** 2. row ***************************
     hostname: mysql2
gtid_executed: 85c17137-4258-11e8-8090-0242ac130002:1-146300,8a093f5f-4258-11e8-8037-0242ac130004:1-5
*************************** 3. row ***************************
     hostname: mysql3
gtid_executed: 85c17137-4258-11e8-8090-0242ac130002:1-146301,8a0ac961-4258-11e8-8003-0242ac130003:1-5

Related tables:

• stats_mysql_global — GTID_consistent_queries and GTID_session_collected counters
• mysql_servers — defines the backend servers tracked here

stats_mysql_prepared_statements_info

Because of multiplexing, it is possible that a client prepares a prepared statement (PS) in a backend connection but that connection is not free when that same client wants to execute the PS. Furthermore, it is possible that multiple clients prepare the same PS. ProxySQL addresses these issues in the following ways:

• for every unique PS, a global stmt_id is generated and its metadata are stored internally in a global cache
• each client preparing a PS gets a stmt_id that is local to that client, but mapped to the global stmt_id
• on every backend connection where a PS is prepared, the stmt_id returned by the backend is mapped to the global stmt_id

CREATE TABLE stats_mysql_prepared_statements_info (
    global_stmt_id INT NOT NULL,
    hostgroup 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)

• global_stmt_id : the global stmt_id to be used across clients and backend servers
• schemaname : the schema the prepared statement is associated with
• username : the username associated with the prepared statement
• digest : digest of the query
• ref_count_client : the number of reference counters for client connections
• ref_count_server : the number of references to backend connections
• num_columns : number of result columns in the prepared statement
• num_params : number of parameter placeholders in the prepared statement
• query : query used for the prepared statement

Admin> SELECT global_stmt_id, schemaname, username, ref_count_client, ref_count_server, num_params, query FROM stats_mysql_prepared_statements_info\G
*************************** 1. row ***************************
global_stmt_id: 1
    schemaname: mydb
      username: app_user
ref_count_client: 4
ref_count_server: 2
    num_params: 1
         query: SELECT c FROM sbtest1 WHERE id=?
*************************** 2. row ***************************
global_stmt_id: 2
    schemaname: mydb
      username: app_user
ref_count_client: 2
ref_count_server: 1
    num_params: 2
         query: INSERT INTO events (type, data) VALUES (?, ?)
2 rows in set (0.00 sec)

Related tables:

• stats_mysql_global — Stmt_Cached, Stmt_Client_Active_Total, and related counters
• stats_mysql_query_digest — query digests including prepared statement queries

stats_mysql_processlist

The stats_mysql_processlist provides information on what ProxySQL connections are doing.

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

The fields have the following semantics:

• ThreadID : the internal ID of the thread within ProxySQL. This is a 0-based numbering of the threads
• SessionID : the internal global numbering of the ProxySQL sessions, or clients’ connections (frontend). It’s useful to be able to uniquely identify such a session, for example in order to be able to kill it, or monitor a specific session only.
• user : the user with which the MySQL client connected to ProxySQL in order to execute this query
• db : the schema currently selected
• cli_host, cli_port - the (host, port) pair of the TCP connection between the MySQL client and ProxySQL
• hostgroup : the current hostgroup. If a query is being processed, this is the hostgroup towards which the query was or will be routed, or the default hostgroup. The routing is done by default in terms of the default destination hostgroup for the username with which the MySQL client connected to ProxySQL (based on mysql_users table, but it can be modified on a per-query basis by using the query rules in mysql_query_rules
• l_srv_host, l_srv_port : the local (host, port) pair of the TCP connection between ProxySQL and the backend MySQL server from the current hostgroup
• srv_host, srv_port : the (host, port) pair on which the backend MySQL server is listening for TCP connections
• command : the type of MySQL query being executed (the MySQL command verb)
• time_ms : the time in millisecond for which the query has been in the specified command state so far
• info : the actual query being executed
• status_flags: internal status flags for the session
• extended_info: JSON object holding additional information. An example:

{
"extended_info": {
"autocommit": true,
"autocommit_on_hostgroup": -1,
"backends": [
{
"conn": {
"MultiplexDisabled": false,
"autocommit": true,
"init_connect": "",
"init_connect_sent": true,
"last_set_autocommit": -1,
"mysql": {
"affected_rows": 18446744073709551615,
"charset": 8,
"db": "test",
"host": "127.0.0.1",
"host_info": "127.0.0.1 via TCP/IP",
"insert_id": 0,
"net": {
"fd": 28,
"last_errno": 0,
"max_packet_size": 1073741824,
"sqlstate": "00000"
},
"options": {
"charset_name": "utf8",
"use_ssl": 0
},
"port": 13308,
"server_status": 16386,
"server_version": "5.7.24-log",
"unix_socket": "",
"user": "root"
},
"no_backslash_escapes": false,
"ps": {
"backend_stmt_to_global_ids": [],
"global_stmt_to_backend_ids": []
},
"sql_log_bin": 1,
"sql_mode": "",
"status": {
"found_rows": false,
"get_lock": false,
"lock_tables": false,
"no_multiplex": false,
"temporary_table": false,
"user_variable": false
},
"time_zone": "SYSTEM"
},
"hostgroup_id": 11
}
],
"client": {
"client_addr": {
"address": "127.0.0.1",
"port": 37810
},
"encrypted": false,
"proxy_addr": {
"address": "0.0.0.0",
"port": 6033
},
"stream": {
"bytes_recv": 111,
"bytes_sent": 89,
"pkts_recv": 2,
"pkts_sent": 2
},
"userinfo": {
"password": "*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B",
"username": "root"
}
},
"conn": {
"autocommit": true,
"charset": 8,
"no_backslash_escapes": false,
"ps": {
"client_stmt_to_global_ids": []
},
"sql_log_bin": 1,
"sql_mode": "",
"status": {
"compression": false,
"transaction": false
},
"time_zone": "SYSTEM"
},
"current_hostgroup": 11,
"default_hostgroup": 10,
"default_schema": "",
"last_HG_affected_rows": -1,
"last_insert_id": 0,
"thread_session_id": 5,
"transaction_persistent": true
}
}

Please note that this is just a snapshot in time of the actual MySQL queries being run. There is no guarantee that the same queries will be running a fraction of a second later. Here is what the results look like without extended_info:

mysql> select * from stats_mysql_processlist;
+----------+-----------+------+------+-----------+----------+-----------+------------+------------+-----------+----------+---------+---------+---------------------------------------+
| ThreadID | SessionID | user | db   | cli_host  | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host  | srv_port | command | time_ms | info                                  |
+----------+-----------+------+------+-----------+----------+-----------+------------+------------+-----------+----------+---------+---------+---------------------------------------+
| 3        | 1         | root | test | 127.0.0.1 | 51831    | 0         | 127.0.0.1  | 55310      | 127.0.0.1 | 3306     | Query   | 0       | SELECT c FROM sbtest1 WHERE id=198898 |
| 0        | 2         | root | test | 127.0.0.1 | 51832    | 0         | 127.0.0.1  | 55309      | 127.0.0.1 | 3306     | Query   | 0       | SELECT c FROM sbtest3 WHERE id=182586 |
| 2        | 3         | root | test | 127.0.0.1 | 51833    | 0         | 127.0.0.1  | 55308      | 127.0.0.1 | 3306     | Query   | 0       | SELECT c FROM sbtest1 WHERE id=199230 |
| 1        | 4         | root | test | 127.0.0.1 | 51834    | 0         | 127.0.0.1  | 55307      | 127.0.0.1 | 3306     | Query   | 0       | SELECT c FROM sbtest2 WHERE id=201110 |
+----------+-----------+------+------+-----------+----------+-----------+------------+------------+-----------+----------+---------+---------+---------------------------------------+
4 rows in set (0.02 sec)

You can also query contents of the extended_info by using JSON_EXTRACT the following way (in this example only age_ms is queried, but you can add further items to the query):

mysql> SELECT ThreadID, SessionID, user, db, hostgroup, JSON_EXTRACT(extended_info, '$.age_ms') age_ms FROM stats_mysql_processlistG
*************************** 1. row ***************************
 ThreadID: 5
SessionID: 8
     user: root
       db: test
hostgroup: 0
   age_ms: 839878
1 row in set (0.03 sec)

Note: ProxySQL also supports the commands SHOW PROCESSLIST and SHOW FULL PROCESSLIST to return information related to current sessions.

Related tables:

• stats_mysql_query_digest — historical query statistics
• mysql_users — defines the users that appear in this table

stats_mysql_query_digest

The stats_mysql_query_digest table provides information about queries that have been processed by ProxySQL. It is very useful for identifying queries that can be routed to readers, rewritten or cached.

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)
)

Each row represents a class of queries all having the same parameters (but with different values) routed through ProxySQL. Here’s what a typical result looks like:

mysql> select * from stats_mysql_query_digest order by count_star desc limit 2;
+------------+----------+--------------------+----------------------------------+------------+------------+------------+------------+----------+----------+
| schemaname | username | digest             | digest_text                      | count_star | first_seen | last_seen  | sum_time   | min_time | max_time |
+------------+----------+--------------------+----------------------------------+------------+------------+------------+------------+----------+----------+
| test       | root     | 0x7721D69250CB40   | SELECT c FROM sbtest3 WHERE id=? | 8122800    | 1441091306 | 1441101551 | 7032352665 | 1010     | 117541   |
| test       | root     | 0x3BC2F7549D058B6F | SELECT c FROM sbtest4 WHERE id=? | 8100134    | 1441091306 | 1441101551 | 7002512958 | 101      | 102285   |
+------------+----------+--------------------+----------------------------------+------------+------------+------------+------------+----------+----------+

The fields have the following semantics:

• hostgroup : the hostgroup where the query was sent. A value of -1 represent a query hitting the Query Cache
• schemaname : the schema that is currently being queried
• username : the username with which the MySQL client connected to ProxySQL
• client_address : the address of the client if mysql-query_digests_track_hostname=true
• digest : a hexadecimal hash that uniquely represents a query with its parameters stripped
• digest_text : the actual text with its parameters stripped
• count_star : the total number of times the query has been executed (with different values for the parameters)
• first_seen : unix timestamp, the first moment when the query was routed through the proxy
• last_seen : unix timestamp, the last moment (so far) when the query was routed through the proxy
• sum_time : the total time in microseconds spent executing queries of this type. This is particularly useful to figure out where the most time is spent in your application’s workload, and provides a good starting point for where to improve
• min_time, max_time : the range of durations to expect when executing such a query. min_time is the minimal execution time seen so far, while max_time represents the maximal execution time, both in microseconds.
• sum_rows_affected : the total number of rows affected
• sum_rows_sent : the total number of rows sent. This doesn’t currently count the number of rows returned from the Query Cache

The time in this table refers to the time elapsed between the time in which ProxySQL receives the query from the client, and the time in which ProxySQL is ready to send the query to the client. Therefore these times represent the elapsed time as close as possible as seen from the client. To be more precise, it is possible that before executing a query, ProxySQL needs to change charset or schema, find a new backend if the current one is not available anymore, run the query on a different backend if the current one fails, or wait for a connection to become free because currently all the connections are in use. Note: statistics for table stats_mysql_query_digest are processed only if global variables mysql-commands_stats and mysql-query_digests are set to true. This is the default, and used for other queries processing. It is recommended to NOT disable them.

Related tables:

• stats_mysql_query_rules — shows which query rules matched traffic
• stats_mysql_commands_counters — aggregated counters by SQL command type
• stats_mysql_query_events — individual query event records

stats_mysql_query_digest_reset

Table stats_mysql_query_digest_reset is identical to stats_mysql_query_digest, but reading from stats_mysql_query_digest_reset causes all statistics to be reset at the end of the SELECT.

stats_mysql_query_events

The stats_mysql_query_events table stores individual query event records for detailed per-query analysis. This table is populated when query logging or query events are enabled via ProxySQL configuration, and provides a granular audit trail of query execution including timing, routing decisions, and outcome details.

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
)

Each row represents a single recorded query event. The fields have the following semantics:

• id : auto-incrementing primary key uniquely identifying each event record
• thread_id : the internal ProxySQL worker thread ID that processed the query
• username : the MySQL username with which the client connected to ProxySQL
• schemaname : the schema/database in use when the query was executed
• start_time : query start time as a Unix timestamp in microseconds
• end_time : query end time as a Unix timestamp in microseconds; the difference end_time - start_time gives the total query execution time in microseconds
• query_digest : hexadecimal digest hash identifying the normalized query pattern; can be joined with stats_mysql_query_digest.digest
• query : the full query text as received from the client
• server : the backend server (host:port) that executed the query
• client : the client address (host:port) that sent the query to ProxySQL
• event_type : numeric code identifying the type of query event logged
• hid : the hostgroup ID to which the query was routed
• extra_info : additional event metadata in JSON format, if available
• affected_rows : the number of rows affected by the query (for DML statements)
• last_insert_id : the value of the last auto-increment insert ID generated by this query, if applicable
• rows_sent : the number of rows returned to the client
• client_stmt_id : the client-side prepared statement ID, if the query was executed as a prepared statement
• gtid : the GTID value associated with this query execution, if GTID tracking is enabled
• errno : MySQL error number if the query resulted in an error; 0 indicates success
• error : MySQL error message text if the query resulted in an error; empty on success

Example query to view recent events:

Admin> SELECT * FROM stats_mysql_query_events ORDER BY id DESC LIMIT 5;

Note: The output of this query depends on query event logging configuration. Query events are only recorded when query event logging is enabled via the appropriate ProxySQL global variables.

Related tables:

• stats_mysql_query_digest — aggregated query statistics by digest pattern
• stats_mysql_processlist — real-time snapshot of currently executing queries

stats_mysql_query_rules

The stats_mysql_query_rules table exports how many times query rules were matching traffic.

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

The fields have the following semantics:

• rule_id : the id of the rule, can be joined with the main.mysql_query_rules table on the rule_id field.
• hits : the total number of hits for this rule. One hit is registered if the current incoming query matches the rule. Each time a new query that matches the rule is processed, the number of hits is increased.

Note that the hits value is reset every time query rules are loaded to runtime, either through explicit LOAD MYSQL QUERY RULES TO RUNTIME or through implicit re-sync via ProxySQL Cluster.

Admin> SELECT * FROM stats_mysql_query_rules;
+---------+-----------+
| rule_id | hits      |
+---------+-----------+
| 1       | 5323667   |
| 2       | 153       |
| 5       | 0         |
+---------+-----------+
3 rows in set (0.00 sec)

Related tables:

• mysql_query_rules — defines the query rules tracked in this table
• stats_mysql_query_digest — per-query-pattern statistics

stats_mysql_users

The stats_mysql_users table reports a list of users, their current number of frontend connections, and the total number of frontend connections they can create (as defined in mysql_users.max_connections).

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

The fields have the following semantics:

• username : the username from the mysql_users table
• frontend_connections : the number of connections currently used by this user
• frontend_max_connections : the maximum number of connections this user is allowed to use, as configured in the mysql_users table

Admin> SELECT username, frontend_connections conns, frontend_max_connections max_conns  FROM stats_mysql_users WHERE frontend_connections > 0;
+----------------+-------+-----------+
| username       | conns | max_conns |
+----------------+-------+-----------+
| proxyab_rw_001 | 138   | 20000     |
| proxyab_ro     | 4     | 20000     |
| proxyab_rw     | 406   | 20000     |
| main_ro        | 4316  | 20000     |
| main_rw        | 800   | 20000     |
| test_rw        | 2     | 5000      |
| test_ro        | 1     | 5000      |
+----------------+-------+-----------+
7 rows in set (0.00 sec)

Related tables:

• mysql_users — defines the users and their max_connections limits tracked here
• stats_mysql_global — Access_Denied_Max_User_Connections counter