Monitoring MySQL resource limits

I have for the first time seen a client implement MySQL Resource Limits. I got the following error tying to connect to the database.

$ mysql -udba -p
ERROR 1226 (42000): User 'dba' has exceeded the 'max_user_connections' resource (current value: 10)

I see from the documentation the ability to see the limits in the mysql.user table. I see this is included in the SHOW GRANTS output.

SHOW GRANTS for 'dba'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*CAABA4CFB7E71E51477E0658FC2D2BBA1267E669' WITH MAX_USER_CONNECTIONS 10 |
+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

The documentation includes details that you can flush the resource limits, however I have found no way to monitor the current usage.

I welcome any feedback from the MySQL Community.

Comments

  1. says

    The only ways I can think of are:

    select count(*) count_dba_connections From information_schema.processlist where user=’dba’ and host=’%';

    or in 5.0, “show processlist” and then outside of mysql filter out dba users which aren’t localhost.

  2. says

    Hi,

    Agree to Ryna’s solution. However, there are even stricter rules which cannot be easily traced:
    MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR
    Unless you are *very lucky* to catch all connections/updates/queries while busy monitoring SHOW PROCESSLIST, I don’t recognize any way to know at any point in time how much of these resources a user has already consumed.