On Wed, May 25, 2011 at 01:37:47PM +0100, Simon Riggs wrote:
>
> That's the way SQLServer and Oracle work, but not PostgreSQL. We can
> clear down WAL files even during a long running transaction.
>
> For us, "unneeded" means prior to the second-to-last checkpoint record.
Well, they're obviously not getting cleared down, so they must be
needed. I know how Postgres is supposed to work in these cases, but
in my experience you cannot rely on the OP's calculation to provide
you with a true maximum. Pathological conditions result in a lot of
WAL segments hanging around.
What I really suspect is that this has to do with the way I/O
scheduling works, particularly in the presence of the bgwriter. But I
don't feel comfortable suggesting particular reasons for what I've
experienced in production. What I _can_ tell you is that, when I've
had to do large restores like this, I wanted plenty of overhead for
WAL. ISTR dedicating 40G to WAL one time for a case like this.
A
--
Andrew Sullivan
ajs@crankycanuck.ca