阿里云自建MySQL的性能测试(多配置对比)

本文测试下在阿里云上自建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模式,基本无性能差异。

Leave a Reply

Your email address will not be published.