The standard MySQL provides quite a lot of performance statistics: CPU usage, number of queries performed rows fetched, etc. However, all of the statistics are available for the whole server. If more users are connect to a server, there is no way to tell how much of the resources each of them used.
Based on the work done by google we enhanced MySQL with the SHOW USER_STATISTICS; command. It allows us to monitor the following server resources per database user (a ‘user’ being an entry in the mysql.User table):

• CPU time used for queries of a selected user;
• the number of select/update/insert queries per user;
• the number of connections, number of rows fetched per user;

Some of the cool things we can do with the results:

• we can tell which user causes most traffic on our servers, and act immediately in case of a server overload (we have 1 minute updates of the data).
• we can enforce a ‘fair use policy’, preferably by asking our customers to upgrade their accounts with an extra CPU allowance;
• we provide our customers with interesting overviews of their resource consumption over time;

We have been using the patched servers in production for almost a year now. We haven’t encountered any crash which could be attributed to the patch. Actually we had almost no crashes at all . (more…)