Re: [psycopg] #92: psycopg2 connection leaks handles under Windows

[psycopg]

#92: psycopg2 connection leaks handles under Windows
------------------------------+---------------------------------------------
 Reporter:  gpd at gpdnet.co.uk  |        Owner:  fog    
     Type:  defect            |       Status:  new    
 Priority:  high              |    Milestone:         
Component:  psycopg2          |      Version:  2.0beta
 Severity:  critical          |   Resolution:         
 Keywords:                    |  
------------------------------+---------------------------------------------
Comment (by jerickso at stickpeople.com):

 I also tried the example listed above (and thanks for a simple test case
 that showed your problem).  I as well was unable to duplicate the problem.
 With the client, I am running Windows XP/SP2, Python 2.4.2 w/ the 2.0b6.2
 psycopg.  The server is a FreeBSD 4.11 machine, running PostgreSQL 8.1.2.
 The server is not on the same LAN as the client.  The ping time from the
 client to the server is 30ms.

 I've also compiled a recent version and reran the test.  Again, it opened
 and closed all 10000.  You can try the new version as well, at:
 [http://www.stickpeople.com/projects/python/win-psycopg/psycopg2-2.0b7-
 r734.win32-py2.4.exe].

 As you indicated, the error message seems to point that psycopg (actually
 libpq) is trying to open up an address/port on the local machine that is
 already in use.  The error 'Address already in use' in libpq is the MS
 error WSAEADDRINUSE.  The strange part is that you typically see the error
 when you are trying to bind to a port already in use (ie. Starting a web
 server when there is already a webserver running and bound to that port).
 The libpq should not care what port it is opening up, since it is
 initializing the connection and should pick the next free port.  Of
 course, if all the ports are in use, that is another matter....


 Running the program and doing a netstat, tho, is showing something that I
 believe is causing the problem (Note: need some win32 unix tools for the
 following command):
   netstat | grep ":5432 " | wc

 The number of 'open' ports to PostgreSQL(5432) starts to increases with
 the number of connections while the script is running.  It levels off
 around 1400-1440.  Now, in fairness, all but 2-3 of the ports are in the
 TIME_WAIT state, with the other 2-3 in the ESTABLISHED state.  Which makes
 sense.  Typically, when a connection closes, the side that requested the
 closure ends up entering a TIME_WAIT state.  The default TIME_WAIT on
 Windows is somewhere between 2 and 4 minutes (Microsoft says it is 240
 seconds, but I'm showing 2 minutes), which matches up with where the
 number of open ports leveled off.  Note that leaving the python prompt
 open for >4minutes after the code finishes running and then running
 netstat shows no ports still open.

 I seem to recall a Windows Server 2003 quirk that would reallocate a port
 that was still in the TIME_WAIT state, but I believe that was fixed in
 SP1.  Not sure if other Windows versions had the same issue.

 If you have a fast network, and you have both a fast client and server
 machine, I can see you running out of ports because of the TIME_WAIT.
 Even thought there are only 65,536 ports available with Windows
 NT/2000/XP/Server*, it only allocates the port numbers up to 5000 for
 outbound connections.  With it starting to allocate somewhere above 1024,
 that leaves less than 4000 ports that one can play with, which corresponds
 with what you were seeing.  If you can create 4000 connections to your
 server in under the timeout, I'm sure this is your culprit.

 What can you do???  There are two options, both of which require adding a
 value to the registry, and you may want to add/change both.  Standard
 disclaimers about editing the registry apply.

 1) Set the TIME_WAIT delay in Windows.
 Add a DWORD key 'TcpTimedWaitDelay' to:
 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

 Value in seconds between 30 and 300.  Default is 2*MSL=240 (MSL is maximum
 segment life of the network).


 2) Increate the Maximum User ports in Windows.
 Add a DWORD key 'MaxUserPorts' to:
 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

 Value between 5000-65534.  Remember that the first 1024 is not used when
 an application requests a port.


 -jason

-- 
Ticket URL: <http://initd.org/tracker/psycopg/ticket/92>
psycopg <http://initd.org/>
psycopg