pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWAL file utilization question
8+ messages / 4 participants
[nested] [flat]
* WAL file utilization question
@ 2007-05-16 16:28 Keaton Adams <kadams@mxlogic.com>
0 siblings, 2 replies; 8+ messages in thread
From: Keaton Adams @ 2007-05-16 16:28 UTC (permalink / raw)
To: pgsql-admin
I have a question related to the WAL log. Our organization is looking at
using log-shipping to build a high-availability standby server. In our
tests the amount of WAL log data generated per minute is significant. On
our busiest database during a WAL archive test, eight 16 MB logs were
being used/archived per minute, which translated into having to move
roughly 7.6 GB of archived WAL log data across a WAN link from one data
center to another.
I read this statement from the PostgreSQL 8.1 manual and have a question
related to it:
"If full_page_writes is set (as is the default), there is another factor
to consider. To ensure data page consistency, the first modification of
a data page after each checkpoint results in logging the entire page
content. In that case, a smaller checkpoint interval increases the
volume of output to the WAL log, partially negating the goal of using a
smaller interval, and in any case causing more disk I/O."
On most database systems I am used to, there is a physical log and
separate logical log. The physical log holds any “before images” of data
pages that have been modified since the last checkpoint. The logical log
holds the individual rows of data that have been inserted, updated,
deleted as well as checkpoint records and the like. If the server
crashes, fast recovery is initiated where (1) any pages in the physical
log are put back in the data files (tablespaces/dbspaces) on disk to get
back to a state of physical consistency and (2) individual transactions
in the logical log since the last checkpoint are rolled forward / rolled
back to get to a point of logical consistency.
Even with full_page_writes set to false and checkpoints taking place
every 60 seconds or so, the amount of WAL log data generated per minute
seems to be significant.
So my question is this: If I kick off a transaction that loads records
with a size of 100 bytes, does the insert for that record take 100 bytes
in the WAL file, or is the data archived in the WAL log in page size
(8k) portions? So with piggyback commits if I can only stack up 2K worth
of data before the next LogFlush, will 2K be written to the WAL file, or
will 8K be written each time regardless of the amount of actual
transaction data that is available to flush?
Since there is no separate physical log to keep track of dirty/modified
pages since the last checkpoint I would assume that the WAL log is
serving a dual purpose of being able to get back to the point of
physical and logical database consistency, but I need to know for
certain that there is not a way to reduce the amount of WAL data being
written for the amount of transaction information we are actually
writing to the database at any given point in time.
Thank you,
Keaton
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: WAL file utilization question
@ 2007-05-16 16:35 Jim C. Nasby <decibel@decibel.org>
parent: Keaton Adams <kadams@mxlogic.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jim C. Nasby @ 2007-05-16 16:35 UTC (permalink / raw)
To: Keaton Adams <kadams@mxlogic.com>; +Cc: pgsql-admin
On Wed, May 16, 2007 at 10:28:29AM -0600, Keaton Adams wrote:
> On most database systems I am used to, there is a physical log and
> separate logical log. The physical log holds any ???before images??? of data
> pages that have been modified since the last checkpoint. The logical log
> holds the individual rows of data that have been inserted, updated,
> deleted as well as checkpoint records and the like. If the server
> crashes, fast recovery is initiated where (1) any pages in the physical
> log are put back in the data files (tablespaces/dbspaces) on disk to get
> back to a state of physical consistency and (2) individual transactions
> in the logical log since the last checkpoint are rolled forward / rolled
> back to get to a point of logical consistency.
PostgreSQL combines the two, though there has been recent discussion
about changing that. There may be some improvements in this regard in
8.3 (I don't remember if the patches were accepted or not).
> Even with full_page_writes set to false and checkpoints taking place
> every 60 seconds or so, the amount of WAL log data generated per minute
> seems to be significant.
Wow, do you really want to be checkpointing every 60 seconds? That's
going to greatly increase your WAL volume, as well as the size of WAL.
> So my question is this: If I kick off a transaction that loads records
> with a size of 100 bytes, does the insert for that record take 100 bytes
> in the WAL file, or is the data archived in the WAL log in page size
> (8k) portions? So with piggyback commits if I can only stack up 2K worth
> of data before the next LogFlush, will 2K be written to the WAL file, or
> will 8K be written each time regardless of the amount of actual
> transaction data that is available to flush?
(Generally) WAL only records differences. The exception is that the
first time a page is modified after a checkpoint, the entire page is
written out to WAL.
> Since there is no separate physical log to keep track of dirty/modified
> pages since the last checkpoint I would assume that the WAL log is
> serving a dual purpose of being able to get back to the point of
> physical and logical database consistency, but I need to know for
> certain that there is not a way to reduce the amount of WAL data being
> written for the amount of transaction information we are actually
> writing to the database at any given point in time.
The only way to do it right now is to reduce the frequency of your
checkpoints. IIRC you can't actually disable full page writes if you're
using PITR.
--
Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: WAL file utilization question
@ 2007-05-16 16:35 Alvaro Herrera <alvherre@commandprompt.com>
parent: Keaton Adams <kadams@mxlogic.com>
1 sibling, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2007-05-16 16:35 UTC (permalink / raw)
To: Keaton Adams <kadams@mxlogic.com>; +Cc: pgsql-admin
Keaton Adams wrote:
> Even with full_page_writes set to false and checkpoints taking place
> every 60 seconds or so, the amount of WAL log data generated per minute
> seems to be significant.
Stop right there. Having checkpoints every 60 seconds is insane. Did
you look into increasing checkpoint_segments a lot?
> So my question is this: If I kick off a transaction that loads records
> with a size of 100 bytes, does the insert for that record take 100 bytes
> in the WAL file, or is the data archived in the WAL log in page size
> (8k) portions?
Vicinity of 100 bytes, except right after checkpoints as you noted.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: WAL file utilization question
@ 2007-05-16 16:49 Keaton Adams <kadams@mxlogic.com>
parent: Jim C. Nasby <decibel@decibel.org>
0 siblings, 2 replies; 8+ messages in thread
From: Keaton Adams @ 2007-05-16 16:49 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-admin
Well, no, I'm not that paranoid where I expect checkpoints to be taking
place so often. I do find it interesting that controlling checkpoint
frequency is a factor of the number of WAL files available. In order to
get up to 60 second checkpoints I had to set checkpoint_segments to 25,
which resulted in the creation of 52 log files in data/pg_xlog. So for
120 second checkpoints I will need to have up to 104 log files in
data/pg_xlog available? That's nearly 2 GB of space for WAL logs, and
it only nets on average a 2 minute checkpoint. That would appear to be
the pattern (2 * checkpoint_segments at the busiest time):
# - Checkpoints -
#3 - 8 seconds - 3 log files
#6 - 16 seconds - 13 log files
#9 - 29 seconds - 19 log files
#20 - 57 seconds - 41 log files
#25 - > 60 sec - 52 log files
checkpoint_segments = 25 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 60 # in seconds, 0 is off
So, if I do set the checkpoint_segments parameter to 50, 75, or even 100
I will have a considerable number of WAL files in data/pg_xlog, but they
will be used more efficiently, so I will be archiving less? That's what
I understand from the comments received so far.
On PITR it makes sense, given the nature of the WAL file, that only full
pages can be used. So I won't be able to set full_page_writes to false
in this case.
-Keaton
On Wed, 2007-05-16 at 11:35 -0500, Jim C. Nasby wrote:
> On Wed, May 16, 2007 at 10:28:29AM -0600, Keaton Adams wrote:
> > On most database systems I am used to, there is a physical log and
> > separate logical log. The physical log holds any ???before images??? of data
> > pages that have been modified since the last checkpoint. The logical log
> > holds the individual rows of data that have been inserted, updated,
> > deleted as well as checkpoint records and the like. If the server
> > crashes, fast recovery is initiated where (1) any pages in the physical
> > log are put back in the data files (tablespaces/dbspaces) on disk to get
> > back to a state of physical consistency and (2) individual transactions
> > in the logical log since the last checkpoint are rolled forward / rolled
> > back to get to a point of logical consistency.
>
> PostgreSQL combines the two, though there has been recent discussion
> about changing that. There may be some improvements in this regard in
> 8.3 (I don't remember if the patches were accepted or not).
>
> > Even with full_page_writes set to false and checkpoints taking place
> > every 60 seconds or so, the amount of WAL log data generated per minute
> > seems to be significant.
>
> Wow, do you really want to be checkpointing every 60 seconds? That's
> going to greatly increase your WAL volume, as well as the size of WAL.
>
> > So my question is this: If I kick off a transaction that loads records
> > with a size of 100 bytes, does the insert for that record take 100 bytes
> > in the WAL file, or is the data archived in the WAL log in page size
> > (8k) portions? So with piggyback commits if I can only stack up 2K worth
> > of data before the next LogFlush, will 2K be written to the WAL file, or
> > will 8K be written each time regardless of the amount of actual
> > transaction data that is available to flush?
>
> (Generally) WAL only records differences. The exception is that the
> first time a page is modified after a checkpoint, the entire page is
> written out to WAL.
>
> > Since there is no separate physical log to keep track of dirty/modified
> > pages since the last checkpoint I would assume that the WAL log is
> > serving a dual purpose of being able to get back to the point of
> > physical and logical database consistency, but I need to know for
> > certain that there is not a way to reduce the amount of WAL data being
> > written for the amount of transaction information we are actually
> > writing to the database at any given point in time.
>
> The only way to do it right now is to reduce the frequency of your
> checkpoints. IIRC you can't actually disable full page writes if you're
> using PITR.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: WAL file utilization question
@ 2007-05-16 16:51 Jim C. Nasby <decibel@decibel.org>
parent: Keaton Adams <kadams@mxlogic.com>
1 sibling, 2 replies; 8+ messages in thread
From: Jim C. Nasby @ 2007-05-16 16:51 UTC (permalink / raw)
To: Keaton Adams <kadams@mxlogic.com>; +Cc: pgsql-admin
On Wed, May 16, 2007 at 10:49:04AM -0600, Keaton Adams wrote:
> So, if I do set the checkpoint_segments parameter to 50, 75, or even 100
> I will have a considerable number of WAL files in data/pg_xlog, but they
> will be used more efficiently, so I will be archiving less? That's what
> I understand from the comments received so far.
Yes. Let it create 500 or 1000 WAL files if it wants... it's much more
important to limit the frequency of checkpoints than to reduce the
number of WAL files (which has virtually no impact on performance).
--
Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: WAL file utilization question
@ 2007-05-16 17:08 Keaton Adams <kadams@mxlogic.com>
parent: Jim C. Nasby <decibel@decibel.org>
1 sibling, 0 replies; 8+ messages in thread
From: Keaton Adams @ 2007-05-16 17:08 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-admin
That's considerably more transaction log space to allocate than what I
am used to (up to 16 GB), but I'll set the parameter and see just how
many logs PostgreSQL creates and what the new rate of WAL file archiving
is over my test period.
Thanks, this information was very helpful,
Keaton
On Wed, 2007-05-16 at 11:51 -0500, Jim C. Nasby wrote:
> On Wed, May 16, 2007 at 10:49:04AM -0600, Keaton Adams wrote:
> > So, if I do set the checkpoint_segments parameter to 50, 75, or even 100
> > I will have a considerable number of WAL files in data/pg_xlog, but they
> > will be used more efficiently, so I will be archiving less? That's what
> > I understand from the comments received so far.
>
> Yes. Let it create 500 or 1000 WAL files if it wants... it's much more
> important to limit the frequency of checkpoints than to reduce the
> number of WAL files (which has virtually no impact on performance).
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: WAL file utilization question
@ 2007-05-16 19:31 Keaton Adams <kadams@mxlogic.com>
parent: Jim C. Nasby <decibel@decibel.org>
1 sibling, 0 replies; 8+ messages in thread
From: Keaton Adams @ 2007-05-16 19:31 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-admin
Given these postgresql.conf settings:
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
fsync = on # turns forced synchronization
on or off
wal_sync_method = fsync # the default is the first
option
# supported by the operating
system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
full_page_writes = on # recover from partial page
writes
wal_buffers = 32 # min 4, 8KB each
commit_delay = 100000 # range 0-100000, in
microseconds
commit_siblings = 1000 # range 1-1000
# - Checkpoints -
checkpoint_segments = 500 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 300 # range 30-3600, in seconds
checkpoint_warning = 120 # in seconds, 0 is off
# - Archiving -
archive_command = '/mnt/logship/scripts/archivemaster.sh %p %f'
# command to use to archive a logfile
# segment
And these tables to load data into:
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | testload | table | postgres
public | testload2 | table | postgres
public | testload3 | table | postgres
(3 rows)
postgres=# \d testload
Table "public.testload"
Column | Type | Modifiers
--------+----------------+-----------
name | character(100) |
postgres=# \d testload2
Table "public.testload2"
Column | Type | Modifiers
--------+----------------+-----------
name | character(100) |
postgres=# \d testload3
Table "public.testload3"
Column | Type | Modifiers
--------+----------------+-----------
name | character(100) |
There are no indexes on the tables.
Using an 8K data page:
8K data page (8192 bytes)
Less page header and row overhead leaves ~8000 bytes
At 100 bytes per row = ~80 rows/page
Rows loaded: 250,000 / 80 = 3125 data pages * 8192 = 25,600,000 bytes /
1048576 = ~ 24.4 MB of data page space.
The test file is shown here (250,000 rows all the same):
-bash-3.1$ more datafile.txt
AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGGHHHHHHHHHHIIIIIIIIIIJJJJJJJJJJ
AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGGHHHHHHHHHHIIIIIIIIIIJJJJJJJJJJ
AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGGHHHHHHHHHHIIIIIIIIIIJJJJJJJJJJ
The load script:
-bash-3.1$ more loaddata.sql
copy testload from '/home/kadams/logship/datafile.txt' delimiter '|';
copy testload2 from '/home/kadams/logship/datafile.txt' delimiter '|';
copy testload3 from '/home/kadams/logship/datafile.txt' delimiter '|';
So the one load process does a COPY into the three tables. 24.4 MB * 3
tables = ~ 73.2 MB of data page space.
This is the only process running on the database. No other loads/users
are on the system.
psql -f sql/loaddata.sql >/dev/null 2>&1 &
It seems that 112 MB of WAL file space (16 MB * 7) is required for 73.2
MB of loaded data, which is an extra 34.8% of disk space to log/archive
the COPY commands:
First pass:
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
LOG: archived transaction log file "00000001000000010000005E"
LOG: archived transaction log file "00000001000000010000005F"
LOG: archived transaction log file "000000010000000100000060"
LOG: archived transaction log file "000000010000000100000061"
LOG: archived transaction log file "000000010000000100000062"
LOG: archived transaction log file "000000010000000100000063"
LOG: archived transaction log file "000000010000000100000064"
# of logs in pg_xlog: 9
Second pass:
LOG: archived transaction log file "000000010000000100000065"
LOG: archived transaction log file "000000010000000100000066"
LOG: archived transaction log file "000000010000000100000067"
LOG: archived transaction log file "000000010000000100000068"
LOG: archived transaction log file "000000010000000100000069"
LOG: archived transaction log file "00000001000000010000006A"
LOG: archived transaction log file "00000001000000010000006B"
# of logs in pg_xlog: 15
Third pass:
LOG: archived transaction log file "00000001000000010000006C"
LOG: archived transaction log file "00000001000000010000006D"
LOG: archived transaction log file "00000001000000010000006E"
LOG: archived transaction log file "00000001000000010000006F"
LOG: archived transaction log file "000000010000000100000070"
LOG: archived transaction log file "000000010000000100000071"
LOG: archived transaction log file "000000010000000100000072"
# of logs in pg_xlog: 22
Fourth pass:
LOG: archived transaction log file "000000010000000100000073"
LOG: archived transaction log file "000000010000000100000074"
LOG: archived transaction log file "000000010000000100000075"
LOG: archived transaction log file "000000010000000100000076"
LOG: archived transaction log file "000000010000000100000077"
LOG: archived transaction log file "000000010000000100000078"
LOG: archived transaction log file "000000010000000100000079"
# of logs in pg_xlog: 29
PostgreSQL continued to add log files in pg_xlog, so my assumption is
that checkpoints did not come into play during the load process,
correct? (Frequent checkpoints would have added even more to the WAL
file overhead, is my understanding.)
So is there anything I can do to reduce the 34.8% overhead in WAL file
space when loading data? Do you see any glaring mistakes in the
calculations themselves, and would you agree with this overhead figure?
(I make no claim to perfection by any means.)
We are running on PostgreSQL 8.1.4 and are planning to move to 8.3 when
it becomes available. Are there space utilization improvements in WAL
logging in the upcoming release?
Thanks,
Keaton
On Wed, 2007-05-16 at 11:51 -0500, Jim C. Nasby wrote:
> On Wed, May 16, 2007 at 10:49:04AM -0600, Keaton Adams wrote:
> > So, if I do set the checkpoint_segments parameter to 50, 75, or even 100
> > I will have a considerable number of WAL files in data/pg_xlog, but they
> > will be used more efficiently, so I will be archiving less? That's what
> > I understand from the comments received so far.
>
> Yes. Let it create 500 or 1000 WAL files if it wants... it's much more
> important to limit the frequency of checkpoints than to reduce the
> number of WAL files (which has virtually no impact on performance).
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: WAL file utilization question
@ 2007-05-16 20:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Keaton Adams <kadams@mxlogic.com>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2007-05-16 20:02 UTC (permalink / raw)
To: kadams@mxlogic.com; +Cc: Jim C. Nasby <decibel@decibel.org>; pgsql-admin
Keaton Adams <kadams@mxlogic.com> writes:
> Well, no, I'm not that paranoid where I expect checkpoints to be taking
> place so often. I do find it interesting that controlling checkpoint
> frequency is a factor of the number of WAL files available. In order to
> get up to 60 second checkpoints I had to set checkpoint_segments to 25,
> which resulted in the creation of 52 log files in data/pg_xlog. So for
> 120 second checkpoints I will need to have up to 104 log files in
> data/pg_xlog available?
No, it's nonlinear, precisely because of the
checkpoints-increase-the-log-size effect. Given the size of modern
disks, I don't see why a couple Gb of WAL space is a problem anyhow
for a large database.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-05-16 20:02 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-16 16:28 WAL file utilization question Keaton Adams <kadams@mxlogic.com>
2007-05-16 16:35 ` Jim C. Nasby <decibel@decibel.org>
2007-05-16 16:49 ` Keaton Adams <kadams@mxlogic.com>
2007-05-16 16:51 ` Jim C. Nasby <decibel@decibel.org>
2007-05-16 17:08 ` Keaton Adams <kadams@mxlogic.com>
2007-05-16 19:31 ` Keaton Adams <kadams@mxlogic.com>
2007-05-16 20:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-16 16:35 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox