本文主要是介绍Postgresql create index,vacuum和cluster进度报告,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
1.CREATE INDEX 进度报告
可查看pg_stat_progress_create_index视图,如下
创建索引可以观察的阶段
实例:
#建表插入测试数据
hank=> create table tbl_idx_tb01 (a int,b text);
CREATE TABLE
hank=> insert into tbl_idx_tb01 select t,'hank_' ||t from generate_series(1,11000000) t;
INSERT 0 11000000
hank=> create index CONCURRENTLY idx_tb01_a on tbl_idx_tb01 (a);
CREATE INDEX
hank=> drop index idx_tb01_a;
DROP INDEX
hank=> create index idx_tb01_a on tbl_idx_tb01 (a);
CREATE INDEX#另外开启一个session,进入数据库观察视图
select * from pg_stat_progress_create_index;
\watch 0.1
Wed 31 Mar 2021 04:56:17 PM CST (every 0.1s)-[ RECORD 1 ]------+---------------------------------------
pid | 1263
datid | 16450
datname | hank
relid | 16959
index_relid | 0
command | CREATE INDEX
phase | building index: loading tuples in tree
lockers_total | 0
lockers_done | 0
current_locker_pid | 0
blocks_total | 0
blocks_done | 0
tuples_total | 11000000
tuples_done | 11000000
partitions_total | 0
partitions_done | 0#通过观察视图可以看到先扫面block然后再扫面的tuples,而且加了CONCURRENTLY,是扫描两次
2.VACUUM进度报告
在做vacuum的时候,可以观察pg_stat_progress_vacuum视图,但是 VACUUM FULL 的时候要看pg_stat_progress_cluster,因为ACUUM FULL和 CLUSTER操作都会重写表。
vacuum 可以观察到的几个阶段如下:
实例如下:
#删除数据,做vacuum观察视图
hank=> delete from tbl_idx_tb01;
DELETE 11000000
hank=> \dt+ tbl_idx_tb01List of relationsSchema | Name | Type | Owner | Size | Description
--------+--------------+-------+-------+---------+-------------hank | tbl_idx_tb01 | table | hank | 4295 MB |
(1 row)hank=> vacuum ANALYZE tbl_idx_tb01 ;
VACUUM#另外开启一个session,执行如下语句
select * from pg_stat_progress_vacuum;
\watch 0.1
扫描阶段
Wed 31 Mar 2021 05:09:50 PM CST (every 0.1s)-[ RECORD 1 ]------+--------------
pid | 1263
datid | 16450
datname | hank
relid | 16959
phase | scanning heap
heap_blks_total | 549637
heap_blks_scanned | 518346
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples | 89478485
num_dead_tuples | 6087469
Wed 31 Mar 2021 05:10:00 PM CST (every 0.1s)#vacuuming indexes阶段发生在scanning heap之后,每次vacuum至少发生一次,当maintenance_work_mem 不足以存放dead tuples的时候,会发生多次。
-[ RECORD 1 ]------+------------------
pid | 1263
datid | 16450
datname | hank
relid | 16959
phase | vacuuming indexes
heap_blks_total | 549637
heap_blks_scanned | 549637
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples | 89478485
num_dead_tuples | 11000000
Wed 31 Mar 2021 05:10:31 PM CST (every 0.1s)#清理数据,返回空页给系统
-[ RECORD 1 ]------+----------------
pid | 1263
datid | 16450
datname | hank
relid | 16959
phase | truncating heap
heap_blks_total | 549637
heap_blks_scanned | 549637
heap_blks_vacuumed | 549637
index_vacuum_count | 1
max_dead_tuples | 89478485
num_dead_tuples | 11000000
3.CLUSTER进度报告
当发生 CLUSTER 或者 VACUUM FULL的时候可以观察如下视图
pg_stat_progress_cluster
cluster和vacuum full阶段介绍如下
篇幅原因,不再测试。通过以上视图,除了观察内部执行的阶段,还可以估算大概执行到哪一步,还需要多长时间。
参考:https://www.postgresql.org/docs/12/progress-reporting.html
这篇关于Postgresql create index,vacuum和cluster进度报告的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!