PostgreSQL物理”備庫”的哪些操作或配置，可能影響”主庫”的效能、垃圾回收、IO波動

德哥發表於2017-04-10

SQL

背景

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提供了幾種方法，但是這些方法要麼會傷害主庫，要麼會傷害備庫。都有一定代價。