Обсуждение: How to Kill IDLE users

Поиск
Список
Период
Сортировка

How to Kill IDLE users

От
"Goran Rakic"
Дата:
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






Re: How to Kill IDLE users

От
Scott Marlowe
Дата:
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. :)

Re: How to Kill IDLE users

От
"Ang Chin Han"
Дата:
On 2/28/07, Scott Marlowe <smarlowe@g2switchworks.com> wrote:

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

There's always pg_cancel_backend().
http://www.postgresql.org/docs/8.2/static/functions-admin.html

So:

select pg_cancel_backend(procpid) from pg_stat_activity where
current_query = '<IDLE>' and now() - query_start > interval '5
minutes';

should work nicely. I've not really tested that properly though.

Re: How to Kill IDLE users

От
Shelby Cain
Дата:
Since he has so many connections, perhaps the crash is related to bugs #2609 and #1641?  8.2.x seems to have the
problemas well. 

Regards,

Shelby Cain

----- Original Message ----
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Goran Rakic <gossa@disyu.com>
Cc: pgsql general <pgsql-general@postgresql.org>
Sent: Tuesday, February 27, 2007 5:16:40 PM
Subject: Re: [GENERAL] How to Kill IDLE users

If your server is crashing from 100 idle users, something is wrong.








____________________________________________________________________________________
Now that's room service!  Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097

Re: How to Kill IDLE users

От
"Ezequias Rodrigues da Rocha"
Дата:
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/