Deep dive into Postgres stats: pg_stat_progress_vacuum
Yet another post about vacuum…
In today’s post I would like to talk about another stats enhancement that has been added in 9.6 and I hope will be expanded in the upcoming versions. The post is about pg_stat_progress_vacuum.
Everyone heard about vacuum (I will be referring to both the regular vacuum and autovacuum in this post), even those who are not familiar with Postgres very well. Vacuum is surrounded by many legends and horror stories, thousands discussions and topics about vacuum and its configurations have been discussed in the mailing lists, forums and blogs. But let’s leave this aside for a minute, since vacuum internals and vacuum configuration can be a subject for a separate post.
What about stats? Since vacuum was invented, there were two key questions DBAs were trying to answer. The first is «How much work is actually done by the vacuum?» and the second — «When will vacuum finish working»? For many years, DBAs came up with different ways to answer those questions. In general, that were various scripts that guessed how much work has been done using various symptoms and signs. Using postgres stat it was possible to count the number of vacuum workers, their execution time but nothing more. The one thing that helped to understand specifics of running a vacuum is the verbose mode that was issuing a regular VACUUM command. Using it, DBA was able to set VERBOSE flag and get a more detailed information about vacuum progress. Also, autovacuum workers were able to save post factum information in postgres logs, but only after the table was vacuumed.
Things became much clearer in PostgreSQL 9.6 with an introduction of pg_stat_progress_vacuum. Using this view it is finally now possible to get a quick overview among running vacuum tasks. Here is an example output of pg_stat_progress_vacuum:
# SELECT * FROM pg_stat_progress_vacuum;
-[ RECORD 1 ]-------+-------------- pid | 104701 datid | 16419 datname | analytics relid | 1911284001 phase | scanning heap heap_blks_total | 155738368 heap_blks_scanned | 75619358 heap_blks_vacuumed | 74784601 index_vacuum_count | 6 max_dead_tuples | 178956970 num_dead_tuples | 32346941
Detailed description of pg_stat_progress_vacuum is available in the official documentation, but I would like to give a short overview of its most interesting features.
The most obvious thing, of course, is the process ID of running a vacuum, a database where it takes place and vacuumed relation. This information can be obtained from the previous version of Postgres using pg_stat_activity. Here are a few additional useful columns:
phase — internally, vacuum has several stages and this field shows the exact phase it is in at the moment.
heap_blks_total, heap_blks_scanned, heap_blks_vacuumed — are about relation’s size (in blocks) and how many blocks have already been processed. These are the main values that help to estimate progress of the running a vacuum.
max_dead_tuples, num_dead_tuples — these values are about dead tuples storage which is based on the autovacuum_work_mem (or maintenance_work_mem if previous isn’t set). When there is no free space in the dead tuples storage, vacuum starts processing indexes and it might happen multiple times. Each time when this occurs, the index_vacuum_count is incremented.
So it’s great, until 9.6 we just had pg_stat_activity and now stats interface extended with pg_stat_progress_vacuum. But IMO default values from pg_stat_progress_vacuum are a bit boring. In the example above, you can see big numbers that we need to interpret and go through some mental work to really understand what’s going on there. Let’s add some spice from other views and functions and make pg_stat_progress_vacuum’s output a little bit more exciting.
First of all, let’s replace relation ID with relation name and translate blocks to bytes. Second, let’s add the field which shows table’s size including indexes, because vacuum processes include not only the table, but its indexes too — so it’s useful to see the total amount of the assumed work. Also, it is useful to join pg_stat_activity and get some details from there — duration of the running vacuum, wait events that help us in cases when vacuum is stuck and information about type of vacuum — whether is it a regular autovacuum or a wraparound or whether it is run by the user? Information about how much blocks were scanned and vacuumed and replaced with human-convenient bytes and percents — it’s much better than raw bigints. Numbers of dead tuples can be replaced with percents as well.
So final query has a bit more fields but, at the same time, it’s more informative than the default content of pg_stat_progress_vacuum.
# 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;
-[ RECORD 1 ]-------+------------------ pid | 104701 duration | 03:21:51.330818 waiting | f mode | regular database | analytics table | events phase | vacuuming indexes table_size | 1188 GB total_size | 1682 GB scanned | 601 GB vacuumed | 571 GB scanned_pct | 50.0 vacuumed_pct | 48.0 index_vacuum_count | 6 dead_tup_pct | 100.0
This is a real-life example of query’s output from one of our customers. There is a long running vacuum which worked for more than 3 hours, and didn’t make even half of the work. Something like this makes any DBA wanting to walk over to a coffee machine or revise autovacuum settings.
New stats look useful, but a seasoned DBA will probably spot a dark corner in the «vacuuming indexes» phase. In this phase, table’s indexes are being processed instead of the table itself and pg_stat_progress_vacuum holds no information about which index is vacuumed and how many blocks have already been processed. Also it should be noted that pg_stat_progress_vacuum doesn’t support VACUUM FULL operations.
So, though postgres stats became more informative, there is work still ongoing to create additional pg_stat_progress views, such as pg_stat_progress_analyze and pg_stat_progress_cluster and this is really awesome.
Which stats view would you like me to discuss in my next post?
Let me know!
