Re: [Again] Postgres performance problem

On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> db(at)zigo(dot)dhs(dot)org escribi?:
> >> Last time I had this problem i solved it stopping website, restarting
> >> database, vacuumm it, run again website. But I guess this is going to
> >> happen again.
> >>
> >> I would like to detect and solve the problem. Any ideas to detect it?
> >
> > Do you have very long transactions? Maybe some client that is connected
> > all the time that is idle in transaction?
>
> There should not be long transactions. I ll keep an eye on Idle transactions
>
> I m detecting it using:
>
> echo 'SELECT current_query FROM pg_stat_activity;' |
> /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l

If you're using VACUUM FULL, you're doing something wrong. :) Run lazy
vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's
autovac parameters in half), and make sure your FSM is big enough
(periodic vacuumdb -av | tail is an easy way to check that).

Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
easy for them to seriously bloat.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)