如您需要技术咨询、解决方案定制、故障排除、运维监控等服务，可联系ericwcn#at#163.com。
> 数据库 > sysbench测试MySQL性能

sysbench测试MySQL性能

数据库 立杰 565℃ 0评论

sysbench介绍

sysbench 是一款基于LuaJIT的多线程性能基准测试工具,通常用来测试数据库的性能指标.同时它也支持创建其他复杂测试功能.

sysbench提供如下基准测试:

  • oltp_*.lua: 收集数据库OLTP基准
  • fileio: 文件系统I/O基准
  • cpu: 简单的CPU基准
  • memory: 内存访问基准
  • threads: 基于线程的调度器基准
  • mutex: POSIX互斥基准

sysbench安装

Linux RPM/DEB 方式安装

sysbench提供了基于x86_64,i386,aarch64平台的二进制安装包,同时提供了Shell脚本,能够自动添加安装源.

#Debian/Ubuntu
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench

#RHEL/CentOS:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

#Fedora:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash	
sudo dnf -y install sysbench

sysbench通用语法

* prepare                       准备阶段，也就是装载数据
* run                           实际测试阶段
* cleanup                       收尾阶段，清除之前测试的数据。

--threads=N                     使用的线程数量，默认1 [1]
--events=N                      limit for total number of events [0]
--time=N                        总执行时间，单位/s [10]
--forced-shutdown=STRING        超过--time后强制中断，默认为off。 [off]
--thread-stack-size=SIZE        每个线程的stack大小，默认为64K [64K]
--rate=N                        平均请求率, 0 为不限制 [0]
--report-interval=N             表示N秒输出一次测试进度报告，0表示关闭测试进度报告输出，仅输出最终的报告结果，默认值为0。[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. []
--debug[=on|off]                print more debugging info [off]
--validate[=on|off]             perform validation checks where possible [off]
--help[=on|off]                 print help and exit [off]
--version[=on|off]              print version and exit [off]
--config-file=FILENAME          File containing command line options
--tx-rate=N                     deprecated alias for --rate [0]
--max-requests=N                deprecated alias for --events [0]
--max-time=N                    deprecated alias for --time [0]
--num-threads=N                 deprecated alias for --threads [1]

MySQL数据库性能测试

sysbench oltp.lua --threads=2 --db-driver=mysql --mysql-host=172.17.88.8 --mysql-port=3306 --mysql-user=sysbench --mysql-password=password --mysql-db=sbtest --oltp_tables_count=10 --oltp-table-size=1000000 --oltp-read-only=off --report-interval=10 --rand-type=uniform --time=3600 --requests=0 --percentile=99 prepare
sysbench oltp.lua --threads=2 --db-driver=mysql --mysql-host=172.17.88.8 --mysql-port=3306 --mysql-user=sysbench --mysql-password=password --mysql-db=sbtest --oltp_tables_count=10 --oltp-table-size=1000000 --oltp-read-only=off --report-interval=10 --rand-type=uniform --time=3600 --requests=0 --percentile=99 run

fileio测试

获取fileio参数帮助信息:

sysbench fileio help
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)

fileio options:
--file-num=N                       创建测试文件数量，默认是 [128]
--file-block-size=N                测试时文件块大小，默认是[16384]，即16K
--file-total-size=SIZE             测试文件的总大小，默认是 [2G]
--file-test-mode=STRING            测试模式 {seqwr（顺序写）, seqrewr（顺序读写）, seqrd（顺序读）, rndrd（随机读）, rndwr（随机写）, rndrw（随机读写）}
--file-io-mode=STRING              文件操作模式{sync,async,mmap} [sync]
--file-extra-flags=STRING          使用额外的标志打开文件 {sync,dsync,direct} []
--file-fsync-freq=N                执行fsync()频率，0表示不使用，默认100
--file-fsync-all=[on|off]          每执行一次写操作执行一次fsync(),默认 [off]
--file-fsync-end=[on|off]          测试结束执行fsync(),默认 [on]
--file-fsync-mode=STRING           同步方法 {fsync, fdatasync} [fsync]
--file-merged-requests=N           如果可以，则合并IO请求数，默认0表示不合并
--file-rw-ratio=N                  测试读写比例，默认[1.5]

FileIO测试：

# prepare 命令创建了128个文件总共大小为20G ，文件读写模式为随机读写混合方式。
$ sysbench fileio --threads=2 --file-num=128  --file-total-size=20G --file-test-mode=rndrw prepare

sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)

128 files, 163840Kb each, 20480Mb total
Creating files for the test...
Extra file open flags: 0
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
.....

#run 命令则进行测试，并返回结果
$ sysbench fileio --threads=2 --file-num=128  --file-total-size=20G --file-test-mode=rndrw run
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 2
Initializing random number generator from current time


Extra file open flags: 0
128 files, 160MiB each
20GiB 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!


File operations:
    reads/s:                      696.14
    writes/s:                     463.99
    fsyncs/s:                     1484.46

Throughput:
    read, MiB/s:                  10.88
    written, MiB/s:               7.25

General statistics:
    total time:                          10.0002s
    total number of events:              26452

Latency (ms):
         min:                                  0.00
         avg:                                  0.75
         max:                                 30.29
         95th percentile:                      1.86
         sum:                              19952.43

Threads fairness:
    events (avg/stddev):           13226.0000/171.00
    execution time (avg/stddev):   9.9762/0.00

# 请求数是(696.14+463.99+1484.46)/s，吞吐量是（10.88+7.25）MB/s
#cleanup 删除测试产生的文件！
$ sysbench fileio --num-threads=2 --file-num=128  --file-total-size=20G --file-test-mode=rndrw cleanup

CPU测试

CPU性能测试主要是计算最大素数(时间越短越好).如:100W内最大的的素数.

$ sysbench cpu help
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)

cpu options:
  --cpu-max-prime=N upper limit for primes generator [10000]
#计算小于1百万的最大质数,并发线程数2,最大请求数20
$ sysbench cpu --num-threads=2 --max-requests=20 --debug --cpu-max-prime=1000000 run
...
General statistics:
    total time:                          10.9840s
    total number of events:              14

Latency (ms):
         min:                               1410.55
         avg:                               1568.67
         max:                               1709.88
         95th percentile:                   1708.63
         sum:                              21961.36

Threads fairness:
    events (avg/stddev):           7.0000/0.00
    execution time (avg/stddev):   10.9807/0.00
...

内存测试

内存测试时顺序读/写内存.以不同块大小传输一定数量的数据吞吐量大小(结果越大越好)

$ sysbench memory help
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)

memory options:
  --memory-block-size=SIZE    测试时内存块大小 [1K]
  --memory-total-size=SIZE    总的数据传输量 [100G]
  --memory-scope=STRING       内存访问访问 {global,local} [global]
  --memory-hugetlb[=on|off]   从 HugeTLB pool 分配内存 [off]
  --memory-oper=STRING        内存操作类型 {read, write, none} [write]
  --memory-access-mode=STRING 内存访问方式 {seq,rnd} [seq]
  
# 测试传输4G数据,每个块大小为8K,连续读写的数据吞吐量.
$ sysbench memory --memory-block-size=8K --memory-total-size=4G run
#测试范围8G,并发线程数2,最大请求数20, 读
$ sysbench memory --num-threads=2 --max-requests=20 --memory-block-size=8K --memory-total-size=8G --memory-oper=read run
...
Total operations: 1048576 (2010092.05 per second)

8192.00 MiB transferred (15703.84 MiB/sec)

General statistics:
    total time:                          0.5195s
    total number of events:              1048576
...

#测试范围8G,并发线程数2,最大请求数20, 写
$ sysbench memory --threads=2 --max-requests=20 --memory-block-size=8K --memory-total-size=8G --memory-oper=write run
...
Total operations: 1048576 (1138371.74 per second)

8192.00 MiB transferred (8893.53 MiB/sec)

General statistics:
    total time:                          0.9186s
    total number of events:              1048576
...

参考:

转载请注明：知识库 » sysbench测试MySQL性能

喜欢 (0)
发表我的评论
取消评论

表情

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址