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 349CD53CFE for ; Tue, 12 Apr 2005 23:29:53 -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 34618-07 for ; Wed, 13 Apr 2005 02:29:50 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.193]) by svr1.postgresql.org (Postfix) with ESMTP id 5F1E553848 for ; Tue, 12 Apr 2005 23:29:49 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 40so1469811nzk for ; Tue, 12 Apr 2005 19:29:53 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=aTB7kQxsSjKZut5jXBXQwPv6RVxp4W4GkeQp9NDBkyIOtXejAVoGDHzeWL54TPaYgrXs/JNfQ/9sh2mrfBnXNSFC6x1SKixXotqNjrb1Pvp3P4iYW8ntKzM+vOi7kI556OiM8dW9GfYLHCmQlgIX/9ZFMkkITP+dx8mWFE8U6b4= Received: by 10.36.86.19 with SMTP id j19mr6298nzb; Tue, 12 Apr 2005 19:29:53 -0700 (PDT) Received: by 10.36.96.6 with HTTP; Tue, 12 Apr 2005 19:29:53 -0700 (PDT) Message-ID: Date: Wed, 13 Apr 2005 12:29:53 +1000 From: Slavisa Garic Reply-To: Slavisa Garic To: pgsql-performance@postgresql.org, pgsql-novice@postgresql.org Subject: Many connections lingering Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.398 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/157 X-Sequence-Number: 13040 Hi all, I've just noticed an interesting behaviour with PGSQL. My software is made up of few different modules that interact through PGSQL database. Almost every query they do is an individual transaction and there is a good reason for that. After every query done there is some processing done by those modules and I didn't want to lock the database in a single transaction while that processing is happening. 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_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:40720 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:39135 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43002 remus.dstc.monash:41631 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:41119 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:41311 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash.:8649 remus.dstc.monash:41369 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:40479 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:39454 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:39133 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43002 remus.dstc.monash:41501 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:39132 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:41308 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43002 remus.dstc.monash:40667 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:41179 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:39323 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:41434 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43002 remus.dstc.monash:40282 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:41050 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:41177 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:39001 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:41305 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:38937 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:39128 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43001 remus.dstc.monash:40600 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43002 remus.dstc.monash:41624 TIME_WA= IT =20 tcp 0 0 remus.dstc.monash:43002 remus.dstc.monash:39000 TIME_WA= IT=20 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. This may sound confusing so I'll try to explain this. We have this scenario, there is a PGSQL server (postmaster) which is running on machine A. Then there is a custom server called DBServer which is running on machine B. This server accepts connections from a client called an Agent. Agent may ran on any machine out there and it would connect back to DBServer asking for some information. The communication between these two is in the form of SQL queries. When agent sends a query to DBServer it passes that query to machine A postmaster and then passes back the result of the query to that Agent. The connection problem I mentioned in the paragraph above happens when Agent tries to connect to DBServer. So the only question I have here is would those lingering socket connections above have any effect on the problem I am having. If not I am sorry for bothering you all with this, if yes I would like to know what I could do to avoid that. Any help would be appreciated, Regards, Slavisa