Workload monitoring with USER_STATISTICS

Percona has done a great job improving the user_statistics code and making it available to others. The data from user_statistics can be used for real-time and long-term workload monitoring.

A common problem for real-time monitoring is determining the source of the load on a database server. If the load on a server suddenly increases and the cause is many fast queries, you may not be able to identify the problem using SHOW PROCESSLIST. You can use tcpdump or enable the slow query log, but neither is easy to use. If you are an Enterprise customer and use the MySQL Proxy, then there is a new workload monitoring tool that can be used.

All of the data you need to identify the problem is in user_statistics -- find the account with large changes for Busy_time, Cpu_time, Select_commands, Update_commands or Other_commands. It helps to have a tool display the differences in values from user_statistics over time. I hope that innotop provides this.

Another problem is long-term workload monitoring.  Changes in workload are easier to spot if you sample and log statistics. I run a cron job to read the values once per day and then generate reports that list the top-N accounts over the past day and week. If you use MySQL 5.1, then you can use the event scheduler (I can enter the use-case competition now). I imagine that third-party monitoring tools might also be a good place to put this data, but I don't have much experience with them. An advantage of putting this data into a SQL database is that you can query it and evaluate expressions (Cpu_time / (Select_commands + Update_commands)).