Re: autovacuum question

On Tue, Mar 9, 2010 at 1:47 PM, Scot Kreienkamp <SKreien(at)la-z-boy(dot)com> wrote:
> I found a way to do it very easily using LVM snapshots and WAL log
> shipping, but the net effect is I'm bringing a new LVM snapshot copy of
> the database out of recovery every 1-2 hours.  That means I'd have to
> spend 15 minutes, or one-quarter of the time, doing an analyze every
> time I refresh the database.  That's fairly painful.  The LVM snap and
> restart only takes 1-2 minutes right now.

Your snapshot should have the same stats that the server does, so this
doesn't actually seem to explain the discrepancy.

You be running into performance problems with LVM if the snapshot is
the one paying the price for all the CoW copies. Or it could be that
doing retail block copies as needed results in them being fragmented
and destroying the sequential scan performance. You might be able to
reduce the difference by making sure to do a vacuum and a checkpoint
immediately prior to the snapshot. That would hopefully achieve
setting most hint bits so that read-only queries on the snapshot don't
cause writes to blocks just to set them.

There might be an option in LVM to materialize the entire snapshot
which might be able to bring the performance up to the same level and
hopefully allocate all the blocks sequentially.

--
greg