Re: How do you change the size of the WAL files?

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of runner
Sent: Friday, March 11, 2011 2:31 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How do you change the size of the WAL files?

>> > Our WAL files are 16 Mb in size each. I cannot find where you
>> > configure them to make them larger. 16 Mb is too small for our
>> > instalation. If necessary, I can rebuild the entire cluster. Our

>> > DBA initialized a new cluster and specified a larger WAL size but it

>> > stayed at 16 Mb. Any info on this?

>>

>> Why is 16MB too small? Most people increase checkpoint_segments for

>> larger installs and it works fine.

>To answer the question, it is possible to change the wal log size. It is

>a setting in pg_config.h: #define XLOG_SEG_SIZE (16 * 1024 * 1024) .

>

>If you change that and recompile, your wal segment size will be

>different. I have heard of people running 32meg sizes. We have tried

>with smaller sizes but it ended up thrashes the disk.

>

>Further keep in mind you will have to do a dump and restore to the new

>cluster. Your existing cluster will not be usable once you do this.

>Probably won't change then. The boss was worried that a 16 Mb file size would cause performance issues on a busy database server.

I would be more concerned about encountering performance problems by changing to a non-standard setting for something like wal segment size. I'd also want to do so very intensive performance testing of such a change before deploying it. Be very aware that just because something works in one fashion on another database like Oracle, it does not mean that it will work in the same fashion for Postgres (or any other DB for that matter).

Brad.