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

Re: WAL Log Size

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: John Evans <admin(at)kilnar(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: WAL Log Size
Date: 2008-02-28 21:35:44
Message-ID: Pine.GSO.4.64.0802281616280.20906@westnet.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Scara MaccaiDate: 2008-02-28 21:46:54
Subject: Re: partitioning using dblink
Previous:From: Terry Lee TuckerDate: 2008-02-28 21:20:56
Subject: LIMIT Question

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