技术分享|sysbench 压测工具用法浅析 mysql数据库sql

GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

介绍 【技术分享|sysbench 压测工具用法浅析】新业务上线的时候通常需要对数据库性能进行压力测试，以确认是否满足需要，今天简单介绍下sysbench的用法：

1.sysbench 是一个开源跨平台的多线程性能测试工具。
2.可以用来进行 CPU、内存、磁盘IO、线程、数据库的性能测试。
3.目前支持的数据库是 MySQL、Oracle 和 PostgreSQL。

安装 1.YUM 安装

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

2.安装完成后会有一些 lua 脚本

[root@mgr1 src]# ll /usr/share/sysbench/ total 132 -rwxr-xr-x. 1 root root1452 May82020 bulk_insert.lua -rw-r--r--. 1 root root 14369 May82020 oltp_common.lua -rwxr-xr-x. 1 root root1290 May82020 oltp_delete.lua -rwxr-xr-x. 1 root root2415 May82020 oltp_insert.lua ......

3.部分lua脚本说明

insert.lua 单值插入数据
bulk_inert.lua 批量插入数据
delete.lua 删除数据
select.lua 简单主键查询
oltp.lua 混合读写测试

用法 1.sysbench 常用参数

sysbench [general-options]... --test= [test-options]... command --num-threads=N# 创建测试线程的数，默认为1. --max-requests=N# 请求的最大数目，默认为10000，0 代表不限制。 --max-time=N# 最大执行时间，单位是s。默认是0,不限制。 --forced-shutdown=STRING# 超过 max-time 的时候会强制中断。默认是off。 --thread-stack-size=SIZE# 每个线程的堆栈大小。默认是32K。 --init-rng=[on|off]# 在测试开始时是否初始化随机数发生器。默认是off。 --test=STRING# 指定测试项目名称。 --debug=[on|off]# 是否显示更多的调试信息。默认是off。 --validate=[on|off]# 在可能情况下执行验证检查。默认是off。 --version=[on|off]# 版本信息。 --help=[on|off]# 帮助信息。

2.可以测试的项目

fileio # IO cpu -# CPU memory # 内存 threads # 线程 mutex # 互斥性能 oltp # 数据库，事务处理

3.各个测试项目查看帮助

# IO sysbench--test=fileio help # CPU sysbench--test=cpu help # 内存 sysbench--test=memory help # 线程 sysbench--test=threads help # 互斥性能 sysbench--test=mutex help # 事务处理 sysbench --test=oltp help

4.测试阶段

prepare：测试前准备工作，例如为fileis测试在磁盘上创建必要的文件，为OLTP测试准备测试数据 run：执行完整的测试，必须指定–-test选项 cleanup：测试结束后删除数据

三、测试 3.1 文件IO测试
磁盘IO性能测试，主要查看请求数(request)和总体的吞吐量(total)。

[root@mgr1 src]# sysbench --test=fileio --num-threads=4 --file-total-size=1G --file-test-mode=rndrw prepare sysbench 0.4.12.10:multi-threaded system evaluation benchmark128 files, 8192Kb each, 1024Mb total Creating files for the test... Extra file open flags: 0 Reusing existing file test_file.0 Reusing existing file test_file.1 Reusing existing file test_file.2 Reusing existing file test_file.127 No bytes written.[root@mgr1 src]# sysbench --test=fileio --num-threads=4 --file-total-size=1G --file-test-mode=rndrw run sysbench 0.4.12.10:multi-threaded system evaluation benchmarkRunning the test with following options: Number of threads: 4 Random number generator seed is 0 and will be ignoredExtra file open flags: 0 128 files, 8Mb each 1Gb 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:6000 reads, 4000 writes, 12800 Other = 22800 Total Read 93.75MbWritten 62.5MbTotal transferred 156.25Mb(6.3811Mb/sec) 408.39 Requests/sec executedGeneral statistics: total time:24.4863s total number of events:10000 total time taken by event execution: 0.1070 response time: min:0.00ms avg:0.01ms max:13.36ms approx.95 percentile:0.01msThreads fairness: events (avg/stddev):2500.0000/526.25 execution time (avg/stddev):0.0267/0.01

3.2、CPU测试 CPU测试计算质数直到某个最大值所需要的时间，主要方法是进行素数的加法运算，在下面的例子中，指定了最大的素数为 10000

[root@mgr1 src]# sysbench --test=cpu --cpu-max-prime=10000 run sysbench 0.4.12.10:multi-threaded system evaluation benchmarkRunning the test with following options: Number of threads: 1 Random number generator seed is 0 and will be ignoredDoing CPU performance benchmarkPrimer numbers limit: 10000Threads started! Done.General statistics: total time:10.7192s total number of events:10000 total time taken by event execution: 10.7031 response time: min:0.90ms avg:1.07ms max:10.32ms approx.95 percentile:1.30msThreads fairness: events (avg/stddev):10000.0000/0.00 execution time (avg/stddev):10.7031/0.00

3.3 线程测试

--thread-yields=N 每个请求产生多少个线程。默认是1000
--thread-locks=N 每个线程的锁的数量。默认是8

[root@mgr1 src]# sysbench --test=threads --num-threads=32 --thread-yields=48 --thread-locks=2 run sysbench 0.4.12.10:multi-threaded system evaluation benchmarkRunning the test with following options: Number of threads: 32 Random number generator seed is 0 and will be ignoredDoing thread subsystem performance test Thread yields per test: 48 Locks used: 2 Threads started! Done.General statistics: total time:2.0646s total number of events:10000 total time taken by event execution: 66.0149 response time: min:0.03ms avg:6.60ms max:69.03ms approx.95 percentile:24.01msThreads fairness: events (avg/stddev):312.5000/22.72 execution time (avg/stddev):2.0630/0.00

3.4 内存测试

测试内存连续读写性能

[root@mgr1 src]# sysbench --test=memory --memory-block-size=8k --memory-total-size=1G run sysbench 0.4.12.10:multi-threaded system evaluation benchmark Operations performed: 1310720 (216525.32 ops/sec) 10140.00 MB transferred (2097.85 MB/sec) Test execution summary: total time:3.6055s total number of events:1410720 total time taken by event execution: 201.0560 per-request statistics: min:0.00ms avg:0.16ms max:1071.04ms approx.95 percentile:0.02ms Threads fairness: events (avg/stddev):12107.2000/3870.38 execution time (avg/stddev):2.0506/0.28

3.5 OLTP 测试

测试事务处理数，和读写数

# prepare准备数据 [root@mgr1 src]# sysbench --test=oltp --mysql-table-engine=InnoDB --oltp-table-size=1000000 --mysql-socket=/data/GreatSQL/mysql.sock --mysql-user=root --mysql-password=GreatSQL --mysql-db=test prepare sysbench 0.4.12.10:multi-threaded system evaluation benchmarkNo DB drivers specified, using mysql Creating table 'sbtest'... Creating 1000000 records in table 'sbtest'...# 正式run测试 [root@mgr1 src]# sysbench --test=oltp --mysql-table-engine=InnoDB --oltp-table-size=1000000 --mysql-socket=/data/GreatSQL/mysql.sock --mysql-user=root --mysql-password=GreatSQL --mysql-db=test run sysbench 0.4.12.10:multi-threaded system evaluation benchmarkNo DB drivers specified, using mysql Running the test with following options: Number of threads: 1 Random number generator seed is 0 and will be ignoredDoing 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 Using 1 test tables Threads started! Done.OLTP test statistics: queries performed: read:140000 write:40000 other:20000 total:200000 transactions:10000(27.65 per sec.) deadlocks:0(0.00 per sec.) read/write requests:180000 (497.67 per sec.) other operations:20000(55.30 per sec.)General statistics: total time:361.6868s total number of events:10000 total time taken by event execution: 361.5698 response time: min:18.73ms avg:36.16ms max:283.84ms approx.95 percentile:63.45msThreads fairness: events (avg/stddev):10000.0000/0.00 execution time (avg/stddev):361.5698/0.00# 删除 sbtest 测试表 [root@mgr1 src]# sysbench --test=oltp --mysql-table-engine=InnoDB --oltp-table-size=1000000 --mysql-socket=/data/GreatSQL/mysql.sock --mysql-user=root --mysql-password=GreatSQL --mysql-db=test cleanup sysbench 0.4.12.10:multi-threaded system evaluation benchmarkNo DB drivers specified, using mysql Dropping table 'sbtest'... Done.

Enjoy GreatSQL :)