Re: Excessive (and slow) fsync() within single transaction

Stephen Tyler wrote:
> The times are apparently in milliseconds. But that doesn't make any
> sense for the 7200rpm SATA HDD, given it has an 8.3ms rotation time.
> And the numbers for the SSD are basically identical to the HDD, apart
> from the bare write timing.
> Do I conclude that the sync timings are all faked by the OS?
PostgreSQL on OS X uses a special fsync method by default named
'fsync_writethrough'. If you do this on your database you should be
able to confirm it's using it:

show wal_sync_method;

This method includes a call to Apple's API to make sure the cache is
properly flushed on the drives. Lots more details about how all this
works at http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm

I'm not sure if the test_fsync tool actually tests this particular
wal_sync_method though, so on OS X you may only be getting reports that
don't include a proper drive cache flush. The server itself does the
right thing. Making test_fsync do exactly the same thing as the real
WAL write in all cases has been a lingering TODO item for a while now, I
submitted a patch to improve the whole thing a while ago but it's not
really robust in all situations yet. Unfortunately I think you're
getting bad data from the test tool, and I'm not sure of a source for a
similar one that includes the right drive flushing behavior for OS X.
(As a general note, there's a similar call on Windows, and I believe it
also suffers from this problem when running test_fsync ; that little
test utility has really only been tested well on UNIX-ish systems).

What you can do in order to test fsync rate as a side-effect of other
work the server is to run pgbench with a custom script. If you grab a
copy of
http://git.postgresql.org/gitweb?p=pgbench-tools.git;a=blob_plain;f=tests/insert.sql;hb=945164aaadf50ea645750fe77c4ffcecafb5c353and
save that as "insert.sql", then do:

createdb pgbench
pgbench -i -s 10 pgbench
pgbench -s 10 -t 1000 -c 1 -f insert.sql

This will tell you how many insert statements/second are committing,
which is basically just a tiny write to the WAL followed by an fsync.
That's basically the fsync rate of the system, as long as you don't get
a checkpoint in the middle slowing it down.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com