Solve the problem of establishing connections in MySQL and quickly recycle TIME_WAIT of TCP.

Solve the problem of establishing a connection to MySQL and quickly recycle TIME_WAIT of TCP. Recently, my colleagues encountered a problem. they could not establish a connection with MySQL during execution using a python tool, the most direct phenomenon is the full article TIME_WAIT. Finally, the problem can be solved by adjusting the tcp_timestamps parameter. record the experience of this solution again.

Ps: However, I feel ashamed first. I am too insensitive to the basic tcp knowledge. I need to go back to the furnace and study again.

1. check the cause of TIME_WAIT

We all know that establishing a connection is a famous three-way handshake mechanism.

So how to close the connection? It is also a famous four-way handshake mechanism.

TIME_WAIT generates the active closing party in the four handshakes, and the server enters the close state.

So when will TIME_WAIT disappear? LINUX will disappear in 2MSL time. (MSL is the maximum segment lifetime. the default value is 2 minutes)

II. cause of connection failure

My colleague's script is used to write data after data collection. to speed up the writing, I use the python map function to write data to the database concurrently. When an error is reported on the script execution server, it is found that all documents are full of TIME_WAIT.

After our internal discussion, we initially thought that because TIME_WAT was not quickly recycled, Linux's available ports were full, leading to connection establishment without sending, and it had no relationship with MySQL settings.

Check the available port range in Linux:

[me]sysctl -a|grep ip_local_port_range net.ipv4.ip_local_port_range = 3276861000

View the currently occupied Port and use netstat directly:

netstat -nat|grep -i time_wait|wc -l

3. how can this problem be solved?

We all know three TCP link optimization parameters: tcp_tw_reuse, tcp_tw_recycle, and tcp_timestamp.

1. let's take a look at the definitions and functions of these three parameters.

Tcp_tw_reuse: Used to reuse sockets in time-wait status

Allow to reuse TIME-WAIT sockets for new connections when it is
safe from protocol viewpoint. Default value is 0.It should not be changed without advice/request of technical experts.

Tcp_tw_recycle: used to accelerate time-wait

Enable fast recycling TIME-WAIT sockets. Default value is 0.It should not be changed without advice/request of technical experts.

Tcp_timestamp: add a timestamp to the TCP header.

Enable timestamps as defined in RFC1323

2. adjust records

First, we checked the server status and found that the reuse and recycle parameters were both 1, but time_wait was not quickly recycled and quickly reused.

[root ~]# cat /proc/sys/net/ipv4/tcp_tw_reuse 1[root ~]# cat /proc/sys/net/ipv4/tcp_tw_recycle 1

Later we found that the reason is that the tcp_timestamp parameter is set to 0.

[root ~] cat /proc/sys/net/ipv4/tcp_timestamps 0

After this parameter is set to 1, the status of time_wati decreases a lot immediately, and the script can be executed normally. It seems that tcp_timestamp is critical.

3. find the reason

But does tcp_tw_reuse take effect? Or does tcp_tw_recycle take effect? We still don't know. we can only find out from the symptoms and solve the problem.

After we set the value of tcp_tw_recycle to 0, we will test again, and there will be a large number of time_wait phenomena again.

To prove that tcp_tw_recycle actually works, we set the tcp_tw_reuse parameter to 1 again. at this time, a large number of time_wait phenomena also occur.

It seems that our final conclusion is that tcp_tw_recycle, tcp_tw_reuse, and tcp_timestamp can be enabled at the same time to quickly recycle and use the time-wait socket.

The answer from stackoverflow is reliable. Click here

However, it is also found that many information is displayed on the internet. if tcp_tw_recycle and tcp_timestamp are enabled at the same time, problems may occur. This is mainly because of a TCP behavior.

RFC 1323　　TCP Extensions for High Performance　　Jacobson, Braden, & Borman

An additional mechanism could be added to the TCP, a per-hostcache of the last timestamp received from any connection.This value could then be used in the PAWS mechanism to rejectold duplicate segments from earlier incarnations of theconnection, if the timestamp clock can be guaranteed to haveticked at least once since the old connection was open.Thiswould require that the TIME-WAIT delay plus the RTT togethermust be at least one tick of the sender's timestamp clock.Such an extension is not part of the proposal of this RFC.

III. Conclusion

1. enabling tcp_timestamp is a prerequisite for enabling tcp_tw_recycle, tcp_tw_reuse, and tcp_timestamp.

2. However, in nat mode, you do not need to enable tcp_tw_recycle and tcp_timestamp at the same time, which causes a tcp timeout failure.

Appendix:

Https://www.kernel.org/doc/Documentation/networking/ip-sysctl.txt

Http://lxr.free-electrons.com/source/net/ipv4/tcp_minisocks.c#L92

Http://www.ietf.org/rfc/rfc1323.txt