pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWAL log performance/efficiency question
6+ messages / 3 participants
[nested] [flat]
* WAL log performance/efficiency question
@ 2007-05-17 14:04 Keaton Adams <kadams@mxlogic.com>
2007-05-17 14:23 ` Re: WAL log performance/efficiency question Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-17 16:01 ` Re: WAL log performance/efficiency question Keaton Adams <kadams@mxlogic.com>
0 siblings, 2 replies; 6+ messages in thread
From: Keaton Adams @ 2007-05-17 14:04 UTC (permalink / raw)
To: pgsql-performance
I sent this to pgsql-admin but didn't receive a response. Would this be
a WAL log performance/efficiency issue?
Thanks,
Keaton
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?
We are running on PostgreSQL 8.1.4 and are planning to move to 8.3 when
it becomes available. Are there space utilization/performance
improvements in WAL logging in the upcoming release?
Thanks,
Keaton
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL log performance/efficiency question
2007-05-17 14:04 WAL log performance/efficiency question Keaton Adams <kadams@mxlogic.com>
@ 2007-05-17 14:23 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-17 14:55 ` Re: WAL log performance/efficiency question Tom Lane <tgl@sss.pgh.pa.us>
2007-05-17 15:34 ` Re: WAL log performance/efficiency question Keaton Adams <kadams@mxlogic.com>
1 sibling, 2 replies; 6+ messages in thread
From: Heikki Linnakangas @ 2007-05-17 14:23 UTC (permalink / raw)
To: kadams@mxlogic.com; +Cc: pgsql-performance
Keaton Adams wrote:
> 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.
That's not accurate. There's 32 bytes of overhead per row, and that
gives you just 61 tuples per page. Anyhow, I'd suggest measuring the
real table size with pg_relpages function (from contrib/pgstattuple) or
from pg_class.relpages column (after ANALYZE).
> We are running on PostgreSQL 8.1.4 and are planning to move to 8.3 when
> it becomes available. Are there space utilization/performance
> improvements in WAL logging in the upcoming release?
One big change in 8.3 is that COPY on a table that's been created or
truncated in the same transaction doesn't need to write WAL at all, if
WAL archiving isn't enabled.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL log performance/efficiency question
2007-05-17 14:04 WAL log performance/efficiency question Keaton Adams <kadams@mxlogic.com>
2007-05-17 14:23 ` Re: WAL log performance/efficiency question Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-05-17 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2007-05-17 14:55 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: kadams@mxlogic.com; pgsql-performance
Heikki Linnakangas <heikki@enterprisedb.com> writes:
> Keaton Adams wrote:
>> We are running on PostgreSQL 8.1.4 and are planning to move to 8.3 when
>> it becomes available. Are there space utilization/performance
>> improvements in WAL logging in the upcoming release?
> One big change in 8.3 is that COPY on a table that's been created or
> truncated in the same transaction doesn't need to write WAL at all, if
> WAL archiving isn't enabled.
There are a couple of improvements in tuple storage (the header is
shorter, and short varlena fields have less overhead); those would
translate pretty directly into less WAL space too.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL log performance/efficiency question
2007-05-17 14:04 WAL log performance/efficiency question Keaton Adams <kadams@mxlogic.com>
2007-05-17 14:23 ` Re: WAL log performance/efficiency question Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-05-17 15:34 ` Keaton Adams <kadams@mxlogic.com>
2007-05-17 15:52 ` Re: WAL log performance/efficiency question Heikki Linnakangas <heikki@enterprisedb.com>
1 sibling, 1 reply; 6+ messages in thread
From: Keaton Adams @ 2007-05-17 15:34 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
So for every data page there is a 20 byte header, for every row there is
a 4 byte identifier (offset into the page), AND there is also a 28 byte
fixed-size header (27 + optional null bitmap)?? (I did find the section
in the 8.1 manual that give the physical page layout.) The other RDBMS
platforms I have worked with have a header in the 28 byte range and a
row pointer of 4 bytes, and that's it. I find it a bit surprising that
PostgreSQL would need another 28 bytes per row to track its contents.
I'll try the pg_relpages function as you suggest and recalculate from
there.
Thanks for the info,
-Keaton
On Thu, 2007-05-17 at 15:23 +0100, Heikki Linnakangas wrote:
> Keaton Adams wrote:
> > 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.
>
> That's not accurate. There's 32 bytes of overhead per row, and that
> gives you just 61 tuples per page. Anyhow, I'd suggest measuring the
> real table size with pg_relpages function (from contrib/pgstattuple) or
> from pg_class.relpages column (after ANALYZE).
>
> > We are running on PostgreSQL 8.1.4 and are planning to move to 8.3 when
> > it becomes available. Are there space utilization/performance
> > improvements in WAL logging in the upcoming release?
>
> One big change in 8.3 is that COPY on a table that's been created or
> truncated in the same transaction doesn't need to write WAL at all, if
> WAL archiving isn't enabled.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL log performance/efficiency question
2007-05-17 14:04 WAL log performance/efficiency question Keaton Adams <kadams@mxlogic.com>
2007-05-17 14:23 ` Re: WAL log performance/efficiency question Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-17 15:34 ` Re: WAL log performance/efficiency question Keaton Adams <kadams@mxlogic.com>
@ 2007-05-17 15:52 ` Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 6+ messages in thread
From: Heikki Linnakangas @ 2007-05-17 15:52 UTC (permalink / raw)
To: kadams@mxlogic.com; +Cc: pgsql-performance
Keaton Adams wrote:
> So for every data page there is a 20 byte header, for every row there is
> a 4 byte identifier (offset into the page), AND there is also a 28 byte
> fixed-size header (27 + optional null bitmap)?? (I did find the section
> in the 8.1 manual that give the physical page layout.) The other RDBMS
> platforms I have worked with have a header in the 28 byte range and a
> row pointer of 4 bytes, and that's it. I find it a bit surprising that
> PostgreSQL would need another 28 bytes per row to track its contents.
Yes, it is more than many other DBMSs. It contains mostly MVCC-related
visibility information that other DBMSs store at page level etc, or
don't have MVCC at all.
As Tom mentioned, that's going to be a bit better in 8.3. We reduced the
header size from 27 + null bitmap to 23 + null bitmap, which makes a big
difference especially on 64-bit architectures, where the header used to
be padded up to 32 bytes, and now it's only 24 bytes.
For character fields, including CHAR(100) like you have, we also store a
4 bytes length header per field. That's been reduced to 1 byte for
string shorter than 127 bytes in 8.3.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL log performance/efficiency question
2007-05-17 14:04 WAL log performance/efficiency question Keaton Adams <kadams@mxlogic.com>
@ 2007-05-17 16:01 ` Keaton Adams <kadams@mxlogic.com>
1 sibling, 0 replies; 6+ messages in thread
From: Keaton Adams @ 2007-05-17 16:01 UTC (permalink / raw)
To: pgsql-performance
OK, I understand.
So one clarifying question on WAL contents:
On an insert of a 100 byte row that is logged, what goes into the WAL
log? Is it 100 bytes, 132 bytes (row + overhead), or other? Does just
the row contents get logged, or the contents plus all of the relative
overhead? I understand that after a checkpoint the first insert
requires the entire 8K page to be written to the WAL, so do subsequent
inserts into WAL follow the same storage pattern as the layout on the
data page, or is the byte count less?
-K
>
> 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.
>
On Thu, 2007-05-17 at 08:04 -0600, Keaton Adams wrote:
> I sent this to pgsql-admin but didn't receive a response. Would this
> be a WAL log performance/efficiency issue?
>
> Thanks,
>
> Keaton
>
>
> 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?
>
> We are running on PostgreSQL 8.1.4 and are planning to move to 8.3
> when it becomes available. Are there space utilization/performance
> improvements in WAL logging in the upcoming release?
>
> Thanks,
>
> Keaton
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-05-17 16:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-17 14:04 WAL log performance/efficiency question Keaton Adams <kadams@mxlogic.com>
2007-05-17 14:23 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-17 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-17 15:34 ` Keaton Adams <kadams@mxlogic.com>
2007-05-17 15:52 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-17 16:01 ` Keaton Adams <kadams@mxlogic.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