小型的mysql测试工具，主要有自带的mysqlslap、super-smack和mybench。嗯，我这里的小型的意思是指工具安装过程简单。mysqlslap的使用方法遍地都是，就不先详细写了。根据个人偏好写写mybench吧，毕竟是perl的。安装很简单，如下：
cpanm dbi dbd::mysql time::hireswget http://jeremy.zawodny.com/mysql/mybench/mybench-1.0.tar.gztar zxvf mybench-1.0.tar.gzcd mybench-1.0perl makefile.pl && make && make install
但是使用就不是太简单了——mysqlslap会自己生成（-a选项）sql，super-smack则带了一个gen-data程序生成数据然后自动导入，但是mybench没有，所以只能自己搞定数据。不过mybench还是自己生成了一个测试模版的脚本在/usr/bin/bench_example，很简单的就知道怎么做了。example如下：
#!/usr/bin/perl -weval 'exec /usr/bin/perl -w -s $0 ${1+$@}' if 0; # not running under some shelluse 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 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__
然后看看/usr/lib/perl5/site_perl/5.8.8/mybench.pm，主要内容就是fork和compute：
package mybench;use strict;$main::version = '1.0';use exporter;@mybench::isa = 'exporter';#导出求最大值、最小值、平均值、综合值的函数给外面用@mybench::export = qw(max min avg tot);sub fork_and_work($$){#关闭输出缓冲 $|=1; use strict; use io::pipe; use io::select; $sig{chld} = 'ignore'; ## let the kids die my $kids_to_fork = shift; my $callback = shift; my $num_kids = 0; my @pipes = (); my @pids = (); my $pid = undef; print forking: ; while ($num_kids reader(); push @pipes, $pipe; push @pids, $pid; } elsif (defined $pid) { ## child#打开管道写入数据的功能 $pipe->writer();#执行select_example脚本传入的mysql请求测试函数 my @result = $callback->($num_kids);#把结果写入管道 print $pipe @result\n;#关闭管道 $pipe->close(); exit 0; } else { print fork failed: $!\n; } } print \n; ## give them a bit of time to setup my $time = int($num_kids / 10) + 1; print sleeping for $time seconds while kids get ready\n; sleep $time; #发送sighup信号给callback函数 kill 1, @pids; ## collect the results my @results; print waiting: ;#从管道中读取数据到数组 for my $pipe (@pipes) { my $data = ; push @results, $data; $pipe->close(); print -; } print \n; return @results;}sub compute_results(@){ my $name = shift; my $recs = 0; my ($cnt, $min, $max, $avg, $tot, @min, @max); while (@_) { ## 6 elements per record my $rec = shift; chomp $rec; my ($id, $cnt, $min, $max, $avg, $tot) = split /\s+/, $rec; $cnt += $cnt; $avg += $avg; $tot += $tot; push @min, $min; push @max, $max; $recs++; } $avg = $avg / $recs; $min = min(@min); $max = max(@max); my $qps = $cnt / ($tot / $recs); print $name: $cnt $min $max $avg $tot $qps\n; print clients : $recs\n; print queries : $cnt\n; print fastest : $min\n; print slowest : $max\n; print average : $avg\n; print serial : $tot\n; print q/sec : $qps\n;}## some numerical helper functions for arrayssub max{ my $val = $_[0]; for (@_) { if ($_ > $val) { $val = $_; } } return $val;}sub min{ my $val = $_[0]; for (@_) { if ($_ 好了，开始准备数据，比较懒，直接用super-smack的gen-data先出了一些./gen-data -n 100000 -f %n,%80-12s%12n,%512-512s,%d > /root/data，然后进mysql里执行：
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;
最后执行./select_bench -h 10.168.170.92 -n 10 -r 1000就能看到结果了：forking: ++++++++++sleeping for 2 seconds while kids get readywaiting: ———-test: 10000 0.00017 0.006809 0.0010413514 10.413514 9602.9063772325 clients : 10 queries : 10000 fastest : 0.00017 slowest : 0.006809 average : 0.0010413514 serial : 10.413514 q/sec : 9602.9063772325
原文地址：mysql测试小工具mybench试用, 感谢原作者分享。