MySQL性能压测工具SysBench详解（非常具体）

概述

背景

近来必要给不同的客户摆设数据库，各自的预算不一样，购买的服务器设置也不一样。那么我们就必要对其数据库的支撑本领进行肯定测试，以保证满意业务真是的必要
数据库性能指标

指标英文寄义说明QPSQuery Per Second数据库每秒实行的SQL数，包罗insert、select、update、delete等。TPSTransaction Per Second数据库每秒实行的事务数，每个事务中包罗18条SQL语句。 sysbench简介

sysbench 支持以下几种测试模式：
1、CPU 运算性能
2、磁盘 IO 性能
3、调理程序性能
4、内存分配及传输速度
5、POSIX 线程性能–互斥基准测试
6、数据库性能(OLTP 基准测试)
安装

二进制方式

RHEL/CentOS:

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

复制代码

Debian/Ubuntu

2. curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
3. sudo apt -y install sysbench

复制代码

优化内核，提升SysBench Client

实行如下命令设置SysBench Client，使内核可以利用全部的CPU处理数据包（默认设置为利用2个CPU），同时减少CPU之间的上下文切换。

2. sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ffffffff>$x/rps_cpus; done'
3. sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
4. sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"
5. sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-1/rps_flow_cnt"

复制代码

此中：ffffffff表现利用32个CPU（1个f表现4个CPU）。请根据现实设置修改，例如ECS为8核CPU，则输入ff。
快速体验

以下命令可以输出各个检测项的帮助文档

2. # IO
3. sysbench  --test=fileio help
5. # CPU
6. sysbench  --test=cpu help
8. # 内存
9. sysbench  --test=memory help
11. # 线程
12. sysbench  --test=threads help
14. # 互斥性能
15. sysbench  --test=mutex help
17. # 事务处理
18. sysbench --test=oltp help

复制代码

格式

通用格式：
sysbench [options]... [testname] [command]
参数说明可选值testname测试项可以是 fileio, memory, cpu, threads, mutex 等测试项，也可以是工具或者自界说的lua脚本。PS：没有这一项，直接实行sysbench，cli会等待输入。command命令prepare：实行测试之前的准备操作，如 创建文件，填充数据等；run：运行测试；cleanup：测试结束后清空数据；help帮助展示测试的利用方式信息 也可以利用sysbench --help 展示全部利用手册。
内置lua脚本

在/usr/share/sysbench目次下有常用操作的lua脚本，要自界说脚本实现特定功能时，可参考这些脚本。

2. bulk_insert.lua
3. oltp_common.lua
4. oltp_delete.lua
5. oltp_insert.lua
6. oltp_point_select.lua
7. oltp_read_only.lua
8. oltp_read_write.lua
9. oltp_update_index.lua
10. oltp_update_non_index.lua
11. oltp_write_only.lua
12. select_random_points.lua
13. select_random_ranges.lua

复制代码

利用

文件IO

磁盘IO性能测试，重要查看请求数(request)和总体的吞吐量(total)。
参数说明默认–file-num=N创建文件的数量默认值：128。–file-block-size=N每次IO操作的block巨细默认值：16K。–file-total-size=SIZE全部文件巨细总和2G–file-test-mode=STRING测试模式：seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)。–file-io-mode=STRING文件操作模式：sync(同步),async(异步),mmap(快速map映射)sync–file-async-backlog=N每个线程列队的异步操作数128–file-extra-flags=[LIST,…]利用额外的标志符来打开文件{sync,dsync,direct}默认值空–file-fsync-freq=N在完成N次请求之后，实行fsync()，0表现倒霉用fsync。fsync重要是同步磁盘文件，由于大概有体系和磁盘缓冲的关系100–file-fsync-all[=on、off]每次写操作后实行fsync()每次实行写操作后就实行一次fsyncoff–file-fsync-end[=on、off]测试结束后实行fsync()on–file-fsync-mode=STRING利用fsync或fdatasync方法进行同步文件同步函数的选择，同样是和API相关的参数，由于多个操作体系对于fdatasync支持不同，因此不建议利用fdatasync。fsync–file-merged-requests=N尽大概的合并N个IO请求数，0表现不合并0–file-rw-ratio=N测试时候的读写比例1.5(即3:2) 准备测试

2. sysbench --test=fileio --file-num=2 --file-block-size=1024 --file-total-size=1G prepare

复制代码

实行测试
1、测试顺序读写，请求10000次，假如不能在30秒内完成，测试结束

2. sysbench fileio \
3. --file-num=2 \
4. --file-block-size=1024 \
5. --file-total-size=1G \
6. --file-test-mode=seqrewr \
7. --time=30 \
8. --events=10000 \
9. --threads=4 \
10. --file-fsync-freq=200 \
11. --file-extra-flags=direct run

复制代码

2、测试随机读写，请求10000次，假如不能在30秒内完成，测试结束

2. sysbench --test=fileio \
3. --file-num=2 \
4. --file-block-size=1024 \
5. --file-total-size=1G \
6. --file-test-mode=rndrw \
7. --time=30 \
8. --events=10000 \
9. --threads=4 \
10. --file-fsync-freq=200 \
11. --file-extra-flags=direct run

复制代码

3、测试随机读，请求10000次，假如不能在30秒内完成，测试结束

2. sysbench --test=fileio \
3. --file-num=2 \
4. --file-block-size=1024 \
5. --file-total-size=1G \
6. --file-test-mode=rndrd \
7. --time=30 \
8. --events=10000 \
9. --threads=4 \
10. --file-fsync-freq=200 \
11. --file-extra-flags=direct run

复制代码

清理文件

2. sysbench --test=fileio --num-threads=4  --file-total-size=1G --file-test-mode=rndrw cleanup

复制代码

结果说明

2. ### 版本说明
3. sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
5. Running the test with following options:
6. ### 线程数
7. Number of threads: 4
8. Initializing random number generator from current time
11. Extra file open flags: directio
12. ### 文件数、单个文件大小
13. 2 files, 5GiB each
14. ### 文件总大小
15. 10GiB total file size
16. ### 单次最大I/O操作block 10k
17. Block size 10KiB
18. ### I/O读取请求次数
19. Number of IO requests: 10000
20. ### 读写测试比例 3/2：1.5
21. Read/Write ratio for combined random IO test: 1.50
22. ### 200次请求会执行记录数据
23. Periodic FSYNC enabled, calling fsync() each 200 requests.
24. Calling fsync() at the end of test, Enabled.
25. Using synchronous I/O mode
26. Doing random r/w test
27. Initializing worker threads...
29. Threads started!
31. ### 文件操作
32. File operations:
33.     reads/s:                      1411.72
34.     writes/s:                     941.22
35.     fsyncs/s:                     25.19
36. ### 吞吐量
37. Throughput:
38.     read, MiB/s:                  13.79
39.     written, MiB/s:               9.19
40. ### 总统计
41. General statistics:
42.     total time:                          4.2069s
43.     total number of events:              10000  
44. ### fileio 磁盘测试，在4秒范围内进行随机读写这个行为一共进行了10000次，实际执行时间为4.2069秒
46. ### 潜在数据
47. Latency (ms):
48.          min:                                    0.04
49.          avg:                                    1.68
50.          max:                                   45.76
51.          95th percentile:                        3.96
52.          sum:                                16810.76
53. ### 这部分数据应该统计的是线程真正执行的时间，总共16810.76ms, 单次执行最少时间为0.04ms,
54. ### 最多时间为45.76ms, 平均时间为1.68ms, 95%次的执行时间在3.96ms左右;
56. Threads fairness:
57.     events (avg/stddev):           2500.0000/26.24
58.     execution time (avg/stddev):   4.2027/0.00
59. ### 归纳总结，线程执行时间为4.2027s, 执行平均次数为2500次,上下差为：26.24

复制代码

CPU测试

选项

sysbench的cpu测试是在指定时间内，循环进行素数盘算。
素数（也叫质数）就是从1开始的自然数中，无法被整除的数，好比2、3、5、7、11、13、17等。编程公式：对正整数n，假如用2到根号n之间的全部整数去除，均无法整除，则n为素数。
选项说明默认值–cpu-max-prime素数生成数量的上限默认值为 10000–threads线程数默认值为 1–time运行时长，单位秒默认值为10 ，假如时间另有剩就再进行一轮素数盘算，直到时间耗尽。每完成一轮就叫一个 event 。雷同时间，比力的是谁完成的event多–eventsevent上限次数默认值为0，则表现不限event次数。雷同event次数，比力的是谁用时更少 测试

2. sysbench cpu --cpu-max-prime=20000 --threads=2 run

复制代码

结果

1. [root@Reseach sysbench-test]#
2. sysbench cpu --cpu-max-prime=20000 --threads=2 run
4. sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)Running the test with following options:Number of threads: 2 // 指定线程数为2Initializing random number generator from current timePrime numbers limit: 20000 // 每个线程产生的素数上限均为2万个Initializing worker threads...Threads started!CPU speed:    events per second:  1955.47 // 全部线程每秒完成了650.74次eventGeneral statistics:    total time:                          10.0006s  // 共耗时10秒    total number of events:              19559 // 10秒内全部线程一共完成了6510次eventLatency (ms):         min:                                    0.87 // 完成1次event的最少耗时3.03秒         avg:                                    1.02 // 全部event的平均耗时3.07毫秒         max:                                    1.71 // 完成1次event的最多耗时3.27毫秒         95th percentile:                        1.67 // 95%次event在3.13秒毫秒内完成         sum:                                19995.36 // 每个线程耗时10秒，2个线程叠加耗时就是20秒Threads fairness:    events (avg/stddev):           9779.5000/6.50 // 平均每个线程完成3255次event，标准差为44    execution time (avg/stddev):   9.9977/0.00 // 每个线程平均耗时10秒，标准差为0

复制代码

假如有2台服务器进行CPU性能对比，当素数上限和线程数一致时：

• 雷同时间，比力event
  
• 雷同event，比力时间
  
• 时间和event都雷同，比力stddev(标准差)
  

线程性能测试

选项

选项说明默认值–thread-yields=N每个请求产生多少个线程1000–thread-locks=N每个线程的锁的数量8 测试

3. sysbench --test=threads --num-threads=4 --thread-yields=12 --thread-locks=2 run

复制代码

结果

2. General statistics:
3.     total time:                          10.0001s
4.     total number of events:              2357161
6. Latency (ms):
7.          min:                                    0.00
8.          avg:                                    0.02
9.          max:                                   18.44
10.          95th percentile:                        0.05
11.          sum:                                39356.63
13. Threads fairness:
14.     events (avg/stddev):           589290.2500/577.33
15.     execution time (avg/stddev):   9.8392/0.00

复制代码

互斥性能测试

Mutex请求的性能与CPU主频及物理CPU个数有关
选项

选项说明默认值–mutex-num=N互斥数组的总巨细4096–mutex-locks=N每个线程要实行的互斥锁数50000–mutex-loops=N在互斥锁之外实行的空循环数10000 测试

2. sysbench mutex --mutex-num=2048  --mutex-locks=5000 --mutex-loops=5000 run

复制代码

结果

2. General statistics:
3.     total time:                          0.0075s
4.     total number of events:              1
6. Latency (ms):
7.          min:                                    7.48
8.          avg:                                    7.48
9.          max:                                    7.48
10.          95th percentile:                        7.43
11.          sum:                                    7.48
13. Threads fairness:
14.     events (avg/stddev):           1.0000/0.00
15.     execution time (avg/stddev):   0.0075/0.00

复制代码

内存性能测试

选项

选项说明默认值–memory-block-size=SIZE测试时内存块巨细。1K–memory-total-size=SIZE传输数据的总巨细。100G–memory-scope=STRING内存访问范围{global,local}global–memory-hugetlb=[on、off]从HugeTLB池内存分配off–memory-oper=STRING内存操作类型。{read, write, none}write–memory-access-mode=STRING存储器存取方式{seq,rnd}seq 测试

2. sysbench --test=memory \
3. --threads=2 \
4. --events=10000 \
5. --memory-total-size=1G \
6. --memory-block-size=8K \
7. --memory-oper=read run

复制代码

2. sysbench --test=memory \
3. --threads=2 \
4. --events=100000 \
5. --memory-total-size=50G \
6. --memory-block-size=8K \
7. --memory-oper=write run

复制代码

数据库OLTP

选项说明

选项形貌默认值–threads工作线程数1–events总的请求数，默认为0 表现无限制0–time实行时间（秒），0表现无限制0–warmup-time预热时间，可以实现在 CPU/database/page/caches 预热之后再进行统计，如许得到的数据指标更准确0–rateTPS，事务数。0表现无限制0–thread-init-timeout工作线程初始化的等待时间（秒）30–thread-stack-size每一线程栈巨细32k–report-interval多少秒输出一次陈诉，0表现克制立刻陈诉0–debug调试模式off–validate在大概的情况下对测试结果进行验证off–help打印有关常规语法或指定测试的帮助，然后退出off–verbosity日志级别，0-致命错误，5-调试信息4–percentilesysbench衡量全部已处理请求的实行时间，以表现统计信息，如最小、平均和最大实行时间。95–luajit-cmd实行LuaJIT控制命令 随机数生成算法
选项形貌默认值–rand-type默认情况下利用随机数分布{uniform，gaussian，special，pareto，zipfian}。基准脚本可以选择利用默认分布，也可以明白指定，即覆盖默认分布。special–rand-seed随机数生成器的种子。当为0时，当前时间用作RNG种子。0–rand-spec-iter特别分布的迭代次数12–rand-spec-pct“特别”值将属于特别分布的整个范围的百分比1–rand-spec-res用于特别分布的“特别”值百分比75–rand-pareto-h帕累托分布的外形参数0.2–rand-zipfian-expzipfian分布的外形参数0.8 支持的lua脚本

选项说明oltp_read_only只读测试oltp_read_write读写测试oltp_insert简单插入测试bulk_insert批量插入测试oltp_deletedelete删除测试oltp_update_index带索引的更新测试oltp_update_non_index不带索引的更新测试oltp_point_select等值查询测试select_random_points随机等值查询测试select_random_ranges随机范围查询测试 脚本参数

选项说明默认值–auto_inc[=on/off]利用 AUTO_INCREMENT 列作为主键（对于 MySQL），或者它在其他 DBMS 中的替换项。禁用时，利用客户端生成的 ID[on]–create_secondary[=on/off]除了 PRIMARY KEY 创建二级索引[on]–delete_inserts=N每个事务的 DELETE/INSERT 组合数[1]–distinct_ranges=N每个事务的 SELECT DISTINCT 查询数[1]–index_updates=N每个事务的 UPDATE 索引查询数[1]–mysql_storage_engine=STRING存储引擎，假如利用 MySQL[innodb]–non_index_updates=N每个事务的 UPDATE 非索引查询数[1]–order_ranges=N每个事务的 SELECT ORDER BY 查询数[1]–pgsql_variant=STRING利用 PostgreSQL 驱动程序运行时利用此 PostgreSQL 变体。当前唯一支持的变体是“redshift”。启用后，create_secondary 主动禁用，delete_inserts 设置为 0–point_selects=N每个事务的点 SELECT 查询数[10]–range_selects[=on/off]启用/禁用全部范围 SELECT 查询[on]–range_size=N范围 SELECT 查询的范围巨细[100]–secondary[=on/off]利用二级索引代替 PRIMARY KEY[off]–simple_ranges=N每个事务的简单范围 SELECT 查询数[1]–skip_trx[=on/off]不要启动显式事务并在 AUTOCOMMIT 模式下实行全部查询[off]–sum_ranges=N每个事务的 SELECT SUM() 查询数[1]–table_size=N每个表的行数[10000]–tables=N表的个数[1] 测试

OLTP读写混淆场景

SQL类型比例SQL语句point_selects10SELECT c FROM sbtest100 WHERE id=?simple_ranges1SELECT c FROM sbtest100 WHERE id BETWEEN ? AND ?sum_ranges1SELECT SUM(k) FROM sbtest100 WHERE id BETWEEN ? AND ?order_ranges1SELECT c FROM sbtest100 WHERE id BETWEEN ? AND ? ORDER BY cdistinct_ranges1SELECT DISTINCT c FROM sbtest100 WHERE id BETWEEN ? AND ? ORDER BY cindex_updates1UPDATE sbtest100 SET k=k+1 WHERE id=?non_index_updates1UPDATE sbtest100 SET c=? WHERE id=?deletesDELETE FROM sbtest100 WHERE id=?inserts_ignore1INSERT IGNORE INTO sbtest100 (id, k, c, pad) VALUES (?, ?, ?, ?) 表结构

实行以下sysbench命令可以创建，sysbench的内置表。

2. sysbench --db-driver=mysql \
3. --mysql-host=127.0.0.1 \
4. --mysql-port=33106 \
5. --mysql-user=root \
6. --mysql-password=Abc123456 \
7. --mysql-db=test_db \
8. --table_size=25000 \
9. --tables=250 \
10. --events=0 \
11. --time=600 \
12. oltp_read_write prepare

复制代码

可以生成如下表结构

2. mysql> show create table sbtest168;
3. +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4. | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                      |
5. +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6. | sbtest168 | CREATE TABLE `sbtest168` (
7.   `id` int NOT NULL AUTO_INCREMENT,
8.   `k` int NOT NULL DEFAULT '0',
9.   `c` char(120) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
10.   `pad` char(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
11.   PRIMARY KEY (`id`),
12.   KEY `k_168` (`k`)
13. ) ENGINE=InnoDB AUTO_INCREMENT=25001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

复制代码

只写

1. ##准备数据
2. sysbench --db-driver=mysql \
3. --mysql-host=XXX \
4. --mysql-port=XXX \
5. --mysql-user=XXX \
6. --mysql-password=XXX \
7. --mysql-db=XXX \
8. --table_size=XXX \
9. --tables=XXX \
10. oltp_write_only run
13. ##运行 workload
14. sysbench --db-driver=mysql \
15. --mysql-host=XXX \
16. --mysql-port=XXX \
17. --mysql-user=XXX \
18. --mysql-password=XXX \
19. --mysql-db=XXX \
20. --table_size=XXX \
21. --tables=XXX \
22. --events=0 \
23. --time=600 \
24. --threads=192 \
25. --percentile=95 \
26. --report-interval=1 \
27. oltp_write_only run
29. ##清理数据
30. sysbench --db-driver=mysql \
31. --mysql-host=XXX \
32. --mysql-port=XXX \
33. --mysql-user=XXX \
34. --mysql-password=XXX \
35. --mysql-db=XXX \
36. --table_size=XXX \
37. --tables=XXX \
38. oltp_write_only cleanup

复制代码

只读（point select）

1. ##准备数据
2. sysbench --db-driver=mysql \
3. --mysql-host=XXX \
4. --mysql-port=XXX \
5. --mysql-user=XXX \
6. --mysql-password=XXX \
7. --mysql-db=XXX \
8. --table_size=XXX \
9. --tables=XXX \
10. oltp_read_only prepare
12. ##运行 workload
13. sysbench --db-driver=mysql \
14. --mysql-host=XXX \
15. --mysql-port=XXX \
16. --mysql-user=XXX \
17. --mysql-password=XXX \
18. --mysql-db=XXX \
19. --table_size=XXX \
20. --tables=XXX \
21. --events=0 \
22. --time=600 \
23. --threads=512 \
24. --percentile=95 \
25. --range_selects=0 \
26. --skip-trx=1 \
27. --report-interval=1 \
28. oltp_read_only run
30. ##清理数据
31. sysbench --db-driver=mysql \
32. --mysql-host=XXX \
33. --mysql-port=XXX \
34. --mysql-user=XXX \
35. --mysql-password=XXX \
36. --mysql-db=XXX \
37. --table_size=XXX \
38. --tables=XXX \
39. oltp_read_only cleanup

复制代码

只读（range select）

1. ##准备数据
2. sysbench --db-driver=mysql \
3. --mysql-host=XXX \
4. --mysql-port=XXX \
5. --mysql-user=XXX \
6. --mysql-password=XXX \
7. --mysql-db=XXX \
8. --table_size=XXX \
9. --tables=XXX \
10. oltp_read_only prepare
12. ##运行 workload
13. sysbench --db-driver=mysql \
14. --mysql-host=XXX \
15. --mysql-port=XXX \
16. --mysql-user=XXX \
17. --mysql-password=XXX \
18. --mysql-db=XXX \
19. --table_size=XXX \
20. --tables=XXX \
21. --events=0 \
22. --time=600 \
23. --threads=512 \
24. --percentile=95 \
25. --skip-trx=1 \
26. --report-interval=1 \
27. oltp_read_only run
29. ##清理数据
30. sysbench --db-driver=mysql \
31. --mysql-host=XXX \
32. --mysql-port=XXX \
33. --mysql-user=XXX \
34. --mysql-password=XXX \
35. --mysql-db=XXX \
36. --table_size=XXX \
37. --tables=XXX \
38. oltp_read_only cleanup

复制代码

混淆读写（point select）

2. ##准备数据
3. sysbench --db-driver=mysql \
4. --mysql-host=XXX \
5. --mysql-port=XXX \
6. --mysql-user=XXX \
7. --mysql-password=XXX \
8. --mysql-db=XXX \
9. --table_size=XXX \
10. --tables=XXX \
11. oltp_read_write run
13. ##运行 workload
14. sysbench --db-driver=mysql \
15. --mysql-host=XXX \
16. --mysql-port=XXX \
17. --mysql-user=XXX \
18. --mysql-password=XXX \
19. --mysql-db=XXX \
20. --table_size=XXX \
21. --tables=XXX \
22. --events=0 \
23. --time=600 \
24. --range_selects=0 \
25. --threads=XXX \
26. --percentile=95 \
27. --report-interval=1 \
28. oltp_read_write run
30. ##清理数据
31. sysbench --db-driver=mysql \
32. --mysql-host=XXX \
33. --mysql-port=XXX \
34. --mysql-user=XXX \
35. --mysql-password=XXX \
36. --mysql-db=XXX \
37. --table_size=XXX \
38. --tables=XXX \
39. oltp_read_write cleanup

复制代码

混淆读写（range select）

2. ##准备数据
3. sysbench --db-driver=mysql \
4. --mysql-host=XXX \
5. --mysql-port=XXX \
6. --mysql-user=XXX \
7. --mysql-password=XXX \
8. --mysql-db=XXX \
9. --table_size=XXX \
10. --tables=XXX \
11. oltp_read_write run
13. ##运行 workload
14. sysbench --db-driver=mysql \
15. --mysql-host=XXX \
16. --mysql-port=XXX \
17. --mysql-user=XXX \
18. --mysql-password=XXX \
19. --mysql-db=XXX \
20. --table_size=XXX \
21. --tables=XXX \
22. --events=0 \
23. --time=600 \
24. --threads=XXX \
25. --percentile=95 \
26. --report-interval=1 \
27. oltp_read_write run
29. ##清理数据
30. sysbench --db-driver=mysql \
31. --mysql-host=XXX \
32. --mysql-port=XXX \
33. --mysql-user=XXX \
34. --mysql-password=XXX \
35. --mysql-db=XXX \
36. --table_size=XXX \
37. --tables=XXX \
38. oltp_read_write cleanup

复制代码

[code]
本文内容到此结束了，
如有收获欢迎点赞