pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: ruben@rentalia.com <ruben@rentalia.com>
Cc: 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 13:28:14 -0500
Message-ID: <dcc563d10709121128o1e5883a9n96b1f9ed74901be9@mail.gmail.com> (raw)
In-Reply-To: <46E79865.906@rentalia.com>
References: <46E636CB.8080008@rentalia.com>
	<43393.192.121.104.48.1189495883.squirrel@zigo.dhs.org>
	<46E64891.9080005@rentalia.com>
	<20070911225242.GG38801@decibel.org>
	<46E79865.906@rentalia.com>

On 9/12/07, ruben@rentalia.com <ruben@rentalia.com> wrote:
>
> 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)

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?



view thread (36+ messages)  latest in thread

Message-ID: <dcc563d10709121128o1e5883a9n96b1f9ed74901be9@mail.gmail.com>
Permalink:  ../dcc563d10709121128o1e5883a9n96b1f9ed74901be9@mail.gmail.com/
Also on:    postgresql.org/message-id/dcc563d10709121128o1e5883a9n96b1f9ed74901be9@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: scott.marlowe@gmail.com, ruben@rentalia.com, decibel@decibel.org, db@zigo.dhs.org
  Subject: Re: [Again] Postgres performance problem
  In-Reply-To: <dcc563d10709121128o1e5883a9n96b1f9ed74901be9@mail.gmail.com>
  References: <46E636CB.8080008@rentalia.com> <43393.192.121.104.48.1189495883.squirrel@zigo.dhs.org> <46E64891.9080005@rentalia.com> <20070911225242.GG38801@decibel.org> <46E79865.906@rentalia.com> <dcc563d10709121128o1e5883a9n96b1f9ed74901be9@mail.gmail.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