sysbench 是一个模块化、跨平台、多线程基准测试工具,主要用于测试不同系统参数下的数据库负载情况,本文主要介绍 0.4 版本的使用。sysbench 主要用于以下性能测试:
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 / lib make make install cp - r sysbench / tests / usr / local / sysbench - 0.4.12 ln - 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,
- inmicroseconds[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 Log options: --verbosity = N verbosity level {
- 5 - debug,
- 0 - only critical messages
- } [4]--percentile = N percentile rank of query response times to count[95] Compiled - intests: 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 test Commands: prepare run cleanup help version See 'sysbench --test=<name> help'
- for a list of options
- for each test.
主要的参数有:
--num-threads、
--max-requests、
--test
以下几个参数也经常会使用:
--max-time 最大的测试时长
--debug(开启 debug 可以显示更详细的每个线程的执行情况)
对 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-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 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
View Code
- [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 run sysbench 0.4.12.10 : multi - threaded system evaluation benchmark Running the test with following options: Number of threads: 12 Random number generator seed is 0 and will be ignored Doing memory operations speed test Memory block size: 8K Memory transfer size: 102400M Memory operations type: write Memory scope type: global Threads 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.02ms Threads fairness: events(avg / stddev) : 1092266.6667 / 4629.30 execution time(avg / stddev) : 7.6598 / 0.02
报告:时间 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
View Code
- [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 run sysbench 0.4.12.10 : multi - threaded system evaluation benchmark Running the test with following options: Number of threads: 12 Random number generator seed is 0 and will be ignored Doing memory operations speed test Memory block size: 8K Memory transfer size: 102400M Memory operations type: write Memory scope type: global Threads 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.02ms Threads fairness: events(avg / stddev) : 1092266.6667 / 19850.15 execution time(avg / stddev) : 5.9615 / 0.02
报告: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
View Code
- [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 run sysbench 0.4.12.10 : multi - threaded system evaluation benchmark Running the test with following options: Number of threads: 12 Random number generator seed is 0 and will be ignored Doing memory operations speed test Memory block size: 16K Memory transfer size: 102400M Memory operations type: write Memory scope type: global Threads 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.02ms Threads fairness: events(avg / stddev) : 546133.3333 / 1321.35 execution time(avg / stddev) : 5.3094 / 0.01
报告: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
View Code
- [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 run sysbench 0.4.12.10 : multi - threaded system evaluation benchmark Running the test with following options: Number of threads: 12 Random number generator seed is 0 and will be ignored Doing memory operations speed test Memory block size: 16K Memory transfer size: 102400M Memory operations type: write Memory scope type: global Threads 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.02ms Threads fairness: events(avg / stddev) : 546133.3333 / 5577.58 execution time(avg / stddev) : 3.0149 / 0.01[root@localhost sysbench - 0.4.12]#
报告:6.4S,15G/s
总结:对于分配同样大小的内存,块月大分配的速率越快,随机分配比顺序分配的速度要快
./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 run sysbench 0.4.12.10 : multi - threaded system evaluation benchmark Running the test with following options: Number of threads: 12 Random number generator seed is 0 and will be ignored Doing mutex performance test Threads 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.78ms Threads fairness: events(avg / stddev) : 1.0000 / 0.00 execution time(avg / stddev) : 0.0858 / 0.00
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 run sysbench 0.4.12.10 : multi - threaded system evaluation benchmark No DB drivers specified,
- using mysql Running the test with following options: Number of threads: 12 Random number generator seed is 0 and will be ignored Doing OLTP test.Running mixed OLTP test Using Special distribution(12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN"
- for starting transactions Using auto_inc on the id column Maximum number of requests
- for OLTP test is limited to 100000 Using 10 test tables Threads 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.00ms Threads fairness: events(avg / stddev) : 8338.7500 / 207.
报告:执行当前测试花费 126S,TPS:791/S,RQ:15046/S,95% 的请求花费 18 毫秒
事务测试,每个事务包含如下语句:
- Point queries: SELECT c FROM sbtest WHERE id = NRange queries: SELECT c FROM sbtest WHERE id BETWEEN N AND MRange SUM() queries: SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and MRange ORDER BY queries: SELECT c FROM sbtest WHERE id between N and M ORDER BY cRange DISTINCT queries: SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY cUPDATEs on index column: UPDATE sbtest SET k = k + 1 WHERE id = NUPDATEs on non - index column: UPDATE sbtest SET c = N WHERE id = MDELETE queries: DELETE FROM sbtest WHERE id = NINSERT queries: INSERT INTO sbtest VALUES(...
非事务测试,执行语句如下
- Point queries: SELECT pad FROM sbtest WHERE id = NUPDATEs on index column: UPDATE sbtest SET k = k + 1 WHERE id = NUPDATEs on non - index column: UPDATE sbtest SET c = N WHERE id = MDELETE queries: DELETE FROM sbtest WHERE id = NThe generated row IDs are unique over each test run,
- so no row is deleted twice.INSERT queries: INSERT INTO sbtest(k, c, pad) VALUES(N, M, S)
sysbench 是使用最广泛的基准压测工具,功能也很齐全报告也非常的详细。
来源: http://www.cnblogs.com/chenmh/p/5866058.html