The User Statistics (userstat) plugin creates the USER_STATISTICS, CLIENT_STATISTICS, the INDEX_STATISTICS, and the TABLE_STATISTICS tables in the INFORMATION_SCHEMA database. As an alternative to these tables, the plugin also adds the SHOW USER_STATISTICS, the SHOW CLIENT_STATISTICS, the SHOW INDEX_STATISTICS, and the SHOW TABLE_STATISTICS statements.
These tables and commands can be used to understand the server activity better and to identify the sources of your database's load.
The plugin also adds the FLUSH USER_STATISTICS, FLUSH CLIENT_STATISTICS, FLUSH INDEX_STATISTICS, and FLUSH TABLE_STATISTICS statements.
The MariaDB implementation of this plugin 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.
The userstat plugin 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.
By default statistics are not collected. This is to ensure that statistics collection does not cause any extra load on the server unless desired.
Set the system variable in a relevant server in an to enable the plugin. For example:
The value can also be changed dynamically. For example:
The userstat plugin creates the , , the , and the tables in the database.
As an alternative to the tables, the userstat plugin also adds the , the , the , and the statements.
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.
The userstat plugin also adds the , , , and statements, which discard the information stored in the specified information schema table.
userstatDescription: If set to 1, user statistics will be activated.
Command line: --userstat=1
Scope: Global
Dynamic: Yes
User Statistics introduced a number of new status variables:
(requires to be set to be recorded)
(requires to be set to be recorded)
This page is licensed: CC BY-SA / Gnu FDL
Data Type: boolean
Default Value: OFF
2.0
Stable
2.0
Gamma
2.0
Stable
2.0
Gamma
2.0
Stable
2.0
Gamma
2.0
Stable
2.0
Gamma
[mariadb]
...
userstat = 1SET GLOBAL userstat=1;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: 1SELECT * 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)SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME = "author";
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| books | author | by_name | 15 |
+--------------+------------+------------+-----------+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 |
+--------------+------------+-----------+--------------+------------------------+SHOW USER_STATISTICS
SHOW CLIENT_STATISTICS
SHOW INDEX_STATISTICS
SHOW TABLE_STATISTICSFLUSH USER_STATISTICS
FLUSH CLIENT_STATISTICS
FLUSH INDEX_STATISTICS
FLUSH TABLE_STATISTICS