The stats database

This database contains metrics gathered by ProxySQL with respect to its internal functioning. Here you will find information on how often certain counters get triggered and the execution times of the queries that pass through ProxySQL.

A user that connects to Admin with admin-stats_credentials credentials can only access this schema.

Generally, the tables from this database are populated on the fly when the SQL query against them is execute, by examining in-memory data structures.

Here are the tables from the "stats" database:

  1. Admin> show tables from stats;
  2. +--------------------------------+
  3. | tables                         |
  4. +--------------------------------+
  5. | stats_mysql_query_rules        |
  6. | stats_mysql_commands_counters  |
  7. | stats_mysql_processlist        |
  8. | stats_mysql_connection_pool    |
  9. | stats_mysql_query_digest       |
  10. | stats_mysql_query_digest_reset |
  11. | stats_mysql_global             |
  12. +--------------------------------+
  13. 7 rows in set (0.00 sec)

The purposes of the tables are as follows:

  • stats_mysql_query_rules - counts how many times each query rule was matched by queries
  • stats_mysql_commands_counters - counts how many times each type of SQL command was executed (e.g. UPDATE, DELETE, TRUNCATE, etc.) and how much time those executions took
  • stats_mysql_processlist - a table that simulates the results of the "SHOW PROCESSLIST" mysqld command. This table will contain similar information aggregated across all backends
  • stats_mysql_connection_pool - a table that contains the statistics related to the usage of the connection pool for each backend server in each hostgroup
  • stats_mysql_query_digest - a table that contains statistics related to the queries routed through the ProxySQL server. How many times each query was executed, and the total execution time are just several provided stats. Here the queries are stripped from their numerical and literal parameters, which are replaced with a question mark, in order to be able to group all queries of the same type under the same row.
  • stats_mysql_query_digest_reset - identical to stats_mysql_query_digest, but querying it also atomically resets the internal statistics to zero. This can be used, for example, before making a change, to be able to compare the statistics before and after the change. This table can also be queried at regular interval to understand how workload change over time. Since ProxySQL has an internal database, it is also possible to save the result into an internal table.
  • stats_mysql_global - global statistics such as total number of queries, total number of successful connections, etc. The list of variables is expected to grow in future release.

stats_mysql_query_rules

Here is the statement used to create the stats_mysql_query_rules table:

  1. CREATE TABLE stats_mysql_query_rules (
  2.     rule_id INTEGER PRIMARY KEY,
  3.     hits INT NOT NULL
  4. )

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.

stats_mysql_commands_counters

Here is the statement used to create the stats_mysql_commands_counters table:

  1. CREATE TABLE stats_mysql_commands_counters (
  2.     Command VARCHAR NOT NULL PRIMARY KEY,
  3.     Total_Time_us INT NOT NULL,
  4.     Total_cnt INT NOT NULL,
  5.     cnt_100us INT NOT NULL,
  6.     cnt_500us INT NOT NULL,
  7.     cnt_1ms INT NOT NULL,
  8.     cnt_5ms INT NOT NULL,
  9.     cnt_10ms INT NOT NULL,
  10.     cnt_50ms INT NOT NULL,
  11.     cnt_100ms INT NOT NULL,
  12.     cnt_500ms INT NOT NULL,
  13.     cnt_1s INT NOT NULL,
  14.     cnt_5s INT NOT NULL,
  15.     cnt_10s INT NOT NULL,
  16.     cnt_INFs
  17. )

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.

stats_mysql_processlist

Here is the statement used to create the stats_mysql_processlist table:

  1. CREATE TABLE stats_mysql_processlist (
  2.     ThreadID INT NOT NULL,
  3.     SessionID INTEGER PRIMARY KEY,
  4.     user VARCHAR,
  5.     db VARCHAR,
  6.     cli_host VARCHAR,
  7.     cli_port VARCHAR,
  8.     hostgroup VARCHAR,
  9.     l_srv_host VARCHAR,
  10.     l_srv_port VARCHAR,
  11.     srv_host VARCHAR,
  12.     srv_port VARCHAR,
  13.     command VARCHAR,
  14.     time_ms INT NOT NULL,
  15.     info VARCHAR
  16. )

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 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
    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:
  1. mysql> select * from stats_mysql_processlist;
  2. +----------+-----------+------+------+-----------+----------+-----------+------------+------------+-----------+----------+---------+---------+---------------------------------------+
  3. | ThreadID | SessionID | user | db   | cli_host  | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host  | srv_port | command | time_ms | info                                  |
  4. +----------+-----------+------+------+-----------+----------+-----------+------------+------------+-----------+----------+---------+---------+---------------------------------------+
  5. | 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 |
  6. | 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 |
  7. | 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 |
  8. | 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 |
  9. +----------+-----------+------+------+-----------+----------+-----------+------------+------------+-----------+----------+---------+---------+---------------------------------------+
  10. 4 rows in set (0.02 sec)

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

stats_mysql_connection_pool

Here is the statement used to create the stats_mysql_connection_pool table:

  1. CREATE TABLE stats_mysql_connection_pool (
  2.     hostgroup VARCHAR,
  3.     srv_host VARCHAR,
  4.     srv_port VARCHAR,
  5.     status VARCHAR,
  6.     ConnUsed INT,
  7.     ConnFree INT,
  8.     ConnOK INT,
  9.     ConnERR INT,
  10.     Queries INT,
  11.     Bytes_data_sent INT,
  12.     Bytes_data_recv INT,
  13.     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 above 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.
  • Queries - the number of queries routed towards this particular backend server
  • 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 currently ping time in microseconds, as reported from Monitor

stats_mysql_query_digest and stats_mysql_query_digest_reset

Here is the statement used to create the stats_mysql_query_digest table:

  1. CREATE TABLE stats_mysql_query_digest (
  2.     hostgroup INT,
  3.     schemaname VARCHAR NOT NULL,
  4.     username VARCHAR NOT NULL,
  5.     digest VARCHAR NOT NULL,
  6.     digest_text VARCHAR NOT NULL,
  7.     count_star INTEGER NOT NULL,
  8.     first_seen INTEGER NOT NULL,
  9.     last_seen INTEGER NOT NULL,
  10.     sum_time INTEGER NOT NULL,
  11.     min_time INTEGER NOT NULL,
  12.     max_time INTEGER NOT NULL,
  13.     PRIMARY KEY(schemaname, username, digest)
  14. )

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

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

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
  • 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.
    Note that the times 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 timers represent the elapsted 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 a connection to become free because currently all the connection are in use.

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

The stats_mysql_query_digest_reset table is identical in content and structure, but querying it also atomically resets the internal statistics to zero.

stats_mysql_global

Here is the statement used to create the stats_mysql_global table:

  1. CREATE TABLE stats_mysql_global (
  2.     Variable_Name VARCHAR NOT NULL PRIMARY KEY,
  3.     Variable_Value VARCHAR NOT NULL
  4. )

Each row represents a global statistic at the proxy level related to MySQL. Currently, the available variables are:

  • Client_Connections_aborted - number of frontend connections aborted due to invalid credential or max_connections reached
  • Client_Connections_connected - number of frontend connections currently connected
  • Client_Connections_created - number of frontend connections created so far
  • Questions - total number of queries sent from frontends
  • Slow_queries - number of queries that ran for longer than the threshold in milliseconds defined in global variable mysql-long_query_time
    The same output is available using the SHOW MYSQL STATUS command.

Example:

  1. Admin> select * from stats.stats_mysql_global;
  2. +------------------------------+----------------+
  3. | Variable_Name                | Variable_Value |
  4. +------------------------------+----------------+
  5. | Client_Connections_aborted   | 0              |
  6. | Client_Connections_connected | 4              |
  7. | Client_Connections_created   | 4              |
  8. | Questions                    | 36337716       |
  9. | Slow_queries                 | 0              |
  10. +------------------------------+----------------+

原文: https://github.com/sysown/proxysql/wiki/STATS-(statistics)