关于mysql:技术分享sysbench-压测工具用法浅析

38次阅读

共计 6480 个字符，预计需要花费 17 分钟才能阅读完成。

  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。

介绍

新业务上线的时候通常须要对数据库性能进行压力测试，以确认是否满足需要，明天简略介绍下 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 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-name> [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 benchmark

128 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 benchmark

Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored


Extra 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.75Mb  Written 62.5Mb  Total transferred 156.25Mb  (6.3811Mb/sec)
  408.39 Requests/sec executed

General 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.01ms

Threads 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 benchmark

Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored

Doing CPU performance benchmark

Primer numbers limit: 10000

Threads 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.30ms

Threads 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 benchmark

Running the test with following options:
Number of threads: 32
Random number generator seed is 0 and will be ignored

Doing 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.01ms

Threads 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 benchmark

No 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 benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 1
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 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.45ms

Threads 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 benchmark

No DB drivers specified, using mysql
Dropping table 'sbtest'...
Done.

Enjoy GreatSQL :)

本文由博客一文多发平台 OpenWrite 公布！

正文完
mysql
发表至： mysql
2022-01-16
 0
BTree-BTree-的-区别
关于mysql:炸了使用-MyBatis-查询千万数据量
关于mysql:2-组复制技术架构-深入浅出MGR
关于mysql:故障分析-MySQL-Router服务器后端那么闲为什么不让访问