How to determine queries per hour executed by a particular MySQL user? I am trying to implement user based quota system in MySQL to limit MAX_QUERIES_PER_HOUR  for a particular user. I have an option to set this limit via MAX_UPDATES_PER_HOUR 10.
But my problem is that I want know that how many queries a user has executed this hour.  I followed this thread but this is for global statistics of dba.  I want this information for every user of my database. I am sure that MySQL is storing this schema somewhere but after a lot of scrutinizing some of databases like information_schema , performance_schema , mysql I can't figure out how it will be achieved.
I talked to the MySQL engineers, and the philosophy of the metrics you will find on performance_schema is to store only global metrics (even if it can also store individual queries and events), as in, no temporal (differential) breakdown. The way they suggested would be to take hourly snapshots of the interesting metrics (query or user statistics) and then store those somewhere else. Because performance_schema stores metrics on memory since restart, you would only have to sample those every hour, or average them over the SHOW GLOBAL STATUS like 'Uptime' seconds.

I normally use performance_schema using sys, as it is way easier to query (but all data there comes from performance_schema). You can install it even if you are using an older MySQL version and it will use the performance_schema data already stored, assuming it is enabled (default).


sys.user_sumary contains the summary status per user, including total statements, total_latency, total_connections, etc.
sys.user_summary_by_statement_type contains the breakdown per user and statement type.


You could enable individual query&lt;->user storage, but you would convert 
performance_schema into the general log, and either storing only a small window, or -if increased the backlog- taking a huge amount of memory.

So to "know that how many queries a user has executed this hour" query the above tables, wait 1 hour, and query it again, and substract the first value from the second. You will have available, the amount of queries, inserts, updates, deletes, rows read, and other resources used. There are tools that will automate the collection for you. If for some reason you cannot use performance_schema, there are plugins such as user_statistics that will create new tables in information_schema with similar information (this was the only way on older MySQL versions).