checkpoint_flush_after引數,官方解釋是:
>Whenever more than checkpoint_flush_after bytes have been written while performing a checkpoint, attempt to force the OS to issue these writes to the underlying storage. Doing so will limit the amount of dirty data in the kernel's page cache, reducing the likelihood of stalls when an fsync is issued at the end of the checkpoint, or when the OS writes data back in larger batches in the background. Often that will result in greatly reduced transaction latency, but there also are some cases, especially with workloads that are bigger than shared_buffers, but smaller than the OS's page cache, where performance might degrade. This setting may have no effect on some platforms. The valid range is between 0, which disables forced writeback, and 2MB. The default is 256kB on Linux, 0 elsewhere. (If BLCKSZ is not 8kB, the default and maximum values scale proportionally to it.) This parameter can only be set in the postgresql.conf file or on the server command line.

在CentOS 7.x下使用sysbench測試,嘗試0/256kB/2MB,只有可以忽略不計的細微區別.
```
-------------------------------------
checkpoint_flush_after = 2MB
-------------------------------------
SQL statistics:
    queries performed:
        read:                            107367
        write:                           111268
        other:                           16840
        total:                           235475
    transactions:                        8419   (84.16 per sec.)
    queries:                             235475 (2353.96 per sec.)
    ignored errors:                      43     (0.43 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      84.1616
    time elapsed:                        100.0337s
    total number of events:              8419

Latency (ms):
         min:                                    2.56
         avg:                                   23.76
         max:                                  311.66
         95th percentile:                       62.19
         sum:                               200002.13

Threads fairness:
    events (avg/stddev):           4209.5000/48.50
    execution time (avg/stddev):   100.0011/0.00

-------------------------------------
checkpoint_flush_after = 256kB
-------------------------------------
SQL statistics:
    queries performed:
        read:                            110021
        write:                           114168
        other:                           17072
        total:                           241261
    transactions:                        8535   (85.31 per sec.)
    queries:                             241261 (2411.50 per sec.)
    ignored errors:                      29     (0.29 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      85.3106
    time elapsed:                        100.0462s
    total number of events:              8535

Latency (ms):
         min:                                    2.62
         avg:                                   23.43
         max:                                  260.37
         95th percentile:                       61.08
         sum:                               200017.57

Threads fairness:
    events (avg/stddev):           4267.5000/37.50
    execution time (avg/stddev):   100.0088/0.02

-------------------------------------
checkpoint_flush_after = 0
-------------------------------------
SQL statistics:
    queries performed:
        read:                            111431
        write:                           115279
        other:                           17380
        total:                           244090
    transactions:                        8689   (86.86 per sec.)
    queries:                             244090 (2440.19 per sec.)
    ignored errors:                      42     (0.42 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      86.8646
    time elapsed:                        100.0293s
    total number of events:              8689

Latency (ms):
         min:                                    2.60
         avg:                                   23.02
         max:                                  213.39
         95th percentile:                       59.99
         sum:                               199980.96

Threads fairness:
    events (avg/stddev):           4344.5000/16.50
    execution time (avg/stddev):   99.9905/0.00

```

**參考資料**
[PG doc](https://www.postgresql.org/docs/12/runtime-config-wal.html)

PostgreSQL DBA(66) - 配置引數(checkpoint_flush_after)

相關文章