标签
PostgreSQL , 物理复制 , 垃圾回收 , vacuum_defer_cleanup_age , hot_standby_feedback , max_standby_archive_delay , max_standby_streaming_delay
背景
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秒以前的数据。
优化
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。
参考
https://www.postgresql.org/docs/9.6/static/runtime-config-replication.html