Stopping mySQL “too many connections” errors in PHP

Posted by Lee under General

At 5:03pm last Friday afternoon whilst demoing a new site to a client I came to see the ever so lovely “too many connections” error from mySQL (or in the case of CodeIgniter - could not connect to database error). I knew what was going to hit me next, the calls from clients who are having problems as well accessing their websites - and sure enough about 5 minutes later they started to come through so I just did a quick restart of mySQL and Apache which cleared the problem, but I wanted to work out how to stop this from happening again.

The first thing I did was to look at the current connections in the mySQL server so I could see what sites were causing the problems which can be achieved by the following when accessing your server by SSH:

mysqladmin -uUSERNAME -pPASSWORD processlist

The first thing that came to my attention was a heap of connections left in sleep mode for 10000 seconds and over! Obviously these were counting to the ‘max_connections’ standard setting of 100.

The other thing I found odd was that majority of these databases were all CodeIgniter (a PHP framework) based. After a bit of quick research I was able to narrow the problem down to the use of the mySQL pconnect (persistant connection) option with many suggestions on not needing to use it. The odd thing was that CodeIgniter has this set to be ON by default and hence why all of these sites were appearing in the processlist.

To fix this issue in CodeIgniter simply goto your /application/config/database.php file and change the following setting as follows:

$db['default']['pconnect'] = FALSE; // Was set to TRUE

For anyone else using PHP avoid using the ‘mysql_pconnect’ command and use the ‘mysql_connect’ command instead.

And lastly for anyone using a server and needing to stop other sites from using this you can change the following setting in your php.ini file:

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = Off