ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(174) - PG 13(Allow inserts to trigger autovacuum)

PostgreSQL DBA(174) - PG 13(Allow inserts to trigger autovacuum)

翻译 PostgreSQL 作者:husthxd 时间:2020-05-22 19:25:37 0 删除 编辑

在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]$

参考资料
Improved (auto)vacuum in PostgreSQL 13

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2693864/,如需转载,请注明出处,否则将追究法律责任。

下一篇: VirtualBox安装
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1504
  • 访问量
    3975458