I manage a community network with approximately 100 virtual servers. A few of our clients have moved to Wordpress and within the last few months have experienced chronic problems with the MYSQL server.
The first time I was notified of the "MYSQL Too many connections" error message, I searched the Virtualmin site and learned to revise the /etc/mysql/my.cnf file to allow more connections. The second time it occurred, we simply restarted the MYSQL server. Yesterday, after another failure, I revised the my.cnf file again increasing the max number of connections to 5000 which is beyond the number recommended.
I realize that this situation is not optimal for my server and that as more and more clients use WordPress, the problem will get worse. My quick and dirty search for information talks about number of processes running and processes that don't expire when their usefulness is over.
Does anyone have a solution for this problem? Will killing MYSQL processes after their usefulness is over, cause different problems elsewhere?
Thanks,
Stella Pool Monticello Avenue Community Network Charlottesville, VA
Howdy,
Well, it's pretty unusual to have to raise that number from the defaults... I've seen some pretty high traffic systems not ever have to change the default MySQL connections limit.
You may want to verify that the traffic you're seeing is legitimate and expected, by reviewing the Apache access logs for your domains. That's available in $HOME/logs/access_logs.
You may also want to log into MySQL, and run "show full processlist", to see which site is generating all the connections.
MySQL only has one process, so you can't kill that. Though, you could edit your Apache config, and lower the TimeOut listed in there. It's 300 by default, if you lower that to something like 30, that should keep processes from lingering around and taking up additional connections.
However, being as Apache only allows some 150 simultaneous connections by default -- that suggests that you may have an application that's generating a lot of connections. It's possible it's a particular plugin that's being used.
Reviewing the Apache access logs, and MySQL connection activity, should help you figure out the source of the issue.
-Eric
Eric,
Thanks for responding and confirming that raising the number of the connections was not the solution to the problem.
I can view the Apache access logs for the domains in question but don't know how to interpret what is legitimate and expected. Is there a term or status that I can search?
Checking the MYSQL server shows me this:
3368 mha.avenue.org localhost mha Sleep 00:26:58
7119 pack107 localhost pack107_moodle Sleep 00:06:20
8957 root localhost mysql Query 00:00:00 show full processlist
Does the sleep mode mean that it is safe to kill the connection?
I'm sorry to be such a newbie. Any further suggestions are appreciated.
Stella
Hi,
I have migrated some sites from cPanel to virtualmin and found that my server is keep getting stuck in between and sites are not accessible. Upon checking found that the mysql connections are increasing rapidly and leading the sites not accessible as no new mysql connections are allowing in the server. I have figured out the site which is causing the this and disabled temporarily. So how can I identify the real reason behind this ? I have checked the logs and found the below :
[root@server1 ~]# tail /var/log/httpd/error_log [Tue May 28 12:58:36 2013] [warn-phpd] The ionCube PHP Loader is disabled because of startup problems. (pid 11785)
[root@server1 ~]# tail /var/log/httpd/access_log ::1 - - [28/May/2013:19:16:00 +0000] "OPTIONS * HTTP/1.0" 200 - "-" "Apache/2.2.15 (internal dummy connection)"
Any idea ??
Thanks, G