Gary Sieling

Regular Expression to match Postgres Vacuum log statements

You can get log entries for every vacuum in Postgres by enabling this setting in postgresql.conf:

log_autovacuum_min_duration=0

Obviously you may prefer to increase the timing too, if you get a lot of entries. This is what the log entries look like:

automatic vacuum of table "db.pg_catalog.pg_class": index scans: 1
	pages: 0 removed, 143 remain
	tuples: 204 removed, 4498 remain
	buffer usage: 463 hits, 87 misses, 131 dirtied
	avg read rate: 1.654 MB/s, avg write rate: 2.490 MB/s
	system usage: CPU 0.00s/0.00u sec elapsed 0.41 sec

For monitoring purposes it is helpful to extract all these values. In testing this, I found that “system usage” was not always reported, so there are actually two regexes:

automatic vacuum of table "(?P<vacuum_table_database>[^"]*)\.
(?P<vacuum_table_schema>[^"]*)\.(?P<vacuum_table_name&gt'[^"]*)": 
index scans: (?P<vacuum_index_scans>\d*).*pages: 
(?P<vacuum_pages_removed>\d*) removed, 
(?P<vacuum_pages_remain>\d*) remain.*tuples: 
(?P<vacuum_tuples_removed>\d*) removed, 
(?P<vacuum_tuples_remain>\d*) remain.*buffer usage: 
(?P<vacuum_buffer_usage_hits>\d*) hits, 
(?P<vacuum_buffer_usage_misses>\d*) misses, 
(?P<vacuum_buffer_usage_dirtied>\d*) dirtied.*
avg read rate: (?P<vacuum_avg_read_rate>[0-9.]*) 
(?P<vacuum_read_rate_units>[^,]*), 
avg write rate: (?P<vacuum_avg_write_rate>[0-9.]*) 
(?P<vacuum_write_rate_units>[^,]*)	

And:

.*system usage: CPU (?P<vacuum_cpu_seconds>[0-9.]*)s.
(?P<vacuum_cpu_time>[0-9.]*)u sec elapsed 
(?P<vacuum_elapsed_time>[0-9.]*) 
(?P<vacuum_cpu_elapsed_unit>.*)	
Exit mobile version