I would like to share the ultimate MySQL performance debugger tool.
Written by my friend Nereu.
The output is like this

The code:

#!/usr/bin/perl
#
# 2011-11-03 Nereu
# 2011-11-05 Nereu - mytop
#
use DBI;
# EDIT HERE FOR USERNAME AND PASSWORD
$dbh = DBI->connect('DBI:mysql:mysql', 'root', '') || die "Could not connect to database: $DBI::errstr";
$sth = $dbh->prepare("show processlist");
$sth2 = $dbh->prepare("show global status");
$last=0;
$clear_string = `clear`;
$S{"Com_select"}=0.0 ; $N{"Com_select"}=0;
for(;;) {
  print $clear_string;
  $now=time();
  $sth2->execute();
  while ( $result2 = $sth2->fetchrow_hashref() ) { $q2{$result2->{Variable_name}}=$result2->{Value}; }
  print "n";
  &pu;
# UNCOMMENT TO SEE ANY OF THESE VALUES
  &p("Queries");
  &pa("Threads_connected");
#  &pa("Open_files");
#  &pa("Open_tables");
  &p("Key_writes");
  &p("Key_write_requests");
  &p("Key_reads");
  &p("Key_read_requests");
  &pv("(Key_blocks_)used/used+unused",$q2{"Key_blocks_used"}/($q2{"Key_blocks_used"}+$q2{"Key_blocks_unused"})*100.0);
  &pa("Key_blocks_used");
  &pa("Key_blocks_unused");
# print "-------------------------------------------n";
#  &p("Com_admin_commands");
#  &p("Com_alter_table");
#  &p("Com_change_db");
#  &p("Com_commit");
  &pm("Com_delete");
  &pm("Com_insert");
#  &p("Com_kill");
#  &p("Com_lock_tables");
#  &p("Com_rollback");
  &pm("Com_select");
  &p("Com_set_option");
#  &p("Com_show_collations");
#  &p("Com_show_create_table");
#  &p("Com_show_databases");
#  &p("Com_show_fields");
#  &p("Com_show_processlist");
#  &p("Com_show_status");
#  &p("Com_show_tables");
#  &p("Com_show_triggers");
#  &p("Com_show_variables");
  &p("Com_stmt_close");
  &p("Com_stmt_execute");
  &p("Com_stmt_prepare");
#  &p("Com_unlock_tables");
# print "-------------------------------------------n";
#   &pa("Innodb_buffer_pool_pages_dirty");
#   &pa("Innodb_buffer_pool_pages_data");
#   &pa("Innodb_buffer_pool_pages_flushed");
#   &pa("Innodb_buffer_pool_pages_free");
#   &pa("Innodb_buffer_pool_pages_misc");
#   &pa("Innodb_buffer_pool_pages_total");
##   &pa("Innodb_buffer_pool_read_ahead_rnd");
#   &pa("Innodb_buffer_pool_read_ahead_seq");
#   &p("Innodb_buffer_pool_read_requests");
#   &pa("Innodb_buffer_pool_reads");
#   &pa("Innodb_buffer_pool_wait_free");
#   &pa("Innodb_buffer_pool_write_requests");
#   &p("Innodb_data_fsyncs");
#   &pa("Innodb_data_pending_fsyncs");
#   &pa("Innodb_data_pending_reads");
#   &pa("Innodb_data_pending_writes");
#   &pa("Innodb_data_read");
#   &pa("Innodb_data_reads");
#   &p("Innodb_data_writes");
#   &p("Innodb_data_written");
#   &pa("Innodb_dblwr_pages_written");
#   &pa("Innodb_dblwr_writes");
#   &pa("Innodb_log_waits");
#   &pa("Innodb_log_write_requests");
#   &pa("Innodb_log_writes");
#   &pa("Innodb_os_log_fsyncs");
##   &pa("Innodb_os_log_pending_fsyncs");
#   &pa("Innodb_os_log_pending_writes");
#   &p("Innodb_os_log_written");
#   &pa("Innodb_page_size");
#   &p("Innodb_pages_created");
#   &pa("Innodb_pages_read");
#   &pa("Innodb_pages_written");
#   &pa("Innodb_row_lock_current_waits");
#   &pa("Innodb_row_lock_time");
#   &pa("Innodb_row_lock_time_avg");
#   &pa("Innodb_row_lock_time_max");
#   &pa("Innodb_row_lock_waits");
#   &pa("Innodb_rows_deleted");
#   &pa("Innodb_rows_inserted");
#   &pa("Innodb_rows_read");
##   &pa("Innodb_rows_updated");
# print "-------------------------------------------n";
# &pa("Qcache_free_blocks");
# &pa("Qcache_free_memory");
# &pa("Qcache_hits");
# &pa("Qcache_inserts");
# &pa("Qcache_lowmem_prunes");
# &pa("Qcache_not_cached");
# &pa("Qcache_queries_in_cache");
# &pa("Qcache_total_blocks");
  print "n";
  %q1 = %q2;
  %Host_connected =  ( );
  $sth->execute();
  printf("n%6s %10s %15s %10s %15s %5s %-20s %sn","Id", "User", "Host", "DB", "Command", "Time", "State", "Info");
  printf("%6s %10s %15s %10s %15s %5s %-20s %75sn","-" x 6, "-" x 10, "-" x 15, "-" x 10, "-" x 15, "-" x 5, "-" x 20, "-" x 75);
  while ( $result = $sth->fetchrow_hashref() ) {
    if ( $result->{Command} ne "Sleep" ) {
      printf("%6s %10s %15s %10s %15s %5s %-20s %-75sn",
             $result->{Id},
             $result->{User},
             $result->{Host},
             $result->{db},
             $result->{Command},
             $result->{Time},
             $result->{State},
             substr($result->{Info},0,75)
      );
    }
    $Host_connected{substr($result->{Host},0,index($result->{Host},":"))}++;
  }
  print "nThreads per hostnn";
  foreach ( sort { $Host_connected{$b} <=> $Host_connected{$a} } keys %Host_connected ) {
    printf("%-15s : %sn",$_,$Host_connected{$_});
  }
  sleep(2);
  $last=$now;
}
#
sub p  {
  $delta= ( ($now - $last) == 0 ? 1 : ( $now - $last) );
  printf("%28s/s = %.0fn", $_[0], ($q2{$_[0]} - $q1{$_[0]}) / $delta );
}
sub pa {
  printf("%30s = %.0fn", $_[0], $q2{$_[0]});
}
sub pm  {
  $delta= ( ($now - $last) == 0 ? 1 : ( $now - $last) );
  if ( $q1{$_[0]} != 0 ) {
    $S{$_[0]} += ($q2{$_[0]} - $q1{$_[0]})/$delta;
    $N{$_[0]}++;
    printf("%28s/s = %.0f media=%.1fn", $_[0], ($q2{$_[0]} - $q1{$_[0]}) / $delta, $S{$_[0]}/$N{$_[0]} );
  } else {
    printf("%28s/s = %.0f media=---n", $_[0], ($q2{$_[0]} - $q1{$_[0]}) / $delta);
  }
}
sub pu {
  local ( $up, $days, $hours, $mins );
  local($s,$M,$H,$d,$m,$y,$wd,$yd,$isdst)=localtime(time() - $q2{"Uptime"});
  $up=$q2{"Uptime"};
  $days=int($up/86400);
  $hours=int(($up - $days*86400) / 3600);
  $mins=int(($up - $days*86400 - $hours*3600)/60);
  printf("%30s = %.0f day(s), %.0f hour(s), %.0f min(s) (%04d/%02d/%02d %02d:%02d:%02d)n",
                                                 "Uptime", $days, $hours, $mins, 1900+$y,$m+1,$d,$H,$M,$s);
}
sub pv {
  printf("%30s = %.1f%%n", $_[0], $_[1]);
}
Tags: , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *