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:
我正在使用MediaTemple的网格服务器(共享/网格托管)运行我正在编写的一些MySQL / PHP站点,并注意到我没有关闭我的MySQL连接,这导致我的网站出错:
"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?
我应该等一下吗?
5 个解决方案
#1
1
If you can't log into MySQL at all, you will probably have to contact your hosting provider to kill the connections.
如果您根本无法登录MySQL,则可能需要联系您的托管服务提供商以终止连接。
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.
如果可以使用MySQL shell,则可以使用show processlist命令查看连接,然后使用kill命令删除连接。
It's been my experience that hung SQL connections tend to stay that way, unfortunately.
根据我的经验,不幸的是,挂起的SQL连接往往会保持这种状态。
#2
1
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.
盲目进入终止连接并不是解决这个问题的方法。首先,您需要了解为什么连接不足。是否选择了max_connections设置以正确匹配最大/预期用户数?你真的不需要持久连接吗?等等
#3
0
Make sure that you're closing the connections with your PHP code. Also, you could increase the maximum connections allowed in /etc/my.cnf.
确保使用PHP代码关闭连接。此外,您可以增加/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.
最后,您可以登录到mysql提示符并键入show status或show processlist以查看服务器的各种统计信息。
If all else fails, restarting the server daemon should clear the persistent connections.
如果所有其他方法都失败,则重新启动服务器守护程序应清除持久连接。
#4
0
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:
好吧,如果你不能偷偷进入连接,我就不知道了,但是如果你偶尔可以潜入,在Ruby中它会接近:
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"
#5
0
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!).
如果您可以使用足够的权限访问命令行,请重新启动MySQL服务器或Apache(假设您使用Apache)服务器 - 因为可能是保持连接打开。成功关闭连接后,请确保您没有使用PHP的持久连接(一般意见似乎它不会产生任何显着的性能提升,但它有各种各样的问题 - 就像您经历过的那样 - 在某些情况下 - 比如使用它PostgreSQL - 它甚至可以显着减慢你的网站!)。
#1
1
If you can't log into MySQL at all, you will probably have to contact your hosting provider to kill the connections.
如果您根本无法登录MySQL,则可能需要联系您的托管服务提供商以终止连接。
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.
如果可以使用MySQL shell,则可以使用show processlist命令查看连接,然后使用kill命令删除连接。
It's been my experience that hung SQL connections tend to stay that way, unfortunately.
根据我的经验,不幸的是,挂起的SQL连接往往会保持这种状态。
#2
1
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.
盲目进入终止连接并不是解决这个问题的方法。首先,您需要了解为什么连接不足。是否选择了max_connections设置以正确匹配最大/预期用户数?你真的不需要持久连接吗?等等
#3
0
Make sure that you're closing the connections with your PHP code. Also, you could increase the maximum connections allowed in /etc/my.cnf.
确保使用PHP代码关闭连接。此外,您可以增加/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.
最后,您可以登录到mysql提示符并键入show status或show processlist以查看服务器的各种统计信息。
If all else fails, restarting the server daemon should clear the persistent connections.
如果所有其他方法都失败,则重新启动服务器守护程序应清除持久连接。
#4
0
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:
好吧,如果你不能偷偷进入连接,我就不知道了,但是如果你偶尔可以潜入,在Ruby中它会接近:
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"
#5
0
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!).
如果您可以使用足够的权限访问命令行,请重新启动MySQL服务器或Apache(假设您使用Apache)服务器 - 因为可能是保持连接打开。成功关闭连接后,请确保您没有使用PHP的持久连接(一般意见似乎它不会产生任何显着的性能提升,但它有各种各样的问题 - 就像您经历过的那样 - 在某些情况下 - 比如使用它PostgreSQL - 它甚至可以显着减慢你的网站!)。