Benchmarking and Profiling in MySQL

在开发的时候,在考虑功能的时候,更多想到的是性能,往往需要新配的系统,或者修改了程序想知道对系统的性能有多大影响.MySQL本身自带了sql-bench套件来测试MySQL性能,用来检测批量执行的效率,还可通过开启MySQL的Profiling功能查看单条sql的执行情况,以便于查看性能的瓶颈,促进sql优化.

除MySQL本身自带的sql-bench外还有很多第三方工具,如:ab,http_load,mysqlslap,sysbench,Database Test Suite等工具.这其中ab和http_load是http测试工具,mysqlslap在MySQL5.1以后的版本中自带,但是它可以支持道4.1级以后版本的MySQL.sysbench是一个多线程工具用来服务器cpu,io,memory,os性能还支持lua脚本.Database Test Suite是Open-Source Development Labs(OSDL)出品的一款测试工具.

这些工具不能一一试用,我只简单用下mysqlslap,sysbench以及sql-bench和MySQL Profiling

先说mysqlslap,mysqlslap在MySQL的安装目录bin下面,默认mysqlslap会依次从/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf这些地方读取my.cnf文件,也可使用--defaults-file=#选项来指定配置文件,另外还可通过--defaults-extra-file=#选项在--defaults-file选项文件之外附加配置选项.由于在mysqlslap里面默认是一行一条sql所以需要加上--limiter选项,eg

shell>mysqlslap --delimiter=";" /

--create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" /

--query="SELECT * FROM a" --concurrency=50 --iterations=200

这个命令的意思就是,先定义结束符为';'然后创建表,并插入数据,下面对这个这个表开50个终端,每个查询200次

返回结果如下:

Benchmark
        Average number of seconds to run all queries: 0.141 seconds
        Minimum number of seconds to run all queries: 0.095 seconds
        Maximum number of seconds to run all queries: 0.184 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

还可以让mysqlslap自己生成sql,手册的例子如下

shell>mysqlslap --concurrency=5 --iterations=20 /

--number-int-cols=2 --number-char-cols=3 /

--auto-generate-sql

这个是5个并发,每个20次,创建一个有2个int字段3个varchar字段的表,--auto-generate-sql的意思是挡在没有提供(文件和命令行)sql的时候自动生成sql.在文件中写sql每个sql之间习惯用";"来分隔这个得告诉mysqlslap,一个加载外部sql的例子如下:

mysqlslap --concurrency=5 /

--iterations=5 --query=query.sql --create=create.sql /

--delimiter=";"

更多是使用方法参考MySQL5.1手册或使用命令mysqlslap --help查看

sysbench,sysbench可从http://sysbench.sourceforge.net下载并找到相关帮助信息.目前最新版的0.4.12没能成功安装:(下了个以前版本的没有问题.可能时新版本的bug.

shell>.configure --prefix=/usr/localsysbench --with-mysql=/usr/local/mysql

shell>make

shell>make install

我安装的时候没有碰到什么问题,运行的时候报错

sysbench: error while loading shared libraries: libmysqlclient_r.so.16: cannot open shared object file: No such file or directory

解决方法是:

shell>cp /usr/local/mysql/lib/mysql/libmysqlclient* /usr/lib/

之后在运行就没有什么问题了.

摘几个<High Performance MySQL Sencond Edition>上的例子:

测试cpu的:sysbench --test=cpu --cpu-max-prime=20000 run

测试fileio的如下:先sysbench --test=fileio --file-total-size=150G prepare

然后sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw --init-rnd=on --max-time=300 --max-requests=0 run

其中的file-test-mode选项有,seqwr,seqrewr,seqrd,rndwr,rndrewr,rndrd其中seq是sequential(顺序),wr是write,rewr是rewrite,rd是read,rnd是random的意思

测试OLTP

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root  prepare

创建一个具有1000000行记录的测试表,在测试

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --
max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

8个线程,不限制请求的数目(0表示不限制),仅执行select语句,得到的结果类似如下:

sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
WARNING: Preparing of "BEGIN" is unsupported, using emulation
(last message repeated 7 times)
Running the test with following options:
Number of threads: 8
Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct
cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.
OLTP test statistics:
queries performed:
read: 179606
write: 0
other: 25658
total: 205264
transactions: 12829 (213.07 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 179606 (2982.92 per sec.)
other operations: 25658 (426.13 per sec.)
Test execution summary:
total time: 60.2114s
total number of events: 12829
total time taken by event execution: 480.2086

per-request statistics:
min: 0.0030s
avg: 0.0374s
max: 1.9106s
approx. 95 percentile: 0.1163s
Threads fairness:
events (avg/stddev): 1603.6250/70.66
execution time (avg/stddev): 60.0261/0.06

这么多的输出最关心的就是关于事务的那部分(sysbench默认创建的表引擎是innodb)

transactions: 12829 (213.07 per sec.)
per-request statistics:
min: 0.0030s
avg: 0.0374s
max: 1.9106s
approx. 95 percentile: 0.1163s

Threads fairness:
events (avg/stddev): 1603.6250/70.66
execution time (avg/stddev): 60.0261/0.06

至于sysbench还可测试memory,thread,mutex等有兴趣的上sysbench的在线手册查看,地址是

http://sysbench.sourceforge.net/docs/

MySQL Banchmark Suite也就是在sql-bench在Linux下安装完MySQL后root目录下有个sql-bench文件夹

里面有很多程序,帮助上讲要求3.20.28 or 3.21.10以上版本的MySQL Server实际这么古老的版本很难见.

这个比较简单通过查看README等帮助信息基本都没什么问题,eg:

shell>cd /usr/share/mysql/sql-bench/
shell> ./run-all-tests --server=mysql --user=root --log –fast

运行完会在output文件中招到运行结果

结果可能如下:

Benchmark DBD suite: 2.15
Date of test:        2009-12-10  0:41:19
Running tests on:    Linux 2.6.18-164.el5 i686
Arguments:           --fast
Comments:           
Limits from:        
Server version:      MySQL 5.1.41 debug log
Optimization:        None
Hardware:           

alter-table: Total time: 23 wallclock secs ( 0.01 usr  0.37 sys +  0.00 cusr  0.00 csys =  0.38 CPU)
ATIS: Total time: 38 wallclock secs ( 0.62 usr  4.93 sys +  0.00 cusr  0.00 csys =  5.55 CPU)
big-tables: Total time: 35 wallclock secs ( 0.43 usr  9.38 sys +  0.00 cusr  0.00 csys =  9.81 CPU)
connect: Total time: 342 wallclock secs ( 5.98 usr 116.60 sys +  0.00 cusr  0.00 csys = 122.58 CPU)
create: Total time: 275 wallclock secs ( 1.63 usr  3.83 sys +  0.00 cusr  0.00 csys =  5.46 CPU)
insert: Failed (output/insert-mysql_fast-Linux_2.6.18_164.el5_i686)
select: Total time: 1335 wallclock secs ( 4.47 usr 77.30 sys +  0.00 cusr  0.00 csys = 81.77 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 16 wallclock secs ( 0.56 usr 12.10 sys +  0.00 cusr  0.00 csys = 12.66 CPU)

Of 9 tests, 1 tests didn't work

...

另外MySQL还有benchmark(n,sql)函数,可以查看执行n次sql的时间.

 

以上这些是压力测试,可以看出MySQL Server可以做什么至于每条SQL怎么做,可以通过explain和Profiling查看,explain可以查看MySQL的执行计划,profiling可以查看MySQL的执行效率,进而招到sql预计的瓶颈.

使用profiling,先要打开profiling,

mysql>set profiling=1;

然后执行query

mysql> SELECT COUNT(DISTINCT actor.first_name) AS cnt_name, COUNT(*) AS cnt
-> FROM sakila.film_actor
-> INNER JOIN sakila.actor USING(actor_id)
-> GROUP BY sakila.film_actor.film_id
-> ORDER BY cnt_name DESC;
...
997 rows in set (0.03 sec)

查看session中的profile

mysql> SHOW PROFILES/G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.02596900
Query: SELECT COUNT(DISTINCT actor.first_name) AS cnt_name,...

查看某条query的执行消耗的cpu和io信息如下

mysql>show profile cpu,block io for query 1;

先简单说这么多,手册上有更详尽的信息,有空好好看看.