Re: pg_test_fsync performance

On Mon, Feb 13, 2012 at 7:42 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> I have heard complaints that /contrib/pg_test_fsync is too slow.  I
> thought it was impossible to speed up pg_test_fsync without reducing its
> accuracy.
>
> However, now that I some consumer-grade SATA 2 drives, I noticed that
> the slowness is really in the open_sync test:
>
>        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          76.421 ops/sec
>                 2 *  8kB open_sync writes         38.689 ops/sec
>                 4 *  4kB open_sync writes         19.140 ops/sec
>                 8 *  2kB open_sync writes          4.938 ops/sec
>                16 *  1kB open_sync writes          2.480 ops/sec
>
> These last few lines can take very long, so I developed the attached
> patch that scales down the number of tests.  This makes it more
> reasonable to run pg_test_fsync.
>
> I would like to apply this for PG 9.2.

On my MacOS X, it's fsync_writethrough that's insanely slow:

[rhaas pg_test_fsync]$ ./pg_test_fsync
2000 operations per test
Direct I/O is not supported on this platform.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 3523.267 ops/sec
fdatasync 3360.023 ops/sec
fsync 2410.048 ops/sec
fsync_writethrough 12.576 ops/sec
open_sync 3649.475 ops/sec

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 1885.284 ops/sec
fdatasync 2544.652 ops/sec
fsync 3241.218 ops/sec
fsync_writethrough ^C

Instead of or in addition to a fixed number operations per test, maybe
we should cut off each test after a certain amount of wall-clock time,
like 15 seconds. It's kind of insane to run one of these tests for 3
minutes.

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