From 45e7088b16821cd44ed5e604858eddb2ba3c67de Mon Sep 17 00:00:00 2001 From: Greg Sabino Mullane Date: Fri, 18 Jul 2008 17:44:42 -0400 Subject: [PATCH] Add autovac_freeze action, bump to version 2.1.0 --- check_postgres.pl | 119 +++++++++++++++++++++++++++++++++++++++-- check_postgres.pl.asc | 6 +-- check_postgres.pl.html | 37 +++++++++++-- index.html | 6 +-- t/99_spellcheck.t | 1 + 5 files changed, 156 insertions(+), 13 deletions(-) diff --git a/check_postgres.pl b/check_postgres.pl index c9c5f5b7d..d25c8cc7e 100755 --- a/check_postgres.pl +++ b/check_postgres.pl @@ -28,7 +28,7 @@ $Data::Dumper::Varname = 'POSTGRES'; $Data::Dumper::Indent = 2; $Data::Dumper::Useqq = 1; -our $VERSION = '2.0.1'; +our $VERSION = '2.1.0'; use vars qw/ %opt $PSQL $res $COM $SQL $db /; @@ -168,6 +168,7 @@ if ($opt{version}) { ## Quick hash to put normal action information in one place: our $action_info = { # Name # clusterwide? # helpstring + autovac_freeze => [1, 'Checks how close databases are to autovacuum_freeze_max_age.'], backends => [1, 'Number of connections, compared to max_connections.'], bloat => [0, 'Check for table and index bloat.'], connection => [0, 'Simple connection check.'], @@ -462,6 +463,7 @@ our $checksumre = qr{^[a-f0-9]{32}$}; ## If in test mode, verify that we can run each requested action our %testaction = ( + autovac_freeze => 'VERSION: 8.2', last_vacuum => 'ON: stats_row_level(<8.3) VERSION: 8.2', last_analyze => 'ON: stats_row_level(<8.3) VERSION: 8.2', last_autovacuum => 'ON: stats_row_level(<8.3) VERSION: 8.2', @@ -653,6 +655,9 @@ check_custom_query() if $action eq 'custom_query'; ## Test of replication check_replicate_row() if $action eq 'replicate_row'; +## See how close we are to autovacuum_freeze_max_age +check_autovac_freeze() if $action eq 'autovac_freeze'; + finishup(); exit 0; @@ -1184,6 +1189,18 @@ sub validate_range { my $regex = ($string =~ s/^~//) ? '~' : '='; $string =~ /^\w+$/ or die qq{Invalid option\n}; } + elsif ('percent' eq $type) { + if (length $critical) { + if ($critical !~ /^\d+\%$/) { + ndie qq{Invalid 'critical' option: must be percentage\n}; + } + } + if (length $warning) { + if ($warning !~ /^\d+\%$/) { + ndie qq{Invalid 'warning' option: must be percentage\n}; + } + } + } elsif ('size or percent' eq $type) { if (length $critical) { if ($critical =~ $sizere) { @@ -1267,6 +1284,79 @@ sub validate_range { } ## end of validate_range +sub check_autovac_freeze { + + ## Check how close all databases are to autovacuum_freeze_max_age + ## Supports: Nagios, MRTG + ## It makes no sense to run this more than once on the same cluster + ## Warning and criticals are percentages + ## Can also ignore databases with exclude, and limit with include + + my ($warning, $critical) = validate_range + ({ + type => 'percent', + default_warning => '90%', + default_critical => '95%', + forcemrtg => 1, + }); + + (my $w = $warning) =~ s/\D//; + (my $c = $critical) =~ s/\D//; + my $SQL = q{SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname}. + q{ FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname}. + q{ FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo}. + q{ ON (true)) AS foo2 ORDER BY 3 DESC, 4 ASC}; + my $info = run_command($SQL, {regex => qr[\w+] } ); + + for $db (@{$info->{db}}) { + my (@crit,@warn,@ok); + my ($maxp,$maxt,$maxdb) = (0,0,''); ## used by MRTG only + SLURP: while ($db->{slurp} =~ /\s*(\d+) \|\s+(\d+) \|\s+(\d+) \| (.+?)$/gsm) { + my ($freeze,$age,$percent,$dbname) = ($1,$2,$3,$4); + next SLURP if skip_item($dbname); + + if ($MRTG) { + if ($percent > $maxp) { + $maxdb = $dbname; + } + elsif ($percent == $maxp) { + $maxdb .= sprintf "%s$dbname", length $maxdb ? ' | ' : ''; + } + $maxt = $age if $age > $maxt; + next; + } + + my $msg = "$dbname=$percent\% ($age)"; + $db->{perf} .= " $msg"; + if (length $critical and $percent >= $c) { + push @crit => $msg; + } + elsif (length $warning and $percent >= $w) { + push @warn => $msg; + } + else { + push @ok => $msg; + } + } + if ($MRTG) { + do_mrtg({one => $maxp, two => $maxt, msg => $maxdb}); + } + if (@crit) { + add_critical join ' ' => @crit; + } + elsif (@warn) { + add_warning join ' ' => @warn; + } + else { + add_ok join ' ' => @ok; + } + } + + return; + +} ## end of check_autovac_freeze + + sub check_backends { ## Check the number of connections @@ -1277,8 +1367,8 @@ sub check_backends { ## critical = 12 -- complain if there are 12 or more connections ## critical = 95% -- complain if >= 95% of available connections are used ## critical = -5 -- complain if there are only 5 or fewer connection slots left - ## Can also ignore databases with exclude, and limit with include ## The former two options only work with simple numbers - no percentage or negative + ## Can also ignore databases with exclude, and limit with include my $warning = $opt{warning} || '90%'; my $critical = $opt{critical} || '95%'; @@ -1383,7 +1473,6 @@ sub check_backends { } ## end of check_backends - sub check_bloat { ## Check how bloated the tables and indexes are @@ -3172,7 +3261,7 @@ check_postgres.pl - Postgres monitoring script for Nagios, MRTG, and others =head1 VERSION -This documents describes B version 2.0.1 +This documents describes B version 2.1.0 =head1 SYNOPSIS @@ -3433,6 +3522,24 @@ The current supported actions are: =over 4 +=item B (symlink: C) + +Checks how close each database is to the Postgres B setting. This +action will only work for databases version 8.2 or higher. The I<--warning> and +I<--critical> options should be expressed as percentages. The 'age' of the transactions +in each database is compared to the autovacuum_freeze_max_age setting (200 million by default) +to generate a rounded percentage. The default values are B<90%> for the warning and B<95%> for +the critical. Databases can be filtered by use of the I<--include> and I<--exclude> options. See +the L section for more details. + +Example 1: Give a warning when any databases on port 5432 are above 80% + + check_postgres_autovac_freeze --port=5432 --warning="80%" + +For MRTG output, the highest overall percentage is reported on the first line, and the highest age is +reported on the second line. All databases which have the percentage from the first line are reported +on the fourth line, separated by a pipe symbol. + =item B (symlink: C) Checks the current number of connections for one or more databases, and optionally @@ -4220,6 +4327,10 @@ Items not specifically attributed are by Greg Sabino Mullane. =over 4 +=item B (July 18, 2008) + +Add the "autovac_freeze" action, thanks to Robert Treat for the idea and design. + =item B (July 16, 2008) Optimizations to speed up the "bloat" action quite a bit. diff --git a/check_postgres.pl.asc b/check_postgres.pl.asc index b8178a866..b65d222b4 100644 --- a/check_postgres.pl.asc +++ b/check_postgres.pl.asc @@ -1,6 +1,6 @@ -----BEGIN PGP SIGNATURE----- -iEYEABEDAAYFAkh+dREACgkQvJuQZxSWSsgZsACg4VDg4TBfOnb93R5xs/M+0OaI -Ai4AoICb2on+HJJVHhiltRcyZqvQ5PpZ -=jv+D +iEYEABEDAAYFAkiBDsEACgkQvJuQZxSWSsjMrwCgow2P033dfXcilqQsg6aPZJ/N +9fYAn0CkMZfaFsxuenwLohnhFEZl8vef +=8qMi -----END PGP SIGNATURE----- diff --git a/check_postgres.pl.html b/check_postgres.pl.html index f80ae1bc0..0ba153e06 100644 --- a/check_postgres.pl.html +++ b/check_postgres.pl.html @@ -63,7 +63,7 @@

VERSION

-

This documents describes check_postgres.pl version 2.0.1

+

This documents describes check_postgres.pl version 2.1.0

@@ -351,6 +351,30 @@ because criticals are always checked first, setting the warning equal to the critical is an effective way to turn warnings off and always give a critical.

The current supported actions are:

+
autovac_freeze (symlink: check_postgres_autovac_freeze) + +
+

Checks how close each database is to the Postgres autovacuum_freeze_max_age setting. This +action will only work for databases version 8.2 or higher. The --warning and +--critical options should be expressed as percentages. The 'age' of the transactions +in each database is compared to the autovacuum_freeze_max_age setting (200 million by default) +to generate a rounded percentage. The default values are 90% for the warning and 95% for +the critical. Databases can be filtered by use of the --include and --exclude options. See +the BASIC FILTERING section for more details.

+
+
+

Example 1: Give a warning when any databases on port 5432 are above 80%

+
+
+
+  check_postgres_autovac_freeze --port=5432 --warning="80%"
+
+
+

For MRTG output, the highest overall percentage is reported on the first line, and the highest age is +reported on the second line. All databases which have the percentage from the first line are reported +on the fourth line, separated by a pipe symbol.

+
+
backends (symlink: check_postgres_backends)
@@ -1343,13 +1367,20 @@ feature requests, and commit notices, send email to HISTORY

Items not specifically attributed are by Greg Sabino Mullane.

+
Version 2.1.0 (July 18, 2008) + +
+

Add the "autovac_freeze" action, thanks to Robert Treat for the idea and design.

+
+
Version 2.0.1 (July 16, 2008)
-

Optimizations to speed up the "bloat" action quite a bit.

+

Optimizations to speed up the "bloat" action quite a bit. +Fix "version" action to not always output in mrtg mode.

-
Version 2.0.0 (July 15, 2008) +
Version 2.0.0 (July 15, 2008)

Add support for MRTG and "simple" output options. diff --git a/index.html b/index.html index 25207488c..2f6825d84 100644 --- a/index.html +++ b/index.html @@ -21,13 +21,13 @@ h1 {

check_postgres.pl

-

check_postgres.pl is a script for checking the state of one or more Postgres databases and reporting back in a Nagios-friendly manner. It was developed by Greg Sabino Mullane of End Point Corporation and is BSD-licensed. The latest version is 2.0.1, and was released on July 16, 2008.

+

check_postgres.pl is a script for checking the state of one or more Postgres databases and reporting back in a Nagios-friendly manner. It was developed by Greg Sabino Mullane of End Point Corporation and is BSD-licensed. The latest version is 2.1.0, and was released on July 18, 2008.

diff --git a/t/99_spellcheck.t b/t/99_spellcheck.t index b5fce26e5..6c2e2d5a1 100644 --- a/t/99_spellcheck.t +++ b/t/99_spellcheck.t @@ -177,6 +177,7 @@ xmlns artemus Astill AUTOanalyze +autovac autovacuum AUTOvacuum backends -- 2.39.5