Solved

Releasing hundreds of connections stuck in TIME_WAIT status

Posted on 2004-10-17
4
859 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now