Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Releasing hundreds of connections stuck in TIME_WAIT status

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

718 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