PostgreSQL物理备库的哪些操作或配置，可能影响主库的性能、垃圾回收、IO波动
背景
PostgreSQL 物理备库的哪些配置，或者哪些操作，可能影响到主库呢？
首先，简单介绍一下PostgreSQL的物理备库，物理备库就是基于PostgreSQL WAL流式复制，实时恢复的备库。物理备库在物理层面与主库完全一致，每一个数据块都一样。物理备库允许在实时恢复的同时，对外提供只读的功能。
问题来了，只读操作可能和恢复会发生冲突，比如用户正在备库读某个数据块的数据，与此同时，实时恢复进程读取到WAL的记录，发现需要修改这个数据块的数据。此时恢复就与只读发生了冲突。
为了避免冲突，数据库有哪些手段呢？
1. 主库配置
1.1 vacuum_defer_cleanup_age
设置主库垃圾回收的延迟，例如配置为1000，表示垃圾版本将延迟1000个事务再被回收。
2. 备库配置
2.1 hot_standby_feedback
如果设置为ON，备库在执行QUERY时会通知主库，哪些版本需要被保留。
2.2 max_standby_archive_delay， max_standby_streaming_delay
表示当备库的QUERY与恢复进程发生冲突时，恢复进程最长的等待时间，当恢复进程从被冲突堵塞开始等待时间超过以上设置时，会主动KILL与之发生冲突的QUERY，然后开始恢复，直到catch up，才允许QUERY与恢复进程再次发生冲突。
问题分析
以上配置，要么会伤害主库，要么会伤害备库。都是有一定代价的。
1. vacuum_defer_cleanup_age > 0
代价1，主库膨胀，因为垃圾版本要延迟若干个事务后才能被回收。
代价2，重复扫描垃圾版本，重复耗费垃圾回收进程的CPU资源。（n_dead_tup会一直处于超过垃圾回收阈值的状态，从而autovacuum 不断唤醒worker进行回收动作）。
当主库的 autovacuum_naptime=很小的值，同时autovacuum_vacuum_scale_factor=很小的值时，尤为明显。
代价3，如果期间发生大量垃圾，垃圾版本可能会在事务到达并解禁后，爆炸性的被回收，产生大量的WAL日志，从而造成WAL的写IO尖刺。
2. hot_standby_feedback=on
如果备库出现了LONG QUERY，或者Repeatable Read的长事务，并且主库对备库还需要或正查询的数据执行了更新并产生了垃圾时，主库会保留这部分垃圾版本（与vacuum_defer_cleanup_age效果类似）。
代价，与vacuum_defer_cleanup_age > 0 一样。
3. max_standby_archive_delay， max_standby_streaming_delay
代价，如果备库的QUERY与APPLY（恢复进程）冲突，那么备库的apply会出现延迟，也许从备库读到的是N秒以前的数据。
影响主库的问题复现
前面分析了，当主库设置了vacuum_defer_cleanup_age > 0或者备库设置了hot_standby_feedback=on同时有LONG QUERY时，都可能造成主库的3个问题。
这个问题很容易复现。
复现方法1 备库hot_standby_feedback=on
开启主库的自动垃圾回收，同时设置为很小的唤醒时间，以及很小的垃圾回收阈值。
这样设置是为了防止膨胀，但是也使得本文提到的问题更加的明显。
postgres=# show autovacuum_naptime ;
-[ RECORD 1 ]------+---
autovacuum_naptime | 1s
postgres=# show autovacuum_vacuum_scale_factor ;
-[ RECORD 1 ]------------------+-------
autovacuum_vacuum_scale_factor | 0.0002
1. 创建测试表
postgres=# create table test(id int , info text, crt_time timestamp);
2. 插入1000万测试数据
postgres=# insert into test select 1,md5(random()::text),now() from generate_series(1,10000000);
3. 在hot standby上开启一个repeatable read事务，执行一笔QUERY，查询test的全表
postgres=# begin transaction isolation level repeatable read;
BEGIN
postgres=# select count(*) from test ;
count
----------
10000000
(1 row)
4. 在主库更新test全表
postgres=# update test set info=info;
5. 查询test表当前的统计信息，有1000万条dead tuple
postgres=# select * from pg_stat_all_tables where relname ='test';
-[ RECORD 1 ]-------+------------------------------
relid | 17621
schemaname | public
relname | test
seq_scan | 1
seq_tup_read | 10000000
idx_scan |
idx_tup_fetch |
n_tup_ins | 10000000
n_tup_upd | 10000000
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 10000000
n_dead_tup | 10000000
n_mod_since_analyze | 0
last_vacuum | 2017-04-10 17:35:02.670226+08
last_autovacuum | 2017-04-10 17:42:03.81277+08
last_analyze |
last_autoanalyze | 2017-04-10 17:34:22.947725+08
vacuum_count | 1
autovacuum_count | 211
analyze_count | 0
autoanalyze_count | 2
6. 造成的影响，读IO巨大（扫描test表，试图回收垃圾，但是回收未遂），以及autovacuum worker的CPU开销很大。
autovacuum worker process 不停被唤醒，扫描垃圾数据，但是不能对其进行回收，所以n_dead_tup一直不会下降，循环往复，autovacuum worker不断被唤醒。
进程CPU 100%
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
45213 dege.zzz 20 0 8570m 1.2g 1.2g R 100.0 0.2 0:01.18 postgres: autovacuum worker process postgres
问题处理
1. 备库设置参数hot_standby_feedback=off
hot_standby_feedback = off
reload
pg_ctl reload -D .
server signaled
问题马上解除，垃圾被回收掉了。
postgres=# select * from pg_stat_all_tables where relname ='test';
-[ RECORD 1 ]-------+------------------------------
relid | 17621
schemaname | public
relname | test
seq_scan | 1
seq_tup_read | 10000000
idx_scan |
idx_tup_fetch |
n_tup_ins | 10000000
n_tup_upd | 10000000
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 10000000
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum | 2017-04-10 17:35:02.670226+08
last_autovacuum | 2017-04-10 17:42:52.455949+08
last_analyze |
last_autoanalyze | 2017-04-10 17:34:22.947725+08
vacuum_count | 1
autovacuum_count | 233
analyze_count | 0
autoanalyze_count | 2
autovacuum worker不会再被唤醒，所以主库的CPU马上下降。
同时垃圾回收会带来一次很大的WAL写IO。造成尖刺。
2. max_standby_archive_delay， max_standby_streaming_delay起作用，备库的事务在apply冲突超时后，被强制kill
postgres=# show hot_standby_feedback;
hot_standby_feedback
----------------------
off
(1 row)
postgres=# select count(*) from test ;
count
----------
10000000
(1 row)
postgres=# select * from test limit 10;
FATAL: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
复现方法2 主库vacuum_defer_cleanup_age > 0
略，复现方法一样。
小结与优化
为了尽量的避免物理备库的QUERY与apply的冲突，PostgreSQL提供了几种方法，但是这些方法要么会伤害主库，要么会伤害备库。都有一定代价。
1. vacuum_defer_cleanup_age > 0
代价1，主库膨胀，因为垃圾版本要延迟若干个事务后才能被回收。
代价2，重复扫描垃圾版本，重复耗费垃圾回收进程的CPU资源。（n_dead_tup会一直处于超过垃圾回收阈值的状态，从而autovacuum 不断唤醒worker进行回收动作）。
当主库的 autovacuum_naptime=很小的值，同时autovacuum_vacuum_scale_factor=很小的值时，尤为明显。
代价3，如果期间发生大量垃圾，垃圾版本可能会在事务到达并解禁后，爆炸性的被回收，产生大量的WAL日志，从而造成WAL的写IO尖刺。
2. hot_standby_feedback=on
如果备库出现了LONG QUERY，或者Repeatable Read的长事务，并且主库对备库还需要或正查询的数据执行了更新并产生了垃圾时，主库会保留这部分垃圾版本（与vacuum_defer_cleanup_age效果类似）。
代价，与vacuum_defer_cleanup_age > 0 一样。
3. max_standby_archive_delay， max_standby_streaming_delay
代价，如果备库的QUERY与APPLY（恢复进程）冲突，那么备库的apply会出现延迟，也许从备库读到的是N秒以前的数据。
4. 引申，还有一些行为可能会导致垃圾记录(dead tuple)无法被回收，一旦dead tuple无法被回收，那么每次autovacuum worker都是干的无用功。同样会引发和本文类似的问题（autovacuum不停被唤醒）。 例如
4.1 主库有LONG XACT，无法回收该事务ID后产生的垃圾。(9.6可以设置snapshot too old，针对只读事务，如果事务超过配置的时间（或者XID），可能遇到快照过旧的错误，从而避免一直无法垃圾回收的问题。)
4.2 主库有LONG 2PC XACT，无法回收该事务ID后产生的垃圾。(9.6可以设置snapshot too old，针对只读事务，如果事务超过配置的时间（或者XID），可能遇到快照过旧的错误，从而避免一直无法垃圾回收的问题。)
紧急解决这个问题的办法很简单，cancel long query,xact,2pc xact即可。
5. 如何判断是否本文提及的原因导致主库负载升高，XLOG增多，备库recovery startup进程CPU 100%？
优化
1. 不建议设置 vacuum_defer_cleanup_age > 0
2. 如果备库有LONG query，同时需要实时性，可以设置hot_standby_feedback=on，同时建议将主库的autovacuum_naptime，autovacuum_vacuum_scale_factor设置为较大值（例如60秒，0.1），主库的垃圾回收唤醒间隔会长一点，如果突然产生很多垃圾，可能会造成一定的膨胀。
3. 如果备库有LONG QUERY，并且没有很高的实时性要求，建议设置设置hot_standby_feedback=off, 同时设置较大的max_standby_archive_delay， max_standby_streaming_delay。
建议参数如下:
hot_standby_feedback = off
autovacuum_naptime=45s
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 1300000000
vacuum_multixact_freeze_min_age = 50000000
vacuum_multixact_freeze_table_age = 1300000000
autovacuum_freeze_max_age = 1500000000
autovacuum_multixact_freeze_max_age = 1500000000
4. 以此类推，还有哪些配置会导致AUTOVACUUM不停发起呢，但是又不回收垃圾呢？
当存在长事务时，当设置了延迟回收垃圾时，当备库设置了hot_standby_feedback=on，同时备库的QUERY的快照很老(导致主库不能回收垃圾)时(快照很老、或者备库LONG SQL，快照很老可能是备库APPLY延迟导致的)。
如果这样的前提存在，并且主库有些表存在垃圾，并且这些垃圾的年龄不足以被回收（参考以上前提导致某些版本无法回收），那么AUTOVACUUM每次被触发后，扫描到这些BLOCK只是空扫，不会回收。
现象就是AUTOVACUUM进程CPU很高，但是IO很低。
解决办法，把这些前提干掉，同时降低AUTOVACUUM触发频率。
5. 备库配置recovery.conf中如果设置了使用SLOT，也可能导致主库wal堆积，导致主库的dead tuple不清理（对应SLOT备库需要的不清理）。
https://www.postgresql.org/docs/10/static/warm-standby.html#STREAMING-REPLICATION-SLOTS
Replication slots provide an automated way to ensure that the master does not remove WAL segments until they have been received by all standbys, and that the master does not remove rows which could cause a recovery conflict even when the standby is disconnected.
参考
https://www.postgresql.org/docs/9.6/static/runtime-config-replication.html