監控mysql之orzdba和orztop

這兩款工具都是基於perl，由淘寶DBA朱旭編寫。

Orzdba

將MySQL效能統計資訊進行計算，統計MySQL/INNODB層面的各種指標（QPS/TPS/thread/buffer/log等）， 將OS和MySQL統計引數放在一個螢幕中展現。

可選項

   -L,--logfile        Print to Logfile.

   -l,--load           Print Load Info.

   -c,--cpu            Print Cpu  Info.

   -s,--swap           Print Swap Info.

   -d,--disk           Print Disk Info.

   -n,--net            Print Net  Info.

   -com                Print MySQL Status(Com_select,Com_insert,Com_update,Com_delete).

   -hit                Print Innodb Hit%.

   -innodb_rows        Print Innodb Rows Status(Innodb_rows_inserted/updated/deleted/read).

   -innodb_pages       Print Innodb Buffer Pool Pages Status(Innodb_buffer_pool_pages_data/free/dirty/flushed)

   -innodb_data        Print Innodb Data Status(Innodb_data_reads/writes/read/written)

   -innodb_log         Print Innodb Log  Status(Innodb_os_log_fsyncs/written)

   -innodb_status      Print Innodb Status from Command: 'Show Engine Innodb Status'

                       (history list/ log unflushed/uncheckpointed bytes/ read views/ queries inside/queued)

   -T,--threads        Print Threads Status(Threads_running,Threads_connected,Threads_created,Threads_cached).

以—com為例檢視原始碼

登入mysql，執行show global status並過濾出候選狀態變數

my $mysql = qq{$MYSQL -e 'show global status where Variable_name in ("Com_select","Com_insert","Com_update","Com_delete","Innodb_buffer_pool_read_requests","Innodb_buffer_pool_reads","Innodb_rows_inserted","Innodb_rows_updated","Innodb_rows_deleted","Innodb_rows_read","Threads_running","Threads_connected","Threads_cached","Threads_created","Bytes_received","Bytes_sent","Innodb_buffer_pool_pages_data","Innodb_buffer_pool_pages_free","Innodb_buffer_pool_pages_dirty","Innodb_buffer_pool_pages_flushed","Innodb_data_reads","Innodb_data_writes","Innodb_data_read","Innodb_data_written","Innodb_os_log_fsyncs","Innodb_os_log_written")'};

將兩次執行結果相減併除以時間間隔，即可得出每秒的資料

my $insert_diff = ( $mystat2{"Com_insert"} - $mystat1{"Com_insert"} ) / $interval;

my $update_diff = ( $mystat2{"Com_update"} - $mystat1{"Com_update"} ) / $interval;

my $delete_diff = ( $mystat2{"Com_delete"} - $mystat1{"Com_delete"} ) / $interval;

my $select_diff = ( $mystat2{"Com_select"} - $mystat1{"Com_select"} ) / $interval;

列印輸出

if ($com) {

  # Com_insert # Com_update # Com_delete

  print $LOG_OUT WHITE();

  printf $LOG_OUT "%5d %5d %5d",$insert_diff,$update_diff,$delete_diff;

  print $LOG_OUT YELLOW();

  # Com_select

  printf $LOG_OUT " %6d",$select_diff;

  # Total TPS

  printf $LOG_OUT " %5d",$insert_diff+$update_diff+$delete_diff;

  print $LOG_OUT GREEN(),"|",RESET();

}

可以執行Orzdba持續監控mysql並把結果輸出到日誌，這樣當非工作時間系統執行慢的時候可以回頭檢視歷史資訊，透過com_select等指標同時配合OS cpu/mem/io資訊，可以快速做出一個大致判斷，

Orztop

可即時顯示mysql當前query list，以及每秒的DML/select以及邏輯讀等指標，用於即時的系統調優；

注：邏輯讀的計算來源於innodb_buffer_pool_read_requests，而innodb_buffer_pool_reads則是須從磁碟讀取的邏輯讀次數； 

該工具選項比較少

-h,–host Hostname/Ip to use for mysql connection.

-u,–user User to use for mysql connection.

-p,–pwd Password to use for mysql connection.

-P,–port Port to use for mysql connection(default 3306).

-S,–socket Socket to use for mysql connection.

-t Time(second) Interval.

程式碼片段

my $sql_processlist  = qq{show full processlist};

my @processlist = &hashes($sql_processlist);

@mysql_processlist = @processlist;

@mysql_processlist = sort{ $a->{time} <=> $b->{time} } @mysql_processlist;

對show full processlist的結果進行封裝，然後遍歷

foreach (@mysql_processlist) {

  next if ($_->{command} eq "Sleep" or $_->{command} eq "Binlog Dump" or $_->{command} eq "Connect") and not $mysql_processlist_idle;

  next if ($_->{db}   !~ $mysql_processlist_filter_db);

  next if ($_->{user} !~ $mysql_processlist_filter_user);

  next if ($_->{info} !~ $mysql_processlist_filter_sql);

  if ( 1 ) {

    printf "%8s %20s %15s %15s %15s %10s", $_->{id},$_->{host}, $_->{user}, $_->{db}, $_->{command},$_->{time} ;

    print color('magenta') ;

    printf "    %-50s\n",$_->{state};

                        

    if ($_->{info} ne '') {

      if ( $mysql_filter ) {

      $_->{info} =~ /$mysql_filter/i ;

      print color('yellow') ;

      print " ==> [ SQL ] ".$`;

      print color('bold red') ;

      print $& ;

      print color('reset yellow') ;

      print $'."\n" ;

    } else {

      print color('yellow') ;

      print " ==> [ SQL ] ".$_->{info}."\n";

   }

 }

}