用Sysbench对MySQL进行压力测试，多线程下的速率和延迟对比；结合gnuplot工具出图（非常好用！）

1. 下载sysbench

(1) WSL下的Ubuntu：

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

(2) CentOS:

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

2. sysbench介绍

（1）捆绑的基准测试

• oltp_*.lua:类似oltp的数据库基准的集合
• fileio:文件系统级基准测试
• cpu:简单的cpu基准测试
• memory:内存访问基准
• threads:基于线程的调度器基准
• mutex: POSIX互斥基准

（2）特性

• 关于速率和延迟的大量统计数据是可用的，包括延迟百分比和直方图；
• 即使有数千个并发线程，开销也很低。sysbench能够每秒生成和跟踪数亿个事件；
• 通过在用户提供的Lua脚本中实现预定义的钩子，可以轻松创建新的基准测试；
• 可以用作通用的Lua解释器，只需使用#!/usr/bin/sysbench替换#!/usr/bin/lua。

3. MySQL测试

(1) 数据库准备

 sudo su -
 mysql -p
...

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
##准备test数据库
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
##创建可以远程连接的root用户
mysql> create user 'root'@'%' identified  with mysql_native_password by 'root2023';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

(2) 测试read_write

读写比例大概3.5 ：1

sysbench --db-driver=mysql --mysql-host= 10.23.21.22 \
         --mysql-port=3306 \
         --mysql-user=root \
         --mysql-password=xxx \
         --threads=xxx \
         --time=60 \
         --report-interval=5 \
         /usr/share/sysbench/oltp_read_write.lua \
         --mysql-db=test \
         --tables=10 \
         --table_size=1000000 \
         run

Threads	TPS	QPS	Latency avg. (ms)	Latency p95 (ms)
5	817	16343	6	9
10	951	19033	10	18
50	1250	25017	40	74
100	1230	24578	82	155
200	1149	23159	184	344

一个示例输出

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 5
Report intermediate results every 5 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!
## 此处结果内容主要包含: 时间计数(1s意为1秒)、thds为线程数、tps为每秒事务数、qps为每秒查询数、r/w/o分别代表每秒的读/写/其他操作数
## lat(ms, 95%)时95%操作的耗时-单位为毫秒、err/s是每秒的错误数、reconn/s是每秒的重连数
[ 5s ] thds: 5 tps: 816.69 qps: 16349.17 (r/w/o: 11445.64/3269.15/1634.38) lat (ms,95%): 9.91 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 5 tps: 810.59 qps: 16212.88 (r/w/o: 11349.91/3241.78/1621.19) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 5 tps: 802.61 qps: 16050.19 (r/w/o: 11235.33/3209.64/1605.22) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 5 tps: 824.60 qps: 16490.98 (r/w/o: 11543.19/3298.60/1649.20) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 5 tps: 833.40 qps: 16666.67 (r/w/o: 11666.65/3333.21/1666.81) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 5 tps: 817.00 qps: 16343.88 (r/w/o: 11440.25/3269.62/1634.01) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 5 tps: 817.00 qps: 16337.53 (r/w/o: 11437.15/3266.39/1633.99) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 5 tps: 836.00 qps: 16720.09 (r/w/o: 11703.06/3345.02/1672.01) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 5 tps: 816.20 qps: 16319.17 (r/w/o: 11423.38/3263.79/1632.00) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 5 tps: 830.81 qps: 16623.33 (r/w/o: 11638.09/3323.23/1662.01) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 5 tps: 808.99 qps: 16176.02 (r/w/o: 11322.28/3235.76/1617.98) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 5 tps: 829.00 qps: 16576.67 (r/w/o: 11603.45/3315.41/1657.81) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
## --SQL统计信息，SQL的读/写/其他/总计次数、总事务数及每秒事务数、总查询数及每秒查询数、忽略错误数据、重新数据
SQL statistics:
    queries performed:
        read:                            689066
        write:                           196876
        other:                           98438
        total:                           984380
    transactions:                        49219  (820.20 per sec.)
    queries:                             984380 (16403.98 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
## 通用统计信息：总时间、总操作数
General statistics:
    total time:                          60.0082s
    total number of events:              49219
## 耗时信息(单位毫秒)：最小、平均、最大、95%耗时、总耗时
Latency (ms):
         min:                                    2.88
         avg:                                    6.09
         max:                                   28.48
         95th percentile:                        9.39
         sum:                               299943.69

Threads fairness:
    events (avg/stddev):           9843.8000/50.86
    execution time (avg/stddev):   59.9887/0.00

(3) 测试read_only

sysbench --db-driver=mysql --mysql-host=10.116.85.132 \
		 --mysql-port=3306 \
         --mysql-user=root \
         --mysql-password=xxx \
         --threads=xxx \
         --time=60 \
         --report-interval=5 \
         /usr/share/sysbench/oltp_read_only.lua \
         --mysql-db=test \
         --tables=10 \
         --table_size=1000000 \
         run

Threads	TPS	QPS	Latency avg. (ms)	Latency p95 (ms)
5	1653	26452	3	4
10	1928	30862	5	7
50	1855	29692	26	39
100	1823	29169	54	75
200	1557	24920	129	164

(4) 测试write_only

sysbench --db-driver=mysql --mysql-host=10.116.85.132 \
		 --mysql-port=3306 \
         --mysql-user=root \
         --mysql-password=xxx \
         --threads=xxx \
         --time=60 \
         --report-interval=5 \
         /usr/share/sysbench/oltp_write_only.lua \
         --mysql-db=test \
         --tables=10 \
         --table_size=1000000 \
         run

Threads	TPS	QPS	Latency avg. (ms)	Latency p95 (ms)
5	1753	10519	2	4
10	2361	14168	4	7
50	3982	23899	12	21
100	4251	25513	23	39
200	4175	25033	49	81

(5) 清理

sysbench --db-driver=mysql --mysql-host=10.116.85.132 \
		 --mysql-port=3306 \
         --mysql-user=root \
         --mysql-password=xxx \
         --threads=xxx \
         --time=60 \
         --report-interval=5 \
         /usr/share/sysbench/oltp_read_write.lua \
         --mysql-db=test \
         --tables=10 \
         --table_size=1000000 \
         cleanup

4. gnuplot

（1）下载

apt install gnuplot -y

（2）用awk处理sysbench生成的测试数据

awk '/\[ 5s \]/,/\[ 60s \]/ {print $2, $7,$9,$11,$14}' result.txt >>awk_result.txt

注：随便哪条命令生成的任意一份数据都可以，这里result.txt是3.(2)中生成的数据写进去的。

cat awk_result.txt
5s 894.30 17924.01 12548.21/3585.20/1790.60) 19.29
10s 936.60 18726.48 13110.65/3742.62/1873.21) 18.95
15s 878.60 17563.34 12294.56/3511.59/1757.19) 20.74
20s 905.00 18095.78 12665.79/3620.00/1810.00) 20.37
25s 952.40 19050.27 13335.65/3809.81/1904.81) 18.61
30s 951.19 19033.45 13324.90/3806.17/1902.39) 17.95
35s 953.41 19072.96 13349.31/3816.83/1906.82) 17.32
40s 949.40 18980.94 13286.55/3795.59/1898.79) 17.95
45s 955.60 19115.65 13380.64/3823.81/1911.21) 17.32
50s 973.20 19464.97 13626.58/3891.99/1946.40) 16.71
55s 961.20 19212.67 13448.85/3841.41/1922.41) 17.01
60s 973.00 19460.73 13622.75/3892.19/1945.79) 17.01

（3）执行gnuplot绘图脚本

# cat plot_script.gp
set terminal png
set output 'sysbench_results.png'
set xlabel 'Time (s)'
set ylabel 'Transactions per second'
plot 'awk_result.txt' using 1:2 with lines title 'Transactions per second'

# bash plot_script.gp

• set xlabel “Time(s)”: 设置x轴标签为"时间(s)"
• set ylabel “Transactions per second”: 设置y轴标签为"Transactions per second"
• plot ‘awk_result.txt’ using 1:2 with lines title ‘Transactions per second’：
  绘制图表，使用"awk_result.txt"文件中的第一列作为x轴数据，第二列作为y轴数据，标题为"Transactions per second"

下面则为生成的图sysbench_results.png，该png文件在Linux不好看可以用scp命令传到本机Windows上查看。