MySQL基准测试、sysbench使用

最新推荐文章于 2024-09-27 01:38:42 发布

椰子啊

最新推荐文章于 2024-09-27 01:38:42 发布

阅读量240

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/yyyxxxs/article/details/98482078

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1、什么是基准测试
基准测试，简单来说，就是针对系统设计的一种压力测试，通常目标是为了掌握系统的行为。基准测试可以观察系统在不同压力下的行为，评估系统的容量，掌握哪些是重要的变化，或者观察系统如何处理不同的数据。基准测试可以在系统实际负载之外创造一些虚构场景进行测试。

2、基准测试策略
(1)集成式基准测试：针对整个系统
在这里插入图片描述
(2)单组件式基准测试：针对数据库

3、测试指标
(1)吞吐量：单位时间内的事务处理数
(2)响应时间或者延迟：用于测试任务所需的整体时间
如，要测量特定MySQL表达式或函数的速度，可以调用BENCHMARK()函数

mysql> select benchmark(10000000,2*5);
+-------------------------+
| benchmark(10000000,2*5) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.26 sec)

它表明MySQL可以在0.26秒内在该系统上执行10000000个简单的乘法表达式。
(3)并发性：并发性关注的是正在工作中的并发操作，或者是同时工作中的线程数或连接数。
要注意的是，数据库连接和并发性是不一样的。一个良好的应用系统，可以同时打开成百上千个数据库连接，但可能只有少数连接在执行查询。
(4)可扩展性：简单来说，可扩展性指的是，给系统增加一倍的工作，理想情况下，就可以获得两倍的结果(即吞吐量增加一倍)。

4、基准测试工具
(1)sysbench安装
sysbench可以执行多种类型的基准测试，它不仅可以用来测试数据库性能,也可以测试运行数据库的服务器性能。
下载：https://packagecloud.io/akopytov/sysbench
在这里插入图片描述
(2)sysbench使用及帮助

[root@relay3.mobvista.com:101.251.254.6 ~]#curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
[root@relay3.mobvista.com:101.251.254.6 ~]#yum install sysbench -y

[root@relay3.mobvista.com:101.251.254.6 ~]#sysbench --help		//查看参数
Usage:
  sysbench [options]... [testname] [command]			//语法格式
  ... ...
  
Compiled-in tests:		//内建的测试项
  fileio - File I/O test
  cpu - CPU performance test
  memory - Memory functions speed test
  threads - Threads subsystem performance test
  mutex - Mutex performance test

See 'sysbench <testname> help' for a list of options for each test.

通过sysbench < testname > help可以查看具体测试的选项，如查看到文件I/O测试（fileio）的测试选项：

[root@relay3.mobvista.com:101.251.254.6 ~]#sysbench --test=fileio help	

fileio options:
  --file-num=N                  number of files to create [128]
  --file-block-size=N           block size to use in all IO operations [16384]
  --file-total-size=SIZE        total size of files to create [2G]
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync]
  --file-async-backlog=N        number of asynchronous operatons to queue per thread [128]
  --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]
  --file-fsync-all[=on|off]     do fsync() after each write operation [off]
  --file-fsync-end[=on|off]     do fsync() at the end of test [on]
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]

(3)使用示例
示例1：文件i/o基准测试

//测试准备阶段，使用16个线程，创建测试文件总大小为1GB，测试模式为随机读写。

[root@relay3.mobvista.com:101.251.254.6 ~]#sysbench --test=fileio --num-threads=16 --file-total-size=1G --file-test-mode=rndrw prepare

sysbench 1.0.17 (using system LuaJIT 2.0.4)
128 files, 8192Kb each, 1024Mb total
Creating files for the test...
Extra file open flags: (none)
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
...	...

//测试运行阶段

[root@relay3.mobvista.com:101.251.254.6 ~]#sysbench --test=fileio --num-threads=16 --file-total-size=1G --file-test-m                                 ode=rndrw run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 16		//线程数
Initializing random number generator from current time

Extra file open flags: (none)
128 files, 8MiB each
1GiB total file size		//文件总大小
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50			//读写比例
Periodic FSYNC enabled, calling fsync() each 100 requests.		//每隔100个请求使用fsync()同步内存和硬盘的数据
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...

Threads started!

File operations:
    reads/s:                      241.51
    writes/s:                     160.12
    fsyncs/s:                     705.43

Throughput:			//吞吐量
    read, MiB/s:                  3.77
    written, MiB/s:               2.50

General statistics:
    total time:                          10.1590s
    total number of events:              9201

Latency (ms):
         min:                                    0.00
         avg:                                   17.64
         max:                                  317.89
         95th percentile:                      227.40			//95%以上的响应时间
         sum:                               162323.47

Threads fairness:
    events (avg/stddev):           575.0625/35.67
    execution time (avg/stddev):   10.1452/0.00

//测试清除阶段，删除所有测试文件：

[root@relay3.mobvista.com:101.251.254.6 ~]#sysbench --test=fileio --num-threads=16 --file-total-size=1G --file-test-mode=rndrw cleanup
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Removing test files...

示例2：CPU基准测试
测试计算素数直到某个最大值所需要的时间

[root@relay3.mobvista.com:101.251.254.6 ~]#sysbench --test=cpu --cpu-max-prime=2000 run		//素数生成器的上限值（默认值为10000）
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Prime numbers limit: 2000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:  8164.94

General statistics:
    total time:                          10.0002s
    total number of events:              81685

Latency (ms):
         min:                                    0.12
         avg:                                    0.12
         max:                                    1.17
         95th percentile:                        0.13
         sum:                                 9965.18

Threads fairness:
    events (avg/stddev):           81685.0000/0.00
    execution time (avg/stddev):   9.9652/0.00