PostgreSQL 13: 新增pg_stat_progress_analyze视图监控表分析进度

发表于 | 分类于 | | 阅读次数:

PostgreSQL的 ANALYZE操作是数据库的常规运维操作，对于大表此操作耗时较长，执行过程中苦于无法知道执行进度。

PostgreSQL 13 版本新增pg_stat_progress_analyze视图，可以监控ANALYZE操作的执行进度，本文做个演示。

关于Pg_stat_progress_analyze

pg_stat_progress_analyze视图的字段解释，详见以下:

主要字段说明:

  • phase: 表分析的主要阶段，详见下图。
  • sample_blks_total: 数据块总数。
  • sample_blks_scanned: 已完成扫描的数据块总数。
  • ext_stats_total: 扩展的统计信息。
  • ext_stats_computed: 已完成的扩展统计信息，这个值只有当phase在computing extended statistics阶段才会增长。
  • child_tables_total: 对于分区表，显示分区表子表的数量，对于非分区表，此值为0。
  • child_tables_done: 对于分区表，显示已完成扫描的分区表子表的数量，对于非分区表，此值为0。
  • current_child_table_relid: 当前正在扫描的分区表子表的oid。

phase的几个阶段图，如下:

环境准备

现有一张按月分区的分区表，如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 pguser | user_reg_log        | partitioned table | pguser | permanent   | 0 bytes |
 pguser | user_reg_log_202001 | table             | pguser | permanent   | 0 bytes |
 pguser | user_reg_log_202002 | table             | pguser | permanent   | 0 bytes |
 pguser | user_reg_log_202003 | table             | pguser | permanent   | 0 bytes |
 pguser | user_reg_log_202004 | table             | pguser | permanent   | 0 bytes |
 pguser | user_reg_log_202005 | table             | pguser | permanent   | 0 bytes |
 pguser | user_reg_log_202006 | table             | pguser | permanent   | 0 bytes |
 pguser | user_reg_log_202007 | table             | pguser | permanent   | 0 bytes |
 pguser | user_reg_log_202008 | table             | pguser | permanent   | 0 bytes |
 pguser | user_reg_log_his    | table             | pguser | permanent   | 0 bytes |
 
mydb=> \d user_reg_log
                                    Partitioned table "pguser.user_reg_log"
   Column    |              Type              | Collation | Nullable |                 Default
-------------+--------------------------------+-----------+----------+------------------------------------------
 id          | integer                        |           | not null | nextval('user_reg_log_id_seq'::regclass)
 user_id     | integer                        |           |          |
 create_time | timestamp(0) without time zone |           |          |
Partition key: RANGE (create_time)
Indexes:
    "idx_user_reg_log_ctime" btree (create_time)
Publications:
    "pub1"
Number of partitions: 9 (Use \d+ to list them.)

插入数据，如下:

1
2
3
4
5
6
7
8
INSERT INTO user_reg_log(user_id,create_time)
SELECT round(100000000*random()),generate_series('2019-10-01'::date, '2020-07-07'::date, '1 seconds');

mydb=> SELECT COUNT(*) FROM user_reg_log;
  count
----------
 24192001
(1 row)

监控Analyze执行进度

会话1: 进行表分析

1
2
mydb=> ANALYZE user_reg_log;
命令执行过程中，暂未结束

会话2: 监控表分析的进度

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
^Cpostgres=# SELECT *FROM pg_stat_progress_analyze;
-[ RECORD 1 ]-------------+--------------------------------
pid                       | 4653
datid                     | 16386
datname                   | mydb
relid                     | 16817
phase                     | acquiring inherited sample rows
sample_blks_total         | 42967
sample_blks_scanned       | 40859
ext_stats_total           | 0
ext_stats_computed        | 0
child_tables_total        | 9
child_tables_done         | 0
current_child_table_relid | 16821

postgres=# SELECT *FROM pg_stat_progress_analyze;
-[ RECORD 1 ]-------------+--------------------------------
pid                       | 4653
datid                     | 16386
datname                   | mydb
relid                     | 16817
phase                     | acquiring inherited sample rows
sample_blks_total         | 14478
sample_blks_scanned       | 13083
ext_stats_total           | 0
ext_stats_computed        | 0
child_tables_total        | 9
child_tables_done         | 1
current_child_table_relid | 16825

postgres=# SELECT *FROM pg_stat_progress_analyze;
-[ RECORD 1 ]-------------+--------------------------------
pid                       | 4653
datid                     | 16386
datname                   | mydb
relid                     | 16817
phase                     | acquiring inherited sample rows
sample_blks_total         | 14478
sample_blks_scanned       | 700
ext_stats_total           | 0
ext_stats_computed        | 0
child_tables_total        | 9
child_tables_done         | 3
current_child_table_relid | 16833

postgres=# SELECT *FROM pg_stat_progress_analyze;
-[ RECORD 1 ]-------------+--------------------------------
pid                       | 4653
datid                     | 16386
datname                   | mydb
relid                     | 16817
phase                     | acquiring inherited sample rows
sample_blks_total         | 14011
sample_blks_scanned       | 7124
ext_stats_total           | 0
ext_stats_computed        | 0
child_tables_total        | 9
child_tables_done         | 4
current_child_table_relid | 16837

postgres=# SELECT *FROM pg_stat_progress_analyze;
-[ RECORD 1 ]-------------+--------------------------------
pid                       | 4653
datid                     | 16386
datname                   | mydb
relid                     | 16817
phase                     | acquiring inherited sample rows
sample_blks_total         | 14011
sample_blks_scanned       | 13725
ext_stats_total           | 0
ext_stats_computed        | 0
child_tables_total        | 9
child_tables_done         | 6
current_child_table_relid | 16845

postgres=# SELECT *FROM pg_stat_progress_analyze;
-[ RECORD 1 ]-------------+--------------------------------
pid                       | 4653
datid                     | 16386
datname                   | mydb
relid                     | 16817
phase                     | acquiring inherited sample rows
sample_blks_total         | 2803
sample_blks_scanned       | 2269
ext_stats_total           | 0
ext_stats_computed        | 0
child_tables_total        | 9
child_tables_done         | 7
current_child_table_relid | 16849

postgres=# SELECT *FROM pg_stat_progress_analyze;
-[ RECORD 1 ]-------------+---------------------
pid                       | 4653
datid                     | 16386
datname                   | mydb
relid                     | 16845
phase                     | computing statistics
sample_blks_total         | 14011
sample_blks_scanned       | 14011
ext_stats_total           | 0
ext_stats_computed        | 0
child_tables_total        | 0
child_tables_done         | 0
current_child_table_relid | 0

postgres=# SELECT *FROM pg_stat_progress_analyze;
(0 rows)

会话3: 根据会话2的第一条输出，查看16821是哪张表？占多少页？如下:

1
2
3
4
5
mydb=> SELECT oid,relname,relpages FROM pg_class WHERE oid='16821';
  oid  |     relname      | relpages
-------+------------------+----------
 16821 | user_reg_log_his |    42967
(1 row)

子表sample_blks_total占用42967个数据页，刚好与会话2第一条输出的sample_blks_total值一致。

参考

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

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

PostgreSQL实战
感谢支持！
0%