User Statistics

You are viewing an old version of this article. View the current version here.
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
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 doubleThe cumulative number of seconds there was activity on connections from this client.
CPU_TIME doubleThe 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_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 COMMIT commands issued by this client's connections.
ROLLBACK_TRANSACTIONS int(21)The number of ROLLBACK commands issued 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.

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
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 doubleThe cumulative number of seconds there was activity on connections from this user.
CPU_TIME doubleThe 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 commands issued by this user's connections.
ROLLBACK_TRANSACTIONS int(21)The number of ROLLBACK commands issued 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 empty queries 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_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.

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

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...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.