Thread: 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!
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@momjian.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@winning.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@momjian.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@momjian.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
>> > 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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of runner Sent: Friday, March 11, 2011 2:31 PM To: pgsql-general@postgresql.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 databaseserver. I would be more concerned about encountering performance problems by changing to a non-standard setting for something likewal segment size. I'd also want to do so very intensive performance testing of such a change before deploying it. Bevery aware that just because something works in one fashion on another database like Oracle, it does not mean that it willwork 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@crankycanuck.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@crankycanuck.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@crankycanuck.ca] > Sent: Friday, March 11, 2011 5:02 PM > To: pgsql-general@postgresql.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@crankycanuck.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@masergy.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@sss.pgh.pa.us> wrote: > rama <rsomaraju@masergy.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.marlowe@gmail.com> writes:
> On Mon, Jan 9, 2012 at 4:58 PM, Tom Lane <tgl@sss.pgh.pa.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 networkresources. 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@sss.pgh.pa.us> wrote: > Scott Marlowe <scott.marlowe@gmail.com> writes: >> On Mon, Jan 9, 2012 at 4:58 PM, Tom Lane <tgl@sss.pgh.pa.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 networkresources. 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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Somaraju Sent: Monday, January 09, 2012 8:21 PM To: Tom Lane Cc: Scott Marlowe; pgsql-general@postgresql.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 functionalityto be able to dynamically increase WAL size without having to re-init the cluster (which I think is the workaround). In this information age an OLTP or OLAP database with an average size of 1to 3Tb size is not uncommon. Withthat in view an active database could generate hundreds of 16Mb files within a very short time. In my case we are movinga large active database from Oracle to Postgresql. In Postgres I am seeing an average of 15 to 23 WAL 16Mb files everyminute. Regards, rs -----Original Message----- From: David Johnston [mailto:polobo@yahoo.com] Sent: Tuesday, January 10, 2012 9:34 AM To: Ron Somaraju; 'Tom Lane' Cc: 'Scott Marlowe'; pgsql-general@postgresql.org Subject: RE: [GENERAL] How do you change the size of the WAL files? -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Somaraju Sent: Monday, January 09, 2012 8:21 PM To: Tom Lane Cc: Scott Marlowe; pgsql-general@postgresql.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 networkresources. 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 havethe best chance of knowing all the different parts of the system that relate to the feature/ability in question. Youare right in that everything should be end-user configurable but maybe there are reasons that is not possible or desirablein specific situations. Regardless, the designers still have to pick reasonable defaults since the configuring500 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 notpresent and so the question becomes whether you can convince the community to add such functionality in a timely enoughfashion or whether it is important enough to you to contract one of the service providers to research and make thenecessary modifications. Simply being right doesn't mean that the current (wrong) state is going to go away - especiallysince 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@masergy.com> wrote: > David, thanks for the response. I appreciate it. I hope the community understands the benefit of providing the functionalityto be able to dynamically increase WAL size without having to re-init the cluster (which I think is the workaround). In this information age an OLTP or OLAP database with an average size of 1to 3Tb size is not uncommon. Withthat in view an active database could generate hundreds of 16Mb files within a very short time. In my case we are movinga large active database from Oracle to Postgresql. In Postgres I am seeing an average of 15 to 23 WAL 16Mb files everyminute. 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.