908
PostgreSQL物理"備庫"的哪些操作或配置，可能影響"主庫"的性能、垃圾回收、IO波動
標簽
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
最後更新：2017-04-10 20:02:47