Re: wal_buffers, redux

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