Categories
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:
|
1
2
3
4
5
6
7
|
cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlmax_connections = 400max_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.
Originally posted on April 29, 2017 @ 6:34 am
