The Road to MariaDB 5.2: User Statistics

One of the features I’m looking forward to using in MariaDB 5.2 is User Statistics. This feature allows me to easily gather various useful data about the tables and indexes in my database and about the users and clients who use my database.

One of the features I’m looking forward to using in MariaDB 5.2 is User Statistics. This feature adds several new information schema tables and several new FLUSH and SHOW commands. These tables and commands can be used to understand MariaDB server activity better and to identify sources of my database’s load.

Enabling Statistics

Statistics gathering is turned off by default in MariaDB 5.2. This is done to prevent the extra load on a server that this feature causes from happening unless you want it.

To enable statistics gathering, just put the following line in the [mysqld] section of your my.cnf file (or use it on the command line when starting the server):

userstat = 1

Now start (or restart) your MariaDB 5.2 server and statistics will be gathered.

Warning: If you switch back and forth between MariaDB 5.1.xx and MariaDB 5.2 (like I do on my MariaDB testing box), and use the same my.cnf file, be aware that the ‘userstat = 1″ line will not be recognized on versions of MariaDB prior to 5.2 and will cause errors.

Showing and Flushing Statistics

The User Statistics SHOW commands supported by MariaDB 5.2 are:

SHOW CLIENT_STATISTICS
SHOW USER_STATISTICS
SHOW INDEX_STATISTICS
SHOW TABLE_STATISTICS

If you want to restart the counters on any of these (like after you roll out a change to your application) you will find the following FLUSH commands handy:

FLUSH CLIENT_STATISTICS
FLUSH USER_STATISTICS
FLUSH INDEX_STATISTICS
FLUSH TABLE_STATISTICS

Information Schemas

The above SHOW and FLUSH commands are just easy ways to view and flush the actual statistics, which are stored in the information_schema database. Specifically, statistics are stored in the following tables in the information_schema database:

CLIENT_STATISTICS
USER_STATISTICS
INDEX_STATISTICS
TABLE_STATISTICS

The CLIENT_STATISTICS table stores information such as the number of concurrent connections from a particular client, the number of rows read by a particular client, the number of SELECT or UPDATE commands executed by a particular client and so on.

The USER_STATISTICS table stores the same information as the CLIENT_STATISTICS table, but on a per user basis.

The INDEX_STATISTICS table stores statistics on index usage and makes it possible to do things like locating unused indexes and generating the commands to remove them.

The TABLE_STATISTICS table is similar to the INDEX_STATISTICS table, but contains statistics on table usage such as the number of rows read and changed in a particular table.

More Information

Detailed information about User Statistics in MariaDB (including schemas for the various tables) is available on the askmonty.org website: http://askmonty.org/wiki/index.php/Manual:Userstat