在PostgreSQL数据库中 ,数据更改是先写入缓冲池中(shared buffers)，缓冲池里面的这些数据更改，在事务提交时，是无需同步写入到磁盘的，因为在事务提交时，会先写入WAL日志，有了WAL日志，就可以在异常情况下将数据恢复，保障数据安全，因此数据本身是否在提交时写入磁盘就没那么重要了。数据库是只是在需要的时候，例如脏页较多时、或一定时间间隔后，才将数据写回磁盘 。
PostgreSQL的bgwriter、checkpointer和backend都可能把脏数据回写到存储上。
checkpointer ，是以特定的时间间隔刷新所有脏页，并创建一个用于数据库恢复用的检查点 。
bgwriter ，是在检查点之间刷新一些脏页面，以便始终有足够多的干净页面可以使用 。
backend ，是在申请 shared buffer时找到了脏块，要先把脏块写回磁盘，再清掉脏块，往shared buffer写入新的数据，这操作开销是很高的。
bgwriter的作用
提高了缓存的替换速度，提高了数据查询性能。因为数据库在进行查询处理时，若发现要读取的数据不在缓冲区要先从磁盘中读入该页，这时如果缓冲区已满，就需要先选择一些缓冲区中的页面替换出去。如果要被替换的页被修改了，则必须先将这个页面读出到磁盘才能替换，这样数据库的查询就会被阻塞在这里。通过bgwriter定期的写出缓冲区的部分页面，就可以为缓冲区腾出空间。就可以防止这一情况。
同时，因为bgwriter预先写出了一些脏页面，可以减少checkpoint时要进行的io操作，使系统的IO负载趋于平稳。
但如果bgwriter设置得不合理的话，会导致更新的数据经常被刷到磁盘，导致不必要的IO负载。
bgwriter配置参数
| 名称 | 含义 | 默认值 |
|---|---|---|
| bgwriter_delay | 每次执行bgwriter的间隔时间 | 200ms |
| bgwriter_lru_maxpages | 每次bgwriter任务写buffer的最大page数，一旦达到这个数量，bgwriter就结束任务开始休息，也就是说bgwriter休眠200毫秒，然后写入几十毫秒就又开始了休息 | 100 |
| bgwriter_lru_multiplier | 用于评估下一次写任务的数量，其依据是这段时间内新申请的buffer数量的倍数，如果这个参数乘以新增加buffer分配的数量后小于bgwriter_lru_maxpages，那么有可能下一个写任务的数量会小于预期的写入量。将这个参数调大，会增加bgwriter回写脏块的数量，不过会增加写IO压力，将这个参数调小，可以降低写IO压力，不过可能导致backend写脏块的比例增加。不同负载的系统中，这个参数的值应该是需要做调整的，而不是只是用缺省值2。如果你发现你的系统的写IO压力还不大，但是 bgwriter写比例偏低，backend写比例偏高，那么尝试加大这个参数还是有效的。 | 2 |
| bgwriter_flush_after | 当局部sync请求达到的最大数，会触发处理 | 512KB |
bgwriter监控
pg_stat_bgwriter视图提供了一组共享缓冲区写入方面性能数据
postgres=# select * from pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed | 53418
checkpoints_req | 34
checkpoint_write_time | 2661874250
checkpoint_sync_time | 384635
buffers_checkpoint | 607926853
buffers_clean | 31275113
maxwritten_clean | 287343
buffers_backend | 1489980490
buffers_backend_fsync | 0
buffers_alloc | 8503586752
stats_reset | 2022-01-18 09:11:16.021864+08
字段说明
| 列 | 类型 | 描述 |
|---|---|---|
checkpoints_timed | bigint | 计划检查点的发生次数，这种检查点是checkpoint_timeout参数规定的超时达到后系统启动的checkpoint； |
checkpoints_req | bigint | 非计划检查点的次数，包含手动的检查点、xlog检查点(比如WAL已经超出了max_wal_size或者checkpoint_segments触发的ckpt) |
checkpoint_write_time | double precision | 检查点写入的总时长，以毫秒为单位。 |
checkpoint_sync_time | double precision | 检查点同步文件的总时长，以毫秒为单位。 |
buffers_checkpoint | bigint | 检查点清理的脏块 |
buffers_clean | bigint | bgwriter清理的脏块数量 |
maxwritten_clean | bigint | bgwriter清理脏块的时候达到bgwriter_lru_maxpages后终止写入批处理的次数，为了防止一次批量写入太大影响数据块IO性能，bgwriter每次都有写入的限制。不过这个参数的缺省值100太小，对于负载较高的数据库，需要加大； |
buffers_backend | bigint | backend清理的脏块数量 |
buffers_backend_fsync | bigint | backend被迫自己调用fsync来同步数据的计数，如果这个计数器不为零，说明当时的fsync队列已经满了，存储子系统肯定出现了性能问题； |
buffers_alloc | bigint | buffer分配的次数 |
stats_reset | timestamp with time zone | 上一次RESET这些统计值的时间 |
重置pg_stat_bgwriter数据
pg_stat_reset_shared('bgwriter')postgresql 提供了4个重置功能，看名字都很容易理解了。
pg_stat_reset()
把用于当前数据库的所有统计计数器重置为零（默认要求超级用户权限，但这个函数的 EXECUTE 可以被授予给其他人）。
pg_stat_reset_shared(text)
把某些集簇范围的统计计数器重置为零，具体哪些取决于参数（默认要求超级用户权限，但这个函数的 EXECUTE 可以被授予给其他人）。
调用pg_stat_reset_shared('bgwriter')把pg_stat_bgwriter 视图中显示的所有计数器清零。
调用pg_stat_reset_shared('archiver') 将会把pg_stat_archiver视图中展示的所有计数器清零。
pg_stat_reset_single_table_counters(oid)
把当前数据库中用于单个表或索引的统计数据重置为零（默认要求超级用户权限，但这个函数的 EXECUTE 可以被授予给其他人）
pg_stat_reset_single_function_counters(oid)
把当前数据库中用于单个函数的统计信息重置为零（默认要求超级用户权限，但这个函数的 EXECUTE 可以被授予给其他人）
其它相关参数
checkpoint
| 参数名 | 说明 | 默认值 |
|---|---|---|
| checkpoint_timeout | 系统自动执行checkpoint之间的最大时间间隔 | 5分钟 |
| checkpoint_completion_target | 表示checkpoint的完成时间占两次checkpoint时间间隔的比例，系统默认值是0.5,也就是说每个checkpoint需要在checkpoints间隔时间的50%内完成 | 0.5 |
| checkpoint_flush_after | 在执行检查点时，只要写入的数据量超过此值，就会强制刷盘。这样做将限制os page cache的脏数据量，从而减少在检查点最后执行fsync或操作系统在后台大批量写回数据时出现io性能的问题。 | 256KB |
| max_wal_size | WAL日志超过max_wal_size就触发检查点开始工作。这是个软限制，PostgreSQL尽量不超过此值，但会在比如高负载和wal_keep_segments值较大的时候超过。 | 1GB |
| min_wal_size | WAL磁盘使用率低于这个设置，旧的WAL文件就会被回收，而不是删除，确保预留足够的WAL空间处理WAL使用中的峰值 | 80MB |
shared_buffers
数据库的共享内存缓冲区量，通常设置成内存的25%，shared_buffers更大的设置通常要求对max_wal_size也
做相应增加。
测试验证
测试过程
生成测试数据
pgbench -i -s 100 temp调整参数
alter system set xxx=xxx;
select pg_reload_conf();
对压测表vacuum
vacuum analyze pgbench_accounts;
vacuum analyze pgbench_branches;
vacuum analyze pgbench_history;
vacuum analyze pgbench_tellers;
checkpoint;
select pg_stat_reset_shared('bgwriter');
执行压测，并统计tps
pgbench -c 5 -j 4 -n -T 310 temp查看checkpoint、bgwriter、backend刷脏的百分比
select round(buffers_checkpoint*100/buffer_total::numeric)||'%' checkpoint_percent
,round(buffers_clean*100/buffer_total::numeric)||'%' bgwriter_percent
,round(buffers_backend*100/buffer_total::numeric)||'%' backend_percent
from pg_stat_bgwriter a
,(select buffers_checkpoint+ buffers_clean+buffers_backend as buffer_total from pg_stat_bgwriter) b;
测试结果
默认配置如下
bgwriter_delay:200
bgwriter_flush_after:64
bgwriter_lru_maxpages:100
bgwriter_lru_multiplier:2
checkpoint_completion_target:0.5
checkpoint_flush_after:32
checkpoint_timeout:60
max_wal_size:4096
min_wal_size:80
shared_buffers:512MB
重复2-5步骤，共进行5项测试结果如下：
| 测试项 | 参数调整 | TPS | ckpt刷脏占比 | bgwriter刷脏占比 | backend刷脏占比 |
|---|---|---|---|---|---|
| 1 | 默认配置 | 1983 | 20% | 30% | 50% |
| 2 | bgwriter_delay=10;bgwriter_lru_maxpages=1000; | 1817 | 22% | 76% | 2% |
| 3 | bgwriter_delay=10;bgwriter_lru_maxpages=1000;checkpoint_timeout='5min'; | 1870 | 1% | 98% | 1% |
| 4 | bgwriter_delay=2000;bgwriter_lru_maxpages=100;checkpoint_timeout='5min'; | 1954 | 4% | 4% | 92% |
| 5 | bgwriter_delay=2000;bgwriter_lru_maxpages=100;checkpoint_timeout='5min';shared_buffers='4GB'; | 2374 | 94% | 0% | 6% |
测试项1作为对比参考
测试项2和3调整了bgwriter刷脏频率较高，checkpoint刷脏其实和bgwriter一样，这样backend刷脏占比就会较低，所以测试结果的tps差异不大。
测试项4调整bgwriter刷脏频率较低，并且checkpoint为5分钟，在测试时间(310秒)内checkpoint占比自然是会低，导致backend刷脏高，因为使用pgbench压测的是tpch性能，磁盘io没有到瓶颈，所以tps会较高。
测试项5调整了shared_buffers='4GB'，压测的表只是1GB多，因为调整了bgwriter频率较低，所以压测中都在shared_buffers中进行，直到5分钟了进行一次checkpoint导致占比高，但整体的tps提升上来了
上面测试，其中参数调整只作为刷脏占比的验证，不作最终配置优化的调整，实际优化中，需要根据实际的数据反映来进行调整。
参考文档
http://mysql.taobao.org/monthly/2015/09/06/
https://blog.csdn.net/qq_35462323/article/details/116268934
https://mp.weixin.qq.com/s/c6rjvlokbCe-8agc5_QAiQ
