Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 486759FBF26 for ; Thu, 17 May 2007 11:04:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 77714-01 for ; Thu, 17 May 2007 11:04:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from p02c11o145.mxlogic.net (p02c11o145.mxlogic.net [208.65.144.78]) by postgresql.org (Postfix) with ESMTP id 7F0C49FBE91 for ; Thu, 17 May 2007 11:04:39 -0300 (ADT) Received: from unknown [208.65.151.10] by p02c11o145.mxlogic.net (mxl_mta-5.0.0-2) with SMTP id 6f06c464.2148125616.372941.00-023.p02c11o145.mxlogic.net (envelope-from ); Thu, 17 May 2007 08:04:38 -0600 (MDT) Received: from 10.30.1.68 ([10.30.1.68]) by mxlhq-exch01.corp.mxlogic.com ([10.30.0.246]) via Exchange Front-End Server webmail.mxlogic.com ([10.30.0.241]) with Microsoft Exchange Server HTTP-DAV ; Thu, 17 May 2007 14:04:30 +0000 Received: from MXLRMT-208 by webmail.mxlogic.com; 17 May 2007 08:04:31 -0600 Subject: WAL log performance/efficiency question From: Keaton Adams Reply-To: kadams@mxlogic.com To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="=-pmoRJzDSZktDKyC1YrUb" Organization: MX Logic, Inc. Date: Thu, 17 May 2007 08:04:30 -0600 Message-Id: <1179410671.26604.3.camel@MXLRMT-208.corp.mxlogic.com> Mime-Version: 1.0 X-Mailer: Evolution 2.10.1 X-Spam: [F=0.0100000000; S=0.010(2007050201); SS=0.500] X-MAIL-FROM: X-SOURCE-IP: [208.65.151.10] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/298 X-Sequence-Number: 24688 --=-pmoRJzDSZktDKyC1YrUb Content-Type: text/plain Content-Transfer-Encoding: 7bit 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 --=-pmoRJzDSZktDKyC1YrUb Content-Type: text/html; charset=utf-8 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

--=-pmoRJzDSZktDKyC1YrUb--