Re: Final(?) proposal for wal_sync_method changes - Mailing list pgsql-hackers

Since any Windows refactoring has been postponed for now (I'll get back
to performance checks on that platform later), during my testing time
this week instead I did a round of pre-release review of the change Tom
has now committed.  All looks good to me, including the docs changes.

I confirmed that:

-Ubuntu system with an older kernel still has the same wal_sync_method
(fdatasync) and performance after pulling the update
-RHEL6 system changes as planned from using open_datasync to fdatasync
once I updated to a HEAD after the commit

On the RHEL6 system, I also checked the commit rate using pgbench with
the attached INSERT only script, rather than relying on test_fsync.
This is 7200 RPM drive, so theoretical max of 120 commits/second, on
ext4; this is the same test setup I described in more detail back in
http://archives.postgresql.org/message-id/4CE2EBF8.4040602@2ndquadrant.com

$ psql -c "show wal_sync_method"
 wal_sync_method
-----------------
 fdatasync
(1 row)

$ pgbench -i -s 10 pgbench

[gsmith@meddle ~]$ pgbench -s 10 -f insert.sql -c 1 -T 60 pgbench
starting vacuum...end.
transaction type: Custom query
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 6733
tps = 112.208795 (including connections establishing)
tps = 112.216904 (excluding connections establishing)

And then manually switched over to test performance of the troublesome
old default:

[gsmith@meddle ~]$ psql -c "show wal_sync_method"
 wal_sync_method
-----------------
 open_datasync

[gsmith@meddle ~]$ pgbench -s 10 -f insert.sql -c 1 -T 60 pgbench
starting vacuum...end.
transaction type: Custom query
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 6672
tps = 111.185802 (including connections establishing)
tps = 111.195089 (excluding connections establishing)

This is interesting, because test_fsync consistently reported a rate of
about half this when using open_datasync instead of the equal
performance I'm getting from the database.  I'll see if I can reproduce
that further, but it's no reason to be concerned about the change that's
been made I think.  Just more evidence that test_fsync has quirks left
to be sorted out.  But that's not backbranch material, it should be part
of 9.1 only refactoring, already in progress via the patch Josh
submitted.  There's a bit of time left to get that done.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);