Is there any way to increase the mysql reserved pool for super users beyond 1?

Question

http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

States that there is 1 connection beyond your maximum number of connections for users with the super privilege.

My question is can you increase that number? Eventually I want to put limits in for all users that leave additional connections open for administrative jobs, but I'm not in a position to do that right now.

I want to increase that number to 10 so I can have replication jobs and monitoring jobs not affected by application connections.

Answer 1

Is there some sort of sane upper limit any one user should never exceed? Maybe your busiest application has at most 20 open connections during peak normal operation. Let's assume you have 20 non root users.

set max_user_connections = 30 (to give a bit of a safe buffer)

set max_connections = max_user_connections * # users + extra buffer, 
   or 30 * 20 + 50 = 650 max connections.  

This way your global max connections is safe from every getting reached and there should always be room for you to get in even if one particularly application or user is getting maxed out.

Answer 2

ASPECT #1

Using MySQL authentication, it is impossible by design. Whenever you get the "Too many connections" error, only one more user can login, and that user must have the SUPER privilege.

For your own protection, only user should have SUPER privilege because that give a user the power to run the following commands:

• CHANGE MASTER TO
• KILL (via mysqladmin or kill other users' sessions)
• PURGE BINARY LOGS
• configuration changes using SET GLOBAL to modify global system variables
• updates on a read-only server
• START SLAVE
• STOP SLAVE
• enabling/disabling logging
• specification of any account in the DEFINER attribute of stored programs and views

This is way too much firepower for any nominal user. Thus, even if all users had SUPER privilege, only one connection beyond max_connections would be allowed to authenticate. The danger of having all users with SUPER privilege becomes apparant when the DBA tries to login and other users with SUPER privileges logs in ahead of you. By restricting mysql to 1 and all users (except root@localhost) being restricted from having SUPER privilege, at least one genuine DBA can login and do things.

Of course, the quick-and-dirty answer would be to find that authentication section of the source code and raise the count from 1 to 10.

ASPECT #2

The next best thing you can do is the following:

Suppose max_connections in /etc/my.cnf is set at 1000. To increase it to 1500, chnage that value in /etc/my.cnf

[mysqld]
max_connections=1500

This may take multiple attempts, but you may want to connect to mysql and run

SET GLOBAL max_conenctions = 1500;

in order to bump up the max_conenctions value.

ASPECT #3

If your monitoring system has to connect to mysql at a fixed interval just to pick up, you will continue having this problem. Try to customize your monitoring so that a constant DB Connection is maintained and is given a heartbeat every 15 seconds. That way, the DB Connection stays up and never competes with other users making connections.