User Statistics

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

Commands

Userstat provides the following new FLUSH and SHOW commands.

<<code lang=mysql inline=true>>FLUSH<</mysql>> 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

This table holds statistics about client connections.

Example

MariaDB [(none)]> 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

This 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.

Example

MariaDB [(none)]> 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

This 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.

Example

MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME = "author";
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| books        | author     | by_name    |        15 |
+--------------+------------+------------+-----------+
1 row in set (0.00 sec)

TABLE_STATISTICS

This table is similar to the INDEX_STATISTICS table. It shows statistics on table usage.

Example

MariaDB [(none)]> 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 |
+--------------+------------+-----------+--------------+------------------------+
1 row in set (0.00 sec)