Use pg_test_fsync test which wal_sync_method is fastest in your filesystem

wal_sync_method 提供了多种fsync的方法。各种方法的效率可能存在差异。

#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

PostgreSQL提供了一个util用来测试系统所有支持的fsync() 方法下的性能 , 包括是否支持multi-processes fsync同一个文件。

原文引用 : 

         * Test whether fsync can sync data written on a different descriptor for

         * the same file.  This checks the efficiency of multi-process fsyncs

         * against the same file. Possibly this should be done with writethrough

         * on platforms which support it.

pg_test_fsync is intended to give you a reasonable idea of what the fastest wal_sync_method is on your specific system, as well as supplying diagnostic information in the event of an identified I/O problem. However, differences shown by pg_test_fsync might not make any difference in real database throughput, especially since many database servers are not speed-limited by their transaction logs.

源代码在$SRC/contrib/pg_test_fsync

例一

在一个sas 10k raid1的磁盘组 , ext3文件系统下的测试 .

postgres@digoal-> pg_test_fsync 

2000 operations 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                                 n/a

        fdatasync                         159.689 ops/sec

        fsync                             161.249 ops/sec

        fsync_writethrough                            n/a

        open_sync                         162.821 ops/sec

Compare file sync methods using two 8kB writes:

(in wal_sync_method preference order, except fdatasync

is Linux's default)

        open_datasync                                 n/a

        fdatasync                         160.920 ops/sec

        fsync                             161.076 ops/sec

        fsync_writethrough                            n/a

        open_sync                          81.102 ops/sec

Compare open_sync with different write sizes:

(This is designed to compare the cost of writing 16kB

in different write open_sync sizes.)

        16kB open_sync write              163.539 ops/sec

         8kB open_sync writes              81.464 ops/sec

         4kB open_sync writes              40.652 ops/sec

         2kB open_sync writes              19.960 ops/sec

         1kB open_sync writes              10.130 ops/sec

Test if fsync on non-write file descriptor is honored:

(If the times are similar, fsync() can sync data written

on a different descriptor.)  以下数值类似的话，则表示支持multi-processes fsync同一个文件

        write, fsync, close               157.128 ops/sec

        write, close, fsync               152.587 ops/sec

Non-Sync'ed 8kB writes:

        write                           170386.778 ops/sec

n/a表示此系统不支持此sync method, 如果设置为不支持的WAL_SYNC_METHOD，启动数据库时将报错，

如下 : 

postgres@digoal-> FATAL:  invalid value for parameter "wal_sync_method": "fsync_writethrough"

HINT:  Available values: fsync, fdatasync, open_sync.

例二:

在RAMDISK上测试 fsync的性能 ,

postgres@digoal-> cd /dev/shm

postgres@digoal-> pg_test_fsync 

2000 operations 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                                 n/a

        fdatasync                       412626.367 ops/sec

        fsync                           409081.612 ops/sec

        fsync_writethrough                            n/a

        open_sync                                    n/a*

* This file system and its mount options do not support direct

I/O, e.g. ext4 in journaled mode.

Compare file sync methods using two 8kB writes:

(in wal_sync_method preference order, except fdatasync

is Linux's default)

        open_datasync                                 n/a

        fdatasync                       232369.002 ops/sec

        fsync                           234000.234 ops/sec

        fsync_writethrough                            n/a

        open_sync                                    n/a*

* This file system and its mount options do not support direct

I/O, e.g. ext4 in journaled mode.

Compare open_sync with different write sizes:

(This is designed to compare the cost of writing 16kB

in different write open_sync sizes.)

        16kB open_sync write                         n/a*

         8kB open_sync writes                        n/a*

         4kB open_sync writes                        n/a*

         2kB open_sync writes                        n/a*

         1kB open_sync writes                        n/a*

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             155557.284 ops/sec

        write, close, fsync             161864.681 ops/sec

Non-Sync'ed 8kB writes:

        write                           282445.982 ops/sec