8

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?

MadHatter
  • 78,442
  • 20
  • 178
  • 229
Himanshu Matta
  • 99
  • 2
  • 2
  • 7

1 Answers1

21

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.

Ladadadada
  • 25,847
  • 7
  • 57
  • 90
  • time_wait is a problem or not ??? you mean I am opening a connection but not closing it?? – Himanshu Matta Feb 14 '13 at 07:56
  • 4
    TIME_WAIT means a connection is closed (FIN packets have been sent) but we're holding the ports in reserve in case some more packets come through due to delays. It also means you can't reuse that combination until it times out. On Linux, only the ports matter and you can't reuse them even on a different IP address. You can have about 30,000 before you run into problems and you can increase the ephemeral port range to delay dealing with that. – Ladadadada Feb 14 '13 at 08:07
  • ok..I got you point. Last question- as you said 'time-wait means connection is closed but we are holding the ports..' Can we reduce this holding time ??? – Himanshu Matta Feb 14 '13 at 08:12
  • 5
    You can, but it is not needed. If you want a very simple overview of what TIME_WAIT is, see the diagram in this post: http://serverfault.com/questions/450055/lot-of-fin-wait2-close-wait-last-ack-and-time-wait-in-haproxy – Hennes Feb 14 '13 at 08:18
  • 1
    You can avoid the TIME_WAIT state altogether if you avoid the "active close" action however if you are creating TCP connections to yourself (such as you are here with MySQL on localhost) then one end of the connection or the other will have to initiate closing the connection. Keeping these connections open is by far the best solution. – Ladadadada Feb 14 '13 at 09:01