你的位置:首页 > 数据库

[数据库]sysbench 压力测试


介绍

sysbench是一个模块化、跨平台、多线程基准测试工具,主要用于测试不同系统参数下的数据库负载情况,本文主要介绍0.4版本的使用。sysbench主要用于以下性能测试:

  • 文件I/O性能
  • 调度
  • 内存分配和传输
  • POSIX线程
  • 数据库

 

安装

1.安装插件

yum install libtool -y

2.安装

./configure --prefix=/usr/local/sysbench-0.4.12 --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/libmake make installcp -r sysbench/tests /usr/local/sysbench-0.4.12ln -s /usr/local/sysbench-0.4.12/bin/sysbench /usr/local/sysbench-0.4.12/sysbench

修改环境变量,在环境变量中加入

export LD_LIBRARY_PATH=/usr/local/mysql/lib

注意:如果安装目录中没有configure那么需要执行以下操作:

chmod +x autogen.sh

./autogen.sh

如果想要让 sysbench 支持 oracle /pgsql 的话,就需要在编译的时候加上参数
--with-oracle

或者

--with-pgsql

一般语法 

prepare:用于文件IO和数据库OLTP测试的数据准备阶段。

run:性能测试阶段

cleanup:移除测试过程中产生的数据

help:显示帮助信息,获取--test帮助可以使用--test=name --help

通用命令选项

这部分命令参数的通用的,不管--test测试什么内容都可以使用这些通用的命令。可以执行sysbench --help了解各参数的具体解释

General options: --num-threads=N       number of threads to use [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 [32K] --init-rng=[on|off]     initialize random number generator [off] --seed-rng=N        seed for random number generator, ignored when 0 [0] --tx-rate=N         target transaction rate (tps) [0] --tx-jitter=N        target transaction variation, in microseconds [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 exitLog options: --verbosity=N   verbosity level {5 - debug, 0 - only critical messages} [4] --percentile=N   percentile rank of query response times to count [95]Compiled-in tests: fileio - File I/O test cpu - CPU performance test memory - Memory functions speed test threads - Threads subsystem performance test mutex - Mutex performance test oltp - OLTP testCommands: prepare run cleanup help versionSee 'sysbench --test=<name> help' for a list of options for each test.

主要的参数有

--num-threads、

--max-requests、

--test

以下几个参数也经常会使用:

--max-time 最大的测试时长

--debug(开启debug可以显示更详细的每个线程的执行情况)

CPU测试

对CPU的性能测试通常有:1. 通过算质数;2计算圆周率等;sysbench使用的就是通过质数相加的测试。对CPU测试直接运行run即可

./sysbench --num-threads=12 --max-requests=10000 --debug=on --test=cpu --cpu-max-prime=20000 run

上面的测试是:12个线程执行1万条请求,每个请求执行质数相加到20000

thread测试

测试线程调度的性能,用于高负载下的线程性能测试。

  --thread-yields=N      每个请求执行“lock/yield/unlock”循环的次数,默认1000

  --thread-locks=N       每个线程的互斥锁,默认8个

./sysbench --num-threads=12 --max-requests=10000 --test=threads --thread-yields=100 --thread-locks=2 run

memory测试

内存分配测试,主要是针对不同的块大小进行内存的连续读写或者随机读写测试。

memory options:

--memory-block-size=SIZE  size of memory block for test [1K] --memory-total-size=SIZE  total size of data to transfer [100G] --memory-scope=STRING    memory access scope {global,local} [global] --memory-hugetlb=[on|off]  allocate memory from HugeTLB pool [off] --memory-oper=STRING    type of memory operations {read, write, none} [write] --memory-access-mode=STRING memory access mode {seq,rnd} [seq]

1.8k顺序分配

./sysbench --num-threads=12 --max-requests=10000 --test=memory --memory-block-size=8K --memory-total-size=100G --memory-access-mode=seq run

[root@localhost sysbench-0.4.12]# ./sysbench --num-threads=12 --max-requests=10000 --test=memory --memory-block-size=8K --memory-total-size=100G --memory-access-mode=seq runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 12Random number generator seed is 0 and will be ignoredDoing memory operations speed testMemory block size: 8KMemory transfer size: 102400MMemory operations type: writeMemory scope type: globalThreads started!Done.Operations performed: 13107200 (993893.95 ops/sec)102400.00 MB transferred (7764.80 MB/sec)General statistics:  total time:             13.1877s  total number of events:       13107200  total time taken by event execution: 91.9173  response time:     min:                 0.00ms     avg:                 0.01ms     max:                 0.93ms     approx. 95 percentile:        0.02msThreads fairness:  events (avg/stddev):      1092266.6667/4629.30  execution time (avg/stddev):  7.6598/0.02

View Code

报告:时间13S,7.7G/S

2.8k随机分配

./sysbench --num-threads=12 --max-requests=10000 --test=memory --memory-block-size=8K --memory-total-size=100G --memory-access-mode=rnd run

[root@localhost sysbench-0.4.12]# ./sysbench --num-threads=12 --max-requests=10000 --test=memory --memory-block-size=8K --memory-total-size=100G --memory-access-mode=rnd runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 12Random number generator seed is 0 and will be ignoredDoing memory operations speed testMemory block size: 8KMemory transfer size: 102400MMemory operations type: writeMemory scope type: globalThreads started!Done.Operations performed: 13107200 (1036066.63 ops/sec)102400.00 MB transferred (8094.27 MB/sec)General statistics:  total time:             12.6509s  total number of events:       13107200  total time taken by event execution: 71.5377  response time:     min:                 0.00ms     avg:                 0.01ms     max:                 0.90ms     approx. 95 percentile:        0.02msThreads fairness:  events (avg/stddev):      1092266.6667/19850.15  execution time (avg/stddev):  5.9615/0.02

View Code

报告:12.6S,8G/s

3.16K顺序分配

./sysbench --num-threads=12 --max-requests=10000 --test=memory --memory-block-size=16K --memory-total-size=100G --memory-access-mode=seq run

[root@localhost sysbench-0.4.12]# ./sysbench --num-threads=12 --max-requests=10000 --test=memory --memory-block-size=16K --memory-total-size=100G --memory-access-mode=seq runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 12Random number generator seed is 0 and will be ignoredDoing memory operations speed testMemory block size: 16KMemory transfer size: 102400MMemory operations type: writeMemory scope type: globalThreads started!Done.Operations performed: 6553600 (832528.85 ops/sec)102400.00 MB transferred (13008.26 MB/sec)General statistics:  total time:             7.8719s  total number of events:       6553600  total time taken by event execution: 63.7133  response time:     min:                 0.00ms     avg:                 0.01ms     max:                 0.90ms     approx. 95 percentile:        0.02msThreads fairness:  events (avg/stddev):      546133.3333/1321.35  execution time (avg/stddev):  5.3094/0.01

View Code

报告:7S,13G/S

4.16K随机分配

./sysbench --num-threads=12 --max-requests=10000 --test=memory --memory-block-size=16K --memory-total-size=100G --memory-access-mode=rnd run

[root@localhost sysbench-0.4.12]# ./sysbench --num-threads=12 --max-requests=10000 --test=memory --memory-block-size=16K --memory-total-size=100G --memory-access-mode=rnd runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 12Random number generator seed is 0 and will be ignoredDoing memory operations speed testMemory block size: 16KMemory transfer size: 102400MMemory operations type: writeMemory scope type: globalThreads started!Done.Operations performed: 6553600 (1018696.38 ops/sec)102400.00 MB transferred (15917.13 MB/sec)General statistics:  total time:             6.4333s  total number of events:       6553600  total time taken by event execution: 36.1789  response time:     min:                 0.00ms     avg:                 0.01ms     max:                 0.88ms     approx. 95 percentile:        0.02msThreads fairness:  events (avg/stddev):      546133.3333/5577.58  execution time (avg/stddev):  3.0149/0.01[root@localhost sysbench-0.4.12]# 

View Code

报告:6.4S,15G/s

总结:对于分配同样大小的内存,块月大分配的速率越快,随机分配比顺序分配的速度要快

文件io测试

./sysbench --test=fileio help

文件IO的测试主要用于测试IO的负载性能。主要的测试选项为--file-test-mode。还有几个可以关注的参数包括--file-block-size、--file-io-mode、--file-fsync-freq 、--file-rw-ratio

--file-num=N          创建测试文件的数量,默认128个 --file-block-size=N     block size大小,默认16K --file-total-size=SIZE    所有文件的总大小,默认2G --file-test-mode=STRING   测试类型 {seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)} --file-io-mode=STRING     I/O模式,需要系统支持默认sync[sync(同步IO),async(异步IO),mmap()] --file-async-backlog=N    每个线程的异步操作队列数,默认128个,需要--file-io-mode=async; --file-extra-flags=STRING   additional flags to use on opening files {sync,dsync,direct} [] --file-fsync-freq=N      当请求数达到多少时执行fsync()刷新,默认100,0代表过程中不执行fsync() --file-fsync-all=[on|off]   执行每一个写操作后执行fsync()刷新操作,默认关闭off --file-fsync-end=[on|off]   测试结束执行fsync()操作,默认开启on --file-fsync-mode=STRING   同步刷新方法,默认fsync {fsync, fdatasync}  --file-merged-requests=N   合并指定数量的IO请求,0代表不合并,默认0 --file-rw-ratio=N      读写比例,默认1.5/1

./sysbench --num-threads=12 --max-requests=10000 --test=fileio --file-total-size=3G --file-test-mode=rndrw prepare./sysbench --num-threads=12 --max-requests=10000 --test=fileio --file-total-size=3G --file-test-mode=rndrw run./sysbench --num-threads=12 --max-requests=10000 --test=fileio --file-total-size=3G --file-test-mode=rndrw clean

 上图中的第二部分的包括内容反映了当前系统的io性能大概:38M/S

互斥锁测试

互斥锁测试模拟所有线程在同一时刻并发运行

./sysbench --num-threads=12 --test=mutex --mutex-num=1024 --mutex-locks=10000 --mutex-loops=10000 run

[root@localhost sysbench-0.4.12]# ./sysbench --num-threads=12 --test=mutex --mutex-num=1024 --mutex-locks=10000 --mutex-loops=10000 runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 12Random number generator seed is 0 and will be ignoredDoing mutex performance testThreads started!Done.General statistics:  total time:             0.0869s  total number of events:       12  total time taken by event execution: 1.0301  response time:     min:                 83.86ms     avg:                 85.84ms     max:                 86.88ms     approx. 95 percentile:       86.78msThreads fairness:  events (avg/stddev):      1.0000/0.00  execution time (avg/stddev):  0.0858/0.00

oltp测试

oltp是针对数据库的基准测试,例如每次对数据库进行优化后执行基准测试来测试不同的配置的tps。可以通过以下命令了解它的有关参数:

./sysbench --test=oltp help

--oltp-test-mode=STRING          测试类型:simple(简单select测试),complex(事务测试),nontrx(非事务测试),sp(存储过程) ;默认complex --oltp-reconnect-mode=STRING       连接类型:session(每个线程到测试结束不重新连接),transaction(执行每个事务重新连接),query(每一个查询重新连接),random(随机);默认 [session] --oltp-sp-name=STRING          指定执行测试的存储过程名 --oltp-read-only=[on|off]        仅执行select测试,默认关闭 --oltp-avoid-deadlocks=[on|off]     更新过程中忽略死锁,默认[off] --oltp-skip-trx=[on|off]         语句以bigin/commit开始结尾,默认[off] --oltp-range-size=N           范围查询的范围大小,默认 [100],例如begin 100 and 200 --oltp-point-selects=N          单个事务中select查询的数量,默认 [10] --oltp-use-in-statement=N        每个查询中主键查找(in 10个值)的数量,默认 [0] --oltp-simple-ranges=N          单个事务中执行范围查询的数量(SELECT c FROM sbtest WHERE id BETWEEN N AND M),默认[1] --oltp-sum-ranges=N           单个事务中执行范围sum查询的数量,默认 [1] --oltp-order-ranges=N          单个事务中执行范围order by查询的数量,默认[1] --oltp-distinct-ranges=N         单个事务中执行范围distinct查询的数量,默认[1] --oltp-index-updates=N          单个事务中执行索引更新的操作的数量,默认[1] --oltp-non-index-updates=N        单个事务中执行非索引更新操作的数量,默认[1] --oltp-nontrx-mode=STRING        指定单独非事务测试类型进行测试,默认select {select, update_key, update_nokey, insert, delete} [select] --oltp-auto-inc=[on|off]         id列默认自增,默认[on] --oltp-connect-delay=N          指定每一次重新连接延时的时长,默认1秒 [10000] --oltp-user-delay-min=N         minimum time in microseconds to sleep after each request [0] --oltp-user-delay-max=N         maximum time in microseconds to sleep after each request [0] --oltp-table-name=STRING         指定测试的表名,默认[sbtest] --oltp-table-size=N           指定表的记录大小,默认[10000] --oltp-dist-type=STRING         随机数分布状态。uniform(均匀分布)、gauss(高斯分布)、special(特殊分布),默认 [special] --oltp-dist-iter=N            number of iterations used for numbers generation [12] --oltp-dist-pct=N            启用百分比特殊分布,默认 [1] --oltp-dist-res=N            special 百分比[75] --oltp-point-select-mysql-handler=[on|off] Use MySQL HANDLER for point select [off] --oltp-point-select-all-cols=[on|off]  select查询测试时select所有列,默认[off] --oltp-secondary=[on|off]        索引不是主键索引而是二级索引,默认[off] --oltp-num-partitions=N         指定表分区的数量,默认 [0] --oltp-num-tables=N           指定测试表的数量,默认[1]General database options: --db-driver=STRING 指定测试数据库类型,默认mysql --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]mysql options: --mysql-host=[LIST,...]    MySQL server host [localhost] --mysql-port=N        MySQL server port [3306] --mysql-socket=STRING     MySQL socket --mysql-user=STRING      MySQL user [sbtest] --mysql-password=STRING    MySQL password [] --mysql-db=STRING       MySQL database name [sbtest] --mysql-table-engine=STRING  storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb] --mysql-engine-trx=STRING   whether storage engine used is transactional or not {yes,no,auto} [auto] --mysql-ssl=[on|off]     use SSL connections, if available in the client library [off] --myisam-max-rows=N      max-rows parameter for MyISAM tables [1000000] --mysql-create-options=STRING additional options passed to CREATE TABLE []

oltp测试主要会有以下相关参数的测试,,其它相关参数默认即可,有需求也可以自定义:

--mysql-engine-trx=STRING   指定不同的存储引擎测试。--oltp-test-mode=STRING    测试类型:simple(简单select测试),complex(事务测试),nontrx(非事务测试),sp(存储过程) ;默认complex--oltp-sp-name=STRING     指定存储过程进行语句测试--oltp-table-size=N      指定表的记录大小,默认[10000]--oltp-num-tables=N      指定测试表的数量,默认[1]

需要先创建好测试数据库sbtest。

1.事务测试,测试12个线程执行1万条请求,10个表,每个表大小100W

./sysbench --num-threads=12 --max-requests=100000 --test=oltp --mysql-user=root --mysql-password=root --oltp-test-mode=complex --mysql-db=sbtest --oltp-table-size=1000000 --oltp-num-tables=10 prepare

./sysbench --num-threads=12 --max-requests=100000 --test=oltp --mysql-user=root --mysql-password=root --oltp-test-mode=complex --mysql-db=sbtest --oltp-table-size=1000000 --oltp-num-tables=10 run

./sysbench --num-threads=12 --max-requests=100000 --test=oltp --mysql-user=root --mysql-password=root --oltp-test-mode=complex --mysql-db=sbtest --oltp-table-size=1000000 --oltp-num-tables=10 cleanup

[root@localhost sysbench-0.4.12]# ./sysbench --num-threads=12 --max-requests=100000 --test=oltp --mysql-user=root --mysql-password=root --oltp-test-mode=complex --mysql-db=sbtest --oltp-table-size=1000000 --oltp-num-tables=10 runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkNo DB drivers specified, using mysqlRunning the test with following options:Number of threads: 12Random number generator seed is 0 and will be ignoredDoing OLTP test.Running mixed OLTP testUsing Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)Using "BEGIN" for starting transactionsUsing auto_inc on the id columnMaximum number of requests for OLTP test is limited to 100000Using 10 test tablesThreads started!Done.OLTP test statistics:  queries performed:    read:              1400910    write:              500325    other:              200130    total:              2101365  transactions:            100065 (791.94 per sec.)  deadlocks:              0   (0.00 per sec.)  read/write requests:         1901235 (15046.89 per sec.)  other operations:          200130 (1583.88 per sec.)General statistics:  total time:             126.3540s  total number of events:       100065  total time taken by event execution: 1513.9202  response time:     min:                 5.82ms     avg:                 15.13ms     max:                2352.66ms     approx. 95 percentile:       18.00msThreads fairness:  events (avg/stddev):      8338.7500/207.

报告:执行当前测试花费126S,TPS:791/S,RQ:15046/S,95%的请求花费18毫秒

事务测试,每个事务包含如下语句:

 Point queries:SELECT c FROM sbtest WHERE id= N Range queries:SELECT c FROM sbtest WHERE id BETWEEN N AND M Range SUM() queries:SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M Range ORDER BY queries:SELECT c FROM sbtest WHERE id between N and M ORDER BY c Range DISTINCT queries:SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BYc UPDATEs on index column:UPDATE sbtest SET k=k+1 WHERE id= N UPDATEs on non-index column:UPDATE sbtest SET c= N WHERE id= M DELETE queries:DELETE FROM sbtest WHERE id= N INSERT queries:INSERT INTO sbtest VALUES ( ...


 非事务测试,执行语句如下

 Point queries:SELECT pad FROM sbtest WHERE id= N UPDATEs on index column:UPDATE sbtest SET k=k+1 WHERE id= N UPDATEs on non-index column:UPDATE sbtest SET c= N WHERE id= M DELETE queries:DELETE FROM sbtest WHERE id= N The generated row IDs are unique over each test run, so no rowis deleted twice. INSERT queries:INSERT INTO sbtest (k, c, pad) VALUES( N , M , S )

总结

 sysbench是使用最广泛的基准压测工具,功能也很齐全报告也非常的详细。

 

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接。

《欢迎交流讨论》