?
Solved

Releasing hundreds of connections stuck in TIME_WAIT status

Posted on 2004-10-17
4
Medium Priority
?
896 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 800 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

621 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