{"id":2997,"date":"2016-01-19T01:20:29","date_gmt":"2016-01-19T01:20:29","guid":{"rendered":"http:\/\/www.garysieling.com\/blog\/?p=2997"},"modified":"2016-01-19T01:20:29","modified_gmt":"2016-01-19T01:20:29","slug":"regular-expression-to-match-postgres-vacuum-log-statements","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/regular-expression-to-match-postgres-vacuum-log-statements\/","title":{"rendered":"Regular Expression to match Postgres Vacuum log statements"},"content":{"rendered":"<p>You can get log entries for every vacuum in Postgres by enabling this setting in postgresql.conf:<\/p>\n<pre>\nlog_autovacuum_min_duration=0\n<\/pre>\n<p>Obviously you may prefer to increase the timing too, if you get a lot of entries. This is what the log entries look like:<\/p>\n<pre>\nautomatic vacuum of table \"db.pg_catalog.pg_class\": index scans: 1\n\tpages: 0 removed, 143 remain\n\ttuples: 204 removed, 4498 remain\n\tbuffer usage: 463 hits, 87 misses, 131 dirtied\n\tavg read rate: 1.654 MB\/s, avg write rate: 2.490 MB\/s\n\tsystem usage: CPU 0.00s\/0.00u sec elapsed 0.41 sec\n<\/pre>\n<p>For monitoring purposes it is helpful to extract all these values. In testing this, I found that &#8220;system usage&#8221; was not always reported, so there are actually two regexes:<\/p>\n<pre lang=\"regex\">\nautomatic vacuum of table \"(?P&lt;vacuum_table_database>[^\"]*)\\.\n(?P&lt;vacuum_table_schema&gt;[^\"]*)\\.(?P&lt;vacuum_table_name&gt'[^\"]*)\": \nindex scans: (?P&lt;vacuum_index_scans&gt;\\d*).*pages: \n(?P&lt;vacuum_pages_removed&gt;\\d*) removed, \n(?P&lt;vacuum_pages_remain&gt;\\d*) remain.*tuples: \n(?P&lt;vacuum_tuples_removed&gt;\\d*) removed, \n(?P&lt;vacuum_tuples_remain&gt;\\d*) remain.*buffer usage: \n(?P&lt;vacuum_buffer_usage_hits&gt;\\d*) hits, \n(?P&lt;vacuum_buffer_usage_misses&gt;\\d*) misses, \n(?P&lt;vacuum_buffer_usage_dirtied&gt;\\d*) dirtied.*\navg read rate: (?P&lt;vacuum_avg_read_rate&gt;[0-9.]*) \n(?P&lt;vacuum_read_rate_units&gt;[^,]*), \navg write rate: (?P&lt;vacuum_avg_write_rate&gt;[0-9.]*) \n(?P&lt;vacuum_write_rate_units&gt;[^,]*)\t\n<\/pre>\n<p>And:<\/p>\n<pre lang=\"regex\">\n.*system usage: CPU (?P&lt;vacuum_cpu_seconds&gt;[0-9.]*)s.\n(?P&lt;vacuum_cpu_time&gt;[0-9.]*)u sec elapsed \n(?P&lt;vacuum_elapsed_time&gt;[0-9.]*) \n(?P&lt;vacuum_cpu_elapsed_unit&gt;.*)\t\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8220;db.pg_catalog.pg_class&#8221;: index scans: 1 pages: 0 removed, 143 remain tuples: &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.garysieling.com\/blog\/regular-expression-to-match-postgres-vacuum-log-statements\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Regular Expression to match Postgres Vacuum log statements&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[4],"tags":[186,370,437,522,565],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/2997"}],"collection":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/comments?post=2997"}],"version-history":[{"count":0,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/2997\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/media?parent=2997"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/categories?post=2997"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/tags?post=2997"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}