OrzDBA简介

OrzDBA是淘宝DBA团队发布的监控脚本，使用perl开发，可以完成对linux系统和MySql相关指标的实时监控。OrzDBA提供了很多监控指标，其中比较实用的指标就有TPS。

TPS简介

Transactions Per Second，意思是每秒事务数。每秒钟系统能够处理的事务的数量。它是衡量数据库系统处理能力的重要指标。

OrzDBA代码分析

主循环

interval是循环的时间间隔， get_mysqlstat中获取dml数据信息，sleep实现等待

while(1) {
        ......
        ......
        
        # (3) Print MySQL Status  
        &get_mysqlstat();

        # (4) TCPRSTAT
        &get_dbrt() if $dbrt;

        #
        print $LOG_OUT "\n";
        sleep($interval);
}

读取数据的代码

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;

interval是时间间隔（ Time(second) Interval. ）

计算并打印tps的代码

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

总结

orzdba通过show global status 获取insert， update， delete的dml数据，然后将两次采集到的数据相减数，和时间间隔一起计算得出每个类型的数值，然后三者相加，得到tps值。

tps = dt(Com_insert) / dt + dt(Com_update) / dt + dt(Com_delete) / dt