-hackers,
I'm working on a vacuum verbose log analyzer for a few weeks now. My
goal is to allow a more systematic study of this log output to help
improve the vacuum strategy.
To do so, I spent a few hours studying the vacuum code and especially
the way it logs what it's doing.
What surprises me is:
* the inconsistency between the display of the duration for lazy
vacuum and vacuum full (especially the index cleanup included in the
former case and not in the latter)
* the fact that there are quite a lot of work done outside of the
duration calculated and displayed in the log (the
vacuum_heap/repair_frag step for example).
The consequence is that it's really hard to figure out how much time
was elapsed for each table.
Moreover in the case of a vacuum analyze, the analyze operation is not
included at all in the time elapsed and we can't know how long it was
for each table.
I attached an analysis on how it works currently.
I'd like to be able to find in the log the time really elapsed for
each operation.
I'll probably be able to write a patch for this but I'd like to know
if anybody else think it's worth it and if anybody has advice/ideas on
how to do it nicely.
Regards,
--
Guillaume
vacuum
for each relation
vacuum_rel
full_vacuum_rel (if vacuum full)
scan_heap
pg_rusage_init(&ru0);
ereport(vacuuming tableX);
vacuum the table
ereport(all information + pg_rusage_show(&ru0))
for each index
vacuum_index
pg_rusage_init(&ru0);
clean the index
ereport(all information + pg_rusage_show(&ru0))
check for tuple count mismatch
vacuum_heap or repair_frag
update free space map
update statistics in pg_class
report results to the stat collector
lazy_vacuum_rel (if vacuum)
open the indexes
lazy_scan_heap
pg_rusage_init(&ru0);
ereport(vacuuming tableX);
for each index
lazy_vacuum_index
pg_rusage_init(&ru0);
clean the index
ereport(all information + pg_rusage_show(&ru0))
lazy_vacuum_heap
ereport(all information + pg_rusage_show(&ru0))
close the indexes
optionnaly truncate the relation
update free space map
update statistics in pg_class
report results to the stat collector
vacuum_rel(toast table if any)
same as above
analyze_rel (if analyze)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
