本文测试下在阿里云上自建MySQL的性能，主要测试工具sysbench，虚拟机环境：阿里云
1 MySQL启动脚本
服务器配置：4核8G，PL0磁盘，ecs.g6.xlarge
为了方便切换版本和重现，我们使用Docker部署，先安装并启用docker：
yum install -y docker service docker start
脚本如下：
#!/bin/bash
NAME="mysql"
PUID="1000"
PGID="1000"
VOLUME="$HOME/docker_data/mysql"
MYSQL_ROOT_PASS="mysql123"
mkdir -p $VOLUME
docker ps -q -a --filter "name=$NAME" | xargs -I {} docker rm -f {}
docker run \
--hostname $NAME \
--name $NAME \
--volume "$VOLUME":/var/lib/mysql \
--env MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASS \
--env PUID=$PUID \
--env PGID=$PGID \
-p 3306:3306 \
--detach \
--restart always \
mysql:5.7
2 测试机准备
测试机：通用性，4核8G，ecs.g6.xlarge，需要单独部署一台，不要和服务器那台共享
安装下MySQL的64位二进制文件，可以在这里下载，并安装sysbench
yum install -y sysbench
3 压测准备
提高连接限制
mysql -h 172.20.3.69 -u root -pmysql123 -e "set global max_connections = 10000;" mysql -h 172.20.3.76 -u root -pmysql123 -e "set global max_prepared_stmt_count=50000;"
创建测试所需的库：
mysqladmin -h 172.20.3.69 -u root -pmysql123 create sbtest
创建测试数据，这里我选的是oltp场景：
sysbench --mysql-host=172.20.3.69 --mysql-user=root --mysql-password=mysql123 /usr/share/sysbench/oltp_common.lua --tables=10 --table_size=100000 prepare
4 压测
开32线程：
sysbench --threads=32 --time=20 --report-interval=5 --mysql-host=172.20.3.69 --mysql-port=3306 --mysql-user=root --mysql-password=mysql123 /usr/share/sysbench/oltp_read_write.lua --tables=10 --table_size=100000 run
结果：
SQL statistics:
queries performed:
read: 506730
write: 144780
other: 72390
total: 723900
transactions: 36195 (1808.14 per sec.)
queries: 723900 (36162.79 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0164s
total number of events: 36195
Latency (ms):
min: 5.38
avg: 17.69
max: 84.91
95th percentile: 33.72
sum: 640220.02
Threads fairness:
events (avg/stddev): 1131.0938/23.49
execution time (avg/stddev): 20.0069/0.00
结果比较复杂，最后我会有一个汇总表和图
这里主要看transaction和query，可以粗略理解为读和写。
query很好理解，transaction包含了，可以看代码：
- 查询：10个点查 + 1个范围查 + 1个Sum + 1个Order By + 1个Distinct
- 一个带索引更新、一个不带索引更新、1个DELETE / INSERT
可见，对于各种场景，还是覆盖的比较全面的。
5 其他压测数据
还在4核机器，ecs.g6.xlarge上，测试其他内容。
试一下关闭双1：
./mysql -h 172.20.3.69 -u root -pmysql123 -e "SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT = 2;" ./mysql -h 172.20.3.69 -u root -pmysql123 -e "SET GLOBAL SYNC_BINLOG = 1000;"
结果小幅提升：
[ 5s ] thds: 32 tps: 1971.76 qps: 39511.50 (r/w/o: 27671.77/7890.03/3949.71) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 32 tps: 1933.70 qps: 38664.51 (r/w/o: 27063.54/7733.58/3867.39) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 32 tps: 1920.60 qps: 38410.12 (r/w/o: 26886.55/7682.38/3841.19) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 32 tps: 1920.42 qps: 38416.95 (r/w/o: 26892.25/7683.87/3840.84) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 542752
write: 155072
other: 77536
total: 775360
transactions: 38768 (1936.66 per sec.)
queries: 775360 (38733.11 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0167s
total number of events: 38768
Latency (ms):
min: 3.25
avg: 16.51
max: 95.71
95th percentile: 33.72
sum: 640174.39
Threads fairness:
events (avg/stddev): 1211.5000/46.63
execution time (avg/stddev): 20.0054/0.00
提升这么小，且关闭双1会带来数据丢失风险，线上还是不要开了吧。
换MySQL 8，结果大跌眼镜，性能下降30%：
SQL statistics:
queries performed:
read: 335706
write: 95916
other: 47958
total: 479580
transactions: 23979 (1197.61 per sec.)
queries: 479580 (23952.21 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0210s
total number of events: 23979
Latency (ms):
min: 7.87
avg: 26.70
max: 170.96
95th percentile: 46.63
sum: 640354.71
Threads fairness:
events (avg/stddev): 749.3438/24.03
execution time (avg/stddev): 20.0111/0.01
可能是一些性能调优参数没有做好，也可能是8就是比5慢...这里没有继续深入分析，总之后续继续用MySQL 5.7。
换一块更好的磁盘，PL1级别，性能微提升，和关双1差不多：
SQL statistics:
queries performed:
read: 536074
write: 153164
other: 76582
total: 765820
transactions: 38291 (1912.84 per sec.)
queries: 765820 (38256.85 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0165s
total number of events: 38291
Latency (ms):
min: 4.23
avg: 16.72
max: 97.98
95th percentile: 34.33
sum: 640218.23
Threads fairness:
events (avg/stddev): 1196.5938/82.32
execution time (avg/stddev): 20.0068/0.00
其实从监控可以看到，主要是CPU瓶颈了。
所以，砸钱，换更好的机器，8核16G，ecs.g6.2xlarge(压测线程64)：
SQL statistics:
queries performed:
read: 963536
write: 275296
other: 137648
total: 1376480
transactions: 68824 (3436.77 per sec.)
queries: 1376480 (68735.35 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0245s
total number of events: 68824
Latency (ms):
min: 5.17
avg: 18.61
max: 105.28
95th percentile: 34.95
sum: 1280586.88
Threads fairness:
events (avg/stddev): 1075.3750/20.80
execution time (avg/stddev): 20.0092/0.01
有提升，但不到核数的线性。
16核，ecs.g6.4xlarge(压测线程150)：
SQL statistics:
queries performed:
read: 1688288
write: 482368
other: 241184
total: 2411840
transactions: 120592 (6012.37 per sec.)
queries: 2411840 (120247.40 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0560s
total number of events: 120592
Latency (ms):
min: 4.50
avg: 24.90
max: 404.92
95th percentile: 44.98
sum: 3002857.58
Threads fairness:
events (avg/stddev): 803.9467/20.72
execution time (avg/stddev): 20.0191/0.01
最后看下AMD的最新拳头产品EPYC米兰，16c ecs.g7a.4xlarge：
SQL statistics:
queries performed:
read: 1562862
write: 446532
other: 223266
total: 2232660
transactions: 111633 (5569.86 per sec.)
queries: 2232660 (111397.28 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0419s
total number of events: 111633
Latency (ms):
min: 3.72
avg: 34.44
max: 406.50
95th percentile: 65.65
sum: 3844893.80
Threads fairness:
events (avg/stddev): 581.4219/13.48
execution time (avg/stddev): 20.0255/0.01
确实比同为8核的Xeon差一些，大概10%，不过(虚拟机标价)价格低20%，性(zhuan)价(qian)比(u)之王当之无愧。
最后是数据汇总：
| Core | Stress_Threads | QPS | TPS | Model | Memo |
|---|---|---|---|---|---|
| 4 | 32 | 36162 | 1804 | ecs.g6.xlarge | 5.7 |
| 4 | 32 | 38733 | 1936 | ecs.g6.xlarge | 5.7 + 关双1 |
| 4 | 32 | 38526 | 1912 | ecs.g6.xlarge | 5.7 + 换PL1磁盘 |
| 4 | 32 | 23952 | 1197 | ecs.g6.xlarge | 8 |
| 8 | 64 | 68735 | 3436 | ecs.g6.2xlarge | 5.7 |
| 16 | 150 | 120247 | 6012 | ecs.g6.4xlarge | 5.7 |
| 16 | 150 | 111397 | 5569 | ecs.g7a.4xlarge | 5.7 + amd机器 |
图汇总：
6 RR和RC
修改隔离级别RR / RC模式，基本无性能差异。