Releasing hundreds of connections stuck in TIME_WAIT status

This is with MySQL 4.1 on WinXP and VS 2003 + C# + ADO.NET

After running a series of thousands of queries (no updates) using the OleDb DataReader object, netstat -n shows litterally hundreds of 3306 connections stuck in TIME_WAIT status. This is spite of the fact that I am using only one OleDbConnection object in my code, and consistently calling .Close on each reader I use before it goes out of scope. No threads, just a sequential series of queries.

At some point, programs can't connect anymore. Waiting a minute or two helps and most of these connections seem to time out and get closed.

I've been told that this is caused by a protocol the server uses to make sure "no data was lost in the connection because of a timing between the client and server" (?)

Is there a way to configure MySQL to not keep such connections alive for that long?

Thanks!
Patrick
patrickqAsked:
Who is Participating?
 
petoskey-001Commented:
this is not a MySql problem, it's a windows problem.  When you close a connection, windows is given permission to close, but doesn't do it right away.  To fix edit your registry and reboot.  You can cut and paste between the lines below or use regedit yourself.

--------------------- runthis.reg -------------------------------------------------------
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"TcpTimedWaitDelay"=dword:00000030
------------------------------------------------------------------------------------------

Create a new DWord named TcpTimedWaitDelay.  Enter the number of seconds to wait before closing a TIME_WAIT connection.  It defaults to 240 seconds if this registry entry doesn't exist.  Something like 30 or less should be good.  This will stop your connections from building up.

I'm assuming you can handle a registry edit if you doing C# + ADO.NET.
0
 
patrickqAuthor Commented:
Thanks, I used 30s at it does work as you describe ... still there might be application scenarios gobbling up all connections within 30s - what's the downside of setting this timeout to, say, 1 second?

Thanks!
Patrick
0
 
petoskey-001Commented:
I think the downside is you miss the fin-ack.  When you close the connection you send a FIN packet it tells the other side you closed the connection, then you wait for the FIN-ACK return packet that tells you the other side has acknowledged that you closed your connection.  So if something happens to that packet and your delay is too short then the other side may still see you as connected but not sending data, thus tieing up connections and threads on their end.  

It should be set to a reasonable amount of time for the other side to completely finish processing all previous packets and send responses back to you.
0
 
petoskey-001Commented:
It works like this....


  Sender                    Receiver
    Fin      ---------->  
              <----------    FIN-ACK

              <----------    DATA
    DATA-ACK ------->

              <----------    FIN
    FIN-ACK  ---------->  


So you could be losing data not yet sent by the other side too, forgot about that part.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.