作者
digoal
日期
2019-03-30
标签
PostgreSQL , pg_stat_progress_cluster , pg_stat_progress_vacuum
背景
通过pg_stat_progress_cluster观察vacuum full和cluster命令的执行过程。
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c16dc1aca5e01e6acaadfcf38f5fc964a381dc62
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6f97457e0ddd8b421ca5e483439ef0318e6fc89a
```
Add simple VACUUM progress reporting.
There's a lot more that could be done here yet - in particular, this
reports only very coarse-grained information about the index vacuuming
phase - but even as it stands, the new pg_stat_progress_vacuum can
tell you quite a bit about what a long-running vacuum is actually
doing.
Amit Langote and Robert Haas, based on earlier work by Vinayak Pokale
and Rahila Syed.
```
```
Add progress reporting for CLUSTER and VACUUM FULL.
This uses the same progress reporting infrastructure added in commit
c16dc1aca5e01e6acaadfcf38f5fc964a381dc62 and extends it to these
additional cases. We lack the ability to track the internal progress
of sorts and index builds so the information reported is
coarse-grained for some parts of the operation, but it still seems
like a significant improvement over having nothing at all.
Tatsuro Yamada, reviewed by Thomas Munro, Masahiko Sawada, Michael
Paquier, Jeff Janes, Alvaro Herrera, Rafia Sabih, and by me. A fair
amount of polishing also by me.
Discussion: http://postgr.es/m/59A77072.3090401@lab.ntt.co.jp
```
系统视图，vacuum full与cluster都涉及重写表，这两个操作的状态通过pg_stat_progress_cluster可得到。
```
postgres=# \d pg_stat_progress_vacuum
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuples | bigint | | |
num_dead_tuples | bigint | | |
postgres=# \d pg_stat_progress_cluster
View "pg_catalog.pg_stat_progress_cluster"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
command | text | | |
phase | text | | |
cluster_index_relid | bigint | | |
heap_tuples_scanned | bigint | | |
heap_tuples_written | bigint | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
index_rebuild_count | bigint | | |
```
pg_stat_progress_cluster视图内容
Whenever CLUSTER or VACUUM FULL is running, the pg_stat_progress_cluster view will contain a row for each backend that is currently running either command. The tables below describe the information that will be reported and provide information about how to interpret it.
Table 27.23. pg_stat_progress_cluster View
Column | Type | Description
---|---|---
pid| integer| Process ID of backend.
datid| oid |OID of the database to which this backend is connected.
datname| name| Name of the database to which this backend is connected.
relid| oid| OID of the table being clustered.
command |text| The command that is running. Either CLUSTER or VACUUM FULL.
phase| text |Current processing phase. See Table 27.24.
cluster_index_relid| bigint |If the table is being scanned using an index, this is the OID of the index being used; otherwise, it is zero.
heap_tuples_scanned| bigint |Number of heap tuples scanned. This counter only advances when the phase is seq scanning heap, index scanning heap or writing new heap.
heap_tuples_written| bigint| Number of heap tuples written. This counter only advances when the phase is seq scanning heap, index scanning heap or writing new heap.
heap_blks_total| bigint |Total number of heap blocks in the table. This number is reported as of the beginning of seq scanning heap.
heap_blks_scanned| bigint |Number of heap blocks scanned. This counter only advances when the phase is seq scanning heap.
index_rebuild_count| bigint |Number of indexes rebuilt. This counter only advances when the phase is rebuilding index.
Table 27.24. CLUSTER and VACUUM FULL phases
Phase |Description
---|---
initializing |The command is preparing to begin scanning the heap. This phase is expected to be very brief.
seq scanning heap |The command is currently scanning the table using a sequential scan.
index scanning heap |CLUSTER is currently scanning the table using an index scan.
sorting tuples |CLUSTER is currently sorting tuples.
swapping relation files |The command is currently swapping newly-built files into place.
rebuilding index |The command is currently rebuilding an index.
performing final cleanup |The command is performing final cleanup. When this phase is completed, CLUSTER or VACUUM FULL will end.
参考
https://www.postgresql.org/docs/devel/progress-reporting.html#CLUSTER-PROGRESS-REPORTING
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c16dc1aca5e01e6acaadfcf38f5fc964a381dc62
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6f97457e0ddd8b421ca5e483439ef0318e6fc89a
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
