暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss中系统自动清理线程（autovacuum）自动执行VACUUM和ANALYZE机制

openGauss中系统自动清理线程（autovacuum）自动执行VACUUM和ANALYZE机制

SmallDB 2024-06-26
268

学习目的

AutoVacuum是后台自动触发Vacuum的一套逻辑，到达了什么样的条件时，自动触发vacuum。后台有常驻线程AVClauncher。这个线程每隔一定的间隔会同志PM线程来唤起一些AVCWorker线程，由AVCWorker进行实际的vacuum与analyze任务。涉及到的时间间隔、唤起的worker的数量等，都有相关的GUC参数可配 ，AutoVacuum 通过后台进程定期扫描数据库中的表 我就是想看看原理，是不是符合预期

openGauss版本

openGauss版本

openGauss=# select version();
                                                                   version
----------------------------------------------------------------------------------------------------------------------------------------------
 (openGauss 5.0.0 build ) compiled at 2023-04-18 09:47:39 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

openGauss=#

openGauss服务端版本

openGauss=# show server_version;
 server_version
----------------
 9.2.4
(1 row)

openGauss=#

相关配置参数

openGauss=# select name,setting from pg_settings where name ilike '%vacuum%';
              name               |  setting
---------------------------------+------------
 autovacuum                      | on
 autovacuum_analyze_scale_factor |0.1
 autovacuum_analyze_threshold    |50
 autovacuum_freeze_max_age       |4000000000
 autovacuum_io_limits            |-1
 autovacuum_max_workers          |3
 autovacuum_mode                 | mix
 autovacuum_naptime              |600
 autovacuum_vacuum_cost_delay    |20
 autovacuum_vacuum_cost_limit    |-1
 autovacuum_vacuum_scale_factor  |0.2
 autovacuum_vacuum_threshold     |50
 enable_debug_vacuum             | off
 log_autovacuum_min_duration     |-1
 vacuum_cost_delay               |0
 vacuum_cost_limit               |200
 vacuum_cost_page_dirty          |20
 vacuum_cost_page_hit            |1
 vacuum_cost_page_miss           |10
 vacuum_defer_cleanup_age        |0
 vacuum_freeze_min_age           |2000000000
 vacuum_freeze_table_age         |4000000000
 vacuum_gtt_defer_check_age      |10000
(23 rows)
openGauss=#

相关参数说明

  • • autovacuum: 指定数据库自动清理线程（autovacuum）的启动。

  • • autovacuum_mode: 该参数仅在autovacuum设置为on的场景下生效，它控制autoanalyze或autovacuum的打开情况。

  • • autovacuum_io_limits：控制autovacuum线程每秒触发IO的上限。

  • • autoanalyze_timeout：设置autoanalyze的超时时间。在对某张表做autoanalyze时，如果该表的analyze时长超过了autoanalyze_timeout，则自动取消该表此次analyze。

  • • log_autovacuum_min_duration：当自动清理的执行时间大于或者等于某个特定的值时，向服务器日志中记录本次自动清理执行的概要信息。设置此选项有助于追踪自动清理的行为。

  • • autovacuum_naptime：设置两次自动清理操作的时间间隔。

  • • autovacuum_vacuum_threshold：指定触发 VACUUM 操作的最小行数阈值。

  • • autovacuum_analyze_threshold：指定触发分析（ANALYZE）操作的最小行数阈值。

  • • autovacuum_vacuum_scale_factor：指定触发 VACUUM 操作的行数相对于表大小的比例因子。

  • • autovacuum_freeze_max_age：设置事务内的最大时间，使得表的pg_class.relfrozenxid字段在VACUUM操作执行之前被写入。

  • • autovacuum_analyze_scale_factor：指定触发分析操作的行数相对于表大小的比例因子。优化 AutoVacuum 性能

    模拟数据

    查看启动数据库

[opengauss@3a5d27765b16 ~]$ gs_ctl status -D /var/lib/opengauss/data/
[2024-06-2510:03:32.806][5744][][gs_ctl]: gs_ctl status,datadir is/var/lib/opengauss/data
gs_ctl: server is running (PID:1)
/usr/local/opengauss/bin/gaussdb
[opengauss@3a5d27765b16 ~]$
[opengauss@3a5d27765b16 ~]$ gsql -d postgres -p 5432-r
openGauss=# SHOW log_autovacuum_min_duration;
 log_autovacuum_min_duration
-----------------------------
-1
(1 row)

openGauss=#
openGauss=#select name,setting from pg_settings where name ilike '%log_autovacuum_min_duration%';
            name             | setting
-----------------------------+---------
 log_autovacuum_min_duration |-1
(1 row)

openGauss=#select name,setting ,context from pg_settings where name ilike '%log_autovacuum_min_duration%';
            name             | setting | context
-----------------------------+---------+---------
 log_autovacuum_min_duration |-1| sighup
(1 row)

openGauss=#

重设log_autovacuum_min_duration

[opengauss@3a5d27765b16 ~]$ gsql -d postgres -p 5432-r
gsql -d postgres -p 5432-r
gsql ((openGauss 5.0.0 build ) compiled at 2023-04-1809:47:39 commit 0last mr  )
NOTICE :The password has been expired, please change the password.
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type"help"for help.

openGauss=#select name,setting ,context from pg_settings where name ilike '%log_autovacuum_min_duration%';
            name             | setting | context
-----------------------------+---------+---------
 log_autovacuum_min_duration |0| sighup
(1 row)

openGauss=#

制造数据

create table test1 (id int, info text);  
insert into test1 select generate_series(1,1000000);  
delete from test1 where id<1000000;

查看死元组

hu1=# \x
Expanded display is on.
hu1=#select*from pg_stat_user_tables where relname ='test1';
-[ RECORD 1]-----+------------------------------
relid             |115142
schemaname        |public
relname           | test1
seq_scan          |1
seq_tup_read      |1000000
idx_scan          |
idx_tup_fetch     |
n_tup_ins         |1000000
n_tup_upd         |0
n_tup_del         |999999
n_tup_hot_upd     |0
n_live_tup        |1
n_dead_tup        |999999
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  |
vacuum_count      |0
autovacuum_count  |0
analyze_count     |0
autoanalyze_count |0
last_data_changed |2024-06-2510:27:21.922809+08
hu1=#

日志

[opengauss@3a5d27765b16 ~]$ tail -f /var/lib/opengauss/data/pg_log/postgresql-2024-06-25_102205.log

内容

2024-06-2510:30:39.742[unknown][unknown] localhost 1397584425754240[0:0#0]  0 [BACKEND] LOG:  start autovacuum on database "hu1"
2024-06-2510:30:39.771 opengauss hu1 localhost 1397584425754240[0:0#0]  0 [VACUUM] LOG:  normally vacuum rel "public.test1" freeze 2000000000 OldestXmin 31230, FreezeLimit 3, freezeTableLimit 3
2024-06-2510:30:50.372 opengauss hu1 localhost 1397584425754240[0:0#0]  0 [VACUUM] LOG:  "test1": found 0 removable, 1000000 nonremovable row versions in 4425 out of 4425 pages
2024-06-2510:30:50.372 opengauss hu1 localhost 1397584425754240[0:0#0]  0 [VACUUM] DETAIL:  999999 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.43u sec elapsed 10.60 sec.
2024-06-2510:30:50.373 opengauss hu1 localhost 1397584425754240[0:0#0]  0 [BACKEND] LOG:  automatic vacuum of table "hu1.public.test1": index scans: 0
        pages:0 removed,4425 remain
        tuples:0 removed,1000000 remain
        buffer usage:8859 hits,2 misses,4429 dirtied
        avg read rate:0.001MiB/s, avg write rate:3.264MiB/s
        system usage: CPU 0.00s/0.43u sec elapsed 10.60 sec
2024-06-2510:30:50.950 opengauss hu1 localhost 1397584425754240[0:0#0]  0 [BACKEND] LOG:  automatic analyze of table "hu1.public.test1" system usage: CPU 0.00s/0.04u sec elapsed 0.56 sec
2024-06-2510:30:51.372[unknown][unknown] localhost 1397586866314880[0:0#0]  0 [UNDO] LOG:  [UndoRecycleMain:750]update globalRecycleXid: oldestXmin=31231, recycleXmin=31231, globalFrozenXid=30514, globalRecycleXid=31230, newRecycleXid=31231.
2024-06-2510:31:05.441[unknown][unknown] localhost 1397594982293120[0:0#0]  0 [DBL_WRT] LOG:  [batch flush] DW truncate end: file_head[dwn 15, start 3958], total_pages 0
2024-06-2510:31:05.447[unknown][unknown] localhost 1397594982293120[0:0#0]  0 [UNDO] LOG:  [CheckPointUndoSystemMeta:355]undo metadata checkPointRedo = 2856995336, oldestXmin = 31231, recycleXmin = 31231, globalFrozenXid = 30514, globalRecycleXid = 31231.
2024-06-2510:31:05.452[unknown][unknown] localhost 1397594982293120[0:0#0]  0 [BACKEND] WARNING:  replicationSlotMinLSN is InvalidXLogRecPtr!!!
2024-06-2510:31:05.452[unknown][unknown] localhost 1397594982293120[0:0#0]  0 [BACKEND] WARNING:  replicationSlotMaxLSN is InvalidXLogRecPtr!!!
2024-06-2510:31:05.453[unknown][unknown] localhost 1397594982293120[0:0#0]  0 [BACKEND] LOG:  CreateCheckPoint PrintCkpXctlControlFile: [checkPoint] oldCkpLoc:0/AA4A4258, oldRedo:0/AA4A41D8, newCkpLoc:0/AA4A4A88, newRedo:0/AA4A4A08, preCkpLoc:0/AA4A40B8

再次查看死元组

hu1=#select*from pg_stat_user_tables where relname ='test1';
-[ RECORD 1]-----+------------------------------
relid             |115142
schemaname        |public
relname           | test1
seq_scan          |1
seq_tup_read      |1000000
idx_scan          |
idx_tup_fetch     |
n_tup_ins         |1000000
n_tup_upd         |0
n_tup_del         |999999
n_tup_hot_upd     |0
n_live_tup        |1
n_dead_tup        |999999
last_vacuum       |2024-06-2510:30:50.373298+08
last_autovacuum   |2024-06-2510:30:50.373298+08
last_analyze      |2024-06-2510:30:50.950394+08
last_autoanalyze  |2024-06-2510:30:50.950394+08
vacuum_count      |1
autovacuum_count  |1
analyze_count     |1
autoanalyze_count |1
last_data_changed |2024-06-2510:27:21.922809+08

hu1=#

日志

2024-06-2510:40:39.831[unknown][unknown] localhost 1397584425754240[0:0#0]  0 [BACKEND] LOG:  start autovacuum on database "hu1"
2024-06-2510:40:39.859 opengauss hu1 localhost 1397584425754240[0:0#0]  0 [VACUUM] LOG:  normally vacuum rel "public.test1" freeze 2000000000 OldestXmin 31231, FreezeLimit 3, freezeTableLimit 3
2024-06-2510:40:50.322 opengauss hu1 localhost 1397584425754240[0:0#0]  0 [VACUUM] LOG:  "test1": found 999999 removable, 1 nonremovable row versions in 4425 out of 4425 pages
2024-06-2510:40:50.322 opengauss hu1 localhost 1397584425754240[0:0#0]  0 [VACUUM] DETAIL:  0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.01s/0.27u sec elapsed 10.46 sec.
2024-06-2510:40:50.322 opengauss hu1 localhost 1397584425754240[0:0#0]  0 [BACKEND] LOG:  automatic vacuum of table "hu1.public.test1": index scans: 0
        pages:0 removed,4425 remain
        tuples:999999 removed,1 remain
        buffer usage:8868 hits,0 misses,4431 dirtied
        avg read rate:0.000MiB/s, avg write rate:3.308MiB/s
        system usage: CPU 0.01s/0.27u sec elapsed 10.46 sec
2024-06-2510:41:06.148[unknown][unknown] localhost 1397594982293120[0:0#0]  0 [DBL_WRT] LOG:  [batch flush] DW truncate end: file_head[dwn 15, start 8401], total_pages 0

再次查看死元组vacuum_count/autovacuum_count 为2

hu1=# \x
Expanded display is on.
hu1=#select*from pg_stat_user_tables where relname ='test1';
-[ RECORD 1]-----+------------------------------
relid             |115142
schemaname        |public
relname           | test1
seq_scan          |1
seq_tup_read      |1000000
idx_scan          |
idx_tup_fetch     |
n_tup_ins         |1000000
n_tup_upd         |0
n_tup_del         |999999
n_tup_hot_upd     |0
n_live_tup        |1
n_dead_tup        |0
last_vacuum       |2024-06-2510:40:50.322862+08
last_autovacuum   |2024-06-2510:40:50.322862+08
last_analyze      |2024-06-2510:30:50.950394+08
last_autoanalyze  |2024-06-2510:30:50.950394+08
vacuum_count      |2
autovacuum_count  |2
analyze_count     |1
autoanalyze_count |1
last_data_changed |2024-06-2510:27:21.922809+08

hu1=#

Autovacuum VACUUM thresold

Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold

本例子数据Autovacuum

 Total number of Obsolete records=(0.2*1000000)+50=200050

autovacuum ANALYZE

Autovacuum ANALYZE threshold for a table = autovacuum_analyze_scale_factor * number of tuples + autovacuum_analyze_threshold

本例子数据AutoANALYZE

 Total number of Inserts/Deletes/Updates=(0.1 * 1000000) + 50 = 100050

验证单独autovacuum(只要有超过100条过时的记录,运行autovacuum vacuum

hu1=#createtable test2 (id int, info text);
CREATETABLE
hu1=#altertablepublic.test2 set(autovacuum_vacuum_threshold =100);
ALTERTABLE
hu1=#altertablepublic.test2 set(autovacuum_vacuum_scale_factor=0);
ALTERTABLE
hu1=# \d+public.test2
Table"public.test2"
Column|Type|Modifiers|Storage|Stats target |Description
--------+---------+-----------+----------+--------------+-------------
 id     |integer|| plain    ||
 info   | text    || extended ||
HasOIDs:no
Options: orientation=row, compression=no, autovacuum_vacuum_threshold=100, autovacuum_vacuum_scale_factor=0

hu1=#insertinto test2 select generate_series(1,101);
INSERT0101
hu1=# \x
Expanded display is on.
hu1=#select*from pg_stat_user_tables where relname ='test2';
-[ RECORD 1]-----+------------------------------
relid             |123334
schemaname        |public
relname           | test2
seq_scan          |0
seq_tup_read      |0
idx_scan          |
idx_tup_fetch     |
n_tup_ins         |101
n_tup_upd         |0
n_tup_del         |0
n_tup_hot_upd     |0
n_live_tup        |101
n_dead_tup        |0
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  |
vacuum_count      |0
autovacuum_count  |0
analyze_count     |0
autoanalyze_count |0
last_data_changed |2024-06-2512:43:08.472596+08

评估是否运行

 Total number of Obsolete records=(0*100)+100=100
 Total number of Inserts/Deletes/Updates=(0.1 * 100) + 50 = 60

autovacuum ANALYZE达标

hu1=#select*from pg_stat_user_tables where relname ='test2';
-[ RECORD 1]-----+------------------------------
relid             |123334
schemaname        |public
relname           | test2
seq_scan          |0
seq_tup_read      |0
idx_scan          |
idx_tup_fetch     |
n_tup_ins         |101
n_tup_upd         |0
n_tup_del         |0
n_tup_hot_upd     |0
n_live_tup        |101
n_dead_tup        |0
last_vacuum       |
last_autovacuum   |
last_analyze      |2024-06-2512:46:38.091925+08
last_autoanalyze  |2024-06-2512:46:38.091925+08
vacuum_count      |0
autovacuum_count  |0
analyze_count     |1
autoanalyze_count |1
last_data_changed |2024-06-2512:43:08.472596+08

hu1=#

Autovacuum VACUUM thresold

参数与产品文档一致，需要达到dead_tuple > all_data_num*autovacuum_vacuum_scale_factor + 50
dead_tuple > 101*0 + 50
select * from pg_stat_user_tables where relname='test2';
n_dead_tup 为0，依据视图，不应该触发。

公式

Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold
dead_tuple >101*0+50

pg_autovac_status视图查看

hu1=#select*from pg_autovac_status('123334');
-[ RECORD 1]------
nspname   |public
relname   | test2
nodename  | gaussdb
doanalyze | f
anltuples |2
anlthresh |60
dovacuum  | f
vactuples |0
vacthresh |100

hu1=#

autovacuum例子

hu1=# alter table public.test2 reset (autovacuum_vacuum_threshold);
ALTER TABLE
hu1=#
hu1=#deletefrom test2 where id<50;
DELETE51
hu1=#select*from pg_stat_user_tables where relname='test2';
-[ RECORD 1]-----+------------------------------
relid             |123334
schemaname        |public
relname           | test2
seq_scan          |1
seq_tup_read      |103
idx_scan          |
idx_tup_fetch     |
n_tup_ins         |103
n_tup_upd         |0
n_tup_del         |51
n_tup_hot_upd     |0
n_live_tup        |52
n_dead_tup        |51
last_vacuum       |
last_autovacuum   |
last_analyze      |2024-06-2512:46:38.091925+08
last_autoanalyze  |2024-06-2512:46:38.091925+08
vacuum_count      |0
autovacuum_count  |0
analyze_count     |1
autoanalyze_count |1
last_data_changed |2024-06-2513:50:25.877303+08

hu1=#

autovacuum 运行成功符合预期

-[ RECORD 1]-----+------------------------------
relid             |123334
schemaname        |public
relname           | test2
seq_scan          |1
seq_tup_read      |103
idx_scan          |
idx_tup_fetch     |
n_tup_ins         |103
n_tup_upd         |0
n_tup_del         |51
n_tup_hot_upd     |0
n_live_tup        |52
n_dead_tup        |0
last_vacuum       |2024-06-2514:06:38.423621+08
last_autovacuum   |2024-06-2514:06:38.423621+08
last_analyze      |2024-06-2512:46:38.091925+08
last_autoanalyze  |2024-06-2512:46:38.091925+08
vacuum_count      |1
autovacuum_count  |1
analyze_count     |1
autoanalyze_count |1
last_data_changed |2024-06-2513:50:25.877303+08

hu1=#

autovacuum 日志运行成功符合预期

2024-06-2514:06:38.423 opengauss hu1 localhost 1397331494805120[0:0#0]  0 [VACUUM] DETAIL:  0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec.
2024-06-2514:06:38.423 opengauss hu1 localhost 1397331494805120[0:0#0]  0 [BACKEND] LOG:  automatic vacuum oftable "hu1.public.test2": index scans: 0
        pages:0 removed,1 remain
        tuples:51 removed,52 remain
        buffer usage:19 hits,4 misses,6 dirtied
        avg read rate:33.351MiB/s, avg write rate:50.027MiB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
2024-06-2514:07:07.488 opengauss postgres localhost 1397331494805120[0:0#0]  0 [BACKEND] LOG:  clean statement thread start
2024-06-2514:07:23.934[unknown][unknown] localhost 1397340656082560[0:0#0]  0 [DBL_WRT] LOG:  [batch flush] DW truncateend: file_head[dwn 15, start8610], total_pages 0
2024-06-2514:07:23.939[unknown][unknown] localhost 1397340656082560[0:0#0]  0 [UNDO] LOG:  [CheckPointUndoSystemMeta:355]undo metadata checkPointRedo =2861900024, oldestXmin =31602, recycleXmin =31602, globalFrozenXid =31237, globalRecycleXid = 31602.

参考文档

自动清理[1] 重设参数[2]

引用链接

[1]
 自动清理: https://docs-opengauss.osinfra.cn/zh/docs/latest/docs/DatabaseReference/%E8%87%AA%E5%8A%A8%E6%B8%85%E7%90%86.html
[2]
 重设参数: https://docs-opengauss.osinfra.cn/zh/docs/latest/docs/DatabaseReference/%E9%87%8D%E8%AE%BE%E5%8F%82%E6%95%B0.html#zh-cn_topic_0283137176_zh-cn_topic_0237121562_zh-cn_topic_0059777490_t290c8f15953843db8d8e53d867cd893d


opengausslasttup
文章转载自SmallDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论