PostgreSQL中如何監控VACUUM的處理過程

149次閱讀

共計 9439 個字符，預計需要花費 24 分鐘才能閱讀完成。

這篇文章主要講解了“PostgreSQL 中如何監控 VACUUM 的處理過程”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“PostgreSQL 中如何監控 VACUUM 的處理過程”吧！

概覽

PG 的 MVCC 要求“過期”的數據不能馬上被物理清除，而是標記為 dead rows，這些 dead rows 后續會通過 vacuuming 過程清理。
vacuuming 通過以下方式讓數據庫保持健康：
1. 標記 dead rows 可用于存儲新數據，這樣可以避免不必要的磁盤浪費以及可以跳過 dead rows 以提升順序掃描的性能；
2. 更新 vm（用于跟蹤過期或已廢棄的數據，反應在 pages 上）。這可以提升 index-only scans 的性能；
3. 避免出現事務 ID 回卷失敗。
PG 提供了 autovacuum 機制，通過周期性的運行 ANALYZE 來收集最近頻繁更新的數據表統計信息。

autovacuum 日志信息

這一小節介紹了配置參數中與 vacuum 相關的參數，包括 log_autovacuum_min_duration、autovacuum_naptime 等。
log_autovacuum_min_duration
如 autovacuum 的執行時間超過了該參數配置的時間（ms 為單位）則在日志中記錄。
設置為較低的閾值：10ms

[pg12@localhost pg121db]$ grep  log_autovacuum  postgresql.conf 
log_autovacuum_min_duration = 10 # -1 disables, 0 logs all actions and

執行 SQL

[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# drop table t_autovacuum_1;
DROP TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# create table t_autovacuum_1(id int,c1 varchar(20),c2 varchar(20));
CREATE TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# insert into t_autovacuum_1 select x, c1 ||x, c2 ||x from generate_series(1,10000) as x;
INSERT 0 10000
[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# drop table t_big_autovacuum_1;
DROP TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# create table t_big_autovacuum_1(id int,c1 varchar(20),c2 varchar(20));
CREATE TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# insert into t_big_autovacuum_1 select x, c1 ||x, c2 ||x from generate_series(1,1000000) as x;
INSERT 0 1000000
[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# explain (analyze,verbose) select * from t_autovacuum_1 a join t_big_autovacuum_1 b on a.id = b.id;
 QUERY PLAN 
------------------------------------------------------------------------------------------------------------------
---------------------------------------
 Merge Join (cost=63985.35..155911.33 rows=6068861 width=240) (actual time=480.678..492.453 rows=10000 loops=1)
 Output: a.id, a.c1, a.c2, b.id, b.c1, b.c2
 Merge Cond: (a.id = b.id)
 -  Sort (cost=301.37..310.03 rows=3465 width=120) (actual time=3.061..3.835 rows=10000 loops=1)
 Output: a.id, a.c1, a.c2
 Sort Key: a.id
 Sort Method: quicksort Memory: 1213kB
 -  Seq Scan on public.t_autovacuum_1 a (cost=0.00..97.65 rows=3465 width=120) (actual time=0.020..1.796
 rows=10000 loops=1)
 Output: a.id, a.c1, a.c2
 -  Materialize (cost=63683.99..65435.46 rows=350295 width=120) (actual time=477.603..481.446 rows=10001 loops
 Output: b.id, b.c1, b.c2
 -  Sort (cost=63683.99..64559.72 rows=350295 width=120) (actual time=477.598..479.173 rows=10001 loops=
 Output: b.id, b.c1, b.c2
 Sort Key: b.id
 Sort Method: external merge Disk: 31144kB
 -  Seq Scan on public.t_big_autovacuum_1 b (cost=0.00..9871.95 rows=350295 width=120) (actual tim
e=0.019..176.859 rows=1000000 loops=1)
 Output: b.id, b.c1, b.c2
 Planning Time: 0.315 ms
 Execution Time: 498.336 ms
(19 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#

日志顯示 autovacuum 的 analyze 操作

2019-12-11 12:31:48.655 CST,,,57525,,5df07134.e0b5,1,,2019-12-11 12:31:48 CST,4/3,3091,LOG,00000, automatic analyze of table  testdb.public.t_autovacuum_1  system usage: CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.10 s ,,,,,,,,, 
2019-12-11 12:31:49.073 CST,,,57525,,5df07134.e0b5,2,,2019-12-11 12:31:48 CST,4/5,3092,LOG,00000, automatic analyze of table  testdb.public.t_big_autovacuum_1  system usage: CPU: user: 0.32 s, system: 0.00 s, elapsed: 0.40 s ,,,,,,,,,

autovacuum_naptime
該參數控制 autovacuum 的調度時間，如有多個數據庫，則每個數據庫每隔一定的時間（autovacuum_naptime/ 數據庫個數）啟動 autovacuum 來進行處理。
從 PG 的源碼可見，通過函數 rebuild_database_list 來構建出現變化后的 DatabaseList，鏈表中的數據庫應出現在 pgstats 中，在 autovacuum_naptime 所設定的時間間隔范圍內均勻分布。
比如 autovacuum_naptime = 60s，有 4 個數據庫 db1- db4，那么每隔 60s/ 4 就會有啟動一個 autovacuum worker 對相應的 DB 進行處理。
可能的一個處理時間序列是：db1- XX（時）:XX（分）:18（秒）,db4- XX:XX:33,db4- XX:XX:48,db4- XX:XX:03
后續如需要對 db1- db4 進行 vacuum，那么 db1- db4 會在下一個 18 秒、33 秒、48 秒和 03 秒觸發 autovacuum。

測試場景：創建 3 個數據庫，db1 – db3，每個數據庫每隔 10s 執行全表更新，通過日志觀察 autovacuum 的行為。

-- session 1
\c db1
drop table t_autovacuum_db1;
create table t_autovacuum_db1(id int,c1 varchar(20),c2 varchar(20));
insert into t_autovacuum_db1 select x, c1 ||x, c2 ||x from generate_series(1,500000) as x;
declare
begin
 for i in 1..20 loop
 update t_autovacuum_db1 set id = i;
 commit;
 perform pg_sleep(10);
 end loop;
-- session 2
\c db2
drop table t_autovacuum_db2;
create table t_autovacuum_db2(id int,c1 varchar(20),c2 varchar(20));
insert into t_autovacuum_db2 select x, c1 ||x, c2 ||x from generate_series(1,500000) as x;
declare
begin
 for i in 1..20 loop
 update t_autovacuum_db2 set id = i;
 commit;
 perform pg_sleep(10);
 end loop;
-- session 3
\c db3
drop table t_autovacuum_db3;
create table t_autovacuum_db3(id int,c1 varchar(20),c2 varchar(20));
insert into t_autovacuum_db3 select x, c1 ||x, c2 ||x from generate_series(1,500000) as x;
declare
begin
 for i in 1..20 loop
 update t_autovacuum_db3 set id = i;
 commit;
 perform pg_sleep(10);
 end loop;
$$;

對應的日志輸出

2019-12-11 15:34:05.298 CST,,,1870,,5df09bec.74e,1,,2019-12-11 15:34:04 CST,6/37,3406,LOG,00000, automatic analyze of table  db2.public.t_autovacuum_db2  system usage: CPU: user: 0.38 s, system: 0.01 s, elapsed: 0.52 s ,,,,,,,,, 
2019-12-11 15:34:20.528 CST,,,1884,,5df09bfb.75c,1,,2019-12-11 15:34:19 CST,6/41,3412,LOG,00000, automatic analyze of table  db1.public.t_autovacuum_db1  system usage: CPU: user: 0.51 s, system: 0.01 s, elapsed: 0.71 s ,,,,,,,,, 
2019-12-11 15:34:35.482 CST,,,1897,,5df09c0a.769,1,,2019-12-11 15:34:34 CST,6/45,3416,LOG,00000, automatic analyze of table  db3.public.t_autovacuum_db3  system usage: CPU: user: 0.40 s, system: 0.01 s, elapsed: 0.61 s ,,,,,,,,, 
2019-12-11 15:35:05.904 CST,,,1924,,5df09c28.784,1,,2019-12-11 15:35:04 CST,6/51,0,LOG,00000, automatic vacuum of table  db2.public.t_autovacuum_db2 : index scans: 0
pages: 0 removed, 25467 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 500149 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3422
buffer usage: 50964 hits, 2 misses, 6 dirtied
avg read rate: 0.015 MB/s, avg write rate: 0.044 MB/s
system usage: CPU: user: 0.43 s, system: 0.00 s, elapsed: 1.07 s ,,,,,,,,, 
2019-12-11 15:35:21.297 CST,,,1937,,5df09c37.791,1,,2019-12-11 15:35:19 CST,6/55,0,LOG,00000, automatic vacuum of table  db1.public.t_autovacuum_db1 : index scans: 0
pages: 0 removed, 31832 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 500270 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3427
buffer usage: 63695 hits, 2 misses, 6 dirtied
avg read rate: 0.010 MB/s, avg write rate: 0.031 MB/s
system usage: CPU: user: 0.51 s, system: 0.04 s, elapsed: 1.52 s ,,,,,,,,, 
2019-12-11 15:35:36.250 CST,,,1950,,5df09c46.79e,1,,2019-12-11 15:35:34 CST,6/59,0,LOG,00000, automatic vacuum of table  db3.public.t_autovacuum_db3 : index scans: 0
pages: 0 removed, 29875 remain, 1 skipped due to pins, 0 skipped frozen
tuples: 270 removed, 499874 remain, 0 are dead but not yet removable, oldest xmin: 3430
buffer usage: 59780 hits, 2 misses, 6 dirtied
avg read rate: 0.011 MB/s, avg write rate: 0.034 MB/s
system usage: CPU: user: 0.43 s, system: 0.03 s, elapsed: 1.38 s ,,,,,,,,, 
2019-12-11 15:38:07.146 CST,,,2081,,5df09cdc.821,1,,2019-12-11 15:38:04 CST,6/81,0,LOG,00000, automatic vacuum of table  db2.public.t_autovacuum_db2 : index scans: 0
pages: 0 removed, 44551 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 500509 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3463
buffer usage: 89140 hits, 1 misses, 5 dirtied
avg read rate: 0.003 MB/s, avg write rate: 0.017 MB/s
system usage: CPU: user: 0.58 s, system: 0.11 s, elapsed: 2.28 s ,,,,,,,,, 
2019-12-11 15:38:07.963 CST,,,2081,,5df09cdc.821,2,,2019-12-11 15:38:04 CST,6/82,3463,LOG,00000, automatic analyze of table  db2.public.t_autovacuum_db2  system usage: CPU: user: 0.38 s, system: 0.00 s, elapsed: 0.81 s ,,,,,,,,, 
2019-12-11 15:38:25.836 CST,,,2094,,5df09ceb.82e,1,,2019-12-11 15:38:19 CST,6/86,0,LOG,00000, automatic vacuum of table  db1.public.t_autovacuum_db1 : index scans: 0
pages: 0 removed, 38185 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 500585 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3464
buffer usage: 76407 hits, 1 misses, 15391 dirtied
avg read rate: 0.001 MB/s, avg write rate: 19.978 MB/s
system usage: CPU: user: 0.66 s, system: 0.03 s, elapsed: 6.01 s ,,,,,,,,, 
2019-12-11 15:38:26.593 CST,,,2094,,5df09ceb.82e,2,,2019-12-11 15:38:19 CST,6/87,3464,LOG,00000, automatic analyze of table  db1.public.t_autovacuum_db1  system usage: CPU: user: 0.35 s, system: 0.00 s, elapsed: 0.75 s ,,,,,,,,, 
2019-12-11 15:38:36.546 CST,,,2107,,5df09cfa.83b,1,,2019-12-11 15:38:34 CST,6/91,0,LOG,00000, automatic vacuum of table  db3.public.t_autovacuum_db3 : index scans: 0
pages: 0 removed, 41365 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 500758 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3465
buffer usage: 82768 hits, 1 misses, 2 dirtied
avg read rate: 0.005 MB/s, avg write rate: 0.010 MB/s
system usage: CPU: user: 0.58 s, system: 0.03 s, elapsed: 1.63 s ,,,,,,,,, 
2019-12-11 15:38:37.276 CST,,,2107,,5df09cfa.83b,2,,2019-12-11 15:38:34 CST,6/92,3465,LOG,00000, automatic analyze of table  db3.public.t_autovacuum_db3  system usage: CPU: user: 0.37 s, system: 0.00 s, elapsed: 0.72 s ,,,,,,,,,

從日志可見，PG 認為目前有 4 個數據庫（60s/4）需要處理，每個 15s 調度一個數據庫。

感謝各位的閱讀，以上就是“PostgreSQL 中如何監控 VACUUM 的處理過程”的內容了，經過本文的學習后，相信大家對 PostgreSQL 中如何監控 VACUUM 的處理過程這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！

正文完