本文测试下在阿里云上自建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)之王当之无愧。

最后是数据汇总：

图汇总：

6 RR和RC

修改隔离级别RR / RC模式，基本无性能差异。