Solved

Releasing hundreds of connections stuck in TIME_WAIT status

Posted on 2004-10-17
4
869 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:patrickq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 7

Accepted Solution

by:
petoskey-001 earned 200 total points
ID: 12344179
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
 

Author Comment

by:patrickq
ID: 12344524
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
 
LVL 7

Expert Comment

by:petoskey-001
ID: 12346508
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
 
LVL 7

Expert Comment

by:petoskey-001
ID: 12346543
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question