mysql TIME_WAIT ; 连接过多问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5062839/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql TIME_WAIT ; too many connections problem
提问by Parag
When i was checking for mysql load time on site. i got result showing connections as TIME_WAIT. Even though i close connection on every page. Sometimes the site doesnt load saying too many connections. What could be solution to this problem?
当我在现场检查 mysql 加载时间时。我得到的结果显示连接为 TIME_WAIT。即使我在每一页上都关闭了连接。有时网站不加载说太多的连接。什么可以解决这个问题？
Thanks in Advance for any replies or suggestions
预先感谢您的任何回复或建议
回答by Trendfischer
If a client connects to a MySQL-Server, it usually opens a local port, example:
如果客户端连接到 MySQL-Server，它通常会打开一个本地端口，例如：
localhost:12345 -> mysqlserver:3306
If the client closes the connection, the client gets a TIME_WAIT. Due to TCP routing, a packet might arrive late on the temporary port. A connection in TIME_WAIT just discards these packets. Without a TIME_WAIT, the local port might be reused for another connection and might receive packets from a former connection.
如果客户端关闭连接，则客户端会收到 TIME_WAIT。由于 TCP 路由，数据包可能会延迟到达临时端口。TIME_WAIT 中的连接只是丢弃这些数据包。如果没有 TIME_WAIT，本地端口可能会被另一个连接重用，并且可能会从以前的连接接收数据包。
On an high frequent application on the web which opens a mysql-connection per request, a high amount of TIME_WAIT connections is expectable. There is nothing wrong with it.
在 web 上的高频应用程序中，每个请求都会打开一个 mysql 连接，预计会有大量的 TIME_WAIT 连接。没有什么问题。
Problems can occur, if your local port range is too low, so you cannot open outgoing connections any more. The usual timeout is set to 60 seconds. So a problem can already occur on more than 400 requests per second on low ranges.
如果您的本地端口范围太小，则可能会出现问题，因此您无法再打开传出连接。通常的超时设置为 60 秒。因此，在低范围内每秒超过 400 个请求可能已经出现问题。
Check:
查看：
To check the amount of TIME_WAIT, you can use the following command:
要检查 TIME_WAIT 的数量，可以使用以下命令：
$ cat /proc/net/sockstat
sockets: used 341
TCP: inuse 12 orphan 0 tw 33365 alloc 23 mem 16
UDP: inuse 9 mem 2
UDPLITE: inuse 0
RAW: inuse 0
FRAG: inuse 0 memory 0
The value after "tw", in this case 33365, shows the amount of TIME_WAIT.
“tw”之后的值，在本例中为 33365，表示 TIME_WAIT 的数量。
Solutions:
解决方案：
a. TIME_WAIT tuning (Linux based OS examples):
一种。TIME_WAIT 调优（基于 Linux 的操作系统示例）：
Reduce the timeout for TIME_WAIT:
减少 TIME_WAIT 的超时时间：
# small values are ok, if your mysql server is in the same local network
echo 15 > /proc/sys/net/ipv4/tcp_fin_timeout
Increase the port range for local ports:
增加本地端口的端口范围：
# check, what you highest listening ports are, before setting this
echo 15000 65000 > /proc/sys/net/ipv4/ip_local_port_range
The settings /proc/sys/net/ipv4/tcp_tw_recycleand /proc/sys/net/ipv4/tcp_tw_reusemight be interesting, too. (But we experienced strange side effects with these settings, so better avoid them. More informations in this answer)
设置/proc/sys/net/ipv4/tcp_tw_recycle也/proc/sys/net/ipv4/tcp_tw_reuse可能很有趣。（但是我们在这些设置中遇到了奇怪的副作用，所以最好避免它们。这个答案中有更多信息）
b. Persistent Connections
湾 持久连接
Some programming languages and libraries support persistent connections. Another solution might be using a locally installed proxy like "ProxySQL". This reduces the amount of new and closed connections.
一些编程语言和库支持持久连接。另一种解决方案可能是使用本地安装的代理，如“ProxySQL”。这减少了新连接和关闭连接的数量。
回答by Anand Shah
As @Zimbabao suggested in the comment, debug your code for any potential errors that may halt the execution of closing the Mysql connection.
正如@Zimbabao 在评论中所建议的那样，调试您的代码以查找可能会停止执行关闭 Mysql 连接的任何潜在错误。
If nothing works, check your my.cnf for a system variable called wait_timeout. If its not present add it to the section [mysqld]and restart your Mysql server.
如果没有任何效果，请检查您的 my.cnf 中是否有名为wait_timeout. 如果它不存在，请将其添加到该部分[mysqld]并重新启动您的 Mysql 服务器。
[mysqld]
wait_timeout = 3600
Its the number of seconds the server waits for activity on a noninteractive connection before closing it. Further information can be found http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout
它是服务器在关闭非交互式连接之前等待活动的秒数。更多信息可以在http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout找到
Tune the figure 3600 (1 hour) to your requirements.
根据您的要求调整数字 3600（1 小时）。
HTH
HTH
回答by user2566717
If you are getting alot of TIME_WAIT connections on the Mysql Server then that means that Mysql server is closing the connection. The most likely case in this instance would be that a host or several hosts got on a block list. You can clear this by running
如果您在 Mysql 服务器上获得大量 TIME_WAIT 连接，则意味着 Mysql 服务器正在关闭连接。在这种情况下，最有可能的情况是一台或多台主机进入了阻止列表。您可以通过运行来清除它
mysqladmin flush-hosts
to get a list of the number of connections you have per ip run,
获取每次 ip 运行的连接数列表，
netstat -nat | awk {'print '} | cut -d ":" -f1 | sort | uniq -c | sort -n
you can also confirm this is happening by going to one of your clients that is having trouble connecting and telnet to port 3306. It will thow a message with something like,
您还可以通过访问您的一个客户端来确认发生这种情况，该客户端在连接和 telnet 到端口 3306 时遇到问题。它会显示一条消息，例如，
telnet mysqlserver 3306
Trying 192.168.1.102...
Connected to mysqlserver.
Escape character is '^]'.
sHost 'clienthost.local' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.
