pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Disconnecting non active (IDLE ) users
6+ messages / 6 participants
[nested] [flat]

* Disconnecting non active (IDLE ) users
@ 2007-02-26 09:05  Goran Rakic <gossa@disyu.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Goran Rakic @ 2007-02-26 09:05 UTC (permalink / raw)
  To: pgsql-admin

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.



Is there parameters which will disconnect IDLE users if they excided some 
time or some program which will purge memory from non active posgres.exe



Thanks in advance



gossa@disyu.com







^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Disconnecting non active (IDLE ) users
@ 2007-02-28 16:01  Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
  parent: Goran Rakic <gossa@disyu.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Ezequias Rodrigues da Rocha @ 2007-02-28 16:01 UTC (permalink / raw)
  To: Goran Rakic <gossa@disyu.com>; +Cc: pgsql-admin

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 ?
Ezequias

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.
>
>
>
> Is there parameters which will disconnect IDLE users if they excided some
> time or some program which will purge memory from non active posgres.exe
>
>
>
> Thanks in advance
>
>
>
> gossa@disyu.com
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


-- 
 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Disconnecting non active (IDLE ) users
@ 2007-02-28 16:35  Shane Ambler <pgsql@Sheeky.Biz>
  parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Shane Ambler @ 2007-02-28 16:35 UTC (permalink / raw)
  To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; +Cc: Goran Rakic <gossa@disyu.com>; pgsql-admin

Ezequias Rodrigues da Rocha wrote:
> 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.

I believe IDLE = not currently processing a query / sending data back to 
client.

> Any explanation ?
> Ezequias
> 
> 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.

200 web based users should be sharing a smaller number of connections 
(maybe 50 to 100).
What are you using to generate the pages? php, asp, perl?
Do you use persistent connections in the above or some sort of pooling 
between the two (like pgpool)? or some fancy tracking of each users very 
own connection?

I would be looking into the web server connections and seeing if the 
number of connections from there grows and in so finding why they don't 
get disconnected when finished.

>> Is there parameters which will disconnect IDLE users if they excided some
>> time or some program which will purge memory from non active posgres.exe
>>


-- 

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [Bulk] Re: Disconnecting non active (IDLE ) users
@ 2007-02-28 17:29  Ted Byers <r.ted.byers@rogers.com>
  parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Ted Byers @ 2007-02-28 17:29 UTC (permalink / raw)
  To: pgsql-admin


>----- 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 





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [Bulk] Re: Disconnecting non active (IDLE ) users
@ 2007-03-19 21:45  August Zajonc <augustz@augustz.com>
  parent: Ted Byers <r.ted.byers@rogers.com>
  0 siblings, 1 reply; 6+ messages in thread

From: August Zajonc @ 2007-03-19 21:45 UTC (permalink / raw)
  To: Ted Byers <r.ted.byers@rogers.com>; +Cc: pgsql-admin

Ted Byers wrote:

[snip]
> 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. 

Let's save the determination of outrageous behavior for others and see
if we can help the user fix their problem.

Two suggestions:
Pgpool

Change keepalive settings in kernel if the machine is only used for
postgresql. If you disable keepalives, at some point the kernel will
likely drop the connection. Postgresql turns keepalives on by default
(which is usually a good thing).

Your apps should login transparently though and know how to handle this
issue. Prompting users repeatedly to login can be frustrating, the usual
question is how to keep a connection open longer :)

I'm not sure that postgresql would itself implement a timeout feature...

Good luck.

- August



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [Bulk] Re: Disconnecting non active (IDLE ) users
@ 2007-03-20 03:32  Christopher Browne <cbbrowne@acm.org>
  parent: August Zajonc <augustz@augustz.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Christopher Browne @ 2007-03-20 03:32 UTC (permalink / raw)
  To: pgsql-admin

The world rejoiced as augustz@augustz.com (August Zajonc) wrote:
> Ted Byers wrote:
>
> [snip]
>> 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. 
>
> Let's save the determination of outrageous behavior for others and see
> if we can help the user fix their problem.
>
> Two suggestions:
> Pgpool
>
> Change keepalive settings in kernel if the machine is only used for
> postgresql. If you disable keepalives, at some point the kernel will
> likely drop the connection. Postgresql turns keepalives on by default
> (which is usually a good thing).
>
> Your apps should login transparently though and know how to handle this
> issue. Prompting users repeatedly to login can be frustrating, the usual
> question is how to keep a connection open longer :)
>
> I'm not sure that postgresql would itself implement a timeout feature...

This fits very nicely into the category of things that can reasonably
vary quite a lot based on local policy.

I'd quite like to be able to say:

  "We can let some users in, with read-only access.  And we can have
  PostgreSQL enforce policies as to maximum connection times so that
  we can ensure they do not hold open <IDLE> in transaction
  connections that will destroy performance."

At present, I can only handle this via creating external utilities to
try to analyze things and look for connections that are breaking
connectivity policies.  It would be rather nice to have something to
support this in the DB engine.

That may not fit your needs: your users may be in a position to tell
you "we don't care if performance suffers - we want our connections."
-- 
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/lsf.html
"Let me blow that up a bit more for you."
-- Colin Powell, Discussing a picture of the intelligence compound in
   Iraq



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2007-03-20 03:32 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-26 09:05 Disconnecting non active (IDLE ) users Goran Rakic <gossa@disyu.com>
2007-02-28 16:01 ` Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-02-28 16:35   ` Shane Ambler <pgsql@Sheeky.Biz>
2007-02-28 17:29   ` Ted Byers <r.ted.byers@rogers.com>
2007-03-19 21:45     ` August Zajonc <augustz@augustz.com>
2007-03-20 03:32       ` Christopher Browne <cbbrowne@acm.org>

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