User Statistics
MariaDB starting with 5.2.0
User statistics were introduced in MariaDB 5.2.0
MariaDB starting with 10.1.1
The TOTAL_SSL_CONNECTIONS
and MAX_STATEMENT_TIME_EXCEEDED
columns were added in MariaDB 10.1.1
Contents
The MariaDB implementation of User Statistics is based on the userstatv2 patch from Percona and Ourdelta. The original code comes from Google (Mark Callaghan's team) with additional work from Percona, Ourdelta, and Weldon Whipple. The MariaDB implementation provides the same functionality as the userstatv2 patch but a lot of changes have been made to make it faster and to better fit the MariaDB infrastructure.
This feature adds several new information schema tables and several new FLUSH and SHOW commands. These tables and commands can be used to understand the server activity better and to identify the sources of your database's load.
How it Works
Userstat works by keeping several hash tables in memory. All variables are incremented while the query is running. At the end of each statement the global values are updated.
Enabling userstat
By default statistics are not collected. This is to ensure that statistics collection does not cause any extra load on the server unless desired.
To enable statistics gathering, add the following line to your my.cnf file in the [mysqld]
section (or use it on the command-line when starting the server):
userstat = 1
Or you can simply change the value of the userstat system variable:
SET GLOBAL userstat=1;
Commands
Userstat provides the following new FLUSH and SHOW commands.
FLUSH Commands
These commands discard the information stored in the specified information schema table.
FLUSH TABLE_STATISTICS FLUSH INDEX_STATISTICS FLUSH USER_STATISTICS FLUSH CLIENT_STATISTICS
SHOW commands
These commands are another way to display the information stored in the information schema tables. WHERE clauses are accepted. LIKE clauses are accepted but ignored.
SHOW CLIENT_STATISTICS SHOW USER_STATISTICS SHOW INDEX_STATISTICS SHOW TABLE_STATISTICS
Information Schema Tables
User statistics adds the following new tables in the INFORMATION_SCHEMA database:
CLIENT_STATISTICS
The CLIENT_STATISTICS table holds statistics about client connections.
Field | Type | Notes |
---|---|---|
CLIENT
| varchar(64) | The IP address or hostname the connection originated from. |
TOTAL_CONNECTIONS
| int(21) | The number of connections created for this client. |
CONCURRENT_CONNECTIONS
| int(21) | The number of concurrent connections for this client. |
CONNECTED_TIME
| int(21) | The cumulative number of seconds elapsed while there were connections from this client. |
BUSY_TIME
| double | The cumulative number of seconds there was activity on connections from this client. |
CPU_TIME
| double | The cumulative CPU time elapsed while servicing this client's connections. Note that this number may be wrong on SMP systems if there was a CPU migration for the thread during the execution of the query. |
BYTES_RECEIVED
| int(21) | The number of bytes received from this client's connections. |
BYTES_SENT
| int(21) | The number of bytes sent to this client's connections. |
BINLOG_BYTES_WRITTEN
| int(21) | The number of bytes written to the binary log from this client's connections. |
ROWS_READ
| int(21) | The number of rows read by this client's connections. |
ROWS_SENT
| int(21) | The number of rows sent by this client's connections. |
ROWS_DELETED
| int(21) | The number of rows deleted by this client's connections. |
ROWS_INSERTED
| int(21) | The number of rows inserted by this client's connections. |
ROWS_UPDATED
| int(21) | The number of rows updated by this client's connections. |
SELECT_COMMANDS
| int(21) | The number of SELECT commands executed from this client's connections. |
UPDATE_COMMANDS
| int(21) | The number of UPDATE commands executed from this client's connections. |
OTHER_COMMANDS
| int(21) | The number of other commands executed from this client's connections. |
COMMIT_TRANSACTIONS
| int(21) | The number of transactions that were committed by this client's connections. |
ROLLBACK_TRANSACTIONS
| int(21) | The number of transactions that were rolled back by this client's connections. |
DENIED_CONNECTIONS
| int(21) | The number of connections denied to this client. |
LOST_CONNECTIONS
| int(21) | The number of this client's connections that were terminated uncleanly. |
ACCESS_DENIED
| int(21) | The number of times this client's connections issued commands that were denied. |
EMPTY_QUERIES
| int(21) | The number of times this client's connections sent queries that returned no results to the server. |
TOTAL_SSL_CONNECTIONS
| int(21) | The number of SSL connections created for this client. (>= MariaDB 10.1.1) |
MAX_STATEMENT_TIME_EXCEEDED
| int(21) | The number of times a statement was aborted, because it was executed longer than its MAX_STATEMENT_TIME threshold. (>= MariaDB 10.1.1) |
Versions
Version | Status | Introduced |
---|---|---|
2.0 | Stable | MariaDB 10.1.13 |
2.0 | Gamma |
Example
SELECT * FROM INFORMATION_SCHEMA.CLIENT_STATISTICS\G *************************** 1. row *************************** CLIENT: localhost TOTAL_CONNECTIONS: 3 CONCURRENT_CONNECTIONS: 0 CONNECTED_TIME: 4883 BUSY_TIME: 0.009722 CPU_TIME: 0.0102131 BYTES_RECEIVED: 841 BYTES_SENT: 13897 BINLOG_BYTES_WRITTEN: 0 ROWS_READ: 0 ROWS_SENT: 214 ROWS_DELETED: 0 ROWS_INSERTED: 207 ROWS_UPDATED: 0 SELECT_COMMANDS: 10 UPDATE_COMMANDS: 0 OTHER_COMMANDS: 13 COMMIT_TRANSACTIONS: 0 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 0 LOST_CONNECTIONS: 0 ACCESS_DENIED: 0 EMPTY_QUERIES: 1 1 row in set (0.00 sec)
USER_STATISTICS
The USER_STATISTICS table holds statistics about user activity. You can use this table to find out such things as which user is causing the most load and which users are being abusive. You can also use this table to measure how close to capacity the server may be.
Field | Type | Notes |
---|---|---|
USER
| varchar(48) | The username. The value '#mysql_system_user#' appears when there is no username (such as for the slave SQL thread). |
TOTAL_CONNECTIONS
| int(21) | The number of connections created for this user. |
CONCURRENT_CONNECTIONS
| int(21) | The number of concurrent connections for this user. |
CONNECTED_TIME
| int(21) | The cumulative number of seconds elapsed while there were connections from this user. |
BUSY_TIME
| double | The cumulative number of seconds there was activity on connections from this user. |
CPU_TIME
| double | The cumulative CPU time elapsed while servicing this user's connections. |
BYTES_RECEIVED
| int(21) | The number of bytes received from this user's connections. |
BYTES_SENT
| int(21) | The number of bytes sent to this user's connections. |
BINLOG_BYTES_WRITTEN
| int(21) | The number of bytes written to the binary log from this user's connections. |
ROWS_READ
| int(21) | The number of rows read by this user's connections. |
ROWS_SENT
| int(21) | The number of rows sent by this user's connections. |
ROWS_DELETED
| int(21) | The number of rows deleted by this user's connections. |
ROWS_INSERTED
| int(21) | The number of rows inserted by this user's connections. |
ROWS_UPDATED
| int(21) | The number of rows updated by this user's connections. |
SELECT_COMMANDS
| int(21) | The number of SELECT commands executed from this user's connections. |
UPDATE_COMMANDS
| int(21) | The number of UPDATE commands executed from this user's connections. |
OTHER_COMMANDS
| int(21) | The number of other commands executed from this user's connections. |
COMMIT_TRANSACTIONS
| int(21) | The number of COMMIT transactions that were committed by this user's connections. |
ROLLBACK_TRANSACTIONS
| int(21) | The number of transactions that were rolled back by this user's connections. |
DENIED_CONNECTIONS
| int(21) | The number of connections denied to this user. |
LOST_CONNECTIONS
| int(21) | The number of this user's connections that were terminated uncleanly. |
ACCESS_DENIED
| int(21) | The number of times this user's connections issued commands that were denied. |
EMPTY_QUERIES
| int(21) | The number of times this user's connections sent queries that returned no results to the server. |
TOTAL_SSL_CONNECTIONS
| int(21) | The number of SSL connections created for this client. (>= MariaDB 10.1.1) |
MAX_STATEMENT_TIME_EXCEEDED
| int(21) | The number of times a statement was aborted, because it was executed longer than its MAX_STATEMENT_TIME threshold. (>= MariaDB 10.1.1) |
Versions
Version | Status | Introduced |
---|---|---|
2.0 | Gamma |
Example
SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS\G *************************** 1. row *************************** USER: root TOTAL_CONNECTIONS: 1 CONCURRENT_CONNECTIONS: 0 CONNECTED_TIME: 297 BUSY_TIME: 0.001725 CPU_TIME: 0.001982 BYTES_RECEIVED: 388 BYTES_SENT: 2327 BINLOG_BYTES_WRITTEN: 0 ROWS_READ: 0 ROWS_SENT: 12 ROWS_DELETED: 0 ROWS_INSERTED: 13 ROWS_UPDATED: 0 SELECT_COMMANDS: 4 UPDATE_COMMANDS: 0 OTHER_COMMANDS: 3 COMMIT_TRANSACTIONS: 0 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 0 LOST_CONNECTIONS: 0 ACCESS_DENIED: 0 EMPTY_QUERIES: 1
INDEX_STATISTICS
The INDEX_STATISTICS table shows statistics on index usage and makes it possible to do such things as locating unused indexes and generating the commands to remove them.
Field | Type | Notes |
---|---|---|
TABLE_SCHEMA
| varchar(192) | The schema (database) name. |
TABLE_NAME
| varchar(192) | The table name. |
INDEX_NAME
| varchar(192) | The index name (as visible in SHOW CREATE TABLE
). |
ROWS_READ
| int(21) | The number of rows read from this index. |
Versions
Version | Status | Introduced |
---|---|---|
2.0 | Stable | MariaDB 10.1.13 |
2.0 | Gamma |
Example
SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME = "author"; +--------------+------------+------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+------------+------------+-----------+ | books | author | by_name | 15 | +--------------+------------+------------+-----------+
TABLE_STATISTICS
The TABLE_STATISTICS table is similar to the INDEX_STATISTICS
table. It shows statistics on table usage.
Field | Type | Notes |
---|---|---|
TABLE_SCHEMA
| varchar(192) | The schema (database) name. |
TABLE_NAME
| varchar(192) | The table name. |
ROWS_READ
| int(21) | The number of rows read from the table. |
ROWS_CHANGED
| int(21) | The number of rows changed in the table. |
ROWS_CHANGED_X_INDEXES
| int(21) | The number of rows changed in the table, multiplied by the number of indexes changed. |
Versions
Version | Status | Introduced |
---|---|---|
2.0 | Gamma |
Example
SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_NAME='user'; +--------------+------------+-----------+--------------+------------------------+ | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES | +--------------+------------+-----------+--------------+------------------------+ | mysql | user | 5 | 2 | 2 | +--------------+------------+-----------+--------------+------------------------+