Avoid TIME_WAIT connections [duplicate]

When I use netstat command it shows..

tcp 0 0 localhost:18056 localhost:mysql TIME_WAIT
tcp 0 0 localhost:16683 localhost:mysql TIME_WAIT
tcp 0 0 localhost:16701 localhost:mysql TIME_WAIT
tcp 0 0 localhost:16888 localhost:mysql TIME_WAIT
tcp 0 0 localhost:16832 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17725 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17682 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17414 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17606 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17737 localhost:mysql TIME_WAIT
tcp 0 0 localhost:16632 localhost:mysql TIME_WAIT
tcp 0 0 localhost:16825 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17807 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17715 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17304 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17217 localhost:mysql TIME_WAIT
tcp 0 0 localhost:18098 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17624 localhost:mysql TIME_WAIT
tcp 0 0 localhost:17734 localhost:mysql TIME_WAIT

Time_wait connection is around 2000.

To avoid this I added net.ipv4.tcp_fin_timeout=30 to /etc/sysctl.conf

But still I have some problem,,how to avoid it?


Solution 1:

TIME_WAIT exists for a reason and the reason is that TCP packets can be delayed and arrive out of order. Messing with it will cause extra broken connections when they ought to have succeeded. There's an excellent explanation of all of this here.

Your problem is that you are not reusing your MySQL connections within your app but instead you are creating a new connection every time you want to run an SQL query. This involves not only setting up a TCP connection, but then also passing authentication credentials across it. And this is happening for every query (or at least every front-end web request) and it's wasteful and time consuming.

If you don't know how to enable persistent MySQL connection pooling in whatever language you are using, StackOverflow would be a good place to ask.