Re: WAL Log Size

On Thu, 28 Feb 2008, John Evans wrote:

> How can I change the WAL log size from 16MB to something smaller?
> Ideally, I would like to shoot for 8MB or even 4MB. The only thing that
> I've found is to hack the code to change all instances of
> YY_READ_BUF_SIZE from 16777216 to 4194304, but I'm not sure what else
> that will affect, if anything.

Nope; you'd want to play with XLOG_SEG_SIZE to change this. Have to
dump/initdb/reload your database as well to do it. Really just not a good
idea.

> I've heard that upgrading to 8.2 or 8.3 will allow me to setup a
> timeout value for WAL log creation, but upgrading at this time is not an
> option for various reasons.

Yes, the archive_timeout feature introduced into 8.2 is the one you want
but don't have yet.

Courtesy of Simon (
http://archives.postgresql.org/pgsql-general/2007-06/msg00015.php ) you
can force 16MB worth of WAL activity that doesn't leave any changes behind
with:

create table xlog_switch as
select '0123456789ABCDE' from generate_series(1,1000000);
drop table xlog_switch;

Pop that into cron etc. via psql and you can make the window for log
shipping as fine as you'd like even with no activity. I'd guess that
somewhere around every 5 minutes is as often as you'd want to abuse the
WAL features with this hack, if you do it too often you're increasing te
odds it will interfere with real transactions.

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