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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
