PostgreSQL 12: 新增 pg_stat_progress_create_index 视图监控索引创建进度

PostgreSQL 12 版本之前,对PostgreSQL大表创建索引时是一个比较痛苦的过程,创建索引过程中无法得知索引创建进度,PostgreSQL 12 在运维监控功能方面得到增强,新增 pg_stat_progress_create_index 视图可以监控索引的创建进度,本文简单演示。

CREATE INDEXREINDEX 创建索引都可以通过 pg_stat_progress_create_index 视图监控。

发行说明

Report progress of CREATE INDEX and REINDEX operations (álvaro Herrera, Peter Eisentraut)

Progress is reported in the pg_stat_progress_create_index system view

环境准备

创建测试表并插入 1000 万数据 ,如下:

1
2
3
4
5
mydb=> CREATE TABLE t1 (id int4,name text,ctime timestamp without time zone default clock_timestamp());
CREATE TABLE

mydb=> INSERT INTO t1 (id,name ) SELECT n, n || '_CREATE_INDEX' FROM generate_series(1,10000000) n;
INSERT 0 10000000

监控索引创建进度

开启会话1,创建索引,如下:

1
2
mydb=> CREATE INDEX idx_t1_ctime ON t1 USING BTREE(ctime);
命令未结束

注意: 会话1未结束,在创建索引过程中。

开启会话2,查询 pg_stat_progress_create_index 视图,监控索引创建进度,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mydb=> SELECT * FROM pg_stat_progress_create_index ;
-[ RECORD 1 ]------+-------------------------------
pid | 20539
datid | 16387
datname | mydb
relid | 16527
index_relid | 0
command | CREATE INDEX
phase | building index: scanning table
lockers_total | 0
lockers_done | 0
current_locker_pid | 0
blocks_total | 82353
blocks_done | 52293
tuples_total | 0
tuples_done | 0
partitions_total | 0
partitions_done | 0

mydb=> SELECT * FROM pg_stat_progress_create_index ;
-[ RECORD 1 ]------+---------------------------------------
pid | 20539
datid | 16387
datname | mydb
relid | 16527
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 | 10000000
tuples_done | 1173763
partitions_total | 0
partitions_done | 0

每条索引创建进程在 pg_stat_progress_create_index 视图中对应一条记录,几个主要的字段解释如下:

  • pid: 索引创建进程号
  • relid: 表的OID
  • index_relid: 索引的OID
  • phase: 索引创建的当前处理阶段
  • current_locker_pid: 阻塞索引创建的进程号
  • blocks_total: 当前处理阶段需要处理的数据块
  • lockers_done: 当前处理阶段已完成的数据块
  • tuples_total: 当前处理阶段需要处理的记录数
  • tuples_done: 当前处理阶段已完成的记录数
  • partitions_total: 当在分区表上创建索引,当前处理阶段需要处理的总分区数
  • partitions_done: 当在分区表上创建索引,当前处理阶段已处理的总分区数。

索引创建的时间主要花在 ‘building index: scanning table’ 和’building index: loading tuples in tree’ 阶段,因此,根据 blocks_total、blocks_done、tuples_total、blocks_done 很容易判断索引创建进度。

总结

本文演示了 CREATE INDEX 命令执行过程中,通过查询 pg_stat_progress_create_index 视图监控索引创建进度,对于大表创建索引场景非常有用,这个功能太棒了!

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%