X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3967D5398E; Wed, 13 Apr 2005 00:27:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55611-03; Wed, 13 Apr 2005 03:27:26 +0000 (GMT) Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by svr1.postgresql.org (Postfix) with ESMTP id E0498539B2; Wed, 13 Apr 2005 00:27:25 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1DLYX0-00012K-00; Tue, 12 Apr 2005 23:27:10 -0400 To: Tom Lane Cc: Slavisa Garic , pgsql-performance@postgresql.org, pgsql-novice@postgresql.org Subject: Re: [PERFORM] Many connections lingering References: <2314.1113360715@sss.pgh.pa.us> In-Reply-To: <2314.1113360715@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 12 Apr 2005 23:27:09 -0400 Message-ID: <87u0mbjr02.fsf@stark.xeocode.com> Lines: 58 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/160 X-Sequence-Number: 13043 Tom Lane writes: > Slavisa Garic writes: > > ... Now, the > > interesting behaviour is this. I've ran netstat on the machine where > > my software is running and I searched for tcp connections to my PGSQL > > server. What i found was hundreds of lines like this: > > > tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:39504 TIME_WAIT > > tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:40720 TIME_WAIT > > tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:39135 TIME_WAIT > > This is a network-level issue: the TCP stack on your machine knows the > connection has been closed, but it hasn't seen an acknowledgement of > that fact from the other machine, and so it's remembering the connection > number so that it can definitively say "that connection is closed" if > the other machine asks. I'd guess that either you have a flaky network > or there's something bogus about the TCP stack on the client machine. > An occasional dropped FIN packet is no surprise, but hundreds of 'em > are suspicious. No, what Tom's describing is a different pair of states called FIN_WAIT_1 and FIN_WAIT_2. TIME_WAIT isn't waiting for a packet, just a timeout. This is to prevent any delayed packets from earlier in the connection causing problems with a subsequent good connection. Otherwise you could get data from the old connection mixed in the data for later ones. > > Now could someone explain to me what this really means and what effect > > it might have on the machine (the same machine where I ran this > > query)? Would there eventually be a shortage of available ports if > > this kept growing? The reason I am asking this is because one of my > > modules was raising exception saying that TCP connection could not be > > establish to a server it needed to connect to. What it does indicate is that each query you're making is probably not just a separate transaction but a separate TCP connection. That's probably not necessary. If you have a single long-lived process you could just keep the TCP connection open and issue a COMMIT after each transaction. That's what I would recommend doing. Unless you have thousands of these TIME_WAIT connections they probably aren't actually directly the cause of your failure to establish connections. But yes it can happen. What's more likely happening here is that you're stressing the server by issuing so many connection attempts that you're triggering some bug, either in the TCP stack or Postgres that is causing some connection attempts to not be handled properly. I'm skeptical that there's a bug in Postgres since lots of people do in fact run web servers configured to open a new connection for every page. But this wouldn't happen to be a Windows server would it? Perhaps the networking code in that port doesn't do the right thing in this case? -- greg