From 0c5abeb90c4d2d185163cbe094e8a494eb8ee314 Mon Sep 17 00:00:00 2001
From: Guillaume Lelarge <guillaume@lelarge.info>
Date: Thu, 17 Jan 2013 16:33:29 +0100
Subject: [PATCH] New autovacuum hourly report and its graph

---
 pgbadger | 39 ++++++++++++++++++++++++++++++++++++++-
 1 file changed, 38 insertions(+), 1 deletion(-)

diff --git a/pgbadger b/pgbadger
index 0a4ea7e..a6de8f1 100755
--- a/pgbadger
+++ b/pgbadger
@@ -1842,7 +1842,7 @@ div#littleToc a:hover { text-decoration:none; background-color:#DDDDDD; }
 .sql .nu0 {color: #cc66cc;}
 .sql span.xtra { display:block; }
 
-#queriespersecond_graph, #connectionspersecond_graph, #allqueries_graph, #checkpointwritebuffers_graph, #checkpointfiles_graph, #temporaryfile_graph, #selectqueries_graph, #writequeries_graph {
+#queriespersecond_graph, #connectionspersecond_graph, #allqueries_graph, #checkpointwritebuffers_graph, #checkpointfiles_graph, #temporaryfile_graph, #autovacuum_graph, #selectqueries_graph, #writequeries_graph {
 	width : 1025px;
 	height: 400px;
 	background:#F3F2ED;
@@ -2470,6 +2470,9 @@ sub dump_as_html
 			if (exists $checkpoint_info{warning}) {
 				print $fh qq{<th colspan="2">Checkpoint warning</th>};
 			}
+			if (exists $autovacuum_info{chronos}) {
+				print $fh "		<th colspan=\"2\">Autovacuum</th>\n";
+			}
 			if ($tempfile_info{count} || exists $checkpoint_info{chronos}) {
 				print $fh qq{</tr><tr>};
 			}
@@ -2483,6 +2486,9 @@ qq{<th>Wrote buffers</th><th>Added</th><th>Removed</th><th>Recycled</th><th>Writ
 			if (exists $checkpoint_info{warning}) {
 				print $fh qq{<th>Count</th><th>Av. time (sec)</th>};
 			}
+			if (exists $autovacuum_info{chronos}) {
+				print $fh "		<th>VACUUMs</th><th>ANALYZEs</th>\n";
+			}
 			if ($tempfile_info{count} || exists $checkpoint_info{chronos}) {
 				print $fh qq{</tr>};
 				foreach my $d (sort {$a <=> $b} keys %per_hour_info) {
@@ -2532,6 +2538,10 @@ qq{<th>Wrote buffers</th><th>Added</th><th>Removed</th><th>Recycled</th><th>Writ
 								}
 							}
 						}
+						if (exists $autovacuum_info{chronos}) {
+							print $fh "<td class=\"right\">", &comma_numbers($autovacuum_info{chronos}{"$d"}{"$h"}{count} || 0), "</td>",
+							          "<td class=\"right\">", &comma_numbers($autoanalyze_info{chronos}{"$d"}{"$h"}{count} || 0), "</td>";
+						}
 						print $fh "</tr>\n";
 						$c++;
 					}
@@ -2611,6 +2621,31 @@ qq{<th>Wrote buffers</th><th>Added</th><th>Removed</th><th>Recycled</th><th>Writ
 				$d2 = '';
 			}
 
+			# VACUUMs and ANALYZEs
+			if (exists $autovacuum_info{chronos}) {
+				foreach my $tm (sort {$a <=> $b} keys %{$autovacuum_info{chronos}}) {
+					$tm =~ /(\d{4})(\d{2})(\d{2})/;
+					my $y  = $1 - 1900;
+					my $mo = $2 - 1;
+					my $d  = $3;
+					foreach my $h ("00" .. "23") {
+						my $t = timegm_nocheck(0, 0, $h, $d, $mo, $y) * 1000;
+						next if ($t < $t_min_hour);
+						last if ($t > $t_max_hour);
+						$d1 .= "[$t, " . ($autovacuum_info{chronos}{$tm}{$h}{count}  || 0) . "],";
+						$d2 .= "[$t, " . ($autoanalyze_info{chronos}{$tm}{$h}{count} || 0) . "],";
+					}
+				}
+				$d1 =~ s/,$//;
+				$d2 =~ s/,$//;
+				&flotr2_graph(
+					9, 'autovacuum_graph', $d1, $d2, '', 'Autovacuum actions',
+					'', 'VACUUMs', 'ANALYZEs'
+				);
+				$d1 = '';
+				$d2 = '';
+			}
+
 			# VACUUM stats per table
 			if ($autovacuum_info{count} > 0) {
 				print $fh qq{
@@ -4278,6 +4313,7 @@ sub parse_query
 		$autovacuum_info{count}++;
 		$autovacuum_info{tables}{$1}{vacuums} += 1;
 		$autovacuum_info{tables}{$1}{idxscans} += $2;
+		$autovacuum_info{chronos}{$date_part}{$prefix_vars{'t_hour'}}{count}++;
 	}
 	if (
 		($prefix_vars{'t_loglevel'} eq 'LOG')
@@ -4289,6 +4325,7 @@ sub parse_query
 		return if ($disable_autovacuum);
 		$autoanalyze_info{count}++;
 		$autoanalyze_info{tables}{$1}{analyzes} += 1;
+		$autoanalyze_info{chronos}{$date_part}{$prefix_vars{'t_hour'}}{count}++;
 	}
 
 	# Store checkpoint information
-- 
2.40.0