MySQL基准测试工具-sysbench安装测试

173次阅读

共计 7673 个字符，预计需要花费 20 分钟才能阅读完成。

环境：CentOS6.5，MySQL5.7.22
1. 下载 sysbench 下载地址
2. 安装

tar -zxvf sysbench-1.1.0.tar.gz
yum install automake libtool –y #安装依赖包
cd sysbench-0.4.12.14
./autogen.sh
./configure –with-mysql-includes=/usr/local/mysql/include –with-mysql-libs=/usr/local/mysql/lib –with-mysql
export LD_LIBRARY_PATH=/usr/local/mysql/include #这里换成机器中 mysql 路径下的 include
make
make install
sysbench –version # 检验安装是否成功

3. 简介
sysbench 是跨平台的基准测试工具，支持多线程，支持多种数据库，主要包括以下几种测试：
•CPU 性能
•磁盘 io 性能
•调度程序性能
•内存分配及传输速度
•POSIX 线程性能
•数据库性能（基于 OLTP 的基本测试）

4.sysbench 语法 sysbench [options] [testname] [command]
•command sysbench 要执行的命令，包括 prepare，run，cleanup。
•testname 指定要进行的测试

•options
◦–mysql-host MySQL 服务器主机名
◦–mysql-port MySQL 服务器端口，默认为 3306
◦–mysql-user MySQL 用户名
◦–mysql-password MySQL 密码
◦–oltp-test-mode 执行模式，默认为 complex
◦–oltp-tables-size 测试表的大小
◦–oltp-tables-count 测试表的数量
◦–threads 客户端的并发连接
◦–time 测试执行的时间
5. 测试实例

•CPU 测试 ◦–cpu-max-prime=N upper limit for primes generator [10000]
sysbench –cpu-max-prime=20000 –report-interval=10

•fileio 测试
* 4 个线程，随机读写，每 10s 输出一次结果
[root@slave1 sysbench-master]# sysbench fileio help
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
fileio options:
–file-num=N number of files to create [128] #需要创建的文件数，默认为 128
–file-block-size=N block size to use in all IO operations [16384]
–file-total-size=SIZE total size of files to create [2G] 创建的文件总大小
–file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw} #读写方式
–file-io-mode=STRING file operations mode {sync,async,mmap} [sync] # 文件操作模式 {sync( 同步),async(异步),fastmmap(快速 map 映射),slowmmap(慢 map 映射)}。默认是 sync
–file-async-backlog=N number of asynchronous operatons to queue per thread [128]
–file-extra-flags=[LIST,…] list of additional flags to use to open files {sync,dsync,direct} [] #使用额外的标志来打开文件 {sync,dsync,direct}。默认为空
–file-fsync-freq=N do fsync() after this number of requests (0 – don’t use fsync()) [100]
–file-fsync-all[=on|off] do fsync() after each write operation [off]
–file-fsync-end[=on|off] do fsync() at the end of test [on]
–file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync] #使用哪种方法进行同步 {fsync, fdatasync}。默认是 fsync
–file-merged-requests=N merge at most this number of IO requests if possible (0 – don’t merge) [0] #如果可以，合并最多的 IO 请求数 (0–表示不合并)。默认是 0
–file-rw-ratio=N reads/writes ratio for combined test [1.5] #测试时的读写比例。默认是 1.5

[root@slave1 sysbench-master]# sysbench –threads=4 –file-test-mode=rndwr –report-interval=10 fileio prepare #准备阶段
[root@slave1 sysbench-master]# sysbench –threads=4 –time=60 –report-interval=10 –file-test-mode=rndrw fileio run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Extra file open flags: (none)
128 files, 16MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads…

Threads started!

[10s] reads: 3.65 MiB/s writes: 2.44 MiB/s fsyncs: 494.49/s latency (ms,95%): 30.265
[20s] reads: 3.00 MiB/s writes: 2.00 MiB/s fsyncs: 412.60/s latency (ms,95%): 34.954
[30s] reads: 3.28 MiB/s writes: 2.19 MiB/s fsyncs: 444.19/s latency (ms,95%): 35.589
[40s] reads: 3.19 MiB/s writes: 2.13 MiB/s fsyncs: 434.41/s latency (ms,95%): 38.247
[50s] reads: 3.00 MiB/s writes: 2.00 MiB/s fsyncs: 410.90/s latency (ms,95%): 37.565
[60s] reads: 3.09 MiB/s writes: 2.06 MiB/s fsyncs: 419.19/s latency (ms,95%): 35.589

Throughput:
read: IOPS=204.22 3.19 MiB/s (3.35 MB/s)
write: IOPS=136.15 2.13 MiB/s (2.23 MB/s)
fsync: IOPS=442.70

Latency (ms):
min: 0.00
avg: 5.14
max: 310.70
95th percentile: 35.59
sum: 239996.51

[root@slave1 sysbench-master]# sysbench –threads=4 –time=60 –report-interval=10 –file-test-mode=rndrw fileio cleanup #清理文件
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Removing test files…

•内存测试 ◦测试 4 个线程，60 秒，每 10 秒输出一次报告信息，总内存大小 100G，随机访问模式

[root@slave1 sysbench-master]# sysbench –threads=4 –time=60 –report-interval=10 –memory-total-size=100G –memory-access-mode=rnd memory run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Running memory speed test with the following options:
block size: 1KiB
total size: 102400MiB
operation: write
scope: global

Initializing worker threads…

Threads started!

[10s] 671.96 MiB/sec
[20s] 672.65 MiB/sec
[30s] 667.18 MiB/sec
[40s] 675.73 MiB/sec
[50s] 676.82 MiB/sec
Total operations: 41341475 (689021.00 per second)

40372.53 MiB transferred (672.87 MiB/sec)

Throughput:
events/s (eps): 689021.0010
time elapsed: 60.0003s
total number of events: 41341475

Latency (ms):
min: 0.00
avg: 0.01
max: 20.25
95th percentile: 0.00
sum: 218390.97

Threads fairness:
events (avg/stddev): 10335368.7500/59009.67
execution time (avg/stddev): 54.5977/0.14
•MySQL 测试 ◦压测实例，4 张表，每张表 10W 条记录，压测 120 秒

sysbench –db-driver=mysql –time=120 –threads=4 –report-interval=10 –mysql-host=localhost –mysql-port=2433 –mysql-socket=/data/mysqldata/mysql.sock –mysql-user=7roaddba –mysql-password=y%6bm@3o_q1a –mysql-db=inc –tables=4 –table-size=100000 oltp_read_write prepare ## 准备

sysbench –db-driver=mysql –time=120 –threads=4 –report-interval=10 –mysql-host=localhost –mysql-port=3306 –mysql-socket=/data/mysqldata/mysql.sock –mysql-user=7roaddba –mysql-password=y%6bm@3o_q1a –mysql-db=inc –tables=4 –table-size=100000 oltp_read_write run ## 测试
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads…

Threads started!

[10s] thds: 4 tps: 111.37 qps: 2233.72 (r/w/o: 1564.69/445.88/223.14) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00
[20s] thds: 4 tps: 197.50 qps: 3950.22 (r/w/o: 2764.42/790.80/395.00) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[30s] thds: 4 tps: 211.10 qps: 4222.06 (r/w/o: 2955.87/843.99/422.20) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[40s] thds: 4 tps: 213.70 qps: 4272.79 (r/w/o: 2991.39/854.00/427.40) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[50s] thds: 4 tps: 211.80 qps: 4239.27 (r/w/o: 2967.15/848.41/423.71) lat (ms,95%): 43.39 err/s: 0.10 reconn/s: 0.00
[60s] thds: 4 tps: 223.10 qps: 4459.15 (r/w/o: 3121.36/891.59/446.19) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
[70s] thds: 4 tps: 190.40 qps: 3811.24 (r/w/o: 2667.73/762.71/380.80) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[80s] thds: 4 tps: 183.00 qps: 3660.05 (r/w/o: 2561.97/732.09/366.00) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[90s] thds: 4 tps: 211.60 qps: 4226.63 (r/w/o: 2958.32/845.11/423.20) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[100s] thds: 4 tps: 213.30 qps: 4272.92 (r/w/o: 2991.64/854.58/426.69) lat (ms,95%): 39.65 err/s: 0.10 reconn/s: 0.00
[110s] thds: 4 tps: 212.51 qps: 4249.12 (r/w/o: 2974.88/849.22/425.01) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
[120s] thds: 4 tps: 183.00 qps: 3660.19 (r/w/o: 2561.80/732.40/366.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 330820 #总 select 数量
write: 94513 # 总 update，insert，delete 数量
other: 47258 ##commit，unlock tables 数量
total: 472591
transactions: 23628 (196.88 per sec.) #通常需要关注的数字 (TPS)
queries: 472591 (3937.94 per sec.) #
ignored errors: 2 (0.02 per sec.) # 忽略的错误数
reconnects: 0 (0.00 per sec.)