Skip site navigation (1) Skip section navigation (2)

Re: WAL archiving to network drive

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Glen Parker <glenebob(at)nwlink(dot)com>
Cc: postgres general <pgsql-general(at)postgresql(dot)org>
Subject: Re: WAL archiving to network drive
Date: 2008-08-29 04:16:20
Message-ID: Pine.GSO.4.64.0808282354130.23621@westnet.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Tino WildenhainDate: 2008-08-29 04:20:49
Subject: Re: SQL optimization - WHERE SomeField STARTING WITH ...
Previous:From: Alvaro HerreraDate: 2008-08-29 02:41:20
Subject: Re: indexes on functions and create or replace function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group