介绍
新业务上线的时候通常需要对数据库性能进行压力测试,以确认是否满足需要,今天简单介绍下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 bashsudo yum -y install sysbench
2.安装完成后会有一些 lua 脚本
[root@mgr1 src]# ll /usr/share/sysbench/total 132-rwxr-xr-x. 1 root root 1452 May 8 2020 bulk_insert.lua-rw-r--r--. 1 root root 14369 May 8 2020 oltp_common.lua-rwxr-xr-x. 1 root root 1290 May 8 2020 oltp_delete.lua-rwxr-xr-x. 1 root root 2415 May 8 2020 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.各个测试项目查看帮助
# IOsysbench --test=fileio help# CPUsysbench --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 preparesysbench 0.4.12.10: multi-threaded system evaluation benchmark128 files, 8192Kb each, 1024Mb totalCreating files for the test...Extra file open flags: 0Reusing existing file test_file.0Reusing existing file test_file.1Reusing existing file test_file.2Reusing existing file test_file.127No bytes written.[root@mgr1 src]# sysbench --test=fileio --num-threads=4 --file-total-size=1G --file-test-mode=rndrw runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 4Random number generator seed is 0 and will be ignoredExtra file open flags: 0128 files, 8Mb each1Gb total file sizeBlock size 16KbNumber of random requests for random IO: 10000Read/Write ratio for combined random IO test: 1.50Periodic FSYNC enabled, calling fsync() each 100 requests.Calling fsync() at the end of test, Enabled.Using synchronous I/O modeDoing random r/w testThreads started!Done.Operations performed: 6000 reads, 4000 writes, 12800 Other = 22800 TotalRead 93.75Mb Written 62.5Mb Total 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 runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Random 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 runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 32Random number generator seed is 0 and will be ignoredDoing thread subsystem performance testThread yields per test: 48 Locks used: 2Threads 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 runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkOperations performed: 1310720 (216525.32 ops/sec)10140.00 MB transferred (2097.85 MB/sec)Test execution summary:total time: 3.6055stotal number of events: 1410720total time taken by event execution: 201.0560per-request statistics:min: 0.00msavg: 0.16msmax: 1071.04msapprox. 95 percentile: 0.02msThreads fairness:events (avg/stddev): 12107.2000/3870.38execution 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 preparesysbench 0.4.12.10: multi-threaded system evaluation benchmarkNo DB drivers specified, using mysqlCreating 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 runsysbench 0.4.12.10: multi-threaded system evaluation benchmarkNo DB drivers specified, using mysqlRunning the test with following options:Number of threads: 1Random 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 10000Using 1 test tablesThreads 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 cleanupsysbench 0.4.12.10: multi-threaded system evaluation benchmarkNo DB drivers specified, using mysqlDropping table 'sbtest'...Done.
Enjoy GreatSQL 🙂