MariaDB starting with 5.2.0

User statistics were introduced in MariaDB 5.2.0

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.

FieldTypeNotes
CLIENTvarchar(64)The IP address or hostname the connection originated from.
TOTAL_CONNECTIONSint(21)The number of connections created for this client.
CONCURRENT_CONNECTIONSint(21)The number of concurrent connections for this client.
CONNECTED_TIMEint(21)The cumulative number of seconds elapsed while there were connections from this client.
BUSY_TIMEdoubleThe cumulative number of seconds there was activity on connections from this client.
CPU_TIMEdoubleThe cumulative CPU time elapsed while servicing this client's connections. Note that this number may be wrong on SMP system if there was a CPU migration for the thread during the execution of the query.
BYTES_RECEIVEDint(21)The number of bytes received from this client's connections.
BYTES_SENTint(21)The number of bytes sent to this client's connections.
BINLOG_BYTES_WRITTENint(21)The number of bytes written to the binary log from this client's connections.
ROWS_READint(21)The number of rows read by this client's connections.
ROWS_SENTint(21)The number of rows sent by this client's connections.
ROWS_DELETEDint(21)The number of rows deleted by this client's connections.
ROWS_INSERTEDint(21)The number of rows inserted by this client's connections.
ROWS_UPDATEDint(21)The number of rows updated by this client's connections.
SELECT_COMMANDSint(21)The number of SELECT commands executed from this client's connections.
UPDATE_COMMANDSint(21)The number of UPDATE commands executed from this client's connections.
OTHER_COMMANDSint(21)The number of other commands executed from this client's connections.
COMMIT_TRANSACTIONSint(21)The number of COMMIT commands issued by this client's connections.
ROLLBACK_TRANSACTIONSint(21)The number of ROLLBACK commands issued by this client's connections.
DENIED_CONNECTIONSint(21)The number of connections denied to this client.
LOST_CONNECTIONSint(21)The number of this client's connections that were terminated uncleanly.
ACCESS_DENIEDint(21)The number of times this client's connections issued commands that were denied.
EMPTY_QUERIESint(21)The number of times this client's connections sent queries that returned no results to the server.

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.

FieldTypeNotes
USERvarchar(48)The username. The value '#mysql_system_user#' appears when there is no username (such as for the slave SQL thread).
TOTAL_CONNECTIONSint(21)The number of connections created for this user.
CONCURRENT_CONNECTIONSint(21)The number of concurrent connections for this user.
CONNECTED_TIMEint(21)The cumulative number of seconds elapsed while there were connections from this user.
BUSY_TIMEdoubleThe cumulative number of seconds there was activity on connections from this user.
CPU_TIMEdoubleThe cumulative CPU time elapsed while servicing this user's connections.
BYTES_RECEIVEDint(21)The number of bytes received from this user's connections.
BYTES_SENTint(21)The number of bytes sent to this user's connections.
BINLOG_BYTES_WRITTENint(21)The number of bytes written to the binary log from this user's connections.
ROWS_READint(21)The number of rows read by this user's connections.
ROWS_SENTint(21)The number of rows sent by this user's connections.
ROWS_DELETEDint(21)The number of rows deleted by this user's connections.
ROWS_INSERTEDint(21)The number of rows inserted by this user's connections.
ROWS_UPDATEDint(21)The number of rows updated by this user's connections.
SELECT_COMMANDSint(21)The number of SELECT commands executed from this user's connections.
UPDATE_COMMANDSint(21)The number of UPDATE commands executed from this user's connections.
OTHER_COMMANDSint(21)The number of other commands executed from this user's connections.
COMMIT_TRANSACTIONSint(21)The number of COMMIT commands issued by this user's connections.
ROLLBACK_TRANSACTIONSint(21)The number of ROLLBACK commands issued by this user's connections.
DENIED_CONNECTIONSint(21)The number of connections denied to this user.
LOST_CONNECTIONSint(21)The number of this user's connections that were terminated uncleanly.
ACCESS_DENIEDint(21)The number of times this user's connections issued commands that were denied.
EMPTY_QUERIESint(21)The number of times this user's connections sent queries that returned no results to the server.

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.

FieldTypeNotes
TABLE_SCHEMAvarchar(192)The schema (database) name.
TABLE_NAMEvarchar(192)The table name.
INDEX_NAMEvarchar(192)The index name (as visible in SHOW CREATE TABLE).
ROWS_READint(21)The number of rows read from this index.

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.

FieldTypeNotes
TABLE_SCHEMAvarchar(192)The schema (database) name.
TABLE_NAMEvarchar(192)The table name.
ROWS_READint(21)The number of rows read from the table.
ROWS_CHANGEDint(21)The number of rows changed in the table.
ROWS_CHANGED_X_INDEXESint(21)The number of rows changed in the table, multiplied by the number of indexes changed.

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 |
+--------------+------------+-----------+--------------+------------------------+

Comments

Comments loading...