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.

  • information_schema.USER_STATISTICS does have some minimal information but it does not have any data related to query statistics – salmanarshad1999 Nov 24 '18 at 5:15

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).

You could enable individual query<->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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.