Re: 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?

Thanks!

runner wrote:
>
>
>
> 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.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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

We are doing continuous archiving and we have thousands of these 16 Mb archive files on disk.
My boss is used to using Oracle where you can set the size of the files.
He'd rather have fewer but larger archive files.

On Fri, Mar 11, 2011 at 11:58 AM, runner <runner(at)winning(dot)com> wrote:
> We are doing continuous archiving and we have thousands of these 16 Mb
> archive files on disk.
>
> My boss is used to using Oracle where you can set the size of the files.
>
> He'd rather have fewer but larger archive files.

What advantage is there to a fewer number of files?

Have you tried appending the files to an archive?

merlin

runner wrote:
>
> >> 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.
>
>
> We are doing continuous archiving and we have thousands of these 16 Mb archive files on disk.
> My boss is used to using Oracle where you can set the size of the files.
> He'd rather have fewer but larger archive files.

There is no option to do that, but there is pg_lesslog which shrinks
them, though I realize you want fewer, larger ones. This is a rare
request.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Fri, 2011-03-11 at 12:51 -0500, Bruce Momjian wrote:
> runner wrote:
> >
> >
> >
> > 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.

Joshua D. Drake

>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

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

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.

On Fri, Mar 11, 2011 at 12:44:24PM -0500, runner wrote:

> 16 Mb is too small for our instalation.

How do you know that? (I can think of cases where this is true, but
it's rarer than you may think and it has some nasty side effects.)

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca

On Fri, Mar 11, 2011 at 12:58:30PM -0500, runner wrote:
> My boss is used to using Oracle where you can set the size of the files.

So your boss wants this because a system with a completely different
architecture works that way? Heck, I'd like the Nokia phone I bought
recently as an experiment to work anything like as well as my three
year old iphone, too, but that's not the way of the world.

I suspect that your boss wants this because of a preconceived notion
of what the WAL is doing, and worries about what it would do if it
worked the same way as Oracle. In particular, you might want to ask
your boss if his worry is running out of rollback segments. We don't
have that problem. (We have other ones, and one of them will be made
worse if you make these files bigger.)

I'll bet a pretty good lunch this isn't what you want, although it's
still barely possible that it is.

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca

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

We've decided against making any changes

> -----Original Message-----
> From: Andrew Sullivan [mailto:ajs(at)crankycanuck(dot)ca]
> Sent: Friday, March 11, 2011 5:02 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: How do you change the size of the WAL files?
>
> On Fri, Mar 11, 2011 at 12:58:30PM -0500, runner wrote:
> > My boss is used to using Oracle where you can set the size
> of the files.
>
> So your boss wants this because a system with a completely
> different architecture works that way? Heck, I'd like the
> Nokia phone I bought recently as an experiment to work
> anything like as well as my three year old iphone, too, but
> that's not the way of the world.
>
> I suspect that your boss wants this because of a preconceived
> notion of what the WAL is doing, and worries about what it
> would do if it worked the same way as Oracle. In particular,
> you might want to ask your boss if his worry is running out
> of rollback segments. We don't have that problem. (We have
> other ones, and one of them will be made worse if you make
> these files bigger.)
>
> I'll bet a pretty good lunch this isn't what you want,
> although it's still barely possible that it is.
>
> A
>
> --
> Andrew Sullivan
> ajs(at)crankycanuck(dot)ca
>

A bit out of topic, but a small correction here:

Oracle's analog of WAL files is RedoLog files, and they rollback
segments (or newer UNDO tablespace) is separate from RedoLog files.

Regards,
Igor Neyman

Due to the small file size I see thousands of files being generated and it
takes for ever to list thousands of files from archive directory which
stores 2 days worth of files. Sizing of WAL logs should be left to the
choice of people using the software. Limiting to a very small random 16MB
size isn't a good design.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-do-you-change-the-size-of-the-WAL-files-tp3425516p5132089.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 01/09/12 10:45 AM, rama wrote:
> Due to the small file size I see thousands of files being generated and it
> takes for ever to list thousands of files from archive directory which
> stores 2 days worth of files. Sizing of WAL logs should be left to the
> choice of people using the software. Limiting to a very small random 16MB
> size isn't a good design.

16MB isn't that small of a file, if htey were 8K or somethng, I'd see a
problem. One issue is, when the checkpoint timeout occurs, it starts
a new WAL file whether the current one is full or not. if the file size
is larger, more space would be wasted.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

rama <rsomaraju(at)masergy(dot)com> writes:
> Due to the small file size I see thousands of files being generated and it
> takes for ever to list thousands of files from archive directory which
> stores 2 days worth of files. Sizing of WAL logs should be left to the
> choice of people using the software. Limiting to a very small random 16MB
> size isn't a good design.

There are tradeoffs in the other direction too, but if you feel you must
have a different value, see configure's --with-wal-segsize option. Note
that you cannot change this without re-initdb.

regards, tom lane

On Mon, Jan 9, 2012 at 4:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> rama <rsomaraju(at)masergy(dot)com> writes:
>> Due to the small file size I see thousands of files being generated and it
>> takes for ever to list thousands of files from archive directory which
>> stores 2 days worth of files. Sizing of WAL logs should be left to the
>> choice of people using the software. Limiting to a very small random 16MB
>> size isn't a good design.
>
> There are tradeoffs in the other direction too, but if you feel you must
> have a different value, see configure's --with-wal-segsize option.  Note
> that you cannot change this without re-initdb.

Is there a limit to the max size?

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Mon, Jan 9, 2012 at 4:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> There are tradeoffs in the other direction too, but if you feel you must
>> have a different value, see configure's --with-wal-segsize option. Note
>> that you cannot change this without re-initdb.

> Is there a limit to the max size?

Hmm, it looks like the configure script only allows 1-64MB. I'm not
sure offhand if that's protecting a restriction elsewhere, or just
not bothering to extend the switch for more cases. Keep in mind that
the larger you make this, the more data you can lose because it wasn't
archived yet when your master machine failed.

regards, tom lane

Once again, pros and cons should be left to users discretion because one may have latest and greatest hardware and network resources. For example a SSD on a fiber channel on a high speed network.

Regards,
rs

On Jan 9, 2012, at 7:06 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>> On Mon, Jan 9, 2012 at 4:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> There are tradeoffs in the other direction too, but if you feel you must
>>> have a different value, see configure's --with-wal-segsize option. Note
>>> that you cannot change this without re-initdb.
>
>> Is there a limit to the max size?
>
> Hmm, it looks like the configure script only allows 1-64MB. I'm not
> sure offhand if that's protecting a restriction elsewhere, or just
> not bothering to extend the switch for more cases. Keep in mind that
> the larger you make this, the more data you can lose because it wasn't
> archived yet when your master machine failed.
>
> regards, tom lane

On 01/09/12 5:21 PM, Ron Somaraju wrote:
> Once again, pros and cons should be left to users discretion because one may have latest and greatest hardware and network resources. For example a SSD on a fiber channel on a high speed network.

as I said before, when the checkpoint timeout goes off, a partially
written WAL log will be archived. this file is still the full size,
its not 'short', its just empty.

too big of a file will mean a LOT Of waste data is being copied and
archived.

what is your checkpoint_timeout ? how many WAL files per that timeout
interval are you generating now? (like, if the timeout is 5 minutes, and
you're generating 36 files per hour, that would be about 3 WAL files per
timeout interval...)

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ron Somaraju
Sent: Monday, January 09, 2012 8:21 PM
To: Tom Lane
Cc: Scott Marlowe; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How do you change the size of the WAL files?

Once again, pros and cons should be left to users discretion because one may
have latest and greatest hardware and network resources. For example a SSD
on a fiber channel on a high speed network.

----------------------------------------------------------

And determining whether such a run-time configuration is feasible should be
left to programmer's discretion since they have the best chance of knowing
all the different parts of the system that relate to the feature/ability in
question. You are right in that everything should be end-user configurable
but maybe there are reasons that is not possible or desirable in specific
situations. Regardless, the designers still have to pick reasonable
defaults since the configuring 500 settings just to install the software is
not realistic or desirable in its own right.

While your concerns and reasoning are well-founded currently the capability
to dynamically adjust the WAL file size is not present and so the question
becomes whether you can convince the community to add such functionality in
a timely enough fashion or whether it is important enough to you to contract
one of the service providers to research and make the necessary
modifications. Simply being right doesn't mean that the current (wrong)
state is going to go away - especially since it isn't broken but rather is
not as flexible as it possibly could be.

David J.

David, thanks for the response. I appreciate it. I hope the community understands the benefit of providing the functionality to be able to dynamically increase WAL size without having to re-init the cluster (which I think is the work around). In this information age an OLTP or OLAP database with an average size of 1to 3Tb size is not uncommon. With that in view an active database could generate hundreds of 16Mb files within a very short time. In my case we are moving a large active database from Oracle to Postgresql. In Postgres I am seeing an average of 15 to 23 WAL 16Mb files every minute.

Regards,
rs

-----Original Message-----
From: David Johnston [mailto:polobo(at)yahoo(dot)com]
Sent: Tuesday, January 10, 2012 9:34 AM
To: Ron Somaraju; 'Tom Lane'
Cc: 'Scott Marlowe'; pgsql-general(at)postgresql(dot)org
Subject: RE: [GENERAL] How do you change the size of the WAL files?

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ron Somaraju
Sent: Monday, January 09, 2012 8:21 PM
To: Tom Lane
Cc: Scott Marlowe; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How do you change the size of the WAL files?

Once again, pros and cons should be left to users discretion because one may have latest and greatest hardware and network resources. For example a SSD on a fiber channel on a high speed network.

----------------------------------------------------------

And determining whether such a run-time configuration is feasible should be left to programmer's discretion since they have the best chance of knowing all the different parts of the system that relate to the feature/ability in question. You are right in that everything should be end-user configurable but maybe there are reasons that is not possible or desirable in specific situations. Regardless, the designers still have to pick reasonable defaults since the configuring 500 settings just to install the software is not realistic or desirable in its own right.

While your concerns and reasoning are well-founded currently the capability to dynamically adjust the WAL file size is not present and so the question becomes whether you can convince the community to add such functionality in a timely enough fashion or whether it is important enough to you to contract one of the service providers to research and make the necessary modifications. Simply being right doesn't mean that the current (wrong) state is going to go away - especially since it isn't broken but rather is not as flexible as it possibly could be.

David J.

On Tue, Jan 10, 2012 at 8:56 AM, Ron Somaraju <RSomaraju(at)masergy(dot)com> wrote:
> David, thanks for the response. I appreciate it. I hope the community understands the benefit of providing the functionality to be able to dynamically increase WAL size without having to re-init the cluster (which I think is the work around). In this information age an OLTP or OLAP database with an average size of 1to 3Tb size is not uncommon. With that in view an active database could generate hundreds of 16Mb files within a very short time. In my case we are moving a large active database from Oracle to Postgresql. In Postgres I am seeing an average of 15 to 23 WAL 16Mb files every minute.

While I'd love to see a test of how things work with 64M WAL files,
I'd worry about how well tested that particular code path was to use
in production. OTOH small incremental changes are often the best to
test with.