***1.sysbench 下载
https://github.com/akopytov/sysbench 下载sysbench源代码
sysbench futures:
1、CPU运算性能
2、磁盘IO性能
3、调度程序性能
4、内存分配及传输速度
5、POSIX线程性能
6、数据库性能(OLTP基准测试)
***2.sysbench 安装
[root@db01 mysql]# cd /arp/software/
[root@db01 software]# unzip sysbench-0.5.zip
[root@db01 software]# cd sysbench-0.5
./autogen.sh
./configure --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysql
make && make install
***3.创建zsdtest数据库
[root@db01 software]# mysql -uroot -p'Redhat@7101' -e'create database zsdtest';
***4.OLTP测试数据准备
sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password='Redhat@7101' --mysql-db=zsdtest \
--test=/arp/software/sysbench-0.5/sysbench/tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=500000 --rand-init=on prepare
选项解释:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--test=/arp/software/sysbench-0.5/sysbench/tests/db/oltp.lua 表示调用 /arp/software/sysbench-0.5/sysbench/tests/db/oltp.lua 脚本进行 oltp 模式测试
--oltp_tables_count=10 表示会生成 10 个测试表
--oltp-table-size=500000 表示每个测试表填充数据量为 500000
--rand-init=on 表示每个测试表都是用随机数据来填充的
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***5.OLTP测试数据运行
sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=root \
--mysql-password='Redhat@7101' --mysql-db=zsdtest --test=/arp/software/sysbench-0.5/sysbench/tests/db/oltp.lua --oltp_tables_count=10 \
--oltp-table-size=5000000 --num-threads=8 --oltp-read-only=off \
--report-interval=10 --rand-type=uniform --max-time=3600 \
--max-requests=0 --percentile=99 run >> ./log/sysbench_oltpX_8_201606281607.log
选项解释:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--num-threads=8 表示发起 8个并发连接
--oltp-read-only=off 表示不要进行只读测试，也就是会采用读写混合模式测试
--report-interval=10 表示每10秒输出一次测试进度报告
--rand-type=uniform 表示随机类型为固定模式，其他几个可选随机模式：uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托)
--max-time=3600 表示最大执行时长为 3600秒
--max-requests=0 表示总请求数为 0，因为上面已经定义了总执行时长，所以总请求数可以设定为 0；也可以只设定总请求数，不设定最大执行时长
--percentile=99 表示设定采样比例，默认是 95%，即丢弃1%的长请求，在剩余的99%里取最大值
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***测试结果解读:
[root@db01 log]# more sysbench_oltpX_8_201606281607.log
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored
Initializing worker threads...
Threads started!
....
[3580s] threads: 8, tps: 693.01, reads: 9700.98, writes: 2771.02, response time: 47.64ms (99%), errors: 0.00, reconnects: 0.00
[3590s] threads: 8, tps: 611.50, reads: 8562.18, writes: 2446.79, response time: 139.96ms (99%), errors: 0.00, reconnects: 0.00
[3600s] threads: 8, tps: 552.20, reads: 7732.13, writes: 2209.31, response time: 155.19ms (99%), errors: 0.00, reconnects: 0.00
OLTP test statistics:
queries performed:
read: 33497786 --- 读总数
write: 9570747 --- 写总数
other: 4785357 --- 其他操作总数(select,insert,update,delete以外的操作，例如commit等)
total: 47853890 --- 全部总数
transactions: 2392658 (664.63 per sec.) --- 总事务数(每秒事务数)
read/write requests: 43068533 (11963.45 per sec.) --- 读写总数(每秒读写次数)
other operations: 4785357 (1329.26 per sec.) --- 其他操作总数(每秒其他操作次数)
ignored errors: 41 (0.01 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics: --- 常规统计结果
total time: 3600.0079s --- 耗时时间
total number of events: 2392658 --- 共发生的事务数
total time taken by event execution: 28790.4089s --- 所有事务耗时相加(不考虑并行因素)
response time: --- 响应时间统计
min: 3.18ms --- 最小耗时
avg: 12.03ms --- 平均耗时
max: 2528.39ms --- 最长耗时
approx. 99 percentile: 124.65ms --- 超过99%平均耗时
Threads fairness:
events (avg/stddev): 299082.2500/2282.75
execution time (avg/stddev): 3598.8011/0.03
以上数据来自测试环境如下:
机型: PowerEdge M820
CPU: Intel(R) Xeon(R) CPU E5-4610 v2 @ 2.30Ghz(8核,16线程)*4
内存: 256GB
存储: HP 3PAR 共享存储,(SATA盘 3TB 7200 RPM)
虚拟云平台 Vmware vSphere
操作系统 CENTOS 6.5
文件系统 ext4
MySQL版本 5.7.13 MySQL Community Server
文献参考:http://imysql.com/tag/sysbench sysbench安装、使用、结果解读
