Vbulletin, max_user_connections setting, persistent MYSQL connections and too many open mySQL connectionsVbulletin May 25th, 2008
While you are using vbulletin, you may get a nasty “User …. has already more than ‘max_user_connections’ active connections” database error and your board might go down.
If you contact vbulletin support for this error, they’ll ask you to contact your hosting company and when you email your hosting company, they might not be very helpful telling you :
a) Your vbulletin is openning too many mySQL connections in the server, then fails to close them, hence you get the problem. So they’ll blame vbulletin and ask you to consult with application team so that they’ll revise the application to make less connections to the MYSQL server.
b) They’ll tell your application(s) (=vbulletin) is making persistent MYSQL connections more than acceptable amount and will ask you to stop this.
You’ll then pass this information to vbulletin support team and first thing they’ll tell is to check your MASTER DATABASE PERSISTENT CONNECTIONS setting in your includes/config.php file and make sure it is like this:
$config['MasterServer']['usepconnect'] = 0;
If this setting is like this, your vbulletin shouldn’t make persistent connections to the server and Jelsoft will tell there is not anything they can do and it is your host that should solve the problem at this point.
Well the chances are that this setting is already like this so you’ll get stuck where your host asks Jelsoft to fix the application and Jelsoft tells application is already good and your host should increase max_user_connections setting in the server. Though situation.
As a matter of fact both sides have their points but if I need to pick a side, I’m likely to pick Jelsoft’s side and blame your host. But first let’s have a deeper analyze of the problem and see how come this problem happens in the first place:
Vbulletin is a MYSQL driven application so it connects to MYSQL server everytime it runs (=a website visitor triggers it by visiting ANY vbulletin page). As Jelsoft told you, vbulletin does NOT make persistent connections to the server if config.php file setting is set as you are told. It is also a stable product so it does not create unclosed open connections either. So your host is actually not right while blaming the vbulletin as the culprit of the problem.
Most of the times this error occurs because your host has a very small max_user_connections setting in the server. And in this case, the only fix to the problem will be your hosting’s increasing it. The host will not like this offer much though because it means that MYSQL driven sites will make the server more busy, hence add to the server load and slow down the server when it allows sites to make more connections to the MYSQL. However this is actually the only fix to the problem most of the times.
But there are also some cases where your host has a point. Sometimes they already set this setting to a high number but vbulletin keeps producing the error from time to time. There are usually 2 reasons to this:
a) One of the hacks/pluggins you use in vbulletin might be bogging down the server with too many connections or by slowing down the server somehow. To make sure this is not the culprit make sure you disable too third party pluggins in your vbulletin admin cp and see if you still get the error or not.
b) A vbulletin default query might taking a long time to run, locking all tables during the run and while tables are locked, new MYSQL queries triggered by other visitors compiles and cause the error. If your post count is too many, you have a high traffic and default vbulletin searching is used frequently you can have this problem because database search in a crowded board is always a bottleneck which can cause this error.
If this is your problem, there is not much your host can do actually. You can then consider cutting searching a little (eg. disallow searching for guests), consider moving to a dedicated server or if you are already on dedicated, increase your RAM to deal with more mysql connections.
So if you are getting “User … has already more than ‘max_user_connections’ active connections” database error” in your vbulletin board frequently, here is your path to go:
1- Check vbulletin config.php and make sure usepconnect setting is off like this:
$config['MasterServer']['usepconnect'] = 0;
2- Disable all your vb pluggins and hacks and see if you still have the error
3- Contact your host and ask what’s the value for ‘max_user_connections’ in the server and if it is possible for them to increase it more.
4- If they can’t increase it, you should either switch to a host which has a higher setting in their server, or move to a dedicated server in which you own the server and set settings as you wish. In the meantime you can turn off vbulletin searching features to see if it will help releive the error.
5- If you are already on dedicated, you should check your server load and make sure they are under 1.0. If you have high server load you need to upgrade your server speed. If your server load is fine, then you should consider upgrading your RAM and configure the apache, mysql and php settings to use this increased amount of RAM in a more optimized manner.