Is a single site wreaking havoc on your MySQL server?
In my MySQL optimization service, I often see servers with a couple of sites consuming 100% of the available MySQL connections.
You are likely familiar with the max_connections setting in my.cnf; but did you know that there is a max_user_connections variable as well?
The max_user_connections settings limits the number of database connections on a per user basis.
If you want to learn how to apply this variable for your shared hosting server, then read on.
Max User Connections
This variable does exactly what the name suggests – it sets a limit on the maximum number of simultaneous connections any individual user may have to your MySQL database.
On shared hosting systems, this is a powerful setting as you can use it to prevent a single user from using up all of the MySQL connections.
Setting Limits
In general, I suggest you set max_user_connections to 50-75% of your max_connections settings. You set this value in the mysqld section of your my.cnf:
cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql max_connections = 400 max_user_connections=200
In this example, we have 400 total connections permitted and a single user can use up to 200 connections.
Where did I get this number?
This is based on my work on 100’s of MySQL servers. In general, I find just one or two users contributing to the majority of MySQL connections. By setting this number to 50-75%, you effectively reserve 25-50% of MySQL’s connection slots for your other users and sites.
If you want to tune this more precisely, I recommend you monitor your MySQL to find out when the peak connection periods occur. Then during this period, poll the server so see who’s making the most connections.
I don’t get too scientific about this. I usually just run show process list at the MySQL command line. I combine this with tracking provided to my through tools like NewRelic, ServerDensity or whatever server monitoring tool is installed.
I then set a limit that will prevent a user from taking over the database but still allow enough connections for the site to function properly.
Benefits
By setting max_user_connections, you can reduce the likelihood that a single user account negatively impacts all of the sites on your server. This is particularly useful in shared hosting settings where you may not have any control over the code or traffic your end-users generate.
This can also help if a DoS or search bot generates a lot of traffic to a site. I’ve seen may cases where an aggressive search bot can overwhelm a MySQL system. Unlike normal traffic, search bots hit several pages – most of which are not likely cached in MySQL’s query cache or other caches. As a result, loads and MySQL connections can spike. Setting the max_user_connections limits can help with this situation.
One User Per Application
A common issue I see on single tenant servers is the tendency just to use one user for all database connections.
This is a mistake.
For each application, I suggest you create a specific user and password. This is good both for security and performance reasons.
By using an specific user for each application, you can easily determine which application is making a large number of connections to the database. By combing this is max_user_connections, you can reduce the likelihood that a single application overloads the system.
For example, with a recent MySQL optimization case, I found that search requests to a discussion forum were triggering long running MySQL queries. These queries were filling up the connection slots for the entire server. As a result, the blog and shopping cart failed.
By using max_user_connections, we were able to protect the other applications from the problems of the discussion forum.
Drawbacks
The main drawback Is that if the number is set too low, then it can cause occasional errors on busier sites. As these errors can be sporadic, they can be difficult to detect.
If you are hitting these limits, most PHP applications will return with the MySQL error that there are too many connections to the MySQL server. The Apache or PHP error logs may also provide clues if you need to raise this limit.
Take Away
Use max_user_connections on busy MySQL servers to pinpoint specific MySQL user activities from negatively impacting other users. I recommend setting max_user_connections to 50-75% of your total max_connections.