Re: Strange performance degradation

In response to Lorenzo Allegrucci <lorenzo(dot)allegrucci(at)forinicom(dot)it>:

> Tom Lane wrote:
> > Lorenzo Allegrucci <lorenzo(dot)allegrucci(at)forinicom(dot)it> writes:
> >> So, my main question is.. how can just a plain simple restart of postgres
> >> restore the original performance (3% cpu time)?
> >
> > Are you killing off any long-running transactions when you restart?
>
> After three days of patient waiting it looks like the common
> '<IDLE> in transaction' problem..
>
> [sorry for >80 cols]
>
> 19329 ? S 15:54 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -c config_file=/etc/postgresql/8.3/main/postgresql.conf
> 19331 ? Ss 3:40 \_ postgres: writer process
> 19332 ? Ss 0:42 \_ postgres: wal writer process
> 19333 ? Ss 15:01 \_ postgres: stats collector process
> 19586 ? Ss 114:00 \_ postgres: forinicom weadmin [local] idle
> 20058 ? Ss 0:00 \_ postgres: forinicom weadmin [local] idle
> 13136 ? Ss 0:00 \_ postgres: forinicom weadmin 192.168.4.253(43721) idle in transaction
>
> My app is a Django webapp, maybe there's some bug in the Django+psycopg2 stack?
>
> Anyway, how can I get rid those "idle in transaction" processes?
> Can I just kill -15 them or is there a less drastic way to do it?

Connections idle in transaction do not cause performance problems simply
by being there, at least not when there are so few.

If you -TERM them, any uncommitted data will be rolled back, which may
not be what you want. Don't -KILL them, that will upset the postmaster.

My answer to your overarching question is that you need to dig deeper to
find the real cause of your problem, you're just starting to isolate it.
Try turning full query logging on and track what those connections are
actually doing.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/