Re: wal_buffers, redux

On Mon, Mar 12, 2012 at 4:45 PM, Jeff Janes <jeff.janes@gmail.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@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.654ops/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.072ops/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.091ops/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.597ops/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