mysql測試小工具mybench試用

cpanm DBI DBD::mysql Time::HiRes
wget http://jeremy.zawodny.com/mysql/mybench/mybench-1.0.tar.gz
tar zxvf mybench-1.0.tar.gz
cd mybench-1.0
perl MakeFile.PL && make && make install

#!/usr/bin/perl -w
eval 'exec /usr/bin/perl -w -S $0 ${1+"$@"}'
 if 0; # not running under some shell
use strict;
use MyBench;
use Getopt::Std;
use Time::HiRes qw(gettimeofday tv_interval);
use DBI;
my %opt;
Getopt::Std::getopt('n:r:h:', \%opt);
#這是我見過的最hardcode的perl腳本了（呃，除了我自己寫的垃圾），連db庫、用戶名、密碼都不給運行參數的
my $num_kids = $opt{n} || 10;
my $num_runs = $opt{r} || 100;
my $db = "test";
my $user = "test";
my $pass = "";
my $port = 3306;
my $host = $opt{h} || "192.168.0.1";
my $dsn = "DBI:mysql:$db:$host;port=$port";
my $callback = sub
{
 my $id = shift;
 my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1 });
#為測試準備的請求，測select就寫select，測insert就寫insert唄~
#如果不修改，也就是說測試用的是test.mytable表，而且必須有一個列叫id
 my $sth = $dbh->prepare("SELECT * FROM mytable WHERE ID = ?");
 my $cnt = 0;
 my @times = ();
 ## wait for the parent to HUP me
 local $SIG{HUP} = sub { };
 sleep 600;
#腳本定義的每個進程執行多少次請求
 while ($cnt < $num_runs)
 {
 my $v = int(rand(100_000));
 ## time the query
 my $t0 = [gettimeofday];
#真正的執行sql請求，通過上面的rand知道，之前準備的test.mytable的id列必須是int格式，同時不少于10w行（又一處hard）
 $sth->execute($v);
#通過前后兩次gettimeofday獲得sql的exec耗時
 my $t1 = tv_interval($t0, [gettimeofday]);
#完成一次請求執行，加入數組
 push @times, $t1;
 $sth->finish();
 $cnt++;
 }
 ## cleanup
 $dbh->disconnect();
#計算本進程全部請求的各項數據，幾個大小和均來自MyBench模塊
 my @r = ($id, scalar(@times), min(@times), max(@times), avg(@times), tot(@times));
 return @r;
};
#將上面這個函數交給MyBench模塊的fork_and_work執行，即并發指定數量請求，返回總的結果
my @results = MyBench::fork_and_work($num_kids, $callback);
#計算總的數據
MyBench::compute_results('test', @results);
exit;
__END__

package MyBench;
use strict;
$main::VERSION = '1.0';
use Exporter;
@MyBench::ISA = 'Exporter';
#導出求最大值、最小值、平均值、綜合值的函數給外面用
@MyBench::EXPORT = qw(max min avg tot);
sub fork_and_work($$)
{
#關閉

USE test;
CREATE TABLE mytable (id INT(11) NOT NULL AUTO_INCREMENT, col1 CHAR(100), col2 CHAR(100), col3 INT(11), PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOAD DATA LOCAL INFILE 'data' REPLACE INTO TABLE 'mytable' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
INSERT INTO mytable (col1,col2,col3) SELECT col1,col2,col3 FROM mytable;