开发者社区> 白海生> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

Mysql 压力测试-sysbench

简介: sysbench安装、测试,主要从OLTP、CPU,IO,Memory四个方面测试 报错处理 checking build system type.
+关注继续查看

sysbench安装

./configure  --prefix=/sysbench/sysbench/ --with-mysql-includes=/mysql/mysql/include/ --with-mysql-libs=/mysql/mysql/lib/  --with-mysql

make

make install


安装过程报错处理

checking build system type... Invalid configuration `x86_64-unknown-linux-': machine `x86_64-unknown-linux' not recognized

configure: error: /bin/sh config/config.sub x86_64-unknown-linux- failed


解决方法:安装相关依赖包

yum install libtool


报错信息:

/usr/bin/ld: cannot find -lmysqlclient_r
collect2: ld returned 1 exit status
make[2]: *** [sysbench] Error 1
make[2]: Leaving directory `/sysbench/sysbench/sysbench'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/sysbench/sysbench/sysbench'
make: *** [all-recursive] Error 1


解决方法:

ln -s libmysqlclient.so.20.3.4 libmysqlclient_r.so


##sysbench测试,主要从OLTP、CPU,IO,Memory四个方面测试

1、OLTP测试


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]
 
 
prepare --生成需要的测试表
sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=bhs --oltp-table-size=100000 --db-driver=mysql --num-threads=2 --mysql-socket=/mysql/data/mysql.sock --mysql-user=root --mysql-host=localhost --mysql-password=root prepare

run     --性能测试阶段
sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=bhs --oltp-table-size=100000 --db-driver=mysql --num-threads=2 --mysql-socket=/mysql/data/mysql.sock --mysql-user=root --mysql-host=localhost --mysql-password=root run

cleanup  --清理测试时的测试表
[root@slv2 ~]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=bhs --oltp-table-size=100000 --db-driver=mysql --num-threads=2 --mysql-socket=/mysql/data/mysql.sock --mysql-user=root --mysql-host=localhost --mysql-password=root cleanup


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 10000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (141.69 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (2692.10 per sec.)
    other operations:                    20000  (283.38 per sec.)

Test execution summary:
    total time:                          70.5769s
    total number of events:              10000
    total time taken by event execution: 141.0690
    per-request statistics:
         min:                            0.0063s
         avg:                            0.0141s
         max:                            0.0886s
         approx.  95 percentile:         0.0224s

Threads fairness:
    events (avg/stddev):           5000.0000/69.00
    execution time (avg/stddev):   70.5345/0.00

结果:测试执行时间:70.5769秒,TPS:141.69/s RQ:2692.10/s  95%的请求花费了0.0224s   
   
2、cpu测试
sysbench --test=cpu --num-threads=12 --max-requests=10000 --cpu-max-prime=20000 run

Doing CPU performance benchmark

Threads started!
Done.

Maximum prime number checked in CPU test: 20000


Test execution summary:
    total time:                          31.1477s
    total number of events:              10000
    total time taken by event execution: 373.0990
    per-request statistics:
         min:                            0.0024s
         avg:                            0.0373s
         max:                            0.1288s
         approx.  95 percentile:         0.0532s

Threads fairness:
    events (avg/stddev):           833.3333/2.75
    execution time (avg/stddev):   31.0916/0.03
   
   
3、文件IO测试
/sysbench --test=fileio help

--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  --test=fileio --num-threads=12 --max-requests=10000 --file-total-size=3G --file-test-mode=rndrw prepare
128 files, 24576Kb each, 3072Mb total
Creating files for the test...

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

Running the test with following options:
Number of threads: 12

Extra file open flags: 0
128 files, 24Mb each
3Gb total file size
Block size 16Kb
Number of random requests for random IO: 10000
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
Threads started!
Done.

Operations performed:  6004 Read, 3996 Write, 12800 Other = 22800 Total
Read 93.812Mb  Written 62.438Mb  Total transferred 156.25Mb  (16.923Mb/sec)
 1083.05 Requests/sec executed

Test execution summary:
    total time:                          9.2332s
    total number of events:              10000
    total time taken by event execution: 14.3464
    per-request statistics:
         min:                            0.0000s
         avg:                            0.0014s
         max:                            0.6224s
         approx.  95 percentile:         0.0063s

Threads fairness:
    events (avg/stddev):           833.3333/67.54
    execution time (avg/stddev):   1.1955/0.13
   
   

sysbench  --test=fileio --num-threads=12 --max-requests=10000 --file-total-size=3G --file-test-mode=rndrw  cleanup
Removing test files...


thread测试  --用于高负载下的线程性能测试
thread-yields  每个请求执行“lock/yield/unlock" 循环次数 默认1000
thread-locks    每个线程的互斥锁 默认8个


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

Running the test with following options:
Number of threads: 12

Doing thread subsystem performance test
Thread yields per test: 100 Locks used: 2
Threads started!
Done.


Test execution summary:
    total time:                          1.0595s
    total number of events:              10000
    total time taken by event execution: 12.6745
    per-request statistics:
         min:                            0.0000s
         avg:                            0.0013s
         max:                            0.0374s
         approx.  95 percentile:         0.0086s

Threads fairness:
    events (avg/stddev):           833.3333/36.08
    execution time (avg/stddev):   1.0562/0.00
   
4、memory测试   --主要是针对不同的块大小进行内存的连续读写或者随机读写测试

--memory-access-mode=seq --顺序分配
sysbench  --test=memory --num-threads=12 --max-requests=10000 --memory-block-size=8K --memory-total-size=1G --memory-access-mode=seq  run


Running the test with following options:
Number of threads: 12

Doing memory operations speed test
Memory block size: 8K

Memory transfer size: 1024M

Memory operations type: write
Memory scope type: global
Threads started!
Done.

Operations performed: 131072 (624394.96 ops/sec)

1024.00 MB transferred (4878.09 MB/sec)


Test execution summary:
    total time:                          0.2099s
    total number of events:              131072
    total time taken by event execution: 2.1804
    per-request statistics:
         min:                            0.0000s
         avg:                            0.0000s
         max:                            0.0336s
         approx.  95 percentile:         0.0000s

Threads fairness:
    events (avg/stddev):           10922.6667/1094.34
    execution time (avg/stddev):   0.1817/0.02
   
--memory-access-mode=rnd --随机分配
sysbench  --test=memory --num-threads=12 --max-requests=10000 --memory-block-size=8K --memory-total-size=1G --memory-access-mode=rnd  run

Operations performed: 131072 (961669.38 ops/sec)

1024.00 MB transferred (7513.04 MB/sec)


Test execution summary:
    total time:                          0.1363s
    total number of events:              131072
    total time taken by event execution: 1.0696
    per-request statistics:
         min:                            0.0000s
         avg:                            0.0000s
         max:                            0.0310s
         approx.  95 percentile:         0.0000s

Threads fairness:
    events (avg/stddev):           10922.6667/2127.00
    execution time (avg/stddev):   0.0891/0.01

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

Doing memory operations speed test
Memory block size: 16K

Memory transfer size: 1024M

Memory operations type: write
Memory scope type: global
Threads started!
Done.

Operations performed: 65536 (973518.40 ops/sec)

1024.00 MB transferred (15211.23 MB/sec)


Test execution summary:
    total time:                          0.0673s
    total number of events:              65536
    total time taken by event execution: 0.4436
    per-request statistics:
         min:                            0.0000s
         avg:                            0.0000s
         max:                            0.0272s
         approx.  95 percentile:         0.0000s

Threads fairness:
    events (avg/stddev):           5461.3333/1120.48
    execution time (avg/stddev):   0.0370/0.01

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

Doing memory operations speed test
Memory block size: 16K

Memory transfer size: 1024M

Memory operations type: write
Memory scope type: global
Threads started!
Done.

Operations performed: 65536 (284231.81 ops/sec)

1024.00 MB transferred (4441.12 MB/sec)


Test execution summary:
    total time:                          0.2306s
    total number of events:              65536
    total time taken by event execution: 2.4985
    per-request statistics:
         min:                            0.0000s
         avg:                            0.0000s
         max:                            0.0504s
         approx.  95 percentile:         0.0000s

Threads fairness:
    events (avg/stddev):           5461.3333/462.35
    execution time (avg/stddev):   0.2082/0.01

 测试结果:分配同样大小的内存 块越大分配速率越快,随机分配比顺序分配的速度快







版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用sysbench压力测试MySQL(三)(r12笔记第6天)
  昨天使用gdb调试MySQL中事务临界状态的时候,发现其实有些场景可能比我想得还要复杂一些,所以我在昨天的测试中结尾也是快快扫过,但是表明了意思即可。这一点上我在后面会把Oracle的临界事务状态也拿出来对比一下,还是蛮有意思的。
2053 0
使用sysbench压力测试MySQL(一)(r11笔记第3天)
今天用了下新版本的sysbench,发现和早期版本的差别还不小,确实有不少有趣的地方,是的,我们继续测试下MySQL。 如果大家看过《高性能MySQL》这本书,就会发现里面对于基准测试的描述非常全面和专业,里面的测试场景都是基于早期版本,这个版本有一个不太方便的地方就是无法抓取到更细节的数据,只有平均值,所以要不需要定制脚本,要不就需要更多的测试场景和时间来得到一个报告。
1350 0
MySQL基准测试-sysbench安装,使用
***1.sysbench 下载 https://github.com/akopytov/sysbench 下载sysbench源代码 sysbench futures: 1、CPU运算性能 2、磁盘IO性能 3、调度程序性能 4、内存分配及传输速度 5、POSIX线程性能 6、数据库性能(OLTP基准测试)***2.
1174 0
sysbench测试mysql与postgresql
--安装yum包 yum -y install bzr libtool --对压缩包进行解 --注意不要在mysql的官网下载,因为其只支持mysql不支持pg https://github.
996 0
【工具】基准测试工具之sysbench
sysbench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的数据库负载情况 它主要包括以下几种方式的测试: 1、cpu性能 2、磁盘io性能 3、调度程序性能 4、内存分配及传输速度 5、POSIX线程性能 6、数据库性能(OLTP基准测试) 目前sysbench主要支持 mysql,pgsql,oracle 这3种数据库。
919 0
+关注
白海生
擅长Oracle,Mysql,DataGuard,GoldenGate,目前在国内某金融支付公司负责Oracle,Mysql,DataGuard,GoldenGate管理运维。
16
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载