On Tue, Mar 9, 2010 at 1:47 PM, Scot Kreienkamp <SKreien@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
