(一)背景
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手记》后台评论,大家一起学习~