pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ted Byers <r.ted.byers@rogers.com>
To: pgsql-admin@postgresql.org
Subject: Re: [Bulk] Re: Disconnecting non active (IDLE ) users
Date: Wed, 28 Feb 2007 12:29:50 -0500
Message-ID: <030c01c75b5e$0de7ffb0$6401a8c0@RnDworkstation> (raw)
References: <erv48i$26o7$1@news.hub.org>
	<55c095e90702280801q484b75d8v3833a18c5d5a5c16@mail.gmail.com>


>----- Original Message ----- 
>From: "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com>
>To: "Goran Rakic" <gossa@disyu.com>
>Cc: <pgsql-admin@postgresql.org>
>Sent: Wednesday, February 28, 2007 11:01 AM
>Subject: [Bulk] Re: [ADMIN] Disconnecting non active (IDLE ) users


>I would like to know how postgresql defines that a connection is
>inactive. I have the same problem but in the future I couldn't restart
>my server every day.

>Any explanation ?

I would be quite interested in the answer to this, but it seems to me that 
there is a bigger problem.

>2007/2/26, Goran Rakic <gossa@disyu.com>:
>> I have installed POSTGRESQL 8.2 on W2K3 32bit , 100 users connecting from
>> desktop applications and 200 users connecting thru web service from 
>> handheld
>> computers
>>
>> I have problem with second groups of users.
>>
>> Often they do not disconnect from POSTGRE Server and with time passing 
>> thru
>> I have lot of IDLE users and very much memory consumptions. From time to
>> time that can crash server then only restarting server will kill all
>> postgres.exe from memory and this annoying me, because that I build 
>> script
>> to restart server every night. I could not get programmers to change
>> program.

Part of the solution here seems to be a management issue rather than a 
technological one.

If the web application above is bought from a third party, I would expect 
management to diligently pursue the provider for a reasonable solution.  It 
is simply unacceptable that a company server should crash on a regular basis 
because of slop in an application purchased from some other company. 
Similarly, if the application was developed in house, then, obviously, 
management ought to be aware of the server crash issue and ought to be 
directing the relevant programmers to fix the problem.  It is outrageously 
unethical, IMHO, for a programmer, or group thereof, to refuse to fix a 
problem that has arisen from how their targetted users use their 
application.  Of course, before becoming too hard on the programmers 
mentioned above, the onus should be on Goran to provide substantive proof to 
his management and the other programmers that their client code is 
responsible (or, better, management ought to have created a small team 
including both Goran and the programmers in question to study the problem in 
order to determine precisely what the cause of the problem is and how it is 
best addressed).  After all, it may just be coincidence that the server is 
crashing when there are lots of users connecting using a web service, and 
the real cause may be something entirely different.  Goran noted a 
correlation between memory consumption and the number of idle connections. 
He now needs to determine why, especially since an idle connection by itself 
ought not consume a significant amount of resources.  What has happened 
previously during the establishment and use of these connections?  Did, the 
user create, and not release, a significant number of temporary tables or 
even a single temporary table that was populated with a lot of data?  I have 
known SQL programmers to do precisely this on the assumption the temporary 
table goes away once the session is terminated, but if the session doesn't 
get terminated the temporary table just hangs around.  IMHO, such a 
programmer deserves a good reprimand, and my practice is to drop such 
temporaries the moment they're no longer needed.  I have yet to find a 
fellow programmer that objected to this practice, even when that person is 
responsible for the kind of slop that depends on the server software, or 
middleware code in some cases, to free the resources so carelessly used. 
Over the years, I have encountered serious bugs even in commercial libraries 
in which resources weren't properly freed, and I found ways to use ANSI 
standard specifications to work around such bugs, to solve the problems they 
created for me, until such a time as the vendor fixed the bugs in question. 
Alas, too often fixing such bugs is given a low priority because only a 
small proportion of the vendor's clients are hurt by them because they 
develop applications that don't intensively use resources (mine, 
particularly my analysis and modelling applications, do tend to use a lot of 
memory so I am especially sensitive to memory leaks).

One final observation I would make is that something is seriously wrong with 
either the design or the implementation of the client application if either 
the number of idle connections grows continuously without limit or if it 
gets very large.  I would expect a few idle connections, the actual number 
of which being dependant on how you're doing connection pooling, but I would 
insist on the client code being very careful about closing excess idle 
connections.  I can see a wide variety of programming blunders or slop 
whereby connections are leaked, and never closed until they cause something 
to crash.  If there is evidence of such slop, even if it is not the 
proximate cause of the server crashing, the programmer responsible deserves 
a good reprimand.

On this thread, there have been considerations given of why a few hundred 
idle connections should not consume enough resources to cause a server to 
crash.  If so few of them could cause such a crash, that would raise doubts 
about whether or not the server software is ready for production use.  In 
any event, I haven't seen enough information to begin to figure out the real 
cause of the problem.  But, there has also been information provided about 
how one can disconnect idle connections from within PostgreSQL: the SQL 
looked simple enough.  I might even use it, but only as a last resort since 
in my experience, these kinds of problems are often due to programming slop, 
and this slop ought to never be tolerated.  To rely on this, without dealing 
with any programming slop, masks the real problem and strikes me as being as 
foolish as a programmer restarting his program after each task because it 
has a memory leak instead of fixing the memory leak (or any other favourite 
resource leak you may have experienced, if your programming language of 
choice doesn't support pointers or creation of objects on the heap instead 
of the stack).

HTH

Ted 





view thread (6+ messages)  latest in thread

Message-ID: <030c01c75b5e$0de7ffb0$6401a8c0@RnDworkstation>
Permalink:  ../030c01c75b5e$0de7ffb0$6401a8c0@RnDworkstation/
Also on:    postgresql.org/message-id/030c01c75b5e$0de7ffb0$6401a8c0@RnDworkstation

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: r.ted.byers@rogers.com
  Subject: Re: [Bulk] Re: Disconnecting non active (IDLE ) users
  In-Reply-To: <030c01c75b5e$0de7ffb0$6401a8c0@RnDworkstation>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox