System Library _03_stats Library for 09_ProxySQL Configuration
Note: Subsequent official updates to github were not written between 201904 and 201905
~
~
Statistics]
Stats library [The stats database]
This database contains indicators for data collection of ProxySQL's internal capabilities.Here, you'll find information about how often certain counters are triggered and when queries are executed through ProxySQL.
It should be noted that:
1) Only administrator users accessed with admin-stats_credentials can access the Schema.
2) Typically, by examining in-memory data structures, the tables in this database will be populated dynamically when an SQL query against these metrics is executed.
Admin>SHOW TABLES FROM stats; +--------------------------------------+ | tables | +--------------------------------------+ | global_variables | | stats_memory_metrics | | stats_mysql_commands_counters | | stats_mysql_connection_pool | | stats_mysql_connection_pool_reset | | stats_mysql_errors | | stats_mysql_errors_reset | | stats_mysql_global | | stats_mysql_gtid_executed | | stats_mysql_prepared_statements_info | | stats_mysql_processlist | | stats_mysql_query_digest | | stats_mysql_query_digest_reset | | stats_mysql_query_rules | | stats_mysql_users | | stats_proxysql_servers_checksums | | stats_proxysql_servers_metrics | | stats_proxysql_servers_status | +--------------------------------------+ 18 rows in set (0.00 sec)
1,global_variables
The table stats.global_variables is used only to facilitate connection pooling by issuing SELECT @@max_allowed_pa cket or similar queries.
Its contents are as follows, which can be ignored:
Admin>SELECT * FROM stats.global_variables; +--------------------------+----------------+ | variable_name | variable_value | +--------------------------+----------------+ | mysql-max_allowed_packet | 4194304 | +--------------------------+----------------+ 1 row in set (0.01 sec)
2,stats_memory_metrics
Admin>SELECT * FROM stats.stats_memory_metrics; +------------------------------+----------------+ | Variable_Name | Variable_Value | +------------------------------+----------------+ | SQLite3_memory_bytes | 3152560 | | jemalloc_resident | 11894784 | | jemalloc_active | 10059776 | | jemalloc_allocated | 8550184 | | jemalloc_mapped | 25165824 | | jemalloc_metadata | 1831872 | | jemalloc_retained | 0 | | Auth_memory | 1468 | | query_digest_memory | 0 | | mysql_query_rules_memory | 3985 | | stack_memory_mysql_threads | 41943040 | | stack_memory_admin_threads | 20971520 | | stack_memory_cluster_threads | 0 | +------------------------------+----------------+ 13 rows in set (0.00 sec)
This table shows the memory usage of various structures in ProxySQL.
Currently, only a few structures (SQLite, Auth module, Query Digests) are tracked, but more internal structures will be tracked and more metrics exported in the future.
The most important value to monitor in this table is the value associated with jemalloc, a memory allocator built inside ProxySQL.For a detailed explanation of the various values, visit the jemalloc website ( http://jemalloc.net/jemalloc.3.html).
In short:
jemalloc_allocated: The number of bytes allocated by the application.
jemalloc_active: The total number of bytes of pages allocated by the application.
jemalloc_mapped: The total number of bytes mapped by the allocator address.
jemalloc_metadata: The number of bytes dedicated to metadata.
jemalloc_resident: The number of bytes in the physically hosted data page mapped by the allocator.
Auth_memory: Memory used by the authentication module to store user credentials and attributes
SQLite3_memory_bytes: Memory used by embedded SQLite
query_digest_memory: Memory used to store stats_mysql_query_digest-related data.
3,stats_mysql_commands_counters
The table stats_mysql_commands_counters is used to hold records of all types of queries executed and collect statistics based on execution time, grouping them into corresponding types for storage:
Admin>SELECT * FROM stats_mysql_commands_counters ORDER BY Total_cnt DESC LIMIT 1 \G;
*************************** 1. row ***************************
Command: ALTER_TABLE
Total_Time_us: 0
Total_cnt: 0
cnt_100us: 0
cnt_500us: 0
cnt_1ms: 0
cnt_5ms: 0
cnt_10ms: 0
cnt_50ms: 0
cnt_100ms: 0
cnt_500ms: 0
cnt_1s: 0
cnt_5s: 0
cnt_10s: 0
cnt_INFs: 0
1 row in set (0.00 sec)The above fields have the following semantics:
Command: The type of SQL command that has been executed.For example: FLUSH, INSERT, KILL, SELECT FOR UPDATE, etc.
Total_Time_us: The total time spent executing this type of command in microseconds.
total_cnt: The total number of commands of this type executed.
Cnt_100us, cnt_500us,..., cnt_10s, cnt_INFs: The total number of commands of a given type executed within a specified time limit (record values do not include values in the previous range).
For example, cnt_500us is the number of commands executed within 500 microseconds, but only over 100 microseconds are recorded (because there is also a cnt_100us field).cnt_INFs is the number of commands executed for more than 10 seconds.
Note: Statistics for table stats_mysql_commands_counters are processed only when the global variable mysql-commands_stats is set to true.The default mysql-commands_stats is true for processing other queries (not just information statistics here).It is not recommended to disable it.
4,stats_mysql_connection_pool
The following statements are used to create the stats_mysql_connection_pool table:
Admin>show create table stats.stats_mysql_connection_pool \G;
*************************** 1. row ***************************
table: stats_mysql_connection_pool
Create Table: 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)
1 row in set (0.00 sec)Each row represents a back-end server within a host group.These fields have the following semantics:
hostgroup: The host group to which the back-end server belongs.Note that a single back-end server can belong to multiple host groups.
Srv_host, srv_port: mysqld backend server is listening for connected addresses, TCP endpoints
Status: The status of the back-end server.It can be ONLINE, SHUNNED, OFFLINE_SOFT, OFFLINE_HARD.For more information on what each state means, see the instructions in the mysql_servers table above (system library _01_main Library of the 09_ProxySQL configuration, MEMORY layer table, and RUNTIME layer table.txt).
ConnUsed: How many connections ProxySQL currently uses to send queries to the back-end server.
ConnFree: How many connections are currently free.They need to remain open to minimize the time cost of sending queries to back-end servers (without establishing connections).
ConnOK: The number of successful connection establishment.
ConnERR: Number of connection failures.
MaxConnUsed :
Queries: Number of queries routed to this particular backend server.
Queries_GTID_sync :
Bytes_data_sent: The amount of data sent to the back end in Byte units.This does not include metadata (data header)
Bytes_data_recv: The amount of data received from the back end in Byte units.This does not include metadata (headers, OK/ERR packets, field descriptions, etc.)
Latency_us: The current ping time, or delay, reported from Monitor (in microseconds)
This table outputs statistics about the back-end server.Servers are identified by their host groups, addresses, and ports, and statistics are available about connections, queries, and traffic.
For example:
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)
In the above context, you may want to look at how multiplexing works effectively (it uses very few connections).
5,stats_mysql_connection_pool_reset
The query table stats_mysql_connection_pool_reset is equivalent to the query stats_mysql_connection_pool. The only difference is that for the reset table, all statistics are reset to zero at the end of the SELECT statement.
6,stats_mysql_errors
ToDo==>Under Development
7,stats_mysql_errors_reset
ToDo==>Under Development
8,stats_mysql_global
One of the most important tables in stats mode is stats_mysql_global, which gives counters related internally to various ProxySQL.
The following statements are used to create the stats_mysql_global table:
Admin>show create table stats.stats_mysql_global \G;
*************************** 1. row ***************************
table: stats_mysql_global
Create Table: CREATE TABLE stats_mysql_global (
Variable_Name VARCHAR NOT NULL PRIMARY KEY,
Variable_Value VARCHAR NOT NULL)
1 row in set (0.00 sec)Each row of records in the table represents global statistics at the proxy level associated with MySQL, including:
*) Key Memory Usage (primary memory usage)
*) Prepared Statements (Preprocessing statement)
*) Query Cache
*) Processing Time (processing time)
*) Global Connections (Global Connections)
*) Threads / Workers (Thread/workers)
*) Connection Pooling
*) Transactions (Transactions)
*) SQL Statements(SQL statement)
Use select * from stats.stats_mysql_global s and use the SHOW MYSQL STATUS command to get the same output.
For example:
Admin>select * from stats.stats_mysql_global limit 5; +------------------------------+----------------+ | Variable_Name | Variable_Value | +------------------------------+----------------+ | ProxySQL_Uptime | 176016 | | Active_Transactions | 0 | | Client_Connections_aborted | 0 | | Client_Connections_connected | 0 | | Client_Connections_created | 0 | +------------------------------+----------------+ 5 rows in set (0.00 sec)
Variable Description:
Access_Denied_Max_Connections ==>
Access_Denied_Max_User_Connections ==>
Access_Denied_Wrong_Password ==>
Active_Transactions ==>Provides a count of the number of client connections currently processing transactions.
Backend_query_time_nsec ==>Time spent communicating with back-end MySQL server.
Client_Connections_aborted ==>Count of client connection failures (or incorrect closures).
Client_Connections_connected ==>Number of client connections currently being held connected.
Client_Connections_created ==>Total number of client connections created.
Client_Connections_non_idle ==>Number of client connections currently being processed by the main worker thread.Client_Connections_non_idle always equals Client_Connections_connected if ProxySQL is not running with--idle-threads
Com_autocommit ==>
Com_autocommit_filtered ==>
Com_backend_change_user ==>
Com_backend_init_db ==>
Com_backend_set_names ==>
Com_backend_stmt_CLOSE ==> indicates the number of "CLOSEs" ProxySQL executes against the backend.This value is always zero in the current version because ProxySQL never closes the prepared statement because it is inefficient (network communication is wasted).Conversely, when the back-end connection reaches the'mysql-max_stmts_per_connection'threshold, the connection is returned to the connection pool and reset (at which point all prepared statements are implicitly closed)
Com_backend_stmt_EXECUTE ==>Indicates the number of "EXECUTEs" ProxySQL executes against the backend.It should roughly match Com_frontend_stmt_execute.
Com_backend_stmt_PREPARE ==> indicates the number of "PREPAREs" ProxySQL executes against the backend.ProxySQL tracks and reuses prepared statements across connections when possible, so this value is usually much smaller than Com_frontend_stmt_prepare.
Com_commit ==>
Com_commit_filtered ==>
Com_frontend_init_db ==>
Com_frontend_set_names ==>
Com_frontend_stmt_CLOSE ==> indicates the number of CLOSEs executed by the client.
Com_frontend_stmt_EXECUTE ==>Indicates the number of "EXECUTEs" executed by the client.
Com_frontend_stmt_PREPARE ==>Indicates the number of "PREPAREs" executed by the client.Clients typically execute a statement through the PREPARE, execute, close processes, so these three measures are usually nearly identical.
Com_frontend_use_db ==>
Com_rollback ==>
Com_rollback_filtered ==>
ConnPool_get_conn_failure ==>Number of requests to get a connection failure from the connection pool and create a new connection or no backend is available.
ConnPool_get_conn_immediate ==>The number of connections MySQL threads get from their own local connection pool cache (referring to the MySQL service's own connection pool).This value is only high if there is high concurrency.
ConnPool_get_conn_success ==>Number of requests that successfully acquired an existing connection from the connection pool.
ConnPool_memory_bytes ==>The memory used by the connection pool to store connection metadata.
GTID_consistent_queries ==>
GTID_session_collected ==>
Mirror_concurrency ==>
Mirror_queue_length ==>
MyHGM_myconnpoll_destroy ==>Number of connections that are considered to have an exception and have been closed.
MyHGM_myconnpoll_get ==>Number of requests to the connection pool.
MyHGM_myconnpoll_get_ok ==>Number of successful requests to the connection pool (that is, connections are available).
MyHGM_myconnpoll_push ==>Returns the number of connections to the connection pool.
MyHGM_myconnpoll_reset ==>Number of connections reset/reinitialized using'COM_CHANGE_USER'.
MySQL_Monitor_Workers ==>Number of monitor threads.By default, it is twice the number of worker threads (MySQL_Monitor_Workers column), initially limited to 16, and then automatically creates more threads based on how many tasks are to be checked in the monitoring queue.Monitoring threads perform monitoring blocking network operations without consuming too much CPU.
MySQL_Monitor_Workers_Aux ==>
MySQL_Monitor_Workers_Started ==>
MySQL_Monitor_connect_check_ERR ==>
MySQL_Monitor_connect_check_OK ==>
MySQL_Monitor_ping_check_ERR ==>
MySQL_Monitor_ping_check_OK ==>
MySQL_Monitor_read_only_check_ERR ==>
MySQL_Monitor_read_only_check_OK ==>
MySQL_Monitor_replication_lag_check_ERR ==>
MySQL_Monitor_replication_lag_check_OK ==>
MySQL_Thread_Workers ==>Number of MySQL (working) threads, which is the value of the mysql-threads parameter.
ProxySQL_Uptime ==>Total run time of ProxySQL in seconds.
Queries_backends_bytes_recv ==>
Queries_backends_bytes_sent ==>
Queries_frontends_bytes_recv ==>
Queries_frontends_bytes_sent ==>
Query_Cache_Entries ==>Number of entries currently stored in the query cache.
Query_Cache_Memory_bytes ==>Query cache memory usage.
Query_Cache_Purged ==>Number of entries cleared by query cache due to TTL expiration.
Query_Cache_bytes_IN ==>Number of bytes sent to the query cache.
Query_Cache_bytes_OUT ==>The number of bytes read from the query cache.
Query_Cache_count_GET ==>Number of read requests.
Query_Cache_count_GET_OK ==>Number of successful requests to read.
Query_Cache_count_SET ==>Number of write requests.
Query_Processor_time_nsec ==>The amount of time spent inside the query processor to determine what action the query needs to take (internal module).
Questions ==>Total number of client requests/statements executed.
SQLite3_memory_bytes ==>Memory used by SQLite.
Server_Connections_aborted ==>Number of times the backend connection failed (or was not closed properly).
Server_Connections_connected ==>Number of backend connections currently connected.
Server_Connections_created ==>Total number of backend connections created.
Server_Connections_delayed ==>
Servers_table_version ==>
Slow_queries ==>The number of milliseconds of execution time is greater than the total number of queries for the'mysql-long_query_time'parameter.
Stmt_Cached ==>Number of global prepared statements with metadata for proxysql.
Stmt_Client_Active_Total ==>Total number of prepared statements used by the client.
Stmt_Client_Active_Unique ==>This variable tracks the number of unique prepared statements currently used by clients
Stmt_Max_Stmt_id ==>When a new prepared global statement is created, the new'stmt_id'will be used.Stmt_Max_Stmt_id represents the largest'stmt_id'ever used.'stmt_id'can be reused when removing metadata for a prepared statement.
Stmt_Server_Active_Total ==>Total number of prepared statements currently available in all backend connections.
Stmt_Server_Active_Unique ==>Number of unique prepared statements currently available in all backend connections.
generated_error_packets ==>
max_connect_timeouts ==>
Mysql_backend_buffers_bytes ==>If fast_forward is used, the buffer associated with the backend connection is recorded (0 means fast_forward is not used)
Mysql_frontend_buffers_bytes ==>Buffers associated with front-end connections (read/write buffers and other queues)
mysql_killed_backend_connections ==>
mysql_killed_backend_queries ==>
Mysql_session_internal_bytes ==>Extra memory used by ProxySQL to process MySQL Sessions
mysql_unexpected_frontend_com_quit ==>
mysql_unexpected_frontend_packets ==>
9,stats_mysql_gtid_executed
The table stats_mysql_gtid_executed provides statistics related to consistently read GTID traces.This table shows the GTID set and the number of events executed on each back-end node.
Admin>show create table stats.stats_mysql_gtid_executed\G
*************************** 1. row ***************************
table: stats_mysql_gtid_executed
Create Table: CREATE TABLE stats_mysql_gtid_executed (
hostname VARCHAR NOT NULL,
port INT NOT NULL DEFAULT 3306,
gtid_executed VARCHAR,
events INT NOT NULL)
1 row in set (0.00 sec)For example, here we can see the difference between the GTID set of master(mysql1) and slave(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 hostname \G ;
*************************** 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-510,stats_mysql_prepared_statements_info
Because of the use of multiplexing, the client may prepare a PS(prepared statements) in the backend connection, but the connection is not idle when the same client wants to execute the PS.
In addition, multiple clients may prepare the same PS.
ProxySQL solves these two problems in the following ways:
1) For each unique PS, a global stmt_id is generated and its metadata is stored in the internal global cache.
2) Each client preparing the PS gets an stmt_id, which is the local number of the client but maps to the global stmt_id.
3) On each backend connection that prepares the PS, the stmt_id returned by the backend is mapped to the global stmt_id.
In other words, a global stmt_id can have multiple client stmt_ids associated with it and multiple backend stmt_ids associated with it.The table stats_mysql_prepared_statements_info shows some metadata associated with PS (global_stmt_id, schemaname, username, digest and query), as well as the number of reference counters for client connections (ref_count_client) and back-end connections (ref_count_server).
Admin>SHOW CREATE TABLE stats.stats_mysql_prepared_statements_info\G
*************************** 1. row ***************************
table: stats_mysql_prepared_statements_info
Create Table: 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,
query VARCHAR NOT NULL)
1 row in set (0.00 sec)11,stats_mysql_processlist
The following statements are used to create the stats_mysql_processlist table:
Admin>SHOW CREATE TABLE stats.stats_mysql_processlist\G
*************************** 1. row ***************************
table: stats_mysql_processlist
Create Table: 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)
1 row in set (0.00 sec)These fields have the following semantics:
ThreadID ==>Internal ID of the thread in ProxySQL.This is a thread number starting at 0.
SessionID ==>Internal global number of ProxySQL session or client connection (front end).It is useful to uniquely identify such a session, for example, in order to be able to kill it or only monitor a particular session.
User ==>The user whose MySQL client connects to ProxySQL to execute this query.
DB ==>Currently selected database.
Cli_host ==>The host of the TCP connection where the MySQL client connects to ProxySQL.
Cli_port ==>The port on which the MySQL client connects the TCP connection to ProxySQL.
Hostgroup ==>The current host group.If a query is being processed, this is the host group to which the query will be routed or routed, or the default host group.By default, routing is done using the default target host group used by the user name of the root MySQL client connecting to ProxySQL (based on the mysql_users table, but you can also modify it on the basis of each query using the query rule in mysql_query_rules)
L_srv_host ==>The local host address used by the backend MySQL server that ProxySQL connects to the current host group over TCP.
L_srv_port ==>The local port number used by the backend MySQL server that ProxySQL connects to the current host group over TCP.
Srv_host ==>The backend MySQL server is listening for the host address of a TCP connection.
Srv_port ==>The backend MySQL server is listening on the port of a TCP connection.
Command ==>Type of MySQL query being executed (keyword for MySQL command).
Time_ms ==>The time (in milliseconds) it has taken for the query to execute so far in the specified command state.
Info ==>Actual query being executed.
Note that this is only a snapshot of the actual MySQL query run.There is no guarantee that the same query will run in a fraction of a second.
The results are as follows:
Admin>SELECT * FROM stats.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
Note: ProxySQL also supports commands SHOW PROCESSLIST and SHOW FULL PROCESSLIST to return information related to the current session.
12,stats_mysql_query_digest and stats.stats_mysql_query_digest_reset
The following statements are used to create the stats_mysql_query_digest table:
Admin>SHOW CREATE TABLE stats.stats_mysql_query_digest \G
*************************** 1. row ***************************
table: stats_mysql_query_digest
Create Table: 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,
PRIMARY KEY(hostgroup, schemaname, username, client_address, digest))
1 row in set (0.00 sec)Each row represents a set of queries, and all queries with the same parameters but different values will be routed using the same ProxySQL.
Here's what the typical results look like:
Admin>SELECT * FROM stats.stats_mysql_query_digest ORDER BY count_star DESC LIMIT 2; +-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+------------+----------+----------+ | hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+------------+----------+----------+ | 11 | test | root | 127.0.0.1 | 0x7721D69250CB40 | SELECT c FROM sbtest3 WHERE id=? | 8122800 | 1441091306 | 1441101551 | 7032352665 | 1010 | 117541 | | 12 | test | root | 127.0.0.1 | 0x3BC2F7549D058B6F | SELECT c FROM sbtest4 WHERE id=? | 8100134 | 1441091306 | 1441101551 | 7002512958 | 101 | 102285 | +-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+------------+----------+----------+
These fields have the following semantics:
Hostgroup ==>The host group that issued the query.A value of -1 indicates that the query hit the query cache.
Schemaname ==>The name of the database being queried.
Username ==>User name used by MySQL client to connect to ProxySQL
Client_address ==>The address of the MySQL client.
Digest ==>hexadecimal hash value, which represents the unique identity of the query statement after the stripped parameter, similar to the SQL statement in the form of a bound variable.
Digest_text ==>The text content of the query statement after the stripped parameter.
Count_star ==>Total number of query executions (one SQL, but different parameter values)
First_seen ==>unix timestamp, the time at which the query statement was first routed through the proxy.
Last_seen ==>unix timestamp, the point in time at which the query statement was last routed through the proxy (so far).
Sum_time ==>The total time (in microseconds) spent executing such queries.This is particularly useful for determining where the application spends the most time in the workload and provides a good starting point for where to improve.
Min_time ==>Minimum execution time so far in microseconds.This can be compared with the expected time range during execution of such queries to determine whether the statement is correct.
Max_time ==>Maximum execution time so far in microseconds.This can be compared with the expected time range during execution of such queries to determine whether the statement is correct.
~
~
Note that the time in this table refers to the time elapsed between when ProxySQL receives the query from the client and when ProxySQL is ready to send the query to the client.
As a result, these timers represent a much longer time than the real-time time elapsed by the client executing the query.
More precisely, it includes time spent in situations such as:
1) ProxySQL may take time to change the character set or pattern before executing the query;
2) If the current backend is not available, the time it takes to find a new backend;
3) If the current query fails, the time it takes to run the query on a different backend;
4) The time it takes to wait for the connection to become idle because all the connections are currently in use.
Note: Statistics for table stats_mysql_query_digest are processed only when the global variable mysql-query_digest is set to true.mysql-query_digests is the default because it will also be used to process other queries.It is not recommended to disable it.
The stats_mysql_query_digest_reset table has the same content and structure as the above table, but queries also automatically reset the internal statistics of the reset table to zero.
13,stats_mysql_query_digest_reset
The table stats_mysql_query_digest_reset is the same as stats_mysql_query_digest, but reading data from stats_mysql_query_digest_reset causes all statistics in the table to reset to zero at the end of SELECT.
14,stats_mysql_query_rules
The table stats_mysql_query_rules records the number of times query rules have been matched and transferred.
The following statements are used to create the stats_mysql_query_rules table:
Admin>SHOW CREATE TABLE stats.stats_mysql_query_rules \G
*************************** 1. row ***************************
table: stats_mysql_query_rules
Create Table: CREATE TABLE stats_mysql_query_rules (
rule_id INTEGER PRIMARY KEY,
hits INT NOT NULL)
1 row in set (0.00 sec)Rule_id ==>the ID of the rule, which can be associated with the rule_id field of the main.mysql_query_rules table.
Hits ==>The total number of hits for this rule.If the current incoming query matches the rule, an additional hit is added.The number of hits increases each time a new query matches a rule is processed.
Note that hits are reset each time a query rule is loaded into the Runtime layer; it can be implicitly resynchronized either by explicitly LOAD MYSQL QUERY RULES TO RUNTIME or by ProxySQL Cluster.
15,stats_mysql_users
The table stats_mysql_users presents a list of users, including their current number of front-end connections and the total number of front-end connections they can create (as defined in mysql_users.max_connections)
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)
16,stats_proxysql_servers_checksums
ProxySQL instances that are part of a cluster periodically monitor each other to see if a reconfiguration needs to be triggered.
You can query the current information of the cluster through the table stats_proxysql_servers_checksums:
Admin>SHOW CREATE TABLE stats.stats_proxysql_servers_checksums\G ;
*************************** 1. row ***************************
table: stats_proxysql_servers_checksums
Create Table: 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) )
1 row in set (0.00 sec)Column represents:
Hostname ==>proxy address (remote or local)
Port ==>proxy port (remote or local), default 6032.
Name ==>Name of the module being synchronized
Version ==>Each time a (local) configuration is loaded, its version number is increased by 1
Epoch ==>This is when a specific configuration was created (before local or remote import)
Checksum ==>The checksum of the configuration you used.This is the information used by the proxy to detect whether the configuration has been changed.
Change_at ==>This is when a specific configuration is loaded locally.Note that it is different from epoch, which indicates when the configuration was created
Updated_at ==>This is the last time that the local proxysql checked the remote proxysql instance checksum.If this value is not increased, it means that the local proxysql cannot get data from the remote proxysql.
Diff_check ==>Number of consecutive checks detected when remote configuration is different from local configuration.When the threshold is reached, automatic reconfiguration is triggered.
View information:
Admin> SELECT 'proxy'||SUBSTR(hostname,11,12) hostname,name,version v, epoch,SUBSTR(checksum,0,10)||'...' checksum, changed_at, updated_at, diff_check diff FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY name, hostname; +----------+-------------------+---+------------+--------------+------------+------------+------+ | hostname | name | v | epoch | checksum | changed_at | updated_at | diff | +----------+-------------------+---+------------+--------------+------------+------------+------+ | proxy01 | mysql_query_rules | 1 | 1543750277 | 0x8CE2200... | 1543750278 | 1543761243 | 0 | | proxy02 | mysql_query_rules | 1 | 1542709023 | 0x8CE2200... | 1543750277 | 1543761244 | 0 | | proxy03 | mysql_query_rules | 1 | 1542709056 | 0x8CE2200... | 1543750277 | 1543761244 | 0 | | proxy01 | mysql_servers | 2 | 1543754137 | 0xBB56542... | 1543754137 | 1543761243 | 0 | | proxy02 | mysql_servers | 7 | 1543754141 | 0xBB56542... | 1543754140 | 1543761244 | 0 | | proxy03 | mysql_servers | 6 | 1543754142 | 0xBB56542... | 1543754137 | 1543761244 | 0 | | proxy01 | mysql_users | 1 | 1543750277 | 0xA9533E6... | 1543750278 | 1543761243 | 0 | | proxy02 | mysql_users | 1 | 1542709023 | 0xA9533E6... | 1543750277 | 1543761244 | 0 | | proxy03 | mysql_users | 1 | 1542709056 | 0xA9533E6... | 1543750277 | 1543761244 | 0 | | proxy01 | proxysql_servers | 1 | 1543750277 | 0xA87C55F... | 1543750278 | 1543761243 | 0 | | proxy02 | proxysql_servers | 1 | 1542709023 | 0xA87C55F... | 1543750277 | 1543761244 | 0 | | proxy03 | proxysql_servers | 1 | 1542709056 | 0xA87C55F... | 1543750277 | 1543761244 | 0 | +----------+-------------------+---+------------+--------------+------------+------------+------+ 12 rows in set (0.00 sec)
17,stats_proxysql_servers_metrics
ProxySQL instances in a cluster periodically exchange global states.Some of these states are visible in stats_proxysql_servers_metrics:
Admin>SHOW CREATE TABLE stats.stats_proxysql_servers_metrics \G;
*************************** 1. row ***************************
table: stats_proxysql_servers_metrics
Create Table: 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) )
1 row in set (0.00 sec)For example:
Admin> SELECT 'proxy'||SUBSTR(hostname,11,12) hostname , response_time_ms rtt_ms, Uptime_s, last_check_ms, Queries, Client_Connections_connected c_conn, Client_Connections_created c_created FROM stats_proxysql_servers_metrics ORDER BY hostname; +----------+--------+----------+---------------+-----------+--------+-----------+ | hostname | rtt_ms | Uptime_s | last_check_ms | Queries | c_conn | c_created | +----------+--------+----------+---------------+-----------+--------+-----------+ | proxy01 | 0 | 12111 | 18494 | 52475036 | 9095 | 14445 | | proxy02 | 0 | 1053365 | 18047 | 199072024 | 13552 | 456759 | | proxy03 | 2 | 1053333 | 16950 | 248707015 | 9891 | 471200 | +----------+--------+----------+---------------+-----------+--------+-----------+ 3 rows in set (0.00 sec)
18,stats_proxysql_servers_status
This table was created to display general statistics related to all services configured in the proxysql_servers table.===>Not currently in use
Admin>SHOW CREATE TABLE stats.stats_proxysql_servers_status \G;
*************************** 1. row ***************************
table: stats_proxysql_servers_status
Create Table: 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) )
1 row in set (0.00 sec)~
~
Complete!