在PG 13中,在插入数据时可自动触发autovacumm,从而可以生成表的vm文件而无需手工触发vacumm才能生成.
vm文件可以加快VACUUM清理的速度.
新增的配置选项
autovacuum_vacuum_insert_threshold
控制插入数据触发autovacuum的的阈值,默认值为1000
[local:/data/run/pg13]:5130 pg13@masterdb=# show autovacuum_vacuum_insert_threshold;
autovacuum_vacuum_insert_threshold
1000
(1 row)
autovacuum_vacuum_insert_scale_factor
控制插入数据时超过什么比例会触发autovacuum,默认值为20%
[local:/data/run/pg13]:5130 pg13@masterdb=# show autovacuum_vacuum_insert_scale_factor;
autovacuum_vacuum_insert_scale_factor
0.2
(1 row)
试用体验
创建数据表
[local:/data/run/pg13]:5130 pg13@masterdb=# drop table tbl1;
DROP TABLE
[local:/data/run/pg13]:5130 pg13@masterdb=# create table tbl1(id int,c1 char(20));
CREATE TABLE
[local:/data/run/pg13]:5130 pg13@masterdb=# insert into tbl1(id,c1) select x,‘c1’||x from generate_series(1,1000000) as x;
INSERT 0 1000000
[local:/data/run/pg13]:5130 pg13@masterdb=# select pg_relation_filepath(‘tbl1’);
pg_relation_filepath
base/16384/16395
(1 row)
[local:/data/run/pg13]:5130 pg13@masterdb=#
日志输出,PG自动对tbl1进行了分析
2020-05-22 14:52:56.790 CST,62686,5ec776c8.f4de,1,2020-05-22 14:52:56 CST,4/197,0,LOG,00000,“automatic vacuum of table ““masterdb.public.tbl1"”: index scans: 0
pages: 0 removed, 7353 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 1000000 remain, 0 are dead but not yet removable, oldest xmin: 508
buffer usage: 14734 hits, 2 misses, 4 dirtied
avg read rate: 0.031 MB/s, avg write rate: 0.063 MB/s
system usage: CPU: user: 0.27 s, system: 0.02 s, elapsed: 0.49 s
WAL usage: 7354 records, 1 full page images, 442212 bytes”,”",“autovacuum worker”
2020-05-22 14:52:57.065 CST,62686,5ec776c8.f4de,2,2020-05-22 14:52:56 CST,4/198,508,LOG,00000,“automatic analyze of table ““masterdb.public.tbl1"” system usage: CPU: user: 0.16 s, system: 0.01 s, elapsed: 0.27 s”,”",“autovacuum worker”
[pg13@localhost 16384]$
生成了vm文件
[pg13@localhost 16384]$ pwd
/data/pg13/masterdb/base/16384
[pg13@localhost 16384]$ ls -l 16395*
-rw------- 1 pg13 pg13 60235776 May 22 14:52 16395
-rw------- 1 pg13 pg13 32768 May 22 14:52 16395_fsm
-rw------- 1 pg13 pg13 8192 May 22 14:52 16395_vm
[pg13@localhost 16384]$
