User statistics

Since MariaDB 5.2, a patch from Google, Percona, and other companies has been implemented, which permits you to view the user statistics, client statistics, index statistics (and usage), and table statistics.

You can activate it on the fly using the following command:

MariaDB [(none)]> SET GLOBAL userstat=1;

Alternatively, you can make it persistent in the MariaDB configuration (my.cnf) using the following code:

[mysqld]
userstat = 1

You now have access to the new FLUSH and SHOW commands:

MariaDB [(none)]> FLUSH TABLE_STATISTICS
MariaDB [(none)]> FLUSH INDEX_STATISTICS
MariaDB [(none)]> FLUSH USER_STATISTICS
MariaDB [(none)]> FLUSH CLIENT_STATISTICS
MariaDB [(none)]> SHOW CLIENT_STATISTICS
MariaDB [(none)]> SHOW USER_STATISTICS
MariaDB [(none)]> SHOW INDEX_STATISTICS
MariaDB [(none)]> SHOW TABLE_STATISTICS

Here is an example of what the user statistics look like:

MariaDB [(none)]> SHOW USER_STATISTICS\G;
*************************** 1. row ***************************
 User: root
 Total_connections: 2
Concurrent_connections: 0
 Connected_time: 23
 Busy_time: 0.002942
 Cpu_time: 0.0024703
 Bytes_received: 96
 Bytes_sent: 5147
 Binlog_bytes_written: 0
 Rows_read: 0
 Rows_sent: 3
 Rows_deleted: 0
 Rows_inserted: 0
 Rows_updated: 0
 Select_commands: 1
 Update_commands: 0
 Other_commands: 0
 Commit_transactions: 0
 Rollback_transactions: 0
 Denied_connections: 0
 Lost_connections: 0
 Access_denied: 0
 Empty_queries: 0
1 row in set (0.00 sec)

You can get the fine statistics about a user through the preceding command.