Ronald Bradford
MySQL Expert

MySQL Expert Ronald Bradford shares valuable input in MySQL Performance Tuning, MySQL Scalability and general MySQL Help from his two decades of working with MySQL, Oracle, Ingres and development technologies.

« Monitoring MySQL Product Options
MySQL Permissions – Restarting MySQL »

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 dba@%                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| 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.

Tags: , , ,

This entry was posted on Tuesday, October 27th, 2009 at 8:06 pm and is filed under Databases, MySQL, Professional, Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Monitoring MySQL resource limits”

  1. Ryan says:
    October 27, 2009 at 8:47 pm

    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. Shlomi Noach says:
    October 28, 2009 at 2:02 am

    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.

Leave a Reply