Thread: 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@kilnar.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@kilnar.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@myemma.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@myemma.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@myemma.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@myemma.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@gregsmith.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@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Fri, Feb 29, 2008 at 1:08 AM, Greg Smith <gsmith@gregsmith.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...