第 D 章 MySQL Benchmark
D1、Sysbench
参考 sysbench 项目：https://github.com/akopytov/sysbench
测试环境：
CPU：ARM64 64core
MEM：256G
RAID CARD：AVAGO MegaRAID SAS 9361-8i
DISK：INTEL SSDSC2KB480G8
RAID MODE：Raid0
MySQL VERSION：Mariadb-server v10.3.9
SYSBENCH：v1.0.20
OS：Kylin Linux Advanced Server release V10 (Tercel)
D1.1、安装
安装编译依赖：
yum -y install make automake libtool pkgconfig libaio-devel
# For MySQL support, replace with mysql-devel on RHEL/CentOS 5
yum -y install mariadb-devel openssl-devel
# For PostgreSQL support
yum -y install postgresql-devel源码安装：
cd /tmp && git clone https://github.com/akopytov/sysbench.git
cd /tmp/sysbench/ && ./autogen.sh
./configure --prefix=/usr/local/sysbench
make && make install报错：
drv_mysql.c:420:24: 错误：‘MYSQL_OPT_COMPRESSION_ALGORITHMS’ undeclared (first use in this function); did you mean ‘MYSQL_OPT_COMPRESS’?
mysql_options(con, MYSQL_OPT_COMPRESSION_ALGORITHMS, args.compression_alg);
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MYSQL_OPT_COMPRESS
解决：
将drv_mysql.c中的MYSQL_OPT_COMPRESSION_ALGORITHMS改成MYSQL_OPT_COMPRESS即可。D1.2、测试
准备数据：
./bin/sysbench --threads=64 --mysql-host=10.47.76.23 --mysql-port=3306 --mysql-user=brinnatt --mysql-password='WelC0me168!' --mysql-db=highdb ./share/sysbench/oltp_insert.lua --time=120 prepare进行测试：
./bin/sysbench --threads=64 --mysql-host=10.47.76.23 --mysql-port=3306 --mysql-user=brinnatt --mysql-password='WelC0me168!' --mysql-db=highdb ./share/sysbench/oltp_insert.lua --time=120 run
sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 64
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 0
write: 2899862
other: 0
total: 2899862
transactions: 2899862 (24162.08 per sec.)
queries: 2899862 (24162.08 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 24162.0820
time elapsed: 120.0171s
total number of events: 2899862
Latency (ms):
min: 0.31
avg: 2.64
max: 204.67
95th percentile: 10.84
sum: 7669225.73
Threads fairness:
events (avg/stddev): 45310.3438/411.06
execution time (avg/stddev): 119.8317/0.00D1.3、组合测试
生产环境下，一台服务器会同时运行多个服务，CPU 和内存资源会在争用的状态同时支持这些服务，所以我们多个测试同时进行。
sysbench 官方没有专门的使用手册，可以通过命令帮助摸索测试：
[root@localhost sysbench]# ./bin/sysbench --help
Usage:
sysbench [options]... [testname] [command]
Commands implemented by most tests: prepare run cleanup help
General options:
--threads=N number of threads to use [1]
--events=N limit for total number of events [0]
--time=N limit for total execution time in seconds [10]
......
Compiled-in database drivers:
mysql - MySQL driver
mysql options:
--mysql-host=[LIST,...] MySQL server host [localhost]
--mysql-port=[LIST,...] MySQL server port [3306]
......
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 可以进行 fileio、cpu、memory、threads、mutex 以及 msyql 相关的测试。
查看单项测试帮助：
[root@localhost sysbench]# ./bin/sysbench cpu help
sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)
cpu options:
--cpu-max-prime=N upper limit for primes generator [10000]
[root@localhost sysbench]#
[root@localhost sysbench]# ./bin/sysbench memory help
sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)
memory options:
--memory-block-size=SIZE size of memory block for test [1K]
--memory-total-size=SIZE total size of data to transfer [100G]
--memory-scope=STRING memory access scope {global,local} [global]
--memory-hugetlb[=on|off] allocate memory from HugeTLB pool [off]
--memory-oper=STRING type of memory operations {read, write, none} [write]
--memory-access-mode=STRING memory access mode {seq,rnd} [seq]
[root@localhost sysbench]# ./bin/sysbench fileio help
sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)
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]开启四个终端组合测试：
# mysql测试
./bin/sysbench --threads=22 --mysql-host=10.47.76.23 --mysql-port=3306 --mysql-user=brinnatt --mysql-password='WelC0me168!' --mysql-db=highdb ./share/sysbench/oltp_insert.lua --time=120 run
# 内存测试，注意，该测试分配的内存块是128G，在该内存块上循环写1T，所以内存实际被占用128G而不是1T
./bin/sysbench memory --memory-block-size=128G --memory-total-size=1T --time=120 run
# 文件测试
./bin/sysbench fileio --file-test-mode=rndrw --time=120 --threads=22 run
# cpu测试
./bin/sysbench cpu --threads=22 --cpu-max-prime=1000000 --time=120 runD2、Mysqlslap
官方手册：https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html
mysqlslap 模拟客户端多并发请求 mysql 服务器，并报告各阶段消耗时长。
用法：
mysqlslap [options]你可以通过 --create 或 --query 选项指定 SQL 语句字符串或者指定一个包含 SQL 语句的文件。
如果是用文件，要求每行只有一个 SQL 语句，也就是说，隐式语句分隔符是换行符。当然，可以手动指定 --delimiter 分隔符，就可以一行写多个语句，或者一个语句写多行。
另外，文件里面不要写注释，mysqlslap 不理解注释。
mysqlslap 分三个阶段运行：
- 创建 schema、table，以及用于测试而存储的程序或数据。此阶段使用单个客户端连接。
- 运行负载测试。此阶段可使用多个客户端连接。
- 清理（断开连接、删除表）。此阶段使用单个客户端连接。
举例：
50 个客户 create 和 query 200 次
mysqlslap --delimiter=";"
--create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)"
--query="SELECT * FROM a" --concurrency=50 --iterations=200自动生成表，有 2 个 int 字段，3 个 varchar 字段，5 个客户 query 20 次
mysqlslap --concurrency=5 --iterations=20
--number-int-cols=2 --number-char-cols=3
--auto-generate-sql告诉程序从指定文件中读取 create、insert 和 query SQL 语句，5 个客户 query 5 次
mysqlslap --concurrency=5
--iterations=5 --query=query.sql --create=create.sql
--delimiter=";"