I want to load all the variables and there values in a hash and use the variables to do calculation My inital code to calculate mysql health chek values was like this.#!/usr/bin/perl # Check the health of a mysql server. # use Getopt::Long; use DBI; my $Variable_name = ''; my $status = ''; # -- # Print out the usage message # -- sub usage { print "usage: check_mysqlhealth.pl -H <host> -u <user> -p <passwor +d> \n"; print " Optional parameters:\n"; print " --port <port> \n"; } $|=1; # -- # Parse arguments and read Configuration # -- my ($host, $user, $password, $port); GetOptions ( 'host=s' => \$host, 'H=s' => \$host, 'user=s' => \$user, 'u=s' => \$user, 'password=s' => \$password, 'p:s' => \$password, 'port=i' => \$port, ); if (!$host || !$user) { usage(); exit(1); } if (!$port) { $port = 3306; } my $totalTime = time(); # -- # Establish connection # -- my $state = "OK"; my $dbh; eval { $dbh = DBI->connect("DBI:mysql:host=$host;port=$port", $user, $pas +sword, {'RaiseError' => 1}); print "connected\n"; }; if ($@) { my $status = $@; print 'CRITICAL: Connect failed with reason ' . $status . "\n"; exit 2; } &open_report; sub open_report { my $sgv = $dbh->prepare("show variables"); $sgv->execute(); while (my $gv=$sgv->fetchrow_hashref()) { %MySQL_Variables{@gv[0]} = @gv[1];(is there a systax err here) Then to use a mysql item in perl use %MySQL_Variables{"uptime"} } $sgv->finish(); }
This is cumbersome but Now instead of using fetchrow_array() to fetch each row, i want to use hash and use the values to do calculation..Can anybody help me on this.To use the values dynamically and do the calculation I hope i was clear.. thanks.. show variables gives the following result in mysqlprint "********READ-WRITE RATIO********\n\n"; #The r/w ratio is of course (Com_select + Qcache_hits)/(Com_insert+Com +_update+Com_delete+Com_replace) my $cs = $dbh->prepare("show status like 'com_select'"); my $qch = $dbh->prepare("show status like 'qcache_hits'"); my $ci = $dbh->prepare("show status like 'com_insert'"); my $cu = $dbh->prepare("show status like 'com_update'"); my $cd = $dbh->prepare("show status like 'com_delete'"); my $cr = $dbh->prepare("show status like 'com_replace'"); $cs->execute(); $qch->execute(); $ci->execute(); $cu->execute(); $cd->execute(); $cr->execute(); while (my @csr = $cs->fetchrow_array()) { while (my @qchr = $qch->fetchrow_array()) { print "\tCom_select: $csr[1]\n"; print "\tQcahce_hits: $qchr[1]\n"; my $result = ($csr[1] + $qchr[1]); print "The select rate is :$result\n"; } } $qch->finish(); $cs->finish(); while (my @cir = $ci->fetchrow_array()) { while (my @cur = $cu->fetchrow_array()) { while (my @cdr = $cd->fetchrow_array()) { while (my @crr = $cr->fetchrow_array()) { print "\tCom_Insert: $cir[1]\n"; print "\tCom_Update: $cur[1]\n"; print "\tCom_Delete: $cdr[1]\n"; print "\tCom_Replace: $crr[1]\n"; my $ir =($cir[1] + $cur[1] + $cdr[1] + $crr[1]); print "The insert rate is $ir\n"; } } } } $ci->finish(); $cu->finish(); $cd->finish(); $cr->finish();
to do a health check we need to calculate some parameteres. threhold values($uptime > 10800) && (Handler_read_rnd_next > 4000) && ((100-(((Handler_read_rnd_next + Handler_read_rnd) / (##Handler_read_rnd_next + Handler_read_rnd + Handler_read_first + Handler_read_next + Handler_read_key + Handler_read_prev))*100)) i hope you understandmysql> show variables; +---------------------------------+----------------------------------- +-------+ | Variable_name | Value + | +---------------------------------+----------------------------------- +-------+ | back_log | 50 + | | basedir | / + | | binlog_cache_size | 32768 + | | bulk_insert_buffer_size | 8388608 + | | character_set_client | latin1 + | | character_set_connection | latin1 + | | character_set_database | latin1 + | | character_set_results | latin1 + | | character_set_server | latin1 + | | character_set_system | utf8 + | | character_sets_dir | /usr/share/mysql/charsets/ + | | collation_connection | latin1_swedish_ci + | | collation_database | latin1_swedish_ci + | | collation_server | latin1_swedish_ci + | | concurrent_insert | ON + | | connect_timeout | 5 + | | datadir | /var/lib/mysql/ + | | date_format | %Y-%m-%d + | | datetime_format | %Y-%m-%d %H:%i:%s + | | default_week_format | 0 + | | delay_key_write | ON + | | delayed_insert_limit | 100 + | | delayed_insert_timeout | 300 + | | delayed_queue_size | 1000 + | | expire_logs_days | 0 + |
In reply to Mysql Health script by aalneyperl
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |