监控mysql之orzdba和orztop
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信息，可以快速做出一个大致判断，
http://code.taobao.org/p/orzdba/src/trunk/orzdba
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";
}
}
}
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-1425464/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-1425464/