mysql测试工具下周_mysql测试小工具mybench试用

Dear 丶 2022-10-27 02:36 152阅读 0赞

小型的mysql测试工具，主要有自带的mysqlslap、super-smack和mybench。嗯，我这里的小型的意思是指工具安装过程简单。mysqlslap的使用方法遍地都是，就不先详细写了。根据个人偏好写写mybench吧，毕竟是perl的。安装很简单，如下： cpanm DBI DBD::mysql Tim

小型的mysql测试工具，主要有自带的mysqlslap、super-smack和mybench。嗯，我这里的小型的意思是指工具安装过程简单。

mysqlslap的使用方法遍地都是，就不先详细写了。根据个人偏好写写mybench吧，毕竟是perl的。

安装很简单，如下：

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

但是使用就不是太简单了——mysqlslap会自己生成(-a选项)sql，super-smack则带了一个gen-data程序生成数据然后自动导入，但是mybench没有，所以只能自己搞定数据。

不过mybench还是自己生成了一个测试模版的脚本在/usr/bin/bench\_example，很简单的就知道怎么做了。

example如下：

\#!/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\_\_

然后看看/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 < $kids\_to\_fork)

\#用IO::Pipe管道方式来传递父子进程的信息

my $pipe = new IO::Pipe;

\#fork进程开始

if ($pid = fork())

\#\# parent

$num\_kids++;

\#每fork完成一个打印一个+号

print "+";

\#从管道中读取数据

$pipe->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 arrays

sub max

my $val = $\_\[0\];

for (@\_)

if ($\_ > $val) \{ $val = $\_; \}

return $val;

sub min

my $val = $\_\[0\];

for (@\_)

if ($\_ < $val) \{ $val = $\_; \}

return $val;

sub avg

my $tot;

for (@\_) \{ $tot += $\_; \}

return $tot / @\_;

sub tot

my $tot;

for (@\_) \{ $tot += $\_; \}

return $tot;

好了，开始准备数据，比较懒，直接用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 ready

waiting: ———-

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