postgresql10.x的命令pg_test_fsync运用实例

pg_test_fsync 是测试 wal_sync_method设置哪个值最快，还可以在发生认定的 i/o 问题时提供诊断信息。

pg_test_fsync为 wal_sync_method报告以微秒计的平均文件同步操作时间， 也能被用来提示用于优化commit_delay值的方法。

# lsb_release -a
lsb version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
distributor id: centos
description:    centos release 6.8 (final)
release:    6.8
codename:   final

$ psql
psql (10.3)
type "help" for help.

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 postgresql 10.3 on x86_64-pc-linux-gnu, compiled by gcc (gcc) 4.4.7 20120313 (red hat 4.4.7-18), 64-bit
(1 row)

$ ./pg_test_fsync --help
usage: pg_test_fsync [-f filename] [-s secs-per-test]

$ ./pg_test_fsync -f /tmp/pg_test_fsync.data
5 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                      2587.525 ops/sec     386 usecs/op
        fdatasync                          2570.143 ops/sec     389 usecs/op
        fsync                               701.733 ops/sec    1425 usecs/op
        fsync_writethrough                              n/a
        open_sync                          2667.739 ops/sec     375 usecs/op

compare file sync methods using two 8kb writes:
(in wal_sync_method preference order, except fdatasync is linux's default)
        open_datasync                      1437.557 ops/sec     696 usecs/op
        fdatasync                          2272.541 ops/sec     440 usecs/op
        fsync                               619.062 ops/sec    1615 usecs/op
        fsync_writethrough                              n/a
        open_sync                          1304.595 ops/sec     767 usecs/op

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          2802.908 ops/sec     357 usecs/op
         2 *  8kb open_sync writes         1421.735 ops/sec     703 usecs/op
         4 *  4kb open_sync writes          732.365 ops/sec    1365 usecs/op
         8 *  2kb open_sync writes          374.304 ops/sec    2672 usecs/op
        16 *  1kb open_sync writes          188.895 ops/sec    5294 usecs/op

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                 708.991 ops/sec    1410 usecs/op
        write, close, fsync                 750.575 ops/sec    1332 usecs/op

non-sync'ed 8kb writes:
        write                            242102.347 ops/sec       4 usecs/op

查看 postgresql的wal_sync_method参数

$ vi postgresql.conf
#wal_sync_method = fsync                # the default is the first option
                                        # supported by the operating system:
                                        #   open_datasync
                                        #   fdatasync (default on linux)
                                        #   fsync
                                        #   fsync_writethrough
                                        #   open_sync


postgres=# show wal_sync_method;
 wal_sync_method 
-----------------
 fdatasync
(1 row)