EmergencyEMERGENCY? Get 24/7 Help Now!

Prevent MySQL downtime: Set max_user_connections

 | July 29, 2014 |  Posted In: Insight for DBAs, MySQL


One of the common causes of downtime with MySQL is running out of connections. Have you ever seen this error? “ERROR 1040 (00000): Too many connections.” If you’re working with MySQL long enough you surely have. This is quite a nasty error as it might cause complete downtime… transient errors with successful transactions mixed with failing ones as well as only some processes stopping to run properly causing various kinds of effects if not monitored properly.

There are number of causes for running out of connections, the most common ones involving when the Web/App server is creating unexpectedly large numbers of connections due to a miss-configuration or some script/application leaking connections or creating too many connections in error.

The solution I see some people employ is just to increase max_connections to some very high number so MySQL “never” runs out of connections. This however can cause resource utilization problems – if a large number of connections become truly active it may use a lot of memory and cause the MySQL server to swap or be killed by OOM killer process, or cause very poor performance due to high contention.

There is a better solution: use different user accounts for different scripts and applications and implement resource limiting for them. Specifically set max_user_connections:

This approach (available since MySQL 5.0) has multiple benefits:

Security – different user accounts with only required permissions make your system safer from development errors and more secure from intruders
Preventing Running out of Connections – if there is a bug or miss-configuration the application/script will run out of connections of course but it will be the only part of the system affected and all other applications will be able to use the database normally.
Overload Protection – Additional numbers of connections limits how much queries you can run concurrently. Too much concurrency is often the cause of downtime and limiting it can reduce the impact of unexpected heavy queries running concurrently by the application.

In addition to configuring max_user_connections for given accounts you can set it globally in my.cnf as “max_user_connections=20.” This is too coarse though in my opinion – you’re most likely going to need a different number for different applications/scripts. Where max_user_connections is most helpful is in multi-tenant environments with many equivalent users sharing the system.

Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.


  • Peter I think this approach is especially useful for administrative or service/monitoring accounts. I have seen system brought to a halt because an admin wanted to discern whether the system was under load or near some threshold (thus, causing the condition for which he was monitoring). These are called iatrogenic (doctor caused) disorders. But limiting the number of connections on a per user basis may very well help to stop this sort of thing from occurring.

  • Hi Peter,

    The naive solution of increasing max_connections to a very high number also has another drawback.

    As you pointed, this can lead to unreasonable memory consumption when the server effectively reach the number of connections specified.

    Another point to note is that the performance schema is allocating memory for statistics per connection, and it is by default using the max_connections configuration parameter to determine how many connections are expected, max.

    As of MySQL 5.7.4-m14 and previous releases, all the memory for — anticipated — connection statistics is allocated — up-front — at server startup, which can lead to unreasonable memory consumption (and therefore swap, OOM killer) even when a very high number of connections is not actually reached in the server.

    I agree with you that setting max_user_connection per account is more robust.

    — Marc

  • Thanks Marc,

    I think Performance Schema static memory allocation is one of the challenges, specifically in regards to max connection. If Performance Schema is disabled it is very safe to set max_connections to 10000 to ensure you do not run out of connections and there is not much cost of doing so unless you actually use it.

    I hope you find some solution for Performance Schema to have its memory use more dynamic but yet without drastic performance penalty.

  • Thank you for the advice Peter!

    Additionally, as you know, Percona Server has the extra_port and extra_max_connections variables in versions 5.5.29-30.0 and greater. This was part of the Maria DB thread pool port and can be used even if the thread pool is not. The extra port allows access to the database so the reason max connections was reached can be diagnosed and corrected. This is especially helpful in development databases where max connections can often be hit due to coding bugs.

    Thanks for adding this most welcome feature.

  • Be aware that there was a bug prior to MySQL 5.5.26 (fixed in the latest 5.5/5.6) version where if you reached the limit under some circumstances when the number of connections dropped below the limit you could still not connect. (only fix for that case was to set MAX_USER_CONNECTION to 0 [disable this feature]).
    So for those people using older versions of MySQL you need to be aware of this as it’s quite unpleasant.

    Reference: http://bugs.mysql.com/bug.php?id=65104

  • if we add the limit like max_user_connections=20 than it will drop the user. instead of this if we use mod_qos that it will put the other pages to wait and prevent from high load.

  • I am quite unsure, if there is any other way to use max_user_connections like in a way if max connections limit exceeds than rather giving error i.e. max connection limit exceed, there should be a thereshold on further execution of processes until limit limit processes have been completed.

  • Great advice. I’m trying to figure out what exactly is happening on one of my servers, which is clearly starving from resources — most processes are waiting until ‘something’ happens, and this is usually — but not necessarily always! — related to lack of sockets for connections (or of files to be opened). Restricting the number of connections per user makes a lot of sense in my setup, where each independent web server runs under its own MySQL user — and there is also supposed to be a limit to how many PHP processes can be launched. That way, I can somehow tweak both numbers to make sure that each user does not use more connections than it needs, even under severe serious load.

Leave a Reply


Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.