mysql TIME_WAIT ;连接过多问题 [英] mysql TIME_WAIT ; too many connections problem

问题描述

当我在网站上检查 mysql 加载时间时.我得到的结果显示连接为 TIME_WAIT.即使我在每一页上都关闭了连接.有时网站不加载说太多的连接.有什么办法可以解决这个问题?

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?

预先感谢您的任何回复或建议

Thanks in Advance for any replies or suggestions

推荐答案

如果客户端连接到 MySQL-Server，它通常会打开一个本地端口，例如:

If a client connects to a MySQL-Server, it usually opens a local port, example:

 localhost:12345 -> mysqlserver:3306

如果客户端关闭连接，客户端会得到一个 TIME_WAIT.由于 TCP 路由，数据包可能会延迟到达临时端口.TIME_WAIT 中的连接只是丢弃这些数据包.如果没有 TIME_WAIT，本地端口可能会被另一个连接重用，并可能从前一个连接接收数据包.

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.

在 web 上的高频应用程序中，每个请求都会打开一个 mysql 连接，预计会有大量的 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.

如果您的本地端口范围太小，则可能会出现问题，因此您无法再打开传出连接.通常的超时设置为 60 秒.因此，在低范围内每秒超过 400 个请求时可能已经出现问题.

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.

检查:

要查看TIME_WAIT的数量，可以使用如下命令:

To check the amount of TIME_WAIT, you can use the following command:

$ 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

tw"之后的值，在本例中为 33365，表示 TIME_WAIT 的数量.

The value after "tw", in this case 33365, shows the amount of TIME_WAIT.

解决方案:

一个.TIME_WAIT 调整(基于 Linux 的操作系统示例):

a. TIME_WAIT tuning (Linux based OS examples):

减少 TIME_WAIT 的超时时间:

Reduce the timeout for 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

设置 /proc/sys/net/ipv4/tcp_tw_recycle 和 /proc/sys/net/ipv4/tcp_tw_reuse 也可能很有趣.(但我们在这些设置中遇到了奇怪的副作用，所以最好避免它们.更多信息在这个 答案)

The settings /proc/sys/net/ipv4/tcp_tw_recycle and /proc/sys/net/ipv4/tcp_tw_reuse might be interesting, too. (But we experienced strange side effects with these settings, so better avoid them. More informations in this answer)

B.持久连接

某些编程语言和库支持持久连接.另一种解决方案可能是使用本地安装的代理，如ProxySQL".这减少了新连接和关闭连接的数量.

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.

这篇关于mysql TIME_WAIT ;连接过多问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！