sysbench 安装压测mysql 使用sysbench测试mysql性能时

参考

一、基准测试的作用

sysbench是一个开源的、模块化的、跨平台的多线程性能测试工具，可以用来进行CPU、内存、磁盘I/O、线程、数据库的性能测试。目前支持的数据库有MySQL、Oracle和PostgreSQL。以下操作都将以支持MySQL数据库为例进行。 
Sysbench的测试主要包括以下几个方面： 
1、磁盘io性能 
2、cpu性能 
3、内存分配及传输速度 
4、POSIX线程性能 
5、调度程序性能 
6、数据库性能(OLTP基准测试).

二、基准测试的指标

常见的数据库指标包括：

• TPS/QPS：衡量吞吐量。
• 响应时间：包括平均响应时间、最小响应时间、最大响应时间、时间百分比等，其中时间百分比参考意义较大，如前95%的请求的最大响应时间。。
• 并发量：同时处理的查询请求的数量。

 

三、基准测试的分类

 

对MySQL的基准测试，有如下两种思路：

 

（1）针对整个系统的基准测试：通过http请求进行测试，如通过浏览器、APP或postman等测试工具。该方案的优点是能够更好的针对整个系统，测试结果更加准确；缺点是设计复杂实现困难。

 

（2）只针对MySQL的基准测试：优点和缺点与针对整个系统的测试恰好相反。

 

在针对MySQL进行基准测试时，一般使用专门的工具进行，例如mysqlslap、sysbench等。其中，sysbench比mysqlslap更通用、更强大，且更适合Innodb（因为模拟了许多Innodb的IO特性），下面介绍使用sysbench进行基准测试的方法。

 

四、安装

https://github.com/akopytov/sysbench官网

#git clone https://github.com/akopytov/sysbench.git

#autogen.sh 运行生成configure

#./configure --prefix=/root/sysbench --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/

 

#make && make install

 

 

 

 

默认支持MySQL，如果需要测试Oracle/PostgreSQL，则在configure时需要加上–with-oracle或者–with-pgsql参数.

依赖库安装 
需要安装m4 autoconf automake libtool 这几个包，通过 
yum install m4 autoconf automake libtool可进行安装，否则会报错（ xxx… wasn’t found, exiting） 
./autogen.sh (如果是源码编译安装就不需要执行了) 
执行后在目录下会出现configure文件夹，就说明可以开始执行配置了。

./configure --prefix=/root/sysbench --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/

需要指定include和lib目录。如果没有，说明安装时候没有安装安装开发包，这个要单独安装的，找Development Libraries 包. 
安装完成后执行：

[root@localhost sysbench]# ll -rth sysbench
total 236K
-rw-r--r--. 1 root root 7.1K Jun 18 04:12 sysbench.h
[root@localhost sysbench]# make
Making all in doc
make[1]: Entering directory `/root/sysbench/doc'
......
[root@localhost sysbench]# make install
Making install in doc
make[1]: Entering directory `/root/sysbench/doc'
.....

没有报错的话就在当前的/root/sysbench/sysbench目录下会生成一个二进制可执行文件：sysbench

[root@localhost sysbench]# ll
total 4096
-rw-r--r--. 1 root root    3661 Jun 18 04:12 CMakeLists.txt -rw-r--r--. 1 root root 24391 Jun 18 04:12 db_driver.c -rw-r--r--. 1 root root 9911 Jun 18 04:12 db_driver.h -rw-r--r--. 1 root root 129832 Jun 18 08:49 db_driver.o drwxr-xr-x. 7 root root 4096 Jun 18 12:50 drivers -rw-r--r--. 1 root root 25253 Jun 18 12:50 Makefile -rw-r--r--. 1 root root 2020 Jun 18 04:12 Makefile.am -rw-r--r--. 1 root root 25998 Jun 18 06:12 Makefile.in -rw-r--r--. 1 root root 3640 Jun 18 04:12 sb_list.h -rw-r--r--. 1 root root 16223 Jun 18 04:12 sb_logger.c -rw-r--r--. 1 root root 4554 Jun 18 04:12 sb_logger.h -rw-r--r--. 1 root root 116256 Jun 18 08:49 sb_logger.o -rw-r--r--. 1 root root 13731 Jun 18 04:12 sb_options.c -rw-r--r--. 1 root root 3108 Jun 18 04:12 sb_options.h -rw-r--r--. 1 root root 109536 Jun 18 08:49 sb_options.o -rw-r--r--. 1 root root 3456 Jun 18 04:12 sb_percentile.c -rw-r--r--. 1 root root 1570 Jun 18 04:12 sb_percentile.h -rw-r--r--. 1 root root 64616 Jun 18 08:49 sb_percentile.o -rw-r--r--. 1 root root 5982 Jun 18 04:12 sb_timer.c -rw-r--r--. 1 root root 3796 Jun 18 04:12 sb_timer.h -rw-r--r--. 1 root root 54544 Jun 18 08:49 sb_timer.o -rw-r--r--. 1 root root 6860 Jun 18 04:12 sb_win.c -rw-r--r--. 1 root root 2747 Jun 18 04:12 sb_win.h drwxr-xr-x. 4 root root 4096 Jun 18 12:50 scripting -rwxr-xr-x. 1 root root 3300573 Jun 18 12:24 sysbench [root@localhost sysbench]# ./sysbench --help ./sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory [root@localhost sysbench]# ldd sysbench linux-vdso.so.1 => (0x00007fff3dd70000) libmysqlclient.so.18 => not found //重点在这里，链接库没找到，只好手动加一下 libdl.so.2 => /lib64/libdl.so.2 (0x00007f95b0e55000) librt.so.1 => /lib64/librt.so.1 (0x00007f95b0c4d000) libm.so.6 => /lib64/libm.so.6 (0x00007f95b09c9000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f95b07ab000) libc.so.6 => /lib64/libc.so.6 (0x00007f95b0417000)  /lib64/ld-linux-x86-64.so.2 (0x00007f95b1068000) [root@localhost sysbench]# ln -s /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib //手动建立一个软链接 [root@localhost sysbench]# ldconfig //生效 [root@localhost sysbench]# ldd sysbench //在查看就找到ibmysqlclient.so.18 linux-vdso.so.1 => (0x00007fffc5ffc000) libmysqlclient.so.18 => /usr/lib/libmysqlclient.so.18 (0x00007fa7895d8000) libdl.so.2 => /lib64/libdl.so.2 (0x00007fa7893d4000) librt.so.1 => /lib64/librt.so.1 (0x00007fa7891cb000) libm.so.6 => /lib64/libm.so.6 (0x00007fa788f47000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fa788d2a000) libc.so.6 => /lib64/libc.so.6 (0x00007fa788995000) libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007fa78868f000) libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fa788479000)  /lib64/ld-linux-x86-64.so.2 (0x00007fa789bb0000) [root@localhost sysbench]# ./sysbench --help //执行help成功 [root@localhost ~]# sysbench --help Missing required command argument. Usage: sysbench [general-options]... --test=<test-name> [test-options]... command General options: --num-threads=N 使用线程数 默认[1] --max-requests=N limit for total number of requests [10000] --max-time=N limit for total execution time in seconds [0] --forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off] --thread-stack-size=SIZE size of stack per thread [64K] --tx-rate=N target transaction rate (tps) [0] --report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0] --report-checkpoints=[LIST,...]dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. [] --test=STRING test to run --debug=[on|off] print more debugging info [off] --validate=[on|off] perform validation checks where possible [off] --help=[on|off] print help and exit --version=[on|off] print version and exit [off] --rand-init=[on|off] initialize random number generator [off] --rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special] --rand-spec-iter=N number of iterations used for numbers generation [12] --rand-spec-pct=N percentage of values to be treated as 'special' (for special distribution) [1] --rand-spec-res=N percentage of 'special' values to use (

默认支持MySQL，如果需要测试Oracle/PostgreSQL，则在configure时需要加上–with-oracle或者–with-pgsql参数.

依赖库安装 
需要安装m4 autoconf automake libtool 这几个包，通过 
yum install m4 autoconf automake libtool可进行安装，否则会报错（ xxx… wasn’t found, exiting） 
./autogen.sh (如果是源码编译安装就不需要执行了) 
执行后在目录下会出现configure文件夹，就说明可以开始执行配置了。

./configure --prefix=/root/sysbench --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/

需要指定include和lib目录。如果没有，说明安装时候没有安装安装开发包，这个要单独安装的，找Development Libraries 包. 
安装完成后执行：

[root@localhost sysbench]# ll -rth sysbench
total 236K
-rw-r--r--. 1 root root 7.1K Jun 18 04:12 sysbench.h
[root@localhost sysbench]# make
Making all in doc
make[1]: Entering directory `/root/sysbench/doc'
......
[root@localhost sysbench]# make install
Making install in doc
make[1]: Entering directory `/root/sysbench/doc'
.....

没有报错的话就在当前的/root/sysbench/sysbench目录下会生成一个二进制可执行文件：sysbench

[root@localhost sysbench]# ll
total 4096
-rw-r--r--. 1 root root    3661 Jun 18 04:12 CMakeLists.txt -rw-r--r--. 1 root root 24391 Jun 18 04:12 db_driver.c -rw-r--r--. 1 root root 9911 Jun 18 04:12 db_driver.h -rw-r--r--. 1 root root 129832 Jun 18 08:49 db_driver.o drwxr-xr-x. 7 root root 4096 Jun 18 12:50 drivers -rw-r--r--. 1 root root 25253 Jun 18 12:50 Makefile -rw-r--r--. 1 root root 2020 Jun 18 04:12 Makefile.am -rw-r--r--. 1 root root 25998 Jun 18 06:12 Makefile.in -rw-r--r--. 1 root root 3640 Jun 18 04:12 sb_list.h -rw-r--r--. 1 root root 16223 Jun 18 04:12 sb_logger.c -rw-r--r--. 1 root root 4554 Jun 18 04:12 sb_logger.h -rw-r--r--. 1 root root 116256 Jun 18 08:49 sb_logger.o -rw-r--r--. 1 root root 13731 Jun 18 04:12 sb_options.c -rw-r--r--. 1 root root 3108 Jun 18 04:12 sb_options.h -rw-r--r--. 1 root root 109536 Jun 18 08:49 sb_options.o -rw-r--r--. 1 root root 3456 Jun 18 04:12 sb_percentile.c -rw-r--r--. 1 root root 1570 Jun 18 04:12 sb_percentile.h -rw-r--r--. 1 root root 64616 Jun 18 08:49 sb_percentile.o -rw-r--r--. 1 root root 5982 Jun 18 04:12 sb_timer.c -rw-r--r--. 1 root root 3796 Jun 18 04:12 sb_timer.h -rw-r--r--. 1 root root 54544 Jun 18 08:49 sb_timer.o -rw-r--r--. 1 root root 6860 Jun 18 04:12 sb_win.c -rw-r--r--. 1 root root 2747 Jun 18 04:12 sb_win.h drwxr-xr-x. 4 root root 4096 Jun 18 12:50 scripting -rwxr-xr-x. 1 root root 3300573 Jun 18 12:24 sysbench [root@localhost sysbench]# ./sysbench --help ./sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory [root@localhost sysbench]# ldd sysbench linux-vdso.so.1 => (0x00007fff3dd70000) libmysqlclient.so.18 => not found //重点在这里，链接库没找到，只好手动加一下 libdl.so.2 => /lib64/libdl.so.2 (0x00007f95b0e55000) librt.so.1 => /lib64/librt.so.1 (0x00007f95b0c4d000) libm.so.6 => /lib64/libm.so.6 (0x00007f95b09c9000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f95b07ab000) libc.so.6 => /lib64/libc.so.6 (0x00007f95b0417000)  /lib64/ld-linux-x86-64.so.2 (0x00007f95b1068000) [root@localhost sysbench]# ln -s /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib //手动建立一个软链接 [root@localhost sysbench]# ldconfig //生效 [root@localhost sysbench]# ldd sysbench //在查看就找到ibmysqlclient.so.18 linux-vdso.so.1 => (0x00007fffc5ffc000) libmysqlclient.so.18 => /usr/lib/libmysqlclient.so.18 (0x00007fa7895d8000) libdl.so.2 => /lib64/libdl.so.2 (0x00007fa7893d4000) librt.so.1 => /lib64/librt.so.1 (0x00007fa7891cb000) libm.so.6 => /lib64/libm.so.6 (0x00007fa788f47000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fa788d2a000) libc.so.6 => /lib64/libc.so.6 (0x00007fa788995000) libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007fa78868f000) libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fa788479000)  /lib64/ld-linux-x86-64.so.2 (0x00007fa789bb0000) [root@localhost sysbench]# ./sysbench --help //执行help成功 [root@localhost ~]# sysbench --help Missing required command argument. Usage: sysbench [general-options]... --test=<test-name> [test-options]... command General options: --num-threads=N 使用线程数 默认[1] --max-requests=N limit for total number of requests [10000] --max-time=N limit for total execution time in seconds [0] --forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off] --thread-stack-size=SIZE size of stack per thread [64K] --tx-rate=N target transaction rate (tps) [0] --report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0] --report-checkpoints=[LIST,...]dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. [] --test=STRING test to run --debug=[on|off] print more debugging info [off] --validate=[on|off] perform validation checks where possible [off] --help=[on|off] print help and exit --version=[on|off] print version and exit [off] --rand-init=[on|off] initialize random number generator [off] --rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special] --rand-spec-iter=N number of iterations used for numbers generation [12] --rand-spec-pct=N percentage of values to be treated as 'special' (for special distribution) [1] --rand-spec-res=N percentage of 'special' values to use (