Viewing MySQL Account Resource Limits

Question

Is there any way of viewing an account's remaining resources that are allocated to it? I setup an account that's allowed 7200 queries an hour. At any point, could I then run a query to find out how many remaining queries it's allowed?

MySQL must be storing this information somewhere as FLUSH USER_RESOURCES; will reset the counters however, I tried a few variants such as SHOW USER_RESOURCES and they don't seem to display anything. I've also hunted around information_schema and mysql tables.

Is it just not possible to retrieve that information?

Answer 1

User resources are store in "mysql.user" table :

SELECT max_questions, 
       max_updates, 
       max_connections 
FROM   mysql.user 
WHERE  user = 'myuser'
AND    host = '%';

Max.

Answer 2

It is not really possible, but I have some useful information for you.

If you need to see the counts of an individual DB Connection, perhaps you can try

SHOW SESSION STATUS;

For example:

SHOW SESSION STATUS LIKE 'Questions';
SHOW SESSION STATUS LIKE 'Com_update';

If you want both, in one query

SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
WHERE VARIABLE_NAME IN ('Questions','Com_update');

This will not give you counts for all connections for a specific user unless there is only one persistent DB connection allowed for a user at any one time.

You should look for the ER_USER_LIMIT_REACHED error when connecting to know when you are out of resources.

You could also set the number higher at will using

GRANT USAGE ON *.* TO 'francis'@'localhost'
WITH MAX_CONNECTIONS_PER_HOUR 7300;

then later on

GRANT USAGE ON *.* TO 'francis'@'localhost'
WITH MAX_CONNECTIONS_PER_HOUR 7400;

as a means of throttling up the connection limit.

Answer 3

To get exact data, you may try enabling log (legacy as general-log). And rotate the log file every one hr. Develop a shell script to get the lines matching for your query and subtract it with 7200 returning value stays as your remaining queries. (NOTE: Ensure log file size shouldn't reduce server cpu much).