mysql-5.7.35 aarch64 性能测试

# mysql-5.7.35 aarch64 性能测试
- what: mysql 5.7.35 aarch64 性能测试
- where: centos:7 aarch64
- when: 2021.11.08
- who: jiaorongtao
- why: 知识库初始化
- how:

## 环境

创建容器

```bash
[root@ecs-hk-arm jrt]$ docker run  -itd  --privileged=true --name jrt1  -p 8878:3306 centos:7  
625c2ca790e5d2000c053b5ad3f6fcc4e7c7475112e24fbd1343efcfa15e6280
```

查环境信息

```bash
#查架构
[root@625c2ca790e5 sbin]$ uname -a
Linux 625c2ca790e5 4.18.0-193.28.1.el7.aarch64 $1 SMP Wed Oct 21 16:25:35 UTC 2020 aarch64 aarch64 aarch64 GNU/Linux
#查系统
[root@625c2ca790e5 sbin]$ cat /etc/os-release 
NAME="CentOS Linux"
VERSION="7 (AltArch)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (AltArch)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7:server"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"
#查运行内存
[root@625c2ca790e5 sbin]$ cat /proc/meminfo |grep MemTotal
MemTotal:       32948864 kB
```

## 编译安装测试工具

进入容器

```bash
[root@ecs-hk-arm jrt]$ docker exec -it jrt1 bash
```

安装依赖

```bash
[root@625c2ca790e5 /]$ cd /opt
[root@625c2ca790e5 opt]$ yum install wget vim -y
Installed:
  vim-enhanced.aarch64 2:7.4.629-8.el7_9            wget.aarch64 0:1.14-18.el7_6.1

Dependency Installed:
  gpm-libs.aarch64 0:1.20.7-6.el7                 vim-common.aarch64 2:7.4.629-8.el7_9       
  vim-filesystem.aarch64 2:7.4.629-8.el7_9        which.aarch64 0:2.20-7.el7

Complete!
[root@625c2ca790e5 opt]$ yum install gcc gcc-c++ automake make libtool mysql-devel  -y
Dependency Installed:
  autoconf.noarch 0:2.69-11.el7                    cpp.aarch64 0:4.8.5-44.el7                
  glibc-devel.aarch64 0:2.17-325.el7_9             glibc-headers.aarch64 0:2.17-325.el7_9    
  kernel-headers.aarch64 0:4.18.0-305.10.2.el7     libgomp.aarch64 0:4.8.5-44.el7            
  libmpc.aarch64 0:1.0.1-3.el7                     libstdc++-devel.aarch64 0:4.8.5-44.el7    
  m4.aarch64 0:1.4.16-10.el7                       mpfr.aarch64 0:3.1.1-4.el7                
  perl-Test-Harness.noarch 0:3.28-3.el7            perl-Thread-Queue.noarch 0:3.02-2.el7

Dependency Updated:
  glibc.aarch64 0:2.17-325.el7_9             glibc-common.aarch64 0:2.17-325.el7_9

Complete!
```

安装测试工具

```bash
#安装mysql 5.7.35见mysql安装文档，此处略过
#下载sysbench
[root@625c2ca790e5 opt]$ wget https://github.com/akopytov/sysbench/archive/1.0.16.tar.gz
--2021-11-09 01:46:28--  https://github.com/akopytov/sysbench/archive/1.0.16.tar.gz
Resolving github.com (github.com)... 20.205.243.166
Connecting to github.com (github.com)|20.205.243.166|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.16 [following]
--2021-11-09 01:46:29--  https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.16
Resolving codeload.github.com (codeload.github.com)... 20.205.243.165
Connecting to codeload.github.com (codeload.github.com)|20.205.243.165|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/x-gzip]
Saving to: '1.0.16.tar.gz'

[  <=>                                                ] 1,508,595   6.32MB/s   in 0.2s

2021-11-09 01:46:30 (6.32 MB/s) - '1.0.16.tar.gz' saved [1508595]
[root@625c2ca790e5 opt]$ ls
1.0.16.tar.gz
$解压
[root@625c2ca790e5 opt]$ tar -xf 1.0.16.tar.gz 
[root@625c2ca790e5 opt]$ ls
1.0.16.tar.gz  sysbench-1.0.16
```

查询mysql数据保存路径

```bash
#回显信息中的“pkgincludedir”和“pkglibdir”的值，供后续步骤使用：
[root@625c2ca790e5 opt]$ mysql_config -help                                                  
Usage: /usr/bin/mysql_config [OPTIONS]
Compiler: GNU 7.3.1
Options:
        --cflags         [-I/usr/include/mysql ]
        --cxxflags       [-I/usr/include/mysql ]
        --include        [-I/usr/include/mysql]
        --libs           [-L/usr/lib64/mysql -lmysqlclient -lpthread -lm -lrt -lssl -lcrypto -ldl]
        --libs_r         [-L/usr/lib64/mysql -lmysqlclient -lpthread -lm -lrt -lssl -lcrypto -ldl]
        --plugindir      [/usr/lib64/mysql/plugin]
        --socket         [/var/lib/mysql/mysql.sock]
        --port           [0]
        --version        [5.7.35]
        --libmysqld-libs [-L/usr/lib64/mysql -lmysqld -lpthread -lm -lrt -lssl -lcrypto -ldl -lcrypt -laio -lnuma]
        --variable=VAR   VAR is one of:
                pkgincludedir [/usr/include/mysql]
                pkglibdir     [/usr/lib64/mysql]
                plugindir     [/usr/lib64/mysql/plugin]
[root@625c2ca790e5 opt]$ 
```

编译安装sysbench

```bash
[root@625c2ca790e5 opt]$ cd sysbench-1.0.16/
[root@625c2ca790e5 sysbench-1.0.16]$ ls
COPYING      README-Oracle.md  autogen.sh    debian      m4             rpm      src
ChangeLog    README-WIN.txt    config        doc         missing        scripts  tests
Makefile.am  README.md         configure.ac  install-sh  mkinstalldirs  snap     third_party
[root@625c2ca790e5 sysbench-1.0.16]$ ./autogen.sh 
./autogen.sh: running `autoconf' 
Libtoolized with: libtoolize (GNU libtool) 2.4.2
Automade with: automake (GNU automake) 1.13.4
Configured with: autoconf (GNU Autoconf) 2.69
[root@625c2ca790e5 sysbench-1.0.16]$ ./configure --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysql --with-mysql
LuaJIT             : bundled
LUAJIT_CFLAGS      : -I$(abs_top_builddir)/third_party/luajit/inc
LUAJIT_LIBS        : $(abs_top_builddir)/third_party/luajit/lib/libluajit-5.1.a -ldl
LUAJIT_LDFLAGS     : -rdynamic

Concurrency Kit    : bundled
CK_CFLAGS          : -I$(abs_top_builddir)/third_party/concurrency_kit/include
CK_LIBS            : $(abs_top_builddir)/third_party/concurrency_kit/lib/libck.a
configure flags    : 
===============================================================================
[root@625c2ca790e5 sysbench-1.0.16]$ make
make[2]: Leaving directory `/opt/sysbench-1.0.16/src'
make[1]: Leaving directory `/opt/sysbench-1.0.16/src'
Making all in tests
make[1]: Entering directory `/opt/sysbench-1.0.16/tests'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/opt/sysbench-1.0.16/tests'
make[1]: Entering directory `/opt/sysbench-1.0.16'
make[1]: Nothing to be done for `all-am'.
make[1]: Leaving directory `/opt/sysbench-1.0.16'
[root@625c2ca790e5 sysbench-1.0.16]$ make install
make[2]: Leaving directory `/opt/sysbench-1.0.16/tests'
make[1]: Leaving directory `/opt/sysbench-1.0.16/tests'
make[1]: Entering directory `/opt/sysbench-1.0.16'
make[2]: Entering directory `/opt/sysbench-1.0.16'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/opt/sysbench-1.0.16'
make[1]: Leaving directory `/opt/sysbench-1.0.16'
[root@625c2ca790e5 sysbench-1.0.16]$ sysbench --version
sysbench 1.0.16
```

## 测试准备

进入数据库， 创建 “sysuser”账户及数据库供后续测试sysbench使用

```bash
[root@625c2ca790e5 sysbench-1.0.16]$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.35 MySQL Community Server - (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'sysuser'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO 'sysuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> create database sbtest;
Query OK, 1 row affected (0.01 sec)

mysql>exit
```

使用sysbench创建15个表，每个表40万条记录，存储引擎innodb：

```mysql
[root@625c2ca790e5 sysbench-1.0.16]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --rand-init=on prepare
Inserting 400000 records into 'sbtest14'
Creating secondary indexes on 'sbtest14'...
Creating table 'sbtest15'...
Inserting 400000 records into 'sbtest15'
Creating secondary indexes on 'sbtest15'.
```

调整最大连接数

mysql> set GLOBAL max_connections = 2000;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
```

## 测试

每次测试完成后防止因最大连接数而导致后续测试失败，故需要重启mysql

mysql执行参数

```bash
--oltp-test-mode：执行模式，包括simple、nontrx和complex，默认是complex。simple模式下只测试简单的查询；nontrx不仅测试查询，还测试插入更新等，但是不使用事务；complex模式下测试最全面，会测试增删改查，而且会使用事务。可以根据自己的需要选择测试模式。
--oltp-tables-count：测试的表数量，根据实际情况选择
--oltp-table-size：测试的表的大小，根据实际情况选择
--threads：客户端的并发连接数
--time：测试执行的时间，单位是秒，该值不要太短，可以选择120
--report-interval：生成报告的时间间隔，单位是秒，如10

```

### 只写测试

使用sysbench进行100并发只读测试，测试时间2分钟

第一次测试

```bash
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-write-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 666.08 qps: 4009.35 (r/w/o: 0.00/2667.20/1342.15) lat (ms,99%): 893.56 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 713.80 qps: 4286.60 (r/w/o: 0.00/2859.00/1427.60) lat (ms,99%): 995.51 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 895.20 qps: 5374.21 (r/w/o: 0.00/3583.81/1790.40) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 854.00 qps: 5116.89 (r/w/o: 0.00/3408.90/1708.00) lat (ms,99%): 831.46 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 842.70 qps: 5059.60 (r/w/o: 0.00/3374.20/1685.40) lat (ms,99%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 851.70 qps: 5111.00 (r/w/o: 0.00/3407.60/1703.40) lat (ms,99%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 957.10 qps: 5741.90 (r/w/o: 0.00/3827.70/1914.20) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 877.00 qps: 5261.20 (r/w/o: 0.00/3507.20/1754.00) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 925.60 qps: 5552.60 (r/w/o: 0.00/3701.40/1851.20) lat (ms,99%): 669.89 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 875.30 qps: 5252.40 (r/w/o: 0.00/3501.80/1750.60) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 901.10 qps: 5410.20 (r/w/o: 0.00/3608.00/1802.20) lat (ms,99%): 634.66 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 910.30 qps: 5488.49 (r/w/o: 0.00/3668.30/1820.20) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           411184
        other:                           205592
        total:                           616776
    transactions:                        102796 (856.17 per sec.)
    queries:                             616776 (5137.00 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0644s
    total number of events:              102796

Latency (ms):
         min:                                    1.42
         avg:                                  116.74
         max:                                 1662.06
         99th percentile:                      746.32
         sum:                             12000707.77

Threads fairness:
    events (avg/stddev):           1027.9600/37.10
    execution time (avg/stddev):   120.0071/0.01

[root@625c2ca790e5 opt]$

```

第二次测试

```bash
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-write-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 772.52 qps: 4652.81 (r/w/o: 0.00/3097.77/1555.04) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 857.80 qps: 5149.02 (r/w/o: 0.00/3433.41/1715.61) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 816.80 qps: 4895.48 (r/w/o: 0.00/3261.89/1633.59) lat (ms,99%): 877.61 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 841.39 qps: 5070.62 (r/w/o: 0.00/3387.94/1682.67) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 887.52 qps: 5305.11 (r/w/o: 0.00/3529.97/1775.14) lat (ms,99%): 861.95 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 843.90 qps: 5063.10 (r/w/o: 0.00/3375.30/1687.80) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 928.50 qps: 5568.77 (r/w/o: 0.00/3711.78/1856.99) lat (ms,99%): 816.63 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 875.30 qps: 5257.53 (r/w/o: 0.00/3506.92/1750.61) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 906.50 qps: 5434.68 (r/w/o: 0.00/3621.68/1812.99) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 885.00 qps: 5309.72 (r/w/o: 0.00/3539.71/1770.01) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 849.00 qps: 5095.20 (r/w/o: 0.00/3397.20/1698.00) lat (ms,99%): 831.46 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 884.30 qps: 5309.50 (r/w/o: 0.00/3540.90/1768.60) lat (ms,99%): 646.19 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           414344
        other:                           207172
        total:                           621516
    transactions:                        103586 (862.47 per sec.)
    queries:                             621516 (5174.79 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1035s
    total number of events:              103586

Latency (ms):
         min:                                    1.36
         avg:                                  115.90
         max:                                 2114.28
         99th percentile:                      733.00
         sum:                             12005898.19

Threads fairness:
    events (avg/stddev):           1035.8600/43.87
    execution time (avg/stddev):   120.0590/0.01

[root@625c2ca790e5 opt]$

```

第三次测试

```bash
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-write-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 792.46 qps: 4774.93 (r/w/o: 0.00/3180.02/1594.91) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 870.00 qps: 5213.81 (r/w/o: 0.00/3473.81/1740.00) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 904.80 qps: 5428.30 (r/w/o: 0.00/3618.70/1809.60) lat (ms,99%): 530.08 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 896.40 qps: 5384.50 (r/w/o: 0.00/3591.70/1792.80) lat (ms,99%): 669.89 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 881.20 qps: 5283.70 (r/w/o: 0.00/3521.30/1762.40) lat (ms,99%): 669.89 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 898.90 qps: 5389.40 (r/w/o: 0.00/3591.60/1797.80) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 843.80 qps: 5070.80 (r/w/o: 0.00/3383.20/1687.60) lat (ms,99%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 911.90 qps: 5469.10 (r/w/o: 0.00/3645.30/1823.80) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 923.70 qps: 5539.38 (r/w/o: 0.00/3691.99/1847.39) lat (ms,99%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 885.10 qps: 5311.12 (r/w/o: 0.00/3540.91/1770.21) lat (ms,99%): 719.92 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 913.30 qps: 5479.40 (r/w/o: 0.00/3652.80/1826.60) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 918.70 qps: 5510.88 (r/w/o: 0.00/3673.49/1837.39) lat (ms,99%): 669.89 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           426016
        other:                           213008
        total:                           639024
    transactions:                        106504 (886.21 per sec.)
    queries:                             639024 (5317.26 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1781s
    total number of events:              106504

Latency (ms):
         min:                                    1.40
         avg:                                  112.77
         max:                                 2353.47
         99th percentile:                      669.89
         sum:                             12009956.52

Threads fairness:
    events (avg/stddev):           1065.0400/34.62
    execution time (avg/stddev):   120.0996/0.03

[root@625c2ca790e5 opt]$
```

### 只读测试

第一次测试

```bash
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 2136.70 qps: 34266.45 (r/w/o: 29983.46/0.00/4282.99) lat (ms,99%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 2255.51 qps: 36089.81 (r/w/o: 31578.60/0.00/4511.21) lat (ms,99%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 2257.28 qps: 36118.93 (r/w/o: 31604.47/0.00/4514.47) lat (ms,99%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 2260.70 qps: 36172.01 (r/w/o: 31650.51/0.00/4521.50) lat (ms,99%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 2252.81 qps: 36033.50 (r/w/o: 31528.19/0.00/4505.31) lat (ms,99%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 2256.51 qps: 36105.73 (r/w/o: 31592.21/0.00/4513.52) lat (ms,99%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 2253.30 qps: 36068.52 (r/w/o: 31562.11/0.00/4506.40) lat (ms,99%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 2253.97 qps: 36059.87 (r/w/o: 31551.83/0.00/4508.05) lat (ms,99%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 2254.83 qps: 36069.86 (r/w/o: 31560.10/0.00/4509.76) lat (ms,99%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 2250.19 qps: 36006.92 (r/w/o: 31506.74/0.00/4500.18) lat (ms,99%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 2251.81 qps: 36028.74 (r/w/o: 31525.02/0.00/4503.72) lat (ms,99%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 2248.35 qps: 35969.22 (r/w/o: 31472.82/0.00/4496.40) lat (ms,99%): 158.63 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            3771880
        write:                           0
        other:                           538840
        total:                           4310720
    transactions:                        269420 (2244.21 per sec.)
    queries:                             4310720 (35907.37 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0502s
    total number of events:              269420

Latency (ms):
         min:                                    2.87
         avg:                                   44.55
         max:                                 1254.89
         99th percentile:                      155.80
         sum:                             12001895.45

Threads fairness:
    events (avg/stddev):           2694.2000/42.73
    execution time (avg/stddev):   120.0190/0.01

[2]+  Exit 1                  ./mysqld --user=mysql
[root@625c2ca790e5 opt]$ 
```

第二次测试

```bash
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 1890.63 qps: 30325.60 (r/w/o: 26534.55/0.00/3791.05) lat (ms,99%): 219.36 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 1982.73 qps: 31722.64 (r/w/o: 27757.69/0.00/3964.96) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 1989.69 qps: 31838.27 (r/w/o: 27858.19/0.00/3980.07) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 2072.13 qps: 33155.02 (r/w/o: 29010.97/0.00/4144.05) lat (ms,99%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 2103.70 qps: 33657.75 (r/w/o: 29450.34/0.00/4207.41) lat (ms,99%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 1985.21 qps: 31762.95 (r/w/o: 27792.53/0.00/3970.42) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 1986.09 qps: 31779.32 (r/w/o: 27807.43/0.00/3971.89) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 1955.70 qps: 31292.66 (r/w/o: 27380.75/0.00/3911.91) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 1969.67 qps: 31518.04 (r/w/o: 27579.11/0.00/3938.93) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 1972.21 qps: 31552.03 (r/w/o: 27607.30/0.00/3944.73) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 1962.42 qps: 31396.32 (r/w/o: 27471.78/0.00/3924.54) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 2005.12 qps: 32081.28 (r/w/o: 28071.73/0.00/4009.55) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            3343942
        write:                           0
        other:                           477706
        total:                           3821648
    transactions:                        238853 (1989.53 per sec.)
    queries:                             3821648 (31832.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0539s
    total number of events:              238853

Latency (ms):
         min:                                    2.66
         avg:                                   50.24
         max:                                  975.82
         99th percentile:                      200.47
         sum:                             11999533.83

Threads fairness:
    events (avg/stddev):           2388.5300/50.90
    execution time (avg/stddev):   119.9953/0.02

[root@625c2ca790e5 opt]$ 
```

第三次测试

```bash
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 2135.53 qps: 34246.09 (r/w/o: 29965.44/0.00/4280.65) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 2259.60 qps: 36152.74 (r/w/o: 31633.44/0.00/4519.29) lat (ms,99%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 2259.41 qps: 36148.69 (r/w/o: 31630.07/0.00/4518.62) lat (ms,99%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 2261.76 qps: 36194.41 (r/w/o: 31670.58/0.00/4523.83) lat (ms,99%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 2253.94 qps: 36067.38 (r/w/o: 31559.60/0.00/4507.77) lat (ms,99%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 2256.59 qps: 36092.67 (r/w/o: 31579.50/0.00/4513.17) lat (ms,99%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 2256.89 qps: 36114.50 (r/w/o: 31600.41/0.00/4514.09) lat (ms,99%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 2252.02 qps: 36037.77 (r/w/o: 31533.84/0.00/4503.93) lat (ms,99%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 2264.31 qps: 36223.50 (r/w/o: 31695.18/0.00/4528.31) lat (ms,99%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 2254.30 qps: 36068.72 (r/w/o: 31560.02/0.00/4508.70) lat (ms,99%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 2255.11 qps: 36090.90 (r/w/o: 31580.49/0.00/4510.41) lat (ms,99%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 2256.02 qps: 36088.00 (r/w/o: 31576.16/0.00/4511.84) lat (ms,99%): 173.58 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            3776584
        write:                           0
        other:                           539512
        total:                           4316096
    transactions:                        269756 (2247.03 per sec.)
    queries:                             4316096 (35952.47 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0491s
    total number of events:              269756

Latency (ms):
         min:                                    2.79
         avg:                                   44.49
         max:                                  968.58
         99th percentile:                      183.21
         sum:                             12001746.76

Threads fairness:
    events (avg/stddev):           2697.5600/44.48
    execution time (avg/stddev):   120.0175/0.01

[root@625c2ca790e5 opt]$ 
```

### 读写混合测试

第一次测试

```bash
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 511.64 qps: 10355.68 (r/w/o: 7261.84/2060.66/1033.18) lat (ms,99%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 548.50 qps: 10916.48 (r/w/o: 7636.29/2183.10/1097.10) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 550.90 qps: 11051.31 (r/w/o: 7745.04/2204.58/1101.69) lat (ms,99%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 546.61 qps: 10936.97 (r/w/o: 7658.19/2185.45/1093.33) lat (ms,99%): 773.68 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 520.80 qps: 10387.29 (r/w/o: 7262.39/2083.30/1041.60) lat (ms,99%): 802.05 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 543.70 qps: 10889.79 (r/w/o: 7627.99/2174.40/1087.40) lat (ms,99%): 802.05 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 576.60 qps: 11568.16 (r/w/o: 8107.07/2307.89/1153.20) lat (ms,99%): 759.88 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 557.30 qps: 11120.51 (r/w/o: 7776.91/2229.00/1114.60) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 553.20 qps: 11045.12 (r/w/o: 7726.71/2212.00/1106.40) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 554.70 qps: 11136.08 (r/w/o: 7807.89/2218.80/1109.40) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 526.69 qps: 10493.08 (r/w/o: 7333.32/2106.38/1053.39) lat (ms,99%): 719.92 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 544.91 qps: 10919.95 (r/w/o: 7651.20/2178.93/1089.81) lat (ms,99%): 682.06 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            916384
        write:                           261824
        other:                           130912
        total:                           1309120
    transactions:                        65456  (544.72 per sec.)
    queries:                             1309120 (10894.45 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1630s
    total number of events:              65456

Latency (ms):
         min:                                    4.31
         avg:                                  183.54
         max:                                 1896.96
         99th percentile:                      719.92
         sum:                             12013618.92

Threads fairness:
    events (avg/stddev):           654.5600/21.36
    execution time (avg/stddev):   120.1362/0.01

[1]-  Exit 1                  ./mysqld --user=mysql
[root@625c2ca790e5 opt]$ 
```

第二次测试

```bash
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 516.00 qps: 10413.91 (r/w/o: 7302.50/2069.40/1042.00) lat (ms,99%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 516.91 qps: 10342.91 (r/w/o: 7235.07/2074.02/1033.81) lat (ms,99%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 539.20 qps: 10755.50 (r/w/o: 7530.50/2146.60/1078.40) lat (ms,99%): 1258.08 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 559.70 qps: 11229.81 (r/w/o: 7870.41/2240.00/1119.40) lat (ms,99%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 581.59 qps: 11657.55 (r/w/o: 8154.39/2340.47/1162.68) lat (ms,99%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 560.01 qps: 11200.94 (r/w/o: 7852.10/2228.33/1120.51) lat (ms,99%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 567.70 qps: 11355.49 (r/w/o: 7950.39/2269.70/1135.40) lat (ms,99%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 522.80 qps: 10466.31 (r/w/o: 7329.81/2090.90/1045.60) lat (ms,99%): 746.32 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 526.00 qps: 10506.65 (r/w/o: 7340.57/2114.39/1051.70) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 543.60 qps: 10858.74 (r/w/o: 7605.93/2165.31/1087.50) lat (ms,99%): 634.66 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 533.50 qps: 10703.60 (r/w/o: 7504.20/2132.40/1067.00) lat (ms,99%): 612.21 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 507.79 qps: 10135.06 (r/w/o: 7085.30/2034.17/1015.59) lat (ms,99%): 831.46 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            907886
        write:                           259396
        other:                           129698
        total:                           1296980
    transactions:                        64849  (539.86 per sec.)
    queries:                             1296980 (10797.29 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1199s
    total number of events:              64849

Latency (ms):
         min:                                    4.16
         avg:                                  185.09
         max:                                 2051.53
         99th percentile:                      733.00
         sum:                             12002888.33

Threads fairness:
    events (avg/stddev):           648.4900/21.35
    execution time (avg/stddev):   120.0289/0.03

[root@625c2ca790e5 opt]$ 
```

第三次测试

```bash
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 404.57 qps: 8176.32 (r/w/o: 5736.46/1621.01/818.85) lat (ms,99%): 1089.30 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 473.50 qps: 9479.06 (r/w/o: 6634.54/1897.41/947.11) lat (ms,99%): 909.80 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 565.10 qps: 11288.42 (r/w/o: 7902.01/2256.20/1130.20) lat (ms,99%): 634.66 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 528.40 qps: 10605.56 (r/w/o: 7433.48/2115.29/1056.80) lat (ms,99%): 1013.60 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 544.40 qps: 10877.53 (r/w/o: 7611.62/2177.11/1088.80) lat (ms,99%): 773.68 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 582.69 qps: 11638.80 (r/w/o: 8142.56/2330.86/1165.38) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 560.61 qps: 11227.71 (r/w/o: 7866.04/2240.44/1121.22) lat (ms,99%): 759.88 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 556.20 qps: 11080.39 (r/w/o: 7742.00/2226.00/1112.40) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 552.20 qps: 11097.40 (r/w/o: 7784.20/2208.80/1104.40) lat (ms,99%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 545.29 qps: 10875.95 (r/w/o: 7605.60/2179.77/1090.59) lat (ms,99%): 816.63 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 540.01 qps: 10787.25 (r/w/o: 7545.91/2161.33/1080.02) lat (ms,99%): 646.19 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            889700
        write:                           254200
        other:                           127100
        total:                           1271000
    transactions:                        63550  (529.20 per sec.)
    queries:                             1271000 (10583.99 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0861s
    total number of events:              63550

Latency (ms):
         min:                                    4.36
         avg:                                  188.87
         max:                                 2190.20
         99th percentile:                      802.05
         sum:                             12002618.59

Threads fairness:
    events (avg/stddev):           635.5000/17.46
    execution time (avg/stddev):   120.0262/0.06

[root@625c2ca790e5 opt]$ 
```

## 测试总结

如下表所示，在8核32g的aarch64环境中，利用sysbench工具测试mysql并发100时，只读、只写、读写的平均耗时分别为：115.14ms、46.43ms、  185.83ms

从性能看，mysql可以达到要求。

|          | 平均耗时（ms） |        |        |        |
| -------- | -------------- | ------ | ------ | ------ |
|          | 第一次         | 第二次 | 第三次 | 平均   |
| 只写测试 | 116.74         | 115.90 | 112.77 | 115.14 |
| 只读测试 | 44.55          | 50.24  | 44.49  | 46.43  |
| 读写测试 | 183.54         | 185.09 | 188.87 | 185.83 |