Mysql TIME_WAIT; too many connection problems

Question

When I checked mysql load time on the site. I got a result showing the connections as TIME_WAIT. Although I close the connection on every page. Sometimes a site does not download too many connections. What could be the solution to this problem?

Thanks to Advance for any answers or suggestions.

Answer 1

As @Zimbabao explained in a comment, debug your code for any possible errors that might stop closing a Mysql connection.

If nothing works, check my.cnf for a system variable wait_timeout. If not, add it to the partition [mysqld]and restart the Mysql server.

[mysqld]
wait_timeout = 3600

, , . http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout

3600 (1 ) .

Answer 2

TIME_WAIT Mysql, , Mysql . , . ,

mysqladmin flush-hosts

, ip,

 netstat -nat | awk {'print $5'} | cut -d ":" -f1 | sort | uniq -c | sort -n

, , , 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.

Answer 3

MySQL-, , :

 localhost:12345 -> mysqlserver:3306

, TIME_WAIT. - TCP . TIME_WAIT . TIME_WAIT .

, mysql , TIME_WAIT. .

, , . - 60 . , 400 .

:

. TIME_WAIT ( Linux):

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

:

# 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. ( , . )

.

Some programming languages ​​and libraries support persistent connections. Another solution might be to use a locally installed proxy server, such as ProxySQL. This reduces the number of new and closed connections.