Re: [Again] Postgres performance problem

On 9/12/07, ruben(at)rentalia(dot)com <ruben(at)rentalia(dot)com> wrote:
>
> Decibel! escribió:
> > 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. :)
>
> I do a VACUUM FULL VERBOSE ANALYZE each day. I save all logs so I can
> check if vacuum is done properly.(it is)

Then, like Jim said, you're doing it wrong. Regular vacuum full is
like rebuiling a piece of equipment every night when all it needs is
the filter changed.

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

Yep. ( I assume)

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

Then you're vacuum full is wasted. A reindex accomplishes the same
thing, plus shrinks indexes (vacuum full can bloat indexes).

Just run regular vacuums, preferably by autovacuum, and keep an eye on
the vacuum analyze you run each night to see if your fsm is big
enough.

Occasionally vacuum full is absolutely the right answer. Most the
time it's not.

I'm getting more and more motivated to rewrite the vacuum docs. I
think a rewrite from the ground up might be best... I keep seeing
people doing vacuum full on this list and I'm thinking it's as much
because of the way the docs represent vacuum full as anything. Is
that true for you?