From: ruben@rentalia.com
To: Decibel! <decibel@decibel.org>
Cc: db@zigo.dhs.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [Again] Postgres performance problem
Date: Wed, 12 Sep 2007 09:42:29 +0200
Message-ID: <46E79865.906@rentalia.com> (raw)
In-Reply-To: <20070911225242.GG38801@decibel.org>
References: <46E636CB.8080008@rentalia.com>
<43393.192.121.104.48.1189495883.squirrel@zigo.dhs.org>
<46E64891.9080005@rentalia.com>
<20070911225242.GG38801@decibel.org>
Decibel! escribió:
> On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> db@zigo.dhs.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. :)
I do a VACUUM FULL VERBOSE ANALYZE each day. I save all logs so I can
check if vacuum is done properly.(it is)
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
I checked that there is no warnings about FSM in logs. (also in logs
from vacuum). Is it reliable?
What do u mean for "cut all of 8.1's autovac parameters in half" Maybe
default autovac parameters?
> (periodic vacuumdb -av | tail is an easy way to check that).
I ll keep an eye on it.
>
> Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> easy for them to seriously bloat.
Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
the output averyday and save it into a log, and I can check that it is
done properly.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ruben@rentalia.com, decibel@decibel.org, db@zigo.dhs.org
Subject: Re: [Again] Postgres performance problem
In-Reply-To: <46E79865.906@rentalia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox