Terminate MySQL connections on shared host?

Question

I'm using MediaTemple's Grid Server (shared/grid hosting) to run some MySQL/PHP sites I'm writing and noticed that I wasn't closing one of my MySQL connections, which caused my site to error out, "Too Many Connections".

I can't log in anywhere to close the connections manually.

Is that any way to close open connections using a script or other type of command? Should I just wait?

Answer 1

If you can't log into MySQL at all, you will probably have to contact your hosting provider to kill the connections.

If you can use the MySQL shell, you can use the show processlist command to view connections, then use the kill command to remove the connections.

It's been my experience that hung SQL connections tend to stay that way, unfortunately.

Answer 2

Make sure that you're closing the connections with your PHP code. Also, you could increase the maximum connections allowed in /etc/my.cnf.

max_connections=500

Finally, you can login to a mysql prompt and type show status or show processlist to view various statistics with your server.

If all else fails, restarting the server daemon should clear the persistent connections.

Answer 3

Well, if you cannot ever sneak in with a connection, I dunno', but if you can occasionally sneak in, in Ruby it would be close to:

require 'mysql'

mysql = Mysql.new(ip, user, pass)
processlist = mysql.query("show full processlist")
killed = 0
processlist.each { | process |
  mysql.query("KILL #{process[0].to_i}")
} 
puts "#{Time.new} -- killed: #{killed} connections"

Answer 4

If you can access the command line with enough privileges, restart the MySQL server or the Apache (assuming that you use Apache) server - because probably it is keeping the connections open. After you successfully closed the connections, make sure that you are not using persistent connections from PHP (the general opinion seems to be that it doesn't create any significant performance gain, but it has all kinds of problems - like you've experienced - and in some cases - like using it PostgreSQL - it can even significantly slow down your site!).

Answer 5

blindly going in an terminating connections is not the way to solve this problem. first you need to understand why you are running out of connections. is your max_connections setting selected to correctly match the number of max/anticipated users? are you using persistent connections when you really don't need them? etc.