Re: How to Kill IDLE users

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(at)g2switchworks(dot)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/