Re: WAL Log Size

All,

I've done some searching in the mailing list archives, and the
Internet in general, and come up with a blank. Here's my setup:

I'm working on setting up a replication system between two 8.1.11
servers using WAL log shipping via rsync. Nothing special there, but
the problem that I'm having is that a new WAL log will not be created
(and thus shipped) until 16MB of transactional data is created. The
database that I am setting things up on does not produce a vast amount of
transactions, and it can sometimes take over an hour before a new log is
generated. My boss wants a smaller window between WAL logs.

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.

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.

Any insight that you can provide will be greatly appreciated!

--
John Evans
Administrator of kilnar.com

On Thursday 28 February 2008, John Evans <admin(at)kilnar(dot)com> wrote:
> 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.
>
> Any insight that you can provide will be greatly appreciated!

Write a script that does "something" that results in 16MB of WAL logging and
run it whenever you want a rotation to occur. Inserting a few hundred
thousand rows into an otherwise empty table should work. If it's not
indexed it won't impact your server much, especially if your normal
transaction volume is that low.

--
Alan

On Feb 28, 2008, at 12:51 PM, Alan Hodgson wrote:

> On Thursday 28 February 2008, John Evans <admin(at)kilnar(dot)com> wrote:
>> 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.
>>
>> Any insight that you can provide will be greatly appreciated!
>
> Write a script that does "something" that results in 16MB of WAL
> logging and
> run it whenever you want a rotation to occur. Inserting a few hundred
> thousand rows into an otherwise empty table should work. If it's not
> indexed it won't impact your server much, especially if your normal
> transaction volume is that low.

Or, even simpler:

SELECT pg_switch_xlog();

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Thursday 28 February 2008, Erik Jones <erik(at)myemma(dot)com> wrote:
> Or, even simpler:
>
> SELECT pg_switch_xlog();

The original poster is using 8.1.

--
Alan

On Feb 28, 2008, at 1:58 PM, Alan Hodgson wrote:

> On Thursday 28 February 2008, Erik Jones <erik(at)myemma(dot)com> wrote:
>> Or, even simpler:
>>
>> SELECT pg_switch_xlog();
>
> The original poster is using 8.1.

Ah, I didn't realize that was only available in >= 8.2, thanks for
the clarification.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

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

On Thu, 28 Feb 2008, Greg Smith wrote:

> 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.

Thanks for the insight there. I figured changing the code would be
dangerous. I'm glad I asked, and thanks for the answer!

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

Thanks for the 2-liner. Good stuff. I'll be putting that into place
ASAP!

--
John Evans
Administrator of kilnar.com

On Thu, Feb 28, 2008 at 04:35:44PM -0500, Greg Smith wrote:
> 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;

Just out of interest, why doesn't it do the following?

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

Thanks,
Sam

On Fri, 29 Feb 2008, Sam Mason wrote:

> Just out of interest, why doesn't it do the following?
>
> BEGIN;
> create table xlog_switch as
> select '0123456789ABCDE' from generate_series(1,1000000);
> ROLLBACK;

I'm not 100% sure here what happens when you do the above, and it depends
on version, but there are cases where creating a new or empty table in a
transaction is optimized to not create any WAL as a performance
improvement. This has become a common idiom for that reason:

BEGIN;
truncate table t;
copy t from '/fdsa/fds/afds.csv' with csv;
COMMIT;

To take advantage of this loading without WAL feature.

I wish I had more details here, never have found a more formal definition
of how this works than suggestions on the list.

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

On Fri, Feb 29, 2008 at 1:08 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> On Fri, 29 Feb 2008, Sam Mason wrote:
>
> > Just out of interest, why doesn't it do the following?
> >
> > BEGIN;
> > create table xlog_switch as
> > select '0123456789ABCDE' from generate_series(1,1000000);
> > ROLLBACK;
>
> I'm not 100% sure here what happens when you do the above, and it depends
> on version, but there are cases where creating a new or empty table in a
> transaction is optimized to not create any WAL as a performance
> improvement. This has become a common idiom for that reason:

That's why the create table statement up there had the from
generate_series bit...