Skip site navigation (1) Skip section navigation (2)

Re: wal_buffers, redux

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: wal_buffers, redux
Date: 2012-03-13 12:43:13
Message-ID: CA+TgmoZABESSF+WKnJcfHToYjr8XAsDf0B2V0LDoDbiiBXpg+w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Mar 12, 2012 at 4:45 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> Rerunning all 4 benchmarks (both 16MB and 32MB wal_buffers on both
> machines) with fsync=off (as well as synchronous_commit=off still)
> might help clarify things.
> If it increases the TPS of Nate(at)16MB, but doesn't change the other 3
> situations much, then that suggests the IO system is driving it.
> Basically moving up to 32MB is partially innoculating against slow
> fsyncs upon log switch on that machine.

I had the idea of running pg_test_fsync on each machine.  Here, for
what it's worth, are the numbers.

Nate Boley's box:

2 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                     116.604 ops/sec
        fdatasync                         108.654 ops/sec
        fsync                              20.234 ops/sec
        fsync_writethrough                            n/a
        open_sync                          17.979 ops/sec

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                      39.833 ops/sec
        fdatasync                          58.072 ops/sec
        fsync                              19.756 ops/sec
        fsync_writethrough                            n/a
        open_sync                           8.425 ops/sec

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
         1 * 16kB open_sync write          17.408 ops/sec
         2 *  8kB open_sync writes          9.376 ops/sec
         4 *  4kB open_sync writes          4.912 ops/sec
         8 *  2kB open_sync writes          2.477 ops/sec
        16 *  1kB open_sync writes          1.244 ops/sec

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        write, fsync, close                19.818 ops/sec
        write, close, fsync                19.086 ops/sec

Non-Sync'ed 8kB writes:
        write                           101176.089 ops/sec

IBM POWER7 server:

2 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                     167.009 ops/sec
        fdatasync                         167.091 ops/sec
        fsync                              47.321 ops/sec
        fsync_writethrough                            n/a
        open_sync                          53.735 ops/sec

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                      68.891 ops/sec
        fdatasync                         164.597 ops/sec
        fsync                              50.334 ops/sec
        fsync_writethrough                            n/a
        open_sync                          25.240 ops/sec

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
         1 * 16kB open_sync write          53.305 ops/sec
         2 *  8kB open_sync writes         23.863 ops/sec
         4 *  4kB open_sync writes         12.500 ops/sec
         8 *  2kB open_sync writes          4.734 ops/sec
        16 *  1kB open_sync writes          3.063 ops/sec

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        write, fsync, close                48.730 ops/sec
        write, close, fsync                48.463 ops/sec

Non-Sync'ed 8kB writes:
        write                           186465.321 ops/sec

I can't rerun any more serious benchmarks on Nate Boley's box right
now due to other activity on the box.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-03-13 13:48:11
Subject: Re: patch for parallel pg_dump
Previous:From: Andres FreundDate: 2012-03-13 11:22:26
Subject: Re: Command Triggers, patch v11

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group