Traverses all integer columns checking for capacity limits.
#!/usr/bin/env perl #use 5.010; #use strict; #use warnings; # Richard L. Byrd, written sometime in the 90s and improved bit by bit over the next 20 years # # 2009-01-27: While originally written to take a configfile input on the CLI (--configfile=xxxxx.cfg) I've hacked # that out of this version. The only CLI parameteres required are host, username, password. Hardcoded warning to # 70% and critical to 85%. use utf8; use DBI; use Getopt::Long; binmode STDOUT, ':encoding(UTF-8)'; our $VERSION = 3; GetOptions( 'verbosity=i' => \(my $verbosity), 'critical=f' => \(my $critical), 'warning=f' => \(my $warning), 'dbhost=s' => \(my $dbhost), 'dbuser=s' => \(my $dbuser), 'dbpass=s' => \(my $dbpass), 'configfile=s' => \(my $configfile), ); my %config; if ($configfile) { open my $IN, '<:encoding(UTF-8)', $configfile or die "Cannot open '$configfile' for reading: $!"; while (<$IN>) { chomp; next unless /\S/; next if /^\s*#/; if (/^\s*(\w+)\s*=\s*(\S(?:.*\S)?)\s*\z/) { $config{$1} = $2; } else { die qq[Cannot parse line "$_" in config file "$configfile" (should be: key=value)\n]; } } } $verbosity = $config{verbosity}; # 0; $warning = $config{warning} ; # 0.7; $critical = $config{critical} ; # 0.85; $dbhost = $ARGV[0] ; # 'localhost'; $dbuser = $ARGV[1] ; # 'root'; $dbpass = $ARGV[2] ; $warning=0.2; $critical=0.4; my %max = ( unsigned_bigint => 18446744073709551615, unsigned_int => 4294967295, unsigned_integer => 4294967295, unsigned_smallint => 65535, unsigned_tinyint => 255, unsigned_mediumint => 16777215, signed_bigint => 9223372036854775807, signed_int => 2147483647, signed_integer => 2147483647, signed_smallint => 32767, signed_tinyint => 127, signed_mediumint => 8388607, ); my $dbh = DBI->connect("dbi:mysql:datatbase=mysql;host=$dbhost", $dbuser, $dbpass, {RaiseError => 1}); my $ai = $dbh->prepare(<execute; my $has_warnings = 0; my $has_critical = 0; my $db = ''; my $max_record; while (my ($catalog, $database, $table, $column, $type, $auto_increment, $column_type) = $ai->fetchrow_array) { next unless defined $auto_increment; my $type_with_signed = ( $column_type =~ /unsigned/ ) ? "unsigned_$type" : "signed_$type"; my $max = $max{$type_with_signed}; unless ($max) { if ($verbosity >= 1) { print "Don't know maximal value for data type $type_with_signed"; } next; } my $fill = $auto_increment / $max; # print "RLB: $fill - $critical\n"; if ($verbosity >= 2) { print join "\t", $catalog, $database, $table, $column, $type_with_signed, $auto_increment, $fill; } if ($fill >= $critical) { $has_critical++; printf "CRITICAL: %s.%s.%s at %.3f (%d/%d)\n", $database, $table, $column, $fill, $auto_increment, $max; } elsif ($fill >= $warning) { $has_warnings++; printf "WARNING: %s.%s.%s at %.3f (%d/%d)\n", $database, $table, $column, $fill, $auto_increment, $max; } if (!$max_record || $max_record->{fill} <= $fill) { $max_record = { database => $database, table => $table, column => $column, value => $auto_increment, max => $max, fill => $fill, }; } } $ai->finish; if (!$has_warnings && !$has_critical && $max_record) { printf "OK (maximal value: : %s.%s.%s at %.3f (%d/%d))\n", @{$max_record}{qw/database table column fill value max/}; } if ($has_critical) { exit 2; } elsif ($has_warnings) { exit 1; } else { exit 0; }