SQL|如何查看VACUUM的进度

（一）背景

PG运维老师咨询过这样的一个问题：PG生产运维默认开启auto_vaccum参数，通过pg_stat_activity常常看到有vacuum table的行为，那我怎么样才能知道当前进程的vacuum执行到哪里了呢？完成了百分之多少？

（二）参考

9.6及其以上版本推荐SQL：

SELECT
p.pid,
now() - a.xact_start AS duration,
coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
CASE
WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
WHEN a.query ~*'^vacuum' THEN 'user'
ELSE 'regular'
END AS mode,
p.datname AS database,
p.relid::regclass AS table,
p.phase,
pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
p.index_vacuum_count,
round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct 
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid)
ORDER BY now() - a.xact_start DESC;

为了方便查看psql时我们使用\x实现每行显示一列，输出展示：

SQL说明：

pid: 进程号

duration: 事务执行了多长时间

waiting: 等待事件类型+等待事件，本案例为IO类等待事件DataFileRead读数据文件

mode: 如果是用户手动发起vacuum显示user,如果是后台自动autovacuum（参数autovacuum=on）则显示为wraparound

database: 当前数据库名

table: 执行清理的表名

phase: 当前执行清理的阶段

table_size: 扫描开始时记录的表大小，pg是以堆表形式存放数据的，pg_size_pretty(p.heap_blks_total * current_setting(‘block_size’)::int) AS table_size中的heap_blks_total表示表中堆块的总数，block_size为当前pg块的大小，该参数是在initdb初始化时指定的。

total_size: 总大小pg_total_relation_size(单位字节bytes)，等于表大小+索引大小。如下图

scanned: 来自字段heap_blks_scanned:被扫描的堆块数量，在scanning heap阶段计数。我们知道PG可见性映射VM的设计中，一些不需要vacuum的块将被跳过不做检查从而提高效率，但这里被跳过的块也会被记录到总数里，因此当清理完成时这个数字最终将会等于heap_blks_total。为了方便查看，我们将其乘以block_size块大小，换算成我们更容易理解的KB、MB、GB等单位。

vacuumed: 来自字段heap_blks_vacuumed:被清理的堆块数量，表没有索引的情况下，仅在vacuuming heap阶段计数，有索引则加上索引清理环节。注意：如果不包含死元组(标记为需要删除的行)的块会被跳过，因此这个计数器可能有时会向前跳跃一个比较大的增量。

scanned_pct: scanned进度(百分比)

vacuumed_pct: vacuum进度(百分比)。

index_vacuum_count: 已完成的索引清理周期数

dead_pct: 死元组的清理进度(百分比)

（三）延伸

通过上述的SQL，我们可以监控vacuum执行到哪里了，还差多少完成等等，做到心里有数。下面，我们来看看具体每个阶段执行的内容说明。

vaccum各阶段：

通过vacuum的各个阶段，我们可以总结出：

1）vacuum除了清理表，同时还会清理索引(移除指向标记为删除的行[死元组]的索引行[索引元组])。

2）如果堆表尾部存在空页可以返回给操作系统，也就是页截断(truncating heap),注意仅当尾部空闲空间至少占表的 1/16 大小或已达到 1000 页的长度时才执行截断，那普通vaccum清理后大小是存在减少的情况的。

3）vacuum清理后，会清理FSM，更新统计信息并上报。

注意:vacuum full是不支持通过该视图查看到进行进度的。

后话：

如果有任何需要指正/指教/交流的，可以关注微信公众号《PostgreSQL手记》后台评论，大家一起学习~