postgresql 10.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)
