注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL9.6:新增 pg_stat_progress_vacuum 视图监控 VACUUM  

2016-05-27 16:56:05|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

VACUUM 后台进程是 PostgreSQL MVCC 机制的重要组成部分,数据库里的大表VACUUM过程通常耗时较长,9.6版本前只能看到表在被VACUUM,而看不到具体的VACUUM过程信息,9.6版本之后 PostgreSQL 提供 pg_stat_progress_vacuum 视图,用于监控运行过程中的 VACUUM 进程。


关于 pg_stat_progress_vacuum
ColumnTypeDescription
pidintegerProcess ID of backend.
datidoidOID of the database to which this backend is connected.
datnamenameName of the database to which this backend is connected.
relidoidOID of the table being vacuumed.
phasetextCurrent processing phase of vacuum. See VACUUM phases.
heap_blks_totalbigintTotal number of heap blocks in the table. This number is reported as of the beginning of the scan; blocks added later will not be (and need not be) visited by this VACUUM.
heap_blks_scannedbigintNumber of heap blocks scanned. Because the visibility map is used to optimize scans, some blocks will be skipped without inspection; skipped blocks are included in this total, so that this number will eventually become equal to heap_blks_totalwhen the vacuum is complete. This counter only advances when the phase is scanning heap.
heap_blks_vacuumedbigintNumber of heap blocks vacuumed. Unless the table has no indexes, this counter only advances when the phase isvacuuming heap. Blocks that contain no dead tuples are skipped, so the counter may sometimes skip forward in large increments.
index_vacuum_countbigintNumber of completed index vacuum cycles.
max_dead_tuplesbigintNumber of dead tuples that we can store before needing to perform an index vacuum cycle, based onmaintenance_work_mem.
num_dead_tuplesbigintNumber of dead tuples collected since the last index vacuum cycle.
备注:pg_stat_progress_vacuum 视图的每一行对应一个运行中的VACUUM后台进程, 重要字段 phase 表示 VACUUM 进程的过程,VACUUM 阶段详见以下:

VACUUM 的阶段
PhaseDescription
initializingVACUUM is preparing to begin scanning the heap. This phase is expected to be very brief.
scanning heapVACUUM is currently scanning the heap. It will prune and defragment each page if required, and possibly perform freezing activity. Theheap_blks_scanned column can be used to monitor the progress of the scan.
vacuuming indexesVACUUM is currently vacuuming the indexes. If a table has any indexes, this will happen at least once per vacuum, after the heap has been completely scanned. It may happen multiple times per vacuum if maintenance_work_mem is insufficient to store the number of dead tuples found.
vacuuming heapVACUUM is currently vacuuming the heap. Vacuuming the heap is distinct from scanning the heap, and occurs after each instance of vacuuming indexes. If heap_blks_scanned is less than heap_blks_total, the system will return to scanning the heap after this phase is completed; otherwise, it will begin cleaning up indexes after this phase is completed.
cleaning up indexesVACUUM is currently cleaning up indexes. This occurs after the heap has been completely scanned and all vacuuming of the indexes and the heap has been completed.
truncating heapVACUUM is currently truncating the heap so as to return empty pages at the end of the relation to the operating system. This occurs after cleaning up indexes.
performing final cleanupVACUUM is performing final cleanup. During this phase, VACUUM will vacuum the free space map, update statistics in pg_class, and report statistics to the statistics collector. When this phase is completed, VACUUM will end.
备注:接下来通过一个简单的实验查看VACUUM后台进程的阶段。

--创建测试表

francs=> create table test_big2(id int4,name character varying(64),create_time timestamp(0) without time zone default clock_timestamp());
CREATE TABLE

francs=> insert into test_big2(id,name) select n, n||'_vacuum' from generate_series(1,5000000) n;
INSERT 0 5000000

备注:创建一张 500 万记录的测试表。

--会话一: VACUUM 表 test_big2

francs=> vacuum analyze test_big2;
VACUUM 过程中


--会话二:监控 VACUUM

postgres=# select * from pg_stat_progress_vacuum ;
(0 rows)

postgres=# \watch 1
Fri May 27 16:36:05 2016 (every 1s)

-[ RECORD 1 ]------+--------------
pid                | 19427
datid              | 16386
datname            | francs
relid              | 16445
phase              | scanning heap
heap_blks_total    | 34478
heap_blks_scanned  | 1712
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples    | 291
num_dead_tuples    | 0

省略。。。
Fri May 27 16:36:33 2016 (every 1s)

-[ RECORD 1 ]------+--------------
pid                | 19427
datid              | 16386
datname            | francs
relid              | 16445
phase              | scanning heap
heap_blks_total    | 34478
heap_blks_scanned  | 31612
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples    | 291
num_dead_tuples    | 0

备注:只看到 VACUUM 进程处于 scanning heap 状态,并且 heap_blks_scanned 一直在涨直到VAUUM结束,通过这个字段和 heap_blks_total 可以估算 VACUUM 进程的进度。

--会话一:删除一部分记录再执行 VACUUM

francs=> create index idx_test_big2_id on test_big2 using btree (id);
CREATE INDEX

francs=> delete from test_big2 where id > 4000000;
DELETE 1000000

francs=> vacuum analyze test_big2;
VACUUM 进程执行过程中,表越大,VACUUM耗时越长。

--会话二:监控 VACUUM

postgres=# select * from pg_stat_progress_vacuum ;
(0 rows)

postgres=# \watch 2
Fri May 27 16:39:46 2016 (every 2s)

-[ RECORD 1 ]------+------------------
pid                | 19427
datid              | 16386
datname            | francs
relid              | 16445
phase              | vacuuming indexes
heap_blks_total    | 34478
heap_blks_scanned  | 34478
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples    | 10033098
num_dead_tuples    | 1000000

Fri May 27 16:39:49 2016 (every 2s)

-[ RECORD 1 ]------+--------------------
pid                | 19427
datid              | 16386
datname            | francs
relid              | 16445
phase              | cleaning up indexes
heap_blks_total    | 34478
heap_blks_scanned  | 34478
heap_blks_vacuumed | 34478
index_vacuum_count | 1
max_dead_tuples    | 10033098
num_dead_tuples    | 1000000

备注:删除索引后执行VACUUM过程中,可以查看到 phase 开始处于 vacuuming indexes 阶段,之后进入  cleaning up indexes 阶段,并且 num_dead_tuples 为 100 万。

--参考
对于数据量较大的生产库,如果开启了 autovacuum,通常大表的 VACUUM 周期较长,之前几乎无法判断表级别的 VACUM 进程进度,9.6 版本pg_stat_progress_vacuum 视图的出现可以监控表级别 VACUUM  进程的进度,对于 VACUUM 的监控提供了较大的方便。

--参考


  评论这张
 
阅读(169)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016