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