Skip to content

STATS (statistics)

Jump to bottom
Nick Vyzas edited this page Jan 11, 2019 · 13 revisions

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:

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)

global_variables

Table stats.global_variables exists only to facilitate connections from libraries that issues SELECT @@max_allowed_packet or similar. It content can be ignored:

Admin> SELECT * FROM stats.global_variables;
+--------------------------+----------------+
| variable_name            | variable_value |
+--------------------------+----------------+
| mysql-max_allowed_packet | 4194304        |
+--------------------------+----------------+
1 row in set (0.00 sec)

stats_memory_metrics

Admin> SELECT * FROM stats.stats_memory_metrics;
+------------------------------+----------------+
| Variable_Name                | Variable_Value |
+------------------------------+----------------+
| SQLite3_memory_bytes         | 3002992        |
| jemalloc_resident            | 10342400       |
| jemalloc_active              | 8142848        |
| jemalloc_allocated           | 7124360        |
| jemalloc_mapped              | 39845888       |
| jemalloc_metadata            | 2459072        |
| jemalloc_retained            | 0              |
| Auth_memory                  | 690            |
| query_digest_memory          | 0              |
| stack_memory_mysql_threads   | 8388608        |
| stack_memory_admin_threads   | 8388608        |
| stack_memory_cluster_threads | 0              |
+------------------------------+----------------+
12 rows in set (0.01 sec)

This table is meant to display memory usage of various structures inside ProxySQL.

At today, only few structures are tracked (SQLite, Auth module, Query Digests), but in future a lot more internal structures will be tracked, exporting more metrics.

The most important values to monitor in this table are the ones related to jemalloc (the memory allocator built inside ProxySQL). A detailed description of the various values is available at jemalloc website.

In short:

  • jemalloc_allocated : bytes allocated by the application
  • jemalloc_active: bytes in pages allocated by the application
  • jemalloc_mapped: bytes in extents mapped by the allocator
  • jemalloc_metadata: bytes dedicated to metadata
  • jemalloc_resident: bytes in physically resident data pages mapped by the allocator

Other memory metrics:

  • Auth_memory : memory used by the authentication module to store user credentials and attributes
  • SQLite3_memory_bytes : memory used by the embedded SQLite
  • query_digest_memory : memory used to store data related to stats_mysql_query_digest

stats_mysql_commands_counters

Table stats_mysql_commands_counters keep records of all type of queries executed, and collects statistics based on their execution time, grouping them into buckets:

Admin> SELECT * FROM stats_mysql_commands_counters ORDER BY Total_cnt DESC LIMIT 1\G
*************************** 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)

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_connection_pool

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,
    Queries 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 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

This table export statistics on backend servers. Servers are identified based on their hostgroup, address and port, and the information available are related to connections, queries and traffic.

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 it is possible to note how multiplexing is efficient (few used connections).

stats_mysql_connection_pool_reset

Querying table stats_mysql_connection_pool_reset 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

ToDo

stats_mysql_errors_reset

ToDo

stats_mysql_global

One of the most important tables in “stats” schema is “stats_mysql_global” which exports counters related to various ProxySQL’s 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              |
+------------------------------+----------------+

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.

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 a difference in GTID sets between the master (mysql1) and slaves (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-5

stats_mysql_prepared_statements_info

Because of multiplexing, it is possible that a client prepares a 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 addressed these 2 issues in this 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

In other words, a global stmt_id can have multiple clients stmt_id associated to it, and multiple backends stmt_id associated to it. Table stats_mysql_prepared_statements_info shows some of the metadata associated to the PS (global_stmt_id, schemaname, username, digest and query), as well as the number of reference counters for client connections (ref_count_client) and backend 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)

stats_mysql_processlist

Here is the statement used to create the stats_mysql_processlist table:

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
)

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:

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)

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

stats_mysql_query_digest and stats.stats_mysql_query_digest_reset

Here is the statement used to create the stats_mysql_query_digest table:

CREATE TABLE stats_mysql_query_digest (
    hostgroup INT,
    schemaname VARCHAR NOT NULL,
    username 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(schemaname, username, digest)
)

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:

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
  • 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_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_rules

Table stats_mysql_query_rules exports how many times query rules were matching traffic. Here is the statement used to create the stats_mysql_query_rules table:

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 hits is reset every time query rules are loaded to runtime, either through explicit LOAD MYSQL QUERY RULES TO RUNTIME or through implicit resync via ProxySQL Cluster.

stats_mysql_users

Table stats_mysql_users reports a list of users, their current number of frontend connections, and the total number of frontend connections they can created (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)

stats_proxysql_servers_checksums

ProxySQL instances that are part of a cluster regularly monitor each other to understand if a reconfiguration needs to be triggered. It is possible to query the current view 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)

The columns represent:

  • hostname : address of the proxy (remote or local)

  • port : port of the proxy (remote or local)

  • name : name of the module being synchronized

  • version: every time a configuration is loaded (locally), its version number is increased by 1

  • epoch: this is the time when the specific configuration was created (either locally, or remotely before being imported)

  • checksum: the checksum of the configuration itself. This is the information that proxies use to detect configuration changes

  • changed_at: this is the time with the specific configuration was loaded locally. Note that is different than epoch, that represents when the configuration was created

  • updated_at: this is the last time the local proxysql checked the checksum of the remote proxysql instance. If this value is not increased, it means that the local proxysql cannot fetch data from the remote proxysql

  • diff_check: the number of checks in a row in which it was detected that the remote configuration is different than the local one. When a threshold it reached, an automatic reconfiguration is triggered

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)

stats_proxysql_servers_metrics

ProxySQL instances in a Cluster regularly exchange global statuses. Some of these statuses 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)

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)

stats_proxysql_servers_status

Currently unused - this table was created to show general statistics related to all the services configured in the proxysql_servers table.

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)

 

Clone this wiki locally