Re: How to Kill IDLE users - Mailing list pgsql-general
From | Ezequias Rodrigues da Rocha |
---|---|
Subject | Re: How to Kill IDLE users |
Date | |
Msg-id | 55c095e90702280818w159bcaffp79a5082fe3eee039@mail.gmail.com Whole thread Raw |
In response to | Re: How to Kill IDLE users (Scott Marlowe <smarlowe@g2switchworks.com>) |
List | pgsql-general |
What a explanation ! Are you a teacher ? Thank you for your information. Now I am more calm about my idle coonections. I will mark this e-mail as a "Star" to further retrievings. Regards Ezequias 2007/2/27, Scott Marlowe <smarlowe@g2switchworks.com>: > On Tue, 2007-02-27 at 15:23, Goran Rakic wrote: > > 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. > > There are a few issues here, and I have a few questions. > > Idle users don't normally use a lot of resources, and they certainly > don't generally use increasing resources just sitting idle. However, > idle in transaction users (a different thing) do cause problems in that > the database cannot free up deleted tuples during vacuum. This can > result in a bloated database store. > > If your server is crashing from 100 idle users, something is wrong. > > Are you running 8.2.0 or 8.2.3? If you're running a version of 8.2 from > before 8.2.3 you should upgrade as soon as possible. > > Your programmers are writing broken programs if they are leaving > connections idle in transaction. You have my permission to beat them. > :) If they are just leaving connections idle, plain old idle, then > that's probably no big deal. > > Can you run a shell script that just connects until all the connections > are used up? Or does that crash the server? If it crashes it, then > you've got something configured wrong, and need to either reduce the max > number of connections, or increase the resources available to the > postgresql db server. > > You might wanna post a bit more info, like what things you've changed in > postgresql.conf, how much memory your machine has, and what the output > of > > select * from pg_stat_activity ; > > says > > > Is there parameters which will disconnect IDLE users if they excided some > > time or some program which will purge memory from non active postgres.exe > > The problem is it's hard to tell the difference between someone who's > just taking a long time to commit a real transaction and someone who's > forgotten that they logged in yesterday. The general solution here is > to have a firewall that will time out idle connections after X time. > However, such a configuration can be overcome by judicious use of tcp > keepalives. > > You can use something like: > > select procpid, usename, now()-query_start from pg_stat_activity where > current_query like '%IDLE%' and now()-query_start > interval '5 > minutes'; > > to list all the users that have been idle over the interval in the > list. Using some kind of scripting language, you could then issue kill > signals to those procpids. > > Note that I'm a unix guy, so translating this to the (in my mind) > insanity that is windows is up to you. :) > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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/
pgsql-general by date: