I am running an application PHPlist on my linode server, simultaneously running 12 PHP scripts, each of which opens a MySQL connection. Now when I access PHPlist it often shows this error:
Fatal Error: Sorry, the server is currently too busy, please try again
later.
When I am trying to access phpMyAdmin, it shows me a #1040 error.
Output of my PHP scripts which run through cron
jobs shows:
PHP Warning: mysqli_connect(): (HY000/1040): Too many connections
I am using the LAMP stack on the server with phpMyAdmin; the top
output in terminal shows mysqld
using 100-130% CPU. When I am trying to troubleshoot this problem I got some clues:
- Increase max_connection variable : I am using 200 (100 by default)
- Open table cache: 512 (400 by default)
There are lots of variables to set but I can't determine what specific ones, I am getting some reference from:
too many connections
and http://dev.mysql.com/doc/refman/5.5/en/table-cache.html
But according to my usage how to increase memory and what is the maximum memory difficult for me.
On my server I am using around 12 PHP scripts, PHPlist application for sending emails, and a major database for user registrations.
Kindly help me to resolve this problem.
Best Answer
First you need to do is run this query:
This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do the following:
mysqladmin kill
to kill threads belonging to other accountsYou will need to login as root@localhost and revoke SUPER privilege as follows:
Once you do this, whenever all users flood mysql connections, only
root@localhost
can login. After all, if everybody and his grandmother had SUPER privilege, this would barroot@localhost
from ever connecting ahead of everybody else. If max_connections is at 200 and you need to raise it to 300 without having to restart mysqld, you can dynamically increase the max_connections with this command:That will allow more connections effective immediately, but don't just arbitrarily increase the number on whim. You have to make sure mysql has enough RAM to accommodate the increase.
CAVEAT : If you change max_connections dynamically to 300, please put it in /etc/my.cnf
You can run mysqltuner.pl on your MySQL DB Server. If you do not have it, then run the following:
The 3rd line under Performance Metrics has this
See the 5.4M per thread? That is multipled by max_connections. In this example, that would be a maximum of about 10.8G of RAM. Therefore, each time you bump up max_connections, you should run mysqltuner.pl and check if you are pressing the OS for too much memory.
In any case, limiting who has SUPER privileges give such users opportunity to mitigate flooding mysqld with DB Connections.