Re: WAL archiving to network drive

On Thu, 21 Aug 2008, Glen Parker wrote:

> So you advocate archiving the WAL files from a small xlog volume, to a larger
> local volume. Why not just make the xlog volume large enough to handle
> overruns, since you obviously have the space? Copying each WAL from one
> place to another on the local machine FAR outweighs the extra overhead
> created when WAL files most be created rather than recycled.

Yes, but the WAL creation can end up blocking the database operation in a
way that another process doesn't. Ever fill the write buffer on your OS
and have the whole database come to halt because the 16MB WAL buffer write
needed to create a new segment is blocked waiting for I/O to clear? It's
a bad situation to run into that can't happen if you're just recycling
segments.

That's really the fundamental theme of what I was driving at: anything
that's happening inside the database server processes has a potential for
disrupting the database, and as a rule I avoid all of those whenever
possible. You could easily argue that using a temporary area increases
total I/O and adds complexity compared with copying over the network
directly, and I'd agree with you--and do it anyway in some places.

Also: there are some situations where the xlog disk has a different cost
associated with its use from the others in the system. Some people are
starting to put those on flash, for example, and being able to keep the
maximum usage well under control is important there. There are some other
situations where expanding the xlog just isn't quite the same as the other
disks on the system. I've been known to adjust the disk layout so the
xlog is on a short-stroked parition for example, which also limits its
size.

> If I had to deal with different locations, I'd build more safety into
> the system.

I'm certainly not going to claim that the suggestions I made are
universal. You have a setup that sounds appropriate for you; I'm not
going to say that you should switch to what I suggested. Just trying to
throw out some things people might consider when deciding what makes sense
for their environment.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD