基准测试

使用 SpringBoot 应用测试

业务场景设计

在电商场景中，使用用户和商家创建、查询订单的性能作为基准测试指标对不同版本的数据库性能进行对比。需求如下：

新增订单
根据订单 ID 查询
用户查询订单
- 用户查询指定日期范围+所有状态的订单，查询条件用户ID+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。
- 用户查询指定日期范围+指定状态的订单，查询条件用户ID+订单状态+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。
商家查询订单
- 商家查询指定日期范围+所有状态的订单，查询条件商家ID+订单删除状态+日期范围+分页 ，新的订单排在最前。
- 商家查询指定日期范围+指定状态的订单，查询条件商家ID+订单状态+订单删除状态+日期范围+分页 ，新的订单排在最前。

不同版本的 MariaDB、MySQL 测试对比

测试说明

本测试使用本站示例测试

准备测试环境

VMware ESXi, 7.0.3, 20328353，Intel(R) Xeon(R) Platinum 8269CY CPU @ 2.50GHz

一个数据库实例：8C8G、CentOS8

一个 OpenResty 反向代理实例：8C8G、CentOS8

四个 SpringBoot 应用实例：8C8G、CentOS8

一个 wrk 压力机：10C10G、CentOS8

修改 application.properties 中的配置指向数据库服务

properties

spring.datasource.url=jdbc:mariadb://192.168.1.190:3306/demo?useSSL=false&allowPublicKeyRetrieval=true

编译并推送镜像

bash

./build.sh && ./push.sh

准备数据库服务

复制示例中的 deployer 目录到实例中

bash

scp -r deployer/* root@192.168.1.xxx:~/deployer-mysql-benchmark

启动数据库服务

bash

cd ~/deployer-mysql-benchmark/common && docker compose down -v && docker compose pull && docker compose up -d

准备 SpringBoot 应用服务

复制示例中的 deployer 目录到实例中

bash

scp -r deployer/* root@192.168.1.xxx:~/deployer-mysql-benchmark

启动 SpringBoot 服务

bash

cd ~/deployer-mysql-benchmark/service && docker compose pull && docker compose up -d

准备 OpenResty 反向代理服务

复制代码到 OpenResty 反向代理实例中

修改 nginx.conf 指向各个 SpringBoot 应用，如下：

upstream backend {
    server 192.168.1.187:8080;
    server 192.168.1.188:8080;
    server 192.168.1.193:8080;
    server 192.168.1.194:8080;
}

启动服务
bash
```
docker compose up -d
```

使用示例中的 rest-api.http 测试接口是否正常

准备 wrk 压力机

参考链接编译 wrk

测试 MariaDB10.4

修改示例中 db/Dockerfile 基础镜像，如下：

dockerfile

FROM mariadb:10.4

编译并推送镜像

bash

./build.sh && ./push.sh

重启数据库服务

bash

cd ~/deployer-mysql-benchmark/common && docker compose down -v && docker compose pull && docker compose up -d

重启各个 SpringBoot 应用

bash

cd ~/deployer-mysql-benchmark/service && docker compose pull && docker compose restart

安装 Apache Bench 工具：参考链接

初始化150万数据

bash

ab -n 1500000 -c 64 -k http://192.168.1.185/api/v1/order/create

数据初始化完毕后，分别重启各个 SpringBoot 应用以加载所有订单 ID

bash

cd ~/deployer-mysql-benchmark/service && docker compose pull && docker compose restart

测试根据订单 ID 查询

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/getById
Running 15s test @ http://192.168.1.185/api/v1/order/getById
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   505.18ms  290.59ms   3.03s    82.99%
    Req/Sec   507.87     93.61   838.00     69.67%
  Latency Distribution
     50%  497.86ms
     75%  710.33ms
     90%  848.04ms
     99%    1.44s 
  60710 requests in 15.04s, 33.64MB read
Requests/sec:   4036.13
Transfer/sec:      2.24MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||                                                                      28.5%]   4[|||||||||||||||||||||||||||||||||||                                                                   31.8%]
    1[||||||||||||||||||||||||||||||||||                                                                   30.7%]   5[|||||||||||||||||||||||||||||||                                                                       28.9%]
    2[|||||||||||||||||||||||||||||||||||                                                                  31.4%]   6[||||||||||||||||||||||||||||||||||                                                                    30.4%]
    3[||||||||||||||||||||||||||||||||||                                                                   31.5%]   7[|||||||||||||||||||||||||||||||                                                                       28.5%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||1.81G/7.77G] Tasks: 86, 252 thr, 245 kthr; 7 running
  Swp[                                                                                                  0K/4.93G] Load average: 1.39 1.21 1.47 
                                                                                                                  Uptime: 23:17:09

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  19633 systemd-co  20   0 10.0G 1083M 22572 S 213.8 13.6 22:14.20 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19852 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:29.52 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19864 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:29.62 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19865 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:29.22 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19868 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:29.25 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19872 systemd-co  20   0 10.0G 1083M 22572 R   6.0 13.6  0:29.29 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19877 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:29.37 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19878 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:29.61 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19886 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:29.33 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19850 systemd-co  20   0 10.0G 1083M 22572 S   5.3 13.6  0:33.61 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19851 systemd-co  20   0 10.0G 1083M 22572 S   5.3 13.6  0:29.46 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19853 systemd-co  20   0 10.0G 1083M 22572 S   5.3 13.6  0:29.34 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19854 systemd-co  20   0 10.0G 1083M 22572 S   5.3 13.6  0:29.65 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19856 systemd-co  20   0 10.0G 1083M 22572 S   5.3 13.6  0:29.33 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19857 systemd-co  20   0 10.0G 1083M 22572 R   5.3 13.6  0:34.65 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19860 systemd-co  20   0 10.0G 1083M 22572 S   5.3 13.6  0:33.74 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19862 systemd-co  20   0 10.0G 1083M 22572 S   5.3 13.6  0:29.46 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19866 systemd-co  20   0 10.0G 1083M 22572 S   5.3 13.6  0:29.60 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试用户查询指定日期范围+所有状态的订单，查询条件用户ID+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByUserIdAndWithoutStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByUserIdAndWithoutStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   465.28ms  277.29ms   2.62s    78.82%
    Req/Sec   551.21    159.52     1.60k    69.97%
  Latency Distribution
     50%  319.48ms
     75%  602.44ms
     90%  877.74ms
     99%    1.25s 
  65859 requests in 15.05s, 32.48MB read
Requests/sec:   4375.34
Transfer/sec:      2.16MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||                                                              35.5%]   4[||||||||||||||||||||||||||||||||||||                                                                  33.1%]
    1[|||||||||||||||||||||||||||||||||||||                                                                33.8%]   5[|||||||||||||||||||||||||||||||||||||||                                                               35.1%]
    2[||||||||||||||||||||||||||||||||||||||                                                               35.1%]   6[||||||||||||||||||||||||||||||||||                                                                    30.6%]
    3[|||||||||||||||||||||||||||||||||                                                                    30.8%]   7[|||||||||||||||||||||||||||||||||||                                                                   31.3%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||1.79G/7.77G] Tasks: 86, 252 thr, 240 kthr; 7 running
  Swp[                                                                                                  0K/4.93G] Load average: 1.65 1.00 1.21 
                                                                                                                  Uptime: 23:23:45

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  19633 systemd-co  20   0 10.0G 1083M 22572 S 244.4 13.6 24:22.26 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19887 systemd-co  20   0 10.0G 1083M 22572 S   7.4 13.6  0:37.90 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19855 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:32.71 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19856 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:32.53 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19857 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:37.83 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19862 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:32.63 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19864 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:32.81 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19870 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:42.43 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19879 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:32.66 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19886 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:32.52 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19888 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:32.51 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19889 systemd-co  20   0 10.0G 1083M 22572 S   6.7 13.6  0:32.64 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19850 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:36.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19851 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:32.67 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19852 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:32.74 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19853 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:32.50 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19854 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:32.83 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19858 systemd-co  20   0 10.0G 1083M 22572 S   6.0 13.6  0:32.80 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试用户查询指定日期范围+指定状态的订单，查询条件用户ID+订单状态+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByUserIdAndStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByUserIdAndStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   359.51ms  202.01ms   1.85s    70.56%
    Req/Sec   722.26    196.23     1.60k    69.50%
  Latency Distribution
     50%  312.09ms
     75%  342.03ms
     90%  616.55ms
     99%  954.06ms
  86299 requests in 15.06s, 24.08MB read
Requests/sec:   5730.63
Transfer/sec:      1.60MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||                                                              35.7%]   4[||||||||||||||||||||||||||||||||||||||                                                                34.2%]
    1[|||||||||||||||||||||||||||||||||||||                                                                34.0%]   5[|||||||||||||||||||||||||||||||||||||                                                                 34.0%]
    2[|||||||||||||||||||||||||||||||||||||||                                                              35.5%]   6[|||||||||||||||||||||||||||||||||||||                                                                 34.0%]
    3[||||||||||||||||||||||||||||||||||||||                                                               35.3%]   7[||||||||||||||||||||||||||||||||||||||                                                                34.9%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||1.79G/7.77G] Tasks: 86, 252 thr, 243 kthr; 6 running
  Swp[                                                                                                  0K/4.93G] Load average: 1.83 1.07 1.12 
                                                                                                                  Uptime: 23:29:55

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  19633 systemd-co  20   0 10.0G 1083M 22572 S 252.6 13.6 28:18.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19853 systemd-co  20   0 10.0G 1083M 22572 S   7.3 13.6  0:38.45 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19881 systemd-co  20   0 10.0G 1083M 22572 S   7.3 13.6  0:44.06 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19850 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:42.70 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19852 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.69 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19854 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.74 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19855 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.65 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19857 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:43.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19860 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:42.81 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19864 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.72 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19865 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.33 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19868 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.30 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19872 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.37 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19874 systemd-co  20   0 10.0G 1083M 22572 R   6.6 13.6  0:38.51 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19877 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.45 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19879 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.53 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19886 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.40 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19888 systemd-co  20   0 10.0G 1083M 22572 S   6.6 13.6  0:38.41 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试商家查询指定日期范围+所有状态的订单，查询条件商家ID+订单删除状态+日期范围+分页 ，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByMerchantIdAndWithoutStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByMerchantIdAndWithoutStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency     1.95s   750.33ms   6.31s    62.79%
    Req/Sec   119.77     63.47   535.00     74.36%
  Latency Distribution
     50%    2.01s 
     75%    2.13s 
     90%    3.02s 
     99%    4.10s 
  14070 requests in 15.06s, 76.51MB read
Requests/sec:    934.16
Transfer/sec:      5.08MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                      58.8%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                    60.3%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                  60.9%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                 62.7%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                 62.3%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                    59.7%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                  62.4%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                  62.3%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||1.81G/7.77G] Tasks: 86, 252 thr, 244 kthr; 8 running
  Swp[                                                                                                  0K/4.93G] Load average: 2.24 1.30 1.11 
                                                                                                                  Uptime: 23:37:01

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  19633 systemd-co  20   0 10.0G 1083M 22572 S 484.9 13.6 31:52.36 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19858 systemd-co  20   0 10.0G 1083M 22572 S  15.3 13.6  0:44.82 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19861 systemd-co  23   3 10.0G 1083M 22572 S  15.3 13.6  0:44.60 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19852 systemd-co  21   1 10.0G 1083M 22572 S  14.0 13.6  0:44.47 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19881 systemd-co  21   1 10.0G 1083M 22572 S  14.0 13.6  0:49.84 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19884 systemd-co  23   3 10.0G 1083M 22572 S  14.0 13.6  0:43.79 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19862 systemd-co  20   0 10.0G 1083M 22572 S  13.3 13.6  0:44.64 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19865 systemd-co  23   3 10.0G 1083M 22572 S  13.3 13.6  0:43.47 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19868 systemd-co  23   3 10.0G 1083M 22572 S  13.3 13.6  0:43.39 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19887 systemd-co  23   3 10.0G 1083M 22572 S  13.3 13.6  0:49.03 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19850 systemd-co  21   1 10.0G 1083M 22572 S  12.7 13.6  0:48.56 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19851 systemd-co  20   0 10.0G 1083M 22572 R  12.7 13.6  0:44.65 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19856 systemd-co  20   0 10.0G 1083M 22572 R  12.7 13.6  0:44.49 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19863 systemd-co  23   3 10.0G 1083M 22572 S  12.7 13.6  0:43.89 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19870 systemd-co  23   3 10.0G 1083M 22572 S  12.7 13.6  0:53.55 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19871 systemd-co  23   3 10.0G 1083M 22572 S  12.7 13.6  0:43.40 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19875 systemd-co  23   3 10.0G 1083M 22572 R  12.7 13.6  0:43.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19878 systemd-co  23   3 10.0G 1083M 22572 R  12.7 13.6  0:43.98 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试商家查询指定日期范围+指定状态的订单，查询条件商家ID+订单状态+订单删除状态+日期范围+分页 ，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByMerchantIdAndStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByMerchantIdAndStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency     1.92s   973.19ms   7.36s    73.87%
    Req/Sec   124.65     67.21   450.00     70.74%
  Latency Distribution
     50%    2.01s 
     75%    2.17s 
     90%    3.16s 
     99%    4.99s 
  14506 requests in 15.05s, 18.93MB read
Requests/sec:    963.82
Transfer/sec:      1.26MB

数据库 CPU 利用率

    0[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                   61.5%]   4[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                   61.3%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                    59.6%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                   61.1%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                   60.8%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                   60.4%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                63.8%]   7[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                   61.3%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||1.80G/7.77G] Tasks: 86, 253 thr, 242 kthr; 8 running
  Swp[                                                                                                  0K/4.93G] Load average: 5.88 2.95 1.75 
                                                                                                                  Uptime: 23:40:10

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  19633 systemd-co  20   0 10.0G 1096M 22612 S 486.5 13.8 37:33.56 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19856 systemd-co  20   0 10.0G 1096M 22612 S  16.6 13.8  0:54.39 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19858 systemd-co  20   0 10.0G 1096M 22612 S  14.0 13.8  0:54.72 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19862 systemd-co  20   0 10.0G 1096M 22612 R  14.0 13.8  0:54.67 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19869 systemd-co  23   3 10.0G 1096M 22612 S  14.0 13.8  0:51.96 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19870 systemd-co  23   3 10.0G 1096M 22612 S  14.0 13.8  1:01.90 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19872 systemd-co  23   3 10.0G 1096M 22612 S  14.0 13.8  0:51.90 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19851 systemd-co  20   0 10.0G 1096M 22612 R  13.3 13.8  0:54.52 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19864 systemd-co  23   3 10.0G 1096M 22612 S  13.3 13.8  0:52.10 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19867 systemd-co  23   3 10.0G 1096M 22612 S  13.3 13.8  0:51.91 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19874 systemd-co  23   3 10.0G 1096M 22612 R  13.3 13.8  0:52.02 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19860 systemd-co  23   3 10.0G 1096M 22612 S  12.6 13.8  0:56.24 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19863 systemd-co  23   3 10.0G 1096M 22612 S  12.6 13.8  0:52.27 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19865 systemd-co  23   3 10.0G 1096M 22612 R  12.6 13.8  0:51.97 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19881 systemd-co  21   1 10.0G 1096M 22612 S  12.6 13.8  0:59.11 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19886 systemd-co  23   3 10.0G 1096M 22612 S  12.6 13.8  0:51.78 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19887 systemd-co  23   3 10.0G 1096M 22612 S  12.6 13.8  0:57.42 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  19888 systemd-co  23   3 10.0G 1096M 22612 S  12.6 13.8  0:51.99 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试新增订单

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/create
Running 15s test @ http://192.168.1.185/api/v1/order/create
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   231.39ms  188.33ms   2.42s    76.25%
    Req/Sec     1.19k   137.01     1.60k    73.50%
  Latency Distribution
     50%  202.60ms
     75%  313.62ms
     90%  481.42ms
     99%  862.60ms
  142158 requests in 15.06s, 34.45MB read
Requests/sec:   9440.50
Transfer/sec:      2.29MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||              80.9%]   4[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||       88.1%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||          85.1%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||         86.3%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||             82.4%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||              81.1%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||            82.4%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||        86.7%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||1.87G/7.77G] Tasks: 86, 252 thr, 244 kthr; 8 running
  Swp[                                                                                                  0K/4.93G] Load average: 5.80 1.70 1.06 
                                                                                                                  Uptime: 1 day, 00:35:24

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  51582 systemd-co  20   0 10.0G 1136M 24004 S 595.5 14.3 20:05.14 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  51788 systemd-co  20   0 10.0G 1136M 24004 S  27.5 14.3  1:06.85 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52148 systemd-co  20   0 10.0G 1136M 24004 R  16.8 14.3  0:04.82 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52168 systemd-co  20   0 10.0G 1136M 24004 S  16.8 14.3  0:04.52 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52134 systemd-co  20   0 10.0G 1136M 24004 S  15.4 14.3  0:04.45 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52159 systemd-co  20   0 10.0G 1136M 24004 R  15.4 14.3  0:04.89 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52129 systemd-co  20   0 10.0G 1136M 24004 R  14.8 14.3  0:04.76 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52143 systemd-co  20   0 10.0G 1136M 24004 S  14.8 14.3  0:04.79 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52124 systemd-co  20   0 10.0G 1136M 24004 S  14.1 14.3  0:04.79 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52128 systemd-co  20   0 10.0G 1136M 24004 S  14.1 14.3  0:04.65 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52135 systemd-co  20   0 10.0G 1136M 24004 S  14.1 14.3  0:05.02 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52140 systemd-co  20   0 10.0G 1136M 24004 R  14.1 14.3  0:04.78 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52150 systemd-co  20   0 10.0G 1136M 24004 S  14.1 14.3  0:04.72 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52152 systemd-co  20   0 10.0G 1136M 24004 R  14.1 14.3  0:04.49 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52155 systemd-co  20   0 10.0G 1136M 24004 R  14.1 14.3  0:04.80 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52125 systemd-co  20   0 10.0G 1136M 24004 R  13.4 14.3  0:04.75 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52126 systemd-co  20   0 10.0G 1136M 24004 S  13.4 14.3  0:10.39 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52127 systemd-co  20   0 10.0G 1136M 24004 S  13.4 14.3  0:04.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试 MariaDB11.4

修改示例中 db/Dockerfile 基础镜像，如下：

dockerfile

FROM mariadb:11.4

编译并推送镜像

bash

./build.sh && ./push.sh

重启数据库服务

bash

cd ~/deployer-mysql-benchmark/common && docker compose down -v && docker compose pull && docker compose up -d

重启各个 SpringBoot 应用

bash

cd ~/deployer-mysql-benchmark/service && docker compose pull && docker compose restart

安装 Apache Bench 工具：参考链接

初始化150万数据

bash

ab -n 1500000 -c 64 -k http://192.168.1.185/api/v1/order/create

数据初始化完毕后，分别重启各个 SpringBoot 应用以加载所有订单 ID

bash

cd ~/deployer-mysql-benchmark/service && docker compose pull && docker compose restart

测试根据订单 ID 查询

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/getById
Running 15s test @ http://192.168.1.185/api/v1/order/getById
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    78.59ms   76.06ms 904.54ms   68.03%
    Req/Sec     3.85k   532.37     7.88k    64.42%
  Latency Distribution
     50%   63.01ms
     75%  115.07ms
     90%  178.73ms
     99%  333.02ms
  459302 requests in 15.04s, 254.50MB read
Requests/sec:  30534.41
Transfer/sec:     16.92MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||            82.4%]   4[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||             82.9%]
    1[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||           83.2%]   5[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||              81.6%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||              81.1%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||           84.0%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||            82.8%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||              80.9%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 1.61G/7.77G] Tasks: 86, 229 thr, 241 kthr; 8 running
  Swp[                                                                                                  0K/4.93G] Load average: 7.72 5.87 3.04 
                                                                                                                  Uptime: 1 day, 00:40:58

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  52400 systemd-co  22   2 9780M  877M 28760 S 584.3 11.0 16:25.46 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52581 systemd-co  22   2 9780M  877M 28760 S  16.0 11.0  0:23.40 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52572 systemd-co  22   2 9780M  877M 28760 S  15.4 11.0  0:28.93 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52579 systemd-co  22   2 9780M  877M 28760 R  15.4 11.0  0:23.24 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52585 systemd-co  22   2 9780M  877M 28760 R  15.4 11.0  0:23.45 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52591 systemd-co  22   2 9780M  877M 28760 S  15.4 11.0  0:23.26 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52592 systemd-co  22   2 9780M  877M 28760 R  15.4 11.0  0:23.40 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52602 systemd-co  22   2 9780M  877M 28760 R  15.4 11.0  0:21.74 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52603 systemd-co  22   2 9780M  877M 28760 S  15.4 11.0  0:27.16 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52566 systemd-co  22   2 9780M  877M 28760 S  14.7 11.0  0:27.76 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52569 systemd-co  22   2 9780M  877M 28760 R  14.7 11.0  0:23.17 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52570 systemd-co  22   2 9780M  877M 28760 S  14.7 11.0  0:23.28 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52573 systemd-co  22   2 9780M  877M 28760 R  14.7 11.0  0:23.12 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52574 systemd-co  22   2 9780M  877M 28760 R  14.7 11.0  0:23.13 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52576 systemd-co  22   2 9780M  877M 28760 S  14.7 11.0  0:27.98 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52577 systemd-co  22   2 9780M  877M 28760 R  14.7 11.0  0:23.39 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52580 systemd-co  22   2 9780M  877M 28760 R  14.7 11.0  0:23.38 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52582 systemd-co  22   2 9780M  877M 28760 S  14.7 11.0  0:23.72 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试用户查询指定日期范围+所有状态的订单，查询条件用户ID+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByUserIdAndWithoutStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByUserIdAndWithoutStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    79.87ms   77.42ms 989.54ms   67.68%
    Req/Sec     3.84k   390.55     4.80k    72.83%
  Latency Distribution
     50%   68.61ms
     75%  118.46ms
     90%  179.32ms
     99%  340.99ms
  458724 requests in 15.04s, 227.61MB read
Requests/sec:  30509.88
Transfer/sec:     15.14MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.7%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.0%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||97.3%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||   92.1%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||95.4%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.1%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.7%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 1.59G/7.77G] Tasks: 86, 225 thr, 241 kthr; 8 running
  Swp[                                                                                                  0K/4.93G] Load average: 6.31 5.54 3.08 
                                                                                                                  Uptime: 1 day, 00:41:59

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  52400 systemd-co  22   2 9748M  877M 28760 S 709.1 11.0 17:37.44 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52569 systemd-co  22   2 9748M  877M 28760 S  20.4 11.0  0:25.00 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52573 systemd-co  22   2 9748M  877M 28760 S  19.8 11.0  0:24.91 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52594 systemd-co  22   2 9748M  877M 28760 R  19.8 11.0  0:30.53 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52566 systemd-co  22   2 9748M  877M 28760 S  19.1 11.0  0:29.56 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52602 systemd-co  22   2 9748M  877M 28760 R  19.1 11.0  0:23.63 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52568 systemd-co  22   2 9748M  877M 28760 S  18.5 11.0  0:25.17 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52576 systemd-co  22   2 9748M  877M 28760 S  18.5 11.0  0:29.79 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52581 systemd-co  22   2 9748M  877M 28760 S  18.5 11.0  0:25.19 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52587 systemd-co  22   2 9748M  877M 28760 S  18.5 11.0  0:25.02 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52589 systemd-co  22   2 9748M  877M 28760 S  18.5 11.0  0:25.12 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52591 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:25.04 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52595 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:25.12 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52598 systemd-co  22   2 9748M  877M 28760 S  18.5 11.0  0:23.44 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52603 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:28.92 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52604 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:23.57 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52571 systemd-co  22   2 9748M  877M 28760 R  17.8 11.0  0:25.05 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52572 systemd-co  22   2 9748M  877M 28760 R  17.8 11.0  0:30.71 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试用户查询指定日期范围+指定状态的订单，查询条件用户ID+订单状态+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByUserIdAndStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByUserIdAndStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    67.14ms   67.25ms 832.63ms   85.18%
    Req/Sec     4.70k   387.15     6.28k    70.92%
  Latency Distribution
     50%   49.47ms
     75%   95.82ms
     90%  148.18ms
     99%  291.67ms
  560985 requests in 15.06s, 156.67MB read
Requests/sec:  37254.22
Transfer/sec:     10.40MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||94.1%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.0%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||97.4%]   5[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.7%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||97.4%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||97.4%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.0%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.0%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 1.59G/7.77G] Tasks: 86, 225 thr, 244 kthr; 8 running
  Swp[                                                                                                  0K/4.93G] Load average: 12.04 8.18 4.46 
                                                                                                                  Uptime: 1 day, 00:44:50

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  52400 systemd-co  22   2 9748M  877M 28760 S 719.3 11.0 28:16.95 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52573 systemd-co  22   2 9748M  877M 28760 R  19.2 11.0  0:40.89 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52578 systemd-co  22   2 9748M  877M 28760 S  19.2 11.0  0:41.16 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52581 systemd-co  22   2 9748M  877M 28760 S  19.2 11.0  0:41.35 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52591 systemd-co  22   2 9748M  877M 28760 S  19.2 11.0  0:41.21 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52593 systemd-co  22   2 9748M  877M 28760 R  19.2 11.0  0:41.00 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52594 systemd-co  22   2 9748M  877M 28760 S  19.2 11.0  0:46.49 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52600 systemd-co  22   2 9748M  877M 28760 R  19.2 11.0  0:39.29 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52605 systemd-co  22   2 9748M  877M 28760 R  19.2 11.0  0:39.49 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52570 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:41.07 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52574 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:40.91 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52577 systemd-co  22   2 9748M  877M 28760 S  18.5 11.0  0:41.10 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52580 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:41.38 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52582 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:41.60 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52590 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:40.98 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52598 systemd-co  22   2 9748M  877M 28760 R  18.5 11.0  0:39.52 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52568 systemd-co  22   2 9748M  877M 28760 R  17.9 11.0  0:41.25 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52569 systemd-co  22   2 9748M  877M 28760 S  17.9 11.0  0:40.97 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试商家查询指定日期范围+所有状态的订单，查询条件商家ID+订单删除状态+日期范围+分页 ，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByMerchantIdAndWithoutStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByMerchantIdAndWithoutStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency     1.91s   720.86ms   7.61s    64.31%
    Req/Sec   129.80     73.55     0.89k    75.77%
  Latency Distribution
     50%    1.93s 
     75%    2.08s 
     90%    2.86s 
     99%    3.96s 
  14794 requests in 15.05s, 80.22MB read
Requests/sec:    982.85
Transfer/sec:      5.33MB

数据库 CPU 利用率

    0[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   4[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    1[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    3[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   7[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 1.61G/7.77G] Tasks: 86, 225 thr, 244 kthr; 8 running
  Swp[                                                                                                  0K/4.93G] Load average: 16.57 9.86 5.24 
                                                                                                                  Uptime: 1 day, 00:45:47

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  52400 systemd-co  22   2 9748M  877M 28760 S 793.4 11.0 32:13.45 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52580 systemd-co  22   2 9748M  877M 28760 S  29.0 11.0  0:47.70 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52584 systemd-co  22   2 9748M  877M 28760 R  27.1 11.0  0:56.64 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52582 systemd-co  22   2 9748M  877M 28760 R  25.1 11.0  0:47.76 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52574 systemd-co  22   2 9748M  877M 28760 R  24.4 11.0  0:46.79 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52595 systemd-co  22   2 9748M  877M 28760 R  24.4 11.0  0:47.39 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52602 systemd-co  22   2 9748M  877M 28760 R  23.8 11.0  0:45.52 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52571 systemd-co  22   2 9748M  877M 28760 R  23.1 11.0  0:46.61 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52576 systemd-co  22   2 9748M  877M 28760 S  23.1 11.0  0:51.94 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52568 systemd-co  22   2 9748M  877M 28760 R  22.4 11.0  0:47.13 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52583 systemd-co  22   2 9748M  877M 28760 R  22.4 11.0  0:46.84 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52586 systemd-co  22   2 9748M  877M 28760 R  21.8 11.0  0:51.72 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52589 systemd-co  22   2 9748M  877M 28760 R  21.8 11.0  0:46.79 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52593 systemd-co  22   2 9748M  877M 28760 R  21.8 11.0  0:46.66 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52591 systemd-co  22   2 9748M  877M 28760 R  21.1 11.0  0:46.69 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52597 systemd-co  22   2 9748M  877M 28760 R  21.1 11.0  0:45.29 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52572 systemd-co  22   2 9748M  877M 28760 R  20.5 11.0  0:52.47 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52579 systemd-co  22   2 9748M  877M 28760 R  20.5 11.0  0:47.42 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试商家查询指定日期范围+指定状态的订单，查询条件商家ID+订单状态+订单删除状态+日期范围+分页 ，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByMerchantIdAndStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByMerchantIdAndStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency     1.97s   945.21ms   8.70s    69.74%
    Req/Sec   120.08     49.75   370.00     66.33%
  Latency Distribution
     50%    2.04s 
     75%    2.27s 
     90%    3.20s 
     99%    4.61s 
  14120 requests in 15.06s, 18.37MB read
Requests/sec:    937.66
Transfer/sec:      1.22MB

数据库 CPU 利用率

    0[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    3[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   7[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 1.59G/7.77G] Tasks: 86, 226 thr, 241 kthr; 8 running
  Swp[                                                                                                  0K/4.93G] Load average: 8.79 1.95 0.99 
                                                                                                                  Uptime: 1 day, 01:27:38

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  52400 systemd-co  22   2 9748M  880M 28760 S 789.5 11.1 34:56.14 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52599 systemd-co  22   2 9748M  880M 28760 R  30.3 11.1  0:49.49 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52645 systemd-co  22   2 9748M  880M 28760 R  27.7 11.1  0:31.87 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52576 systemd-co  22   2 9748M  880M 28760 R  27.0 11.1  0:56.38 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52567 systemd-co  22   2 9748M  880M 28760 R  25.7 11.1  0:50.36 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52570 systemd-co  22   2 9748M  880M 28760 R  24.4 11.1  0:51.63 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52596 systemd-co  22   2 9748M  880M 28760 R  23.7 11.1  0:55.03 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52600 systemd-co  22   2 9748M  880M 28760 R  23.7 11.1  0:48.90 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52585 systemd-co  22   2 9748M  880M 28760 R  23.1 11.1  0:51.64 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52601 systemd-co  22   2 9748M  880M 28760 R  23.1 11.1  0:49.38 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52590 systemd-co  22   2 9748M  880M 28760 R  22.4 11.1  0:50.81 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52581 systemd-co  22   2 9748M  880M 28760 R  21.7 11.1  0:50.37 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52595 systemd-co  22   2 9748M  880M 28760 R  21.7 11.1  0:51.52 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52603 systemd-co  22   2 9748M  880M 28760 R  21.7 11.1  0:55.15 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52583 systemd-co  22   2 9748M  880M 28760 R  21.1 11.1  0:51.06 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52584 systemd-co  22   2 9748M  880M 28760 R  21.1 11.1  1:00.89 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52605 systemd-co  22   2 9748M  880M 28760 R  21.1 11.1  0:49.52 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52577 systemd-co  22   2 9748M  880M 28760 R  20.4 11.1  0:50.51 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试新增订单

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/create
Running 15s test @ http://192.168.1.185/api/v1/order/create
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   218.32ms  198.73ms   2.46s    74.97%
    Req/Sec     1.32k   255.56     1.99k    77.00%
  Latency Distribution
     50%  176.03ms
     75%  289.05ms
     90%  456.78ms
     99%  953.40ms
  157193 requests in 15.06s, 38.09MB read
Requests/sec:  10440.22
Transfer/sec:      2.53MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||          85.0%]   4[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||         85.3%]
    1[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||         85.9%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||         86.4%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||        86.5%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||         86.4%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||          84.9%]   7[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||         86.4%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||1.64G/7.77G] Tasks: 86, 243 thr, 240 kthr; 8 running
  Swp[                                                                                                  0K/4.93G] Load average: 5.21 3.09 1.55 
                                                                                                                  Uptime: 1 day, 01:29:59

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  52400 systemd-co  22   2 9852M  910M 28760 S 611.7 11.4 38:41.88 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52566 systemd-co  22   2 9852M  910M 28760 R  16.9 11.4  1:00.51 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52593 systemd-co  22   2 9852M  910M 28760 S  16.9 11.4  0:56.43 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52568 systemd-co  22   2 9852M  910M 28760 R  16.3 11.4  0:57.26 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52569 systemd-co  22   2 9852M  910M 28760 R  16.3 11.4  0:56.21 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52570 systemd-co  22   2 9852M  910M 28760 S  16.3 11.4  0:57.15 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52574 systemd-co  22   2 9852M  910M 28760 S  16.3 11.4  0:55.97 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52579 systemd-co  22   2 9852M  910M 28760 S  16.3 11.4  0:57.47 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52583 systemd-co  22   2 9852M  910M 28760 R  16.3 11.4  0:56.59 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52584 systemd-co  22   2 9852M  910M 28760 S  16.3 11.4  1:06.50 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52586 systemd-co  22   2 9852M  910M 28760 S  16.3 11.4  1:01.25 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52588 systemd-co  22   2 9852M  910M 28760 S  16.3 11.4  0:56.42 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52567 systemd-co  22   2 9852M  910M 28760 S  15.6 11.4  0:55.88 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52571 systemd-co  22   2 9852M  910M 28760 R  15.6 11.4  0:56.44 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52572 systemd-co  22   2 9852M  910M 28760 S  15.6 11.4  1:01.83 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52577 systemd-co  22   2 9852M  910M 28760 S  15.6 11.4  0:56.00 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52578 systemd-co  22   2 9852M  910M 28760 S  15.6 11.4  0:56.07 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  52580 systemd-co  22   2 9852M  910M 28760 R  15.6 11.4  0:58.17 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试 MySQL5.7

修改示例中 db/Dockerfile 基础镜像，如下：

dockerfile

FROM mysql:5.7

编译并推送镜像

bash

./build.sh && ./push.sh

重启数据库服务

bash

cd ~/deployer-mysql-benchmark/common && docker compose down -v && docker compose pull && docker compose up -d

重启各个 SpringBoot 应用

bash

cd ~/deployer-mysql-benchmark/service && docker compose pull && docker compose restart

安装 Apache Bench 工具：参考链接

初始化150万数据

bash

ab -n 1500000 -c 64 -k http://192.168.1.185/api/v1/order/create

数据初始化完毕后，分别重启各个 SpringBoot 应用以加载所有订单 ID

bash

cd ~/deployer-mysql-benchmark/service && docker compose pull && docker compose restart

测试根据订单 ID 查询

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/getById
Running 15s test @ http://192.168.1.185/api/v1/order/getById
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    72.36ms   68.06ms 784.88ms   61.40%
    Req/Sec     4.17k   293.34     5.09k    71.42%
  Latency Distribution
     50%   60.44ms
     75%  109.28ms
     90%  161.19ms
     99%  294.81ms
  498243 requests in 15.04s, 276.06MB read
Requests/sec:  33122.64
Transfer/sec:     18.35MB

数据库 CPU 利用率

    0[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||     89.3%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||      88.5%]
    1[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||       87.2%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||     89.9%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||          85.1%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||     89.3%]
    3[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||         85.2%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||      88.5%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.03G/7.77G] Tasks: 86, 245 thr, 240 kthr; 8 running
  Swp[||                                                                                              508K/4.93G] Load average: 3.53 1.26 1.57 
                                                                                                                  Uptime: 1 day, 01:53:37

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  53432 systemd-co  23   3 9661M 1291M 21488 S 638.1 16.2 18:40.17 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53620 systemd-co  23   3 9661M 1291M 21488 S  17.5 16.2  0:36.68 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53615 systemd-co  23   3 9661M 1291M 21488 R  16.8 16.2  0:25.21 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53616 systemd-co  23   3 9661M 1291M 21488 R  16.8 16.2  0:25.14 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53627 systemd-co  23   3 9661M 1291M 21488 S  16.8 16.2  0:25.95 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53638 systemd-co  23   3 9661M 1291M 21488 S  16.8 16.2  0:36.83 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53640 systemd-co  23   3 9661M 1291M 21488 S  16.8 16.2  0:25.03 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53613 systemd-co  23   3 9661M 1291M 21488 S  16.1 16.2  0:25.30 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53614 systemd-co  23   3 9661M 1291M 21488 R  16.1 16.2  0:25.31 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53617 systemd-co  23   3 9661M 1291M 21488 R  16.1 16.2  0:25.10 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53621 systemd-co  23   3 9661M 1291M 21488 S  16.1 16.2  0:33.36 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53624 systemd-co  23   3 9661M 1291M 21488 R  16.1 16.2  0:37.47 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53626 systemd-co  23   3 9661M 1291M 21488 S  16.1 16.2  0:25.21 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53628 systemd-co  23   3 9661M 1291M 21488 S  16.1 16.2  0:25.17 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53629 systemd-co  23   3 9661M 1291M 21488 R  16.1 16.2  0:25.25 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53631 systemd-co  23   3 9661M 1291M 21488 S  16.1 16.2  0:33.28 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53634 systemd-co  23   3 9661M 1291M 21488 S  16.1 16.2  0:25.25 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53635 systemd-co  23   3 9661M 1291M 21488 R  16.1 16.2  0:25.32 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试用户查询指定日期范围+所有状态的订单，查询条件用户ID+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByUserIdAndWithoutStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByUserIdAndWithoutStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    73.94ms   95.69ms   1.37s    91.52%
    Req/Sec     4.62k   619.76     5.91k    79.75%
  Latency Distribution
     50%   58.72ms
     75%   99.64ms
     90%  158.13ms
     99%  522.18ms
  551378 requests in 15.05s, 272.79MB read
Requests/sec:  36638.34
Transfer/sec:     18.13MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 94.0%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||95.9%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||97.4%]   5[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||94.7%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||   92.0%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||94.6%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.0%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 92.9%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.00G/7.77G] Tasks: 86, 245 thr, 243 kthr; 8 running
  Swp[||                                                                                              508K/4.93G] Load average: 4.18 1.93 1.78 
                                                                                                                  Uptime: 1 day, 01:54:50

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  53432 systemd-co  23   3 9661M 1291M 21488 S 702.8 16.2 20:37.19 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53640 systemd-co  23   3 9661M 1291M 21488 S  20.0 16.2  0:28.08 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53613 systemd-co  23   3 9661M 1291M 21488 S  18.6 16.2  0:28.21 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53617 systemd-co  23   3 9661M 1291M 21488 S  18.6 16.2  0:27.96 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53622 systemd-co  23   3 9661M 1291M 21488 R  18.6 16.2  0:28.04 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53626 systemd-co  23   3 9661M 1291M 21488 R  18.6 16.2  0:28.02 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53634 systemd-co  23   3 9661M 1291M 21488 R  18.6 16.2  0:28.23 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53635 systemd-co  23   3 9661M 1291M 21488 S  18.6 16.2  0:28.36 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53642 systemd-co  23   3 9661M 1291M 21488 S  18.6 16.2  0:28.17 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53644 systemd-co  23   3 9661M 1291M 21488 S  18.6 16.2  0:28.19 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53645 systemd-co  23   3 9661M 1291M 21488 R  18.6 16.2  0:28.20 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53611 systemd-co  23   3 9661M 1291M 21488 S  18.0 16.2  0:35.53 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53614 systemd-co  23   3 9661M 1291M 21488 S  18.0 16.2  0:28.09 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53615 systemd-co  23   3 9661M 1291M 21488 S  18.0 16.2  0:28.07 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53619 systemd-co  23   3 9661M 1291M 21488 R  18.0 16.2  0:28.03 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53621 systemd-co  23   3 9661M 1291M 21488 R  18.0 16.2  0:36.21 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53624 systemd-co  23   3 9661M 1291M 21488 S  18.0 16.2  0:40.38 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53632 systemd-co  23   3 9661M 1291M 21488 S  18.0 16.2  0:28.10 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试用户查询指定日期范围+指定状态的订单，查询条件用户ID+订单状态+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByUserIdAndStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByUserIdAndStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    51.49ms   49.56ms 594.48ms   58.42%
    Req/Sec     5.88k   378.77     7.27k    72.33%
  Latency Distribution
     50%   38.81ms
     75%   74.20ms
     90%  113.00ms
     99%  217.85ms
  701574 requests in 15.04s, 195.83MB read
Requests/sec:  46634.92
Transfer/sec:     13.02MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]   5[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.0%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.7%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.1%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.00G/7.77G] Tasks: 86, 245 thr, 243 kthr; 8 running
  Swp[||                                                                                              508K/4.93G] Load average: 8.46 3.58 2.36 
                                                                                                                  Uptime: 1 day, 01:56:10

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  53432 systemd-co  23   3 9661M 1291M 21488 S 727.0 16.2 24:06.55 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53644 systemd-co  23   3 9661M 1291M 21488 S  19.7 16.2  0:33.54 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53615 systemd-co  23   3 9661M 1291M 21488 S  19.1 16.2  0:33.35 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53617 systemd-co  23   3 9661M 1291M 21488 S  19.1 16.2  0:33.22 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53619 systemd-co  23   3 9661M 1291M 21488 S  19.1 16.2  0:33.28 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53626 systemd-co  23   3 9661M 1291M 21488 S  19.1 16.2  0:33.32 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53629 systemd-co  23   3 9661M 1291M 21488 S  19.1 16.2  0:33.35 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53639 systemd-co  23   3 9661M 1291M 21488 S  19.1 16.2  0:33.43 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53640 systemd-co  23   3 9661M 1291M 21488 R  19.1 16.2  0:33.33 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53641 systemd-co  23   3 9661M 1291M 21488 S  19.1 16.2  0:41.26 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53648 systemd-co  23   3 9661M 1291M 21488 R  19.1 16.2  0:44.89 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53614 systemd-co  23   3 9661M 1291M 21488 R  18.4 16.2  0:33.33 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53624 systemd-co  23   3 9661M 1291M 21488 S  18.4 16.2  0:45.56 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53628 systemd-co  23   3 9661M 1291M 21488 S  18.4 16.2  0:33.16 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53630 systemd-co  23   3 9661M 1291M 21488 S  18.4 16.2  0:33.49 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53634 systemd-co  23   3 9661M 1291M 21488 R  18.4 16.2  0:33.53 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53635 systemd-co  23   3 9661M 1291M 21488 S  18.4 16.2  0:33.62 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53638 systemd-co  23   3 9661M 1291M 21488 S  18.4 16.2  0:45.06 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试商家查询指定日期范围+所有状态的订单，查询条件商家ID+订单删除状态+日期范围+分页 ，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByMerchantIdAndWithoutStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByMerchantIdAndWithoutStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency     1.21s   458.46ms   6.11s    61.63%
    Req/Sec   202.28     73.76   560.00     74.12%
  Latency Distribution
     50%    1.22s 
     75%    1.31s 
     90%    1.84s 
     99%    2.53s 
  24001 requests in 15.06s, 129.38MB read
Requests/sec:   1593.93
Transfer/sec:      8.59MB

数据库 CPU 利用率

    0[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   4[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    1[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]   7[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.02G/7.77G] Tasks: 86, 245 thr, 243 kthr; 8 running
  Swp[||                                                                                              508K/4.93G] Load average: 12.91 5.24 2.97 
                                                                                                                  Uptime: 1 day, 01:57:06

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  53432 systemd-co  23   3 9661M 1292M 21488 S 783.5 16.3 26:33.85 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53648 systemd-co  23   3 9661M 1292M 21488 R  34.9 16.3  0:48.69 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53611 systemd-co  23   3 9661M 1292M 21488 R  33.6 16.3  0:44.59 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53637 systemd-co  23   3 9661M 1292M 21488 R  27.7 16.3  0:37.47 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53640 systemd-co  23   3 9661M 1292M 21488 R  27.7 16.3  0:36.98 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53638 systemd-co  23   3 9661M 1292M 21488 R  25.7 16.3  0:49.21 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53632 systemd-co  23   3 9661M 1292M 21488 R  24.4 16.3  0:37.25 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53613 systemd-co  23   3 9661M 1292M 21488 R  23.7 16.3  0:36.95 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53617 systemd-co  23   3 9661M 1292M 21488 R  23.7 16.3  0:37.08 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53647 systemd-co  23   3 9661M 1292M 21488 R  21.7 16.3  0:36.94 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53615 systemd-co  23   3 9661M 1292M 21488 R  21.1 16.3  0:37.18 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53624 systemd-co  23   3 9661M 1292M 21488 R  21.1 16.3  0:49.34 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53630 systemd-co  23   3 9661M 1292M 21488 S  21.1 16.3  0:37.11 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53628 systemd-co  23   3 9661M 1292M 21488 R  20.4 16.3  0:36.70 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53650 systemd-co  23   3 9661M 1292M 21488 R  20.4 16.3  0:36.95 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53646 systemd-co  23   3 9661M 1292M 21488 R  19.8 16.3  0:37.06 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53623 systemd-co  23   3 9661M 1292M 21488 R  19.1 16.3  0:37.34 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53639 systemd-co  23   3 9661M 1292M 21488 R  19.1 16.3  0:37.18 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试商家查询指定日期范围+指定状态的订单，查询条件商家ID+订单状态+订单删除状态+日期范围+分页 ，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByMerchantIdAndStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByMerchantIdAndStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency     1.27s   620.18ms   6.52s    67.05%
    Req/Sec   195.77     68.39   560.00     75.79%
  Latency Distribution
     50%    1.25s 
     75%    1.51s 
     90%    2.02s 
     99%    3.22s 
  22773 requests in 15.05s, 29.79MB read
Requests/sec:   1513.06
Transfer/sec:      1.98MB

数据库 CPU 利用率

    0[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
    1[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   5[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
    3[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.01G/7.77G] Tasks: 86, 245 thr, 240 kthr; 8 running
  Swp[||                                                                                              508K/4.93G] Load average: 13.72 6.79 3.88 
                                                                                                                  Uptime: 1 day, 02:01:04

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  53432 systemd-co  23   3 9661M 1295M 21488 S 786.1 16.3 32:41.47 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53636 systemd-co  23   3 9661M 1295M 21488 R  25.7 16.3  0:46.43 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53639 systemd-co  23   3 9661M 1295M 21488 R  25.7 16.3  0:46.32 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53644 systemd-co  23   3 9661M 1295M 21488 R  24.4 16.3  0:46.29 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53625 systemd-co  23   3 9661M 1295M 21488 R  23.1 16.3  0:46.06 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53634 systemd-co  23   3 9661M 1295M 21488 R  23.1 16.3  0:46.72 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53647 systemd-co  23   3 9661M 1295M 21488 R  23.1 16.3  0:45.99 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53630 systemd-co  23   3 9661M 1295M 21488 R  21.8 16.3  0:46.51 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53650 systemd-co  23   3 9661M 1295M 21488 R  21.8 16.3  0:46.23 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53613 systemd-co  23   3 9661M 1295M 21488 R  21.1 16.3  0:46.27 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53615 systemd-co  23   3 9661M 1295M 21488 R  21.1 16.3  0:46.31 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53643 systemd-co  23   3 9661M 1295M 21488 R  21.1 16.3  0:46.12 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53611 systemd-co  23   3 9661M 1295M 21488 R  20.5 16.3  0:54.50 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53618 systemd-co  23   3 9661M 1295M 21488 R  20.5 16.3  0:45.67 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53631 systemd-co  23   3 9661M 1295M 21488 R  20.5 16.3  0:53.84 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53635 systemd-co  23   3 9661M 1295M 21488 R  20.5 16.3  0:46.23 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53645 systemd-co  23   3 9661M 1295M 21488 R  20.5 16.3  0:46.17 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53619 systemd-co  23   3 9661M 1295M 21488 R  19.8 16.3  0:46.16 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试新增订单

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/create
Running 15s test @ http://192.168.1.185/api/v1/order/create
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   285.84ms  232.27ms   2.55s    76.14%
    Req/Sec     0.97k   197.13     1.55k    75.25%
  Latency Distribution
     50%  240.73ms
     75%  384.83ms
     90%  596.23ms
     99%    1.09s 
  115807 requests in 15.07s, 28.06MB read
Requests/sec:   7687.09
Transfer/sec:      1.86MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                              65.8%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                              66.4%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                              66.4%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                               65.5%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                              65.8%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                             67.1%]
    3[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                 63.3%]   7[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                               66.0%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.09G/7.77G] Tasks: 86, 245 thr, 240 kthr; 8 running
  Swp[||                                                                                              508K/4.93G] Load average: 8.52 7.12 4.29 
                                                                                                                  Uptime: 1 day, 02:02:35

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  53432 systemd-co  23   3 9661M 1349M 21488 S 460.5 17.0 36:38.52 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53633 systemd-co  23   3 9661M 1349M 21488 S  12.2 17.0  0:52.12 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53612 systemd-co  23   3 9661M 1349M 21488 S  11.5 17.0  0:51.30 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53613 systemd-co  23   3 9661M 1349M 21488 S  11.5 17.0  0:52.00 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53618 systemd-co  23   3 9661M 1349M 21488 S  11.5 17.0  0:51.71 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53623 systemd-co  23   3 9661M 1349M 21488 S  11.5 17.0  0:52.37 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53625 systemd-co  23   3 9661M 1349M 21488 S  11.5 17.0  0:51.92 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53626 systemd-co  23   3 9661M 1349M 21488 S  11.5 17.0  0:52.36 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53629 systemd-co  23   3 9661M 1349M 21488 S  11.5 17.0  0:51.87 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53630 systemd-co  23   3 9661M 1349M 21488 S  11.5 17.0  0:52.52 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53634 systemd-co  23   3 9661M 1349M 21488 S  11.5 17.0  0:52.45 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53611 systemd-co  23   3 9661M 1349M 21488 S  10.8 17.0  1:00.36 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53615 systemd-co  23   3 9661M 1349M 21488 S  10.8 17.0  0:52.54 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53616 systemd-co  23   3 9661M 1349M 21488 S  10.8 17.0  0:51.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53617 systemd-co  23   3 9661M 1349M 21488 S  10.8 17.0  0:51.86 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53619 systemd-co  23   3 9661M 1349M 21488 S  10.8 17.0  0:51.95 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  53622 systemd-co  23   3 9661M 1349M 21488 S  10.8 17.0  0:52.32 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试 MySQL8.0

修改示例中 db/Dockerfile 基础镜像，如下：

dockerfile

FROM mysql:8.0

编译并推送镜像

bash

./build.sh && ./push.sh

重启数据库服务

bash

cd ~/deployer-mysql-benchmark/common && docker compose down -v && docker compose pull && docker compose up -d

重启各个 SpringBoot 应用

bash

cd ~/deployer-mysql-benchmark/service && docker compose pull && docker compose restart

安装 Apache Bench 工具：参考链接

初始化150万数据

bash

ab -n 1500000 -c 64 -k http://192.168.1.185/api/v1/order/create

数据初始化完毕后，分别重启各个 SpringBoot 应用以加载所有订单 ID

bash

cd ~/deployer-mysql-benchmark/service && docker compose pull && docker compose restart

测试根据订单 ID 查询

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/getById
Running 15s test @ http://192.168.1.185/api/v1/order/getById
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    77.64ms   71.04ms 896.36ms   63.59%
    Req/Sec     3.80k   370.67     5.46k    78.17%
  Latency Distribution
     50%   66.82ms
     75%  111.93ms
     90%  167.72ms
     99%  306.49ms
  454204 requests in 15.05s, 251.67MB read
Requests/sec:  30171.83
Transfer/sec:     16.72MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||    91.2%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||    90.6%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||  92.7%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||   91.4%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||  92.1%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||  92.2%]
    3[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||     89.3%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||  92.8%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.22G/7.77G] Tasks: 86, 258 thr, 240 kthr; 8 running
  Swp[||                                                                                              764K/4.93G] Load average: 8.23 2.41 1.98 
                                                                                                                  Uptime: 1 day, 02:43:42

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  54182 systemd-co  22   2 10.1G 1490M 35816 S 667.9 18.7 23:41.60 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54463 systemd-co  22   2 10.1G 1490M 35816 S  17.9 18.7  0:32.18 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54493 systemd-co  22   2 10.1G 1490M 35816 R  17.9 18.7  0:31.95 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54469 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:32.11 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54472 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:40.64 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54474 systemd-co  22   2 10.1G 1490M 35816 S  17.3 18.7  0:32.09 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54475 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:32.11 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54477 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:32.20 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54479 systemd-co  22   2 10.1G 1490M 35816 S  17.3 18.7  0:32.21 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54485 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:32.30 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54486 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:32.16 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54487 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:44.88 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54488 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:32.03 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54489 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:32.17 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54490 systemd-co  22   2 10.1G 1490M 35816 S  17.3 18.7  0:32.14 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54492 systemd-co  22   2 10.1G 1490M 35816 S  17.3 18.7  0:40.38 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54494 systemd-co  22   2 10.1G 1490M 35816 R  17.3 18.7  0:44.31 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54498 systemd-co  22   2 10.1G 1490M 35816 S  17.3 18.7  0:32.07 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试用户查询指定日期范围+所有状态的订单，查询条件用户ID+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByUserIdAndWithoutStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByUserIdAndWithoutStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    87.66ms   80.97ms 971.16ms   61.96%
    Req/Sec     3.31k   345.04     4.22k    76.92%
  Latency Distribution
     50%   56.61ms
     75%  132.45ms
     90%  193.56ms
     99%  351.25ms
  395600 requests in 15.04s, 196.88MB read
Requests/sec:  26301.78
Transfer/sec:     13.09MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.0%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.0%]   5[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.7%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.0%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.20G/7.77G] Tasks: 86, 258 thr, 240 kthr; 8 running
  Swp[||                                                                                              764K/4.93G] Load average: 8.36 3.18 2.25 
                                                                                                                  Uptime: 1 day, 02:44:25

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  54182 systemd-co  22   2 10.1G 1491M 35816 S 732.1 18.7 25:26.73 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54474 systemd-co  22   2 10.1G 1491M 35816 R  19.8 18.7  0:34.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54497 systemd-co  22   2 10.1G 1491M 35816 R  19.8 18.7  0:34.67 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54500 systemd-co  22   2 10.1G 1491M 35816 R  19.8 18.7  0:34.74 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54462 systemd-co  22   2 10.1G 1491M 35816 R  19.1 18.7  0:43.06 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54463 systemd-co  22   2 10.1G 1491M 35816 R  19.1 18.7  0:34.72 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54469 systemd-co  22   2 10.1G 1491M 35816 R  19.1 18.7  0:34.68 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54471 systemd-co  22   2 10.1G 1491M 35816 R  19.1 18.7  0:34.81 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54472 systemd-co  22   2 10.1G 1491M 35816 R  19.1 18.7  0:43.27 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54475 systemd-co  22   2 10.1G 1491M 35816 R  19.1 18.7  0:34.84 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54477 systemd-co  22   2 10.1G 1491M 35816 S  19.1 18.7  0:34.79 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54489 systemd-co  22   2 10.1G 1491M 35816 R  19.1 18.7  0:34.82 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54490 systemd-co  22   2 10.1G 1491M 35816 R  19.1 18.7  0:34.79 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54494 systemd-co  22   2 10.1G 1491M 35816 R  19.1 18.7  0:46.96 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54466 systemd-co  22   2 10.1G 1491M 35816 R  18.5 18.7  0:34.64 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54473 systemd-co  22   2 10.1G 1491M 35816 S  18.5 18.7  0:34.78 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54481 systemd-co  22   2 10.1G 1491M 35816 R  18.5 18.7  0:47.59 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54482 systemd-co  22   2 10.1G 1491M 35816 R  18.5 18.7  0:43.08 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试用户查询指定日期范围+指定状态的订单，查询条件用户ID+订单状态+订单删除状态（固定值未删除）+日期范围+分页，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByUserIdAndStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByUserIdAndStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    66.71ms   60.76ms 867.93ms   59.72%
    Req/Sec     4.28k   360.88     5.67k    77.08%
  Latency Distribution
     50%   52.81ms
     75%   98.83ms
     90%  147.95ms
     99%  266.14ms
  510641 requests in 15.04s, 142.56MB read
Requests/sec:  33944.50
Transfer/sec:      9.48MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.4%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]   5[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.7%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    3[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.4%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.20G/7.77G] Tasks: 86, 258 thr, 242 kthr; 8 running
  Swp[||                                                                                              764K/4.93G] Load average: 18.07 9.26 4.69 
                                                                                                                  Uptime: 1 day, 02:47:19

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  54182 systemd-co  22   2 10.1G 1494M 35816 S 739.5 18.8 38:33.53 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54465 systemd-co  22   2 10.1G 1494M 35816 R  21.7 18.8  0:54.28 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54491 systemd-co  22   2 10.1G 1494M 35816 R  21.7 18.8  0:54.62 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54469 systemd-co  22   2 10.1G 1494M 35816 R  21.0 18.8  0:54.29 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54468 systemd-co  22   2 10.1G 1494M 35816 R  19.7 18.8  0:54.68 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54495 systemd-co  22   2 10.1G 1494M 35816 S  19.7 18.8  0:54.61 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54471 systemd-co  22   2 10.1G 1494M 35816 S  19.1 18.8  0:54.39 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54472 systemd-co  22   2 10.1G 1494M 35816 S  19.1 18.8  1:03.08 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54474 systemd-co  22   2 10.1G 1494M 35816 S  19.1 18.8  0:54.66 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54494 systemd-co  22   2 10.1G 1494M 35816 R  19.1 18.8  1:06.49 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54501 systemd-co  22   2 10.1G 1494M 35816 R  19.1 18.8  0:54.74 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54462 systemd-co  22   2 10.1G 1494M 35816 R  18.4 18.8  1:02.71 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54463 systemd-co  22   2 10.1G 1494M 35816 R  18.4 18.8  0:54.09 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54464 systemd-co  22   2 10.1G 1494M 35816 R  18.4 18.8  1:07.82 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54473 systemd-co  22   2 10.1G 1494M 35816 S  18.4 18.8  0:54.29 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54475 systemd-co  22   2 10.1G 1494M 35816 S  18.4 18.8  0:54.44 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54478 systemd-co  22   2 10.1G 1494M 35816 S  18.4 18.8  0:54.32 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54480 systemd-co  22   2 10.1G 1494M 35816 S  18.4 18.8  0:54.63 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试商家查询指定日期范围+所有状态的订单，查询条件商家ID+订单删除状态+日期范围+分页 ，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByMerchantIdAndWithoutStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByMerchantIdAndWithoutStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency     1.44s   514.87ms   5.17s    64.96%
    Req/Sec   169.82    107.30     1.11k    72.49%
  Latency Distribution
     50%    1.47s 
     75%    1.53s 
     90%    2.19s 
     99%    2.96s 
  19842 requests in 15.05s, 107.68MB read
Requests/sec:   1318.52
Transfer/sec:      7.16MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||95.4%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||97.4%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||96.0%]   5[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 93.3%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||95.4%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||95.4%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||97.4%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||95.4%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.22G/7.77G] Tasks: 86, 258 thr, 241 kthr; 8 running
  Swp[||                                                                                              764K/4.93G] Load average: 20.58 11.66 5.90 
                                                                                                                  Uptime: 1 day, 02:48:44

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  54182 systemd-co  22   2 10.1G 1496M 35816 S 755.2 18.8 44:04.76 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54464 systemd-co  22   2 10.1G 1496M 35816 S  21.8 18.8  1:15.90 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54475 systemd-co  22   2 10.1G 1496M 35816 R  21.8 18.8  1:02.53 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54480 systemd-co  22   2 10.1G 1496M 35816 R  21.8 18.8  1:02.66 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54462 systemd-co  22   2 10.1G 1496M 35816 S  21.1 18.8  1:10.74 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54486 systemd-co  22   2 10.1G 1496M 35816 R  21.1 18.8  1:02.62 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54492 systemd-co  22   2 10.1G 1496M 35816 R  21.1 18.8  1:11.12 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54497 systemd-co  22   2 10.1G 1496M 35816 R  20.4 18.8  1:02.47 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54466 systemd-co  22   2 10.1G 1496M 35816 R  19.8 18.8  1:03.19 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54473 systemd-co  22   2 10.1G 1496M 35816 R  19.8 18.8  1:02.56 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54482 systemd-co  22   2 10.1G 1496M 35816 R  19.8 18.8  1:10.59 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54490 systemd-co  22   2 10.1G 1496M 35816 R  19.8 18.8  1:02.33 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54496 systemd-co  22   2 10.1G 1496M 35816 R  19.8 18.8  1:03.12 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54468 systemd-co  22   2 10.1G 1496M 35816 R  19.1 18.8  1:02.78 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54470 systemd-co  22   2 10.1G 1496M 35816 R  19.1 18.8  1:02.64 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54471 systemd-co  22   2 10.1G 1496M 35816 S  19.1 18.8  1:02.65 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54476 systemd-co  22   2 10.1G 1496M 35816 R  19.1 18.8  1:02.34 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54483 systemd-co  22   2 10.1G 1496M 35816 R  19.1 18.8  1:02.22 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试商家查询指定日期范围+指定状态的订单，查询条件商家ID+订单状态+订单删除状态+日期范围+分页 ，新的订单排在最前。

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/listByMerchantIdAndStatus
Running 15s test @ http://192.168.1.185/api/v1/order/listByMerchantIdAndStatus
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency     1.33s   624.01ms   5.59s    67.21%
    Req/Sec   181.30     73.64   626.00     73.45%
  Latency Distribution
     50%    1.36s 
     75%    1.51s 
     90%    2.14s 
     99%    3.09s 
  21531 requests in 15.04s, 28.17MB read
Requests/sec:   1431.29
Transfer/sec:      1.87MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||97.4%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]
    1[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]   5[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.4%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]   7[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.20G/7.77G] Tasks: 86, 258 thr, 242 kthr; 8 running
  Swp[||                                                                                              764K/4.93G] Load average: 19.38 12.35 6.37 
                                                                                                                  Uptime: 1 day, 02:49:24

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  54182 systemd-co  22   2 10.1G 1496M 35816 S 782.2 18.8 46:22.97 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54471 systemd-co  22   2 10.1G 1496M 35816 S  27.7 18.8  1:06.04 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54494 systemd-co  22   2 10.1G 1496M 35816 R  27.0 18.8  1:18.11 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54482 systemd-co  22   2 10.1G 1496M 35816 S  21.7 18.8  1:14.30 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54483 systemd-co  22   2 10.1G 1496M 35816 R  21.7 18.8  1:05.88 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54501 systemd-co  22   2 10.1G 1496M 35816 R  21.7 18.8  1:07.12 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54463 systemd-co  22   2 10.1G 1496M 35816 R  21.1 18.8  1:05.98 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54466 systemd-co  22   2 10.1G 1496M 35816 R  21.1 18.8  1:06.58 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54462 systemd-co  22   2 10.1G 1496M 35816 R  20.4 18.8  1:14.18 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54475 systemd-co  22   2 10.1G 1496M 35816 R  20.4 18.8  1:05.91 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54481 systemd-co  22   2 10.1G 1496M 35816 R  20.4 18.8  1:18.86 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54493 systemd-co  22   2 10.1G 1496M 35816 R  20.4 18.8  1:05.34 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54496 systemd-co  22   2 10.1G 1496M 35816 R  20.4 18.8  1:06.63 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54499 systemd-co  22   2 10.1G 1496M 35816 R  20.4 18.8  1:06.05 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54469 systemd-co  22   2 10.1G 1496M 35816 R  19.8 18.8  1:06.15 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54473 systemd-co  22   2 10.1G 1496M 35816 S  19.8 18.8  1:05.97 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54474 systemd-co  22   2 10.1G 1496M 35816 R  19.8 18.8  1:06.67 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54476 systemd-co  22   2 10.1G 1496M 35816 R  19.8 18.8  1:05.75 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试新增订单

wrk 测试

bash

$ wrk -t8 -c2048 -d15s --latency --timeout 30 http://192.168.1.185/api/v1/order/create
Running 15s test @ http://192.168.1.185/api/v1/order/create
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   310.32ms  254.98ms   2.70s    75.28%
    Req/Sec     0.91k   158.99     1.83k    76.87%
  Latency Distribution
     50%  264.18ms
     75%  421.40ms
     90%  656.03ms
     99%    1.17s 
  107787 requests in 15.05s, 26.12MB read
Requests/sec:   7161.87
Transfer/sec:      1.74MB

数据库 CPU 利用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                      73.2%]   4[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                     74.8%]
    1[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                     74.5%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                     74.7%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                      73.3%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                      73.9%]
    3[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                       72.8%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                      74.0%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.27G/7.77G] Tasks: 86, 258 thr, 239 kthr; 8 running
  Swp[||                                                                                              764K/4.93G] Load average: 12.68 11.66 6.51 
                                                                                                                  Uptime: 1 day, 02:50:26

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  54182 systemd-co  22   2 10.1G 1537M 35880 S 517.1 19.3 49:25.86 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54470 systemd-co  22   2 10.1G 1537M 35880 S  13.3 19.3  1:10.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54493 systemd-co  22   2 10.1G 1537M 35880 S  13.3 19.3  1:09.61 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54498 systemd-co  22   2 10.1G 1537M 35880 S  13.3 19.3  1:10.57 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54500 systemd-co  22   2 10.1G 1537M 35880 S  13.3 19.3  1:11.09 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54464 systemd-co  22   2 10.1G 1537M 35880 R  12.7 19.3  1:23.82 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54465 systemd-co  22   2 10.1G 1537M 35880 S  12.7 19.3  1:10.76 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54466 systemd-co  22   2 10.1G 1537M 35880 S  12.7 19.3  1:10.93 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54467 systemd-co  22   2 10.1G 1537M 35880 R  12.7 19.3  1:10.32 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54468 systemd-co  22   2 10.1G 1537M 35880 R  12.7 19.3  1:11.35 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54469 systemd-co  22   2 10.1G 1537M 35880 S  12.7 19.3  1:10.70 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54471 systemd-co  22   2 10.1G 1537M 35880 S  12.7 19.3  1:10.67 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54472 systemd-co  22   2 10.1G 1537M 35880 D  12.7 19.3  1:19.17 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54475 systemd-co  22   2 10.1G 1537M 35880 R  12.7 19.3  1:10.38 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54478 systemd-co  22   2 10.1G 1537M 35880 R  12.7 19.3  1:11.10 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54480 systemd-co  22   2 10.1G 1537M 35880 S  12.7 19.3  1:10.42 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  54481 systemd-co  22   2 10.1G 1537M 35880 R  12.7 19.3  1:23.23 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

使用 Sysbench 测试

准备测试环境

VMware ESXi, 7.0.3, 20328353，Intel(R) Xeon(R) Platinum 8269CY CPU @ 2.50GHz

一个数据库实例：8C8G、CentOS8

一个 sysbench 压力机：10C10G、CentOS8

本测试使用本站示例测试

测试 MariaDB10.4

复制示例代码到数据库实例中，编辑 Dockerfile 如下：

dockerfile

FROM mariadb:10.4

编译并启动镜像

bash

docker compose down -v && docker compose build && docker compose up -d

创建数据库 sbtest

mysql

create database if not exists sbtest character set utf8mb4 collate utf8mb4_general_ci;

准备测试数据

bash

sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=192.168.1.190 --mysql-port=3306 --table-size=1500000 --threads=64 --report-interval=2 oltp_read_write prepare

运行测试

测试

bash

$ sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=192.168.1.190 --mysql-port=3306 --table-size=1500000 --threads=64 --report-interval=2 --time=30 oltp_read_write run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

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


Initializing worker threads...

Threads started!

[ 2s ] thds: 64 tps: 1753.91 qps: 35427.86 (r/w/o: 24863.00/7025.11/3539.74) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 64 tps: 1955.89 qps: 39054.80 (r/w/o: 27320.95/7822.56/3911.28) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 64 tps: 2036.77 qps: 40739.85 (r/w/o: 28515.75/8150.57/4073.54) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 64 tps: 2024.68 qps: 40480.57 (r/w/o: 28340.00/8090.71/4049.86) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 64 tps: 2032.08 qps: 40656.64 (r/w/o: 28458.65/8133.83/4064.16) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 64 tps: 2021.71 qps: 40487.74 (r/w/o: 28343.47/8101.35/4042.92) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 64 tps: 2034.88 qps: 40648.55 (r/w/o: 28449.29/8129.01/4070.26) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 64 tps: 2038.35 qps: 40735.46 (r/w/o: 28516.37/8143.39/4075.70) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 64 tps: 2042.79 qps: 40839.82 (r/w/o: 28575.57/8177.67/4086.58) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 64 tps: 2027.28 qps: 40560.70 (r/w/o: 28404.49/8101.64/4054.57) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 64 tps: 2023.67 qps: 40502.45 (r/w/o: 28349.41/8106.19/4046.84) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 64 tps: 2025.94 qps: 40527.75 (r/w/o: 28376.63/8098.74/4052.37) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 64 tps: 2078.96 qps: 41579.72 (r/w/o: 29105.95/8315.84/4157.92) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 64 tps: 2046.66 qps: 40950.70 (r/w/o: 28672.24/8185.14/4093.32) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 64 tps: 2054.20 qps: 41018.40 (r/w/o: 28696.73/8213.78/4107.89) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            846510
        write:                           241860
        other:                           120930
        total:                           1209300
    transactions:                        60465  (2013.67 per sec.)
    queries:                             1209300 (40273.32 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0255s
    total number of events:              60465

Latency (ms):
         min:                                    4.62
         avg:                                   31.77
         max:                                  130.67
         95th percentile:                       48.34
         sum:                              1920731.30

Threads fairness:
    events (avg/stddev):           944.7656/15.08
    execution time (avg/stddev):   30.0114/0.01

数据库实例 CPU 使用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                    59.9%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                      59.5%]
    1[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                       57.4%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                     60.4%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                 62.9%]   6[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                     59.7%]
    3[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                   60.9%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||                                        57.1%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||        1.97G/7.77G] Tasks: 86, 278 thr, 255 kthr; 8 running
  Swp[||                                                                                             1.41M/4.93G] Load average: 6.13 1.59 0.53 
                                                                                                                  Uptime: 1 day, 06:19:58

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  60185 systemd-co  22   2 10.9G 1096M 22144 S 429.3 13.8  2:35.01 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60438 systemd-co  22   2 10.9G 1096M 22144 S  11.5 13.8  0:08.00 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60541 systemd-co  22   2 10.9G 1096M 22144 S   7.4 13.8  0:01.83 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60602 systemd-co  22   2 10.9G 1096M 22144 S   7.4 13.8  0:01.83 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60619 systemd-co  22   2 10.9G 1096M 22144 S   7.4 13.8  0:01.81 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60535 systemd-co  22   2 10.9G 1096M 22144 R   6.7 13.8  0:01.82 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60536 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.80 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60540 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.82 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60544 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.81 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60586 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.82 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60588 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.79 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60592 systemd-co  22   2 10.9G 1096M 22144 R   6.7 13.8  0:01.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60596 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.81 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60598 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.77 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60604 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.84 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60607 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.84 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60609 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.80 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  60610 systemd-co  22   2 10.9G 1096M 22144 S   6.7 13.8  0:01.81 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试 MariaDB11.4

复制示例代码到数据库实例中，编辑 Dockerfile 如下：

dockerfile

FROM mariadb:11.4

编译并启动镜像

bash

docker compose down -v && docker compose build && docker compose up -d

创建数据库 sbtest

mysql

create database if not exists sbtest character set utf8mb4 collate utf8mb4_general_ci;

准备测试数据

bash

sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=192.168.1.190 --mysql-port=3306 --table-size=1500000 --threads=64 --report-interval=2 oltp_read_write prepare

运行测试

测试

bash

$ sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=192.168.1.190 --mysql-port=3306 --table-size=1500000 --threads=64 --report-interval=2 --time=30 oltp_read_write run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

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


Initializing worker threads...

Threads started!

[ 2s ] thds: 64 tps: 4286.22 qps: 86043.66 (r/w/o: 60277.38/17163.37/8602.92) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 64 tps: 4662.81 qps: 93320.25 (r/w/o: 65343.37/18650.25/9326.63) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 64 tps: 4987.69 qps: 99764.33 (r/w/o: 69845.18/19943.26/9975.88) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 64 tps: 4975.00 qps: 99462.59 (r/w/o: 69612.06/19903.02/9947.51) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 64 tps: 4547.20 qps: 90955.06 (r/w/o: 63669.84/18188.81/9096.41) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 64 tps: 5065.54 qps: 101327.85 (r/w/o: 70921.60/20275.17/10131.09) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 64 tps: 5028.54 qps: 100539.39 (r/w/o: 70381.62/20102.18/10055.59) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 64 tps: 5109.03 qps: 102181.08 (r/w/o: 71523.40/20438.62/10219.06) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 64 tps: 4980.87 qps: 99637.39 (r/w/o: 69751.67/19923.98/9961.74) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 64 tps: 4628.13 qps: 92590.61 (r/w/o: 64825.33/18508.52/9256.76) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 64 tps: 4995.31 qps: 99864.64 (r/w/o: 69890.30/19983.73/9990.61) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 64 tps: 5050.45 qps: 101028.07 (r/w/o: 70723.35/20203.81/10100.91) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 64 tps: 4929.58 qps: 98568.65 (r/w/o: 68998.66/19713.33/9856.67) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 64 tps: 5072.90 qps: 101482.41 (r/w/o: 71040.04/20294.08/10148.29) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 64 tps: 5187.56 qps: 103778.65 (r/w/o: 72641.31/20761.73/10375.62) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            2059190
        write:                           588340
        other:                           294170
        total:                           2941700
    transactions:                        147085 (4900.48 per sec.)
    queries:                             2941700 (98009.53 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0126s
    total number of events:              147085

Latency (ms):
         min:                                    4.00
         avg:                                   13.05
         max:                                  103.25
         95th percentile:                       17.63
         sum:                              1919969.77

Threads fairness:
    events (avg/stddev):           2298.2031/40.07
    execution time (avg/stddev):   29.9995/0.00

数据库实例 CPU 使用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]   4[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||99.3%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||98.7%]   7[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100.0%]
  Mem[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||               2.02G/7.77G] Tasks: 89, 286 thr, 261 kthr; 8 running
  Swp[||                                                                                             1.39M/4.93G] Load average: 10.87 2.48 0.88 
                                                                                                                  Uptime: 1 day, 06:47:13

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  62792 systemd-co  22   2 10.7G 1144M 28072 S 717.6 14.4  1:43.28 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63079 systemd-co  22   2 10.7G 1144M 28072 R  25.1 14.4  0:00.41 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63066 systemd-co  22   2 10.7G 1144M 28072 R  12.5 14.4  0:14.60 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63068 systemd-co  22   2 10.7G 1144M 28072 R  11.9 14.4  0:01.32 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63069 systemd-co  22   2 10.7G 1144M 28072 R  11.9 14.4  0:01.24 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63089 systemd-co  22   2 10.7G 1144M 28072 S  11.9 14.4  0:01.22 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63095 systemd-co  22   2 10.7G 1144M 28072 R  11.9 14.4  0:01.34 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63105 systemd-co  22   2 10.7G 1144M 28072 R  11.9 14.4  0:01.33 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63109 systemd-co  22   2 10.7G 1144M 28072 R  11.9 14.4  0:01.26 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63111 systemd-co  22   2 10.7G 1144M 28072 R  11.9 14.4  0:01.30 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63113 systemd-co  22   2 10.7G 1144M 28072 R  11.9 14.4  0:01.25 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63122 systemd-co  22   2 10.7G 1144M 28072 S  11.9 14.4  0:01.26 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63125 systemd-co  22   2 10.7G 1144M 28072 R  11.9 14.4  0:01.24 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63129 systemd-co  22   2 10.7G 1144M 28072 R  11.9 14.4  0:01.23 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63067 systemd-co  22   2 10.7G 1144M 28072 R  11.2 14.4  0:01.28 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63090 systemd-co  22   2 10.7G 1144M 28072 R  11.2 14.4  0:01.26 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63091 systemd-co  22   2 10.7G 1144M 28072 R  11.2 14.4  0:01.27 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  63100 systemd-co  22   2 10.7G 1144M 28072 R  11.2 14.4  0:01.22 mariadbd --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试 MySQL5.7

复制示例代码到数据库实例中，编辑 Dockerfile 如下：

dockerfile

FROM mysql:5.7

编译并启动镜像

bash

docker compose down -v && docker compose build && docker compose up -d

创建数据库 sbtest

mysql

create database if not exists sbtest character set utf8mb4 collate utf8mb4_general_ci;

准备测试数据

bash

sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=192.168.1.190 --mysql-port=3306 --table-size=1500000 --threads=64 --report-interval=2 oltp_read_write prepare

运行测试

测试

bash

$ sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=192.168.1.190 --mysql-port=3306 --table-size=1500000 --threads=64 --report-interval=2 --time=30 oltp_read_write run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

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


Initializing worker threads...

Threads started!

[ 2s ] thds: 64 tps: 2988.85 qps: 60246.50 (r/w/o: 42223.60/12013.28/6009.63) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 64 tps: 2733.05 qps: 54677.42 (r/w/o: 38283.14/10928.68/5465.59) lat (ms,95%): 40.37 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 64 tps: 3137.67 qps: 62767.89 (r/w/o: 43958.87/12533.68/6275.34) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 64 tps: 3214.11 qps: 64277.70 (r/w/o: 44959.54/12889.94/6428.22) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 64 tps: 2980.53 qps: 59576.59 (r/w/o: 41716.91/11900.62/5959.06) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 64 tps: 3312.12 qps: 66191.82 (r/w/o: 46322.62/13242.46/6626.73) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 64 tps: 3259.48 qps: 65276.14 (r/w/o: 45704.25/13053.43/6518.46) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 64 tps: 3371.01 qps: 67361.68 (r/w/o: 47130.63/13491.04/6740.02) lat (ms,95%): 29.72 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 64 tps: 3353.14 qps: 67078.71 (r/w/o: 46995.89/13374.05/6708.77) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 64 tps: 3225.50 qps: 64473.56 (r/w/o: 45110.54/12912.02/6451.01) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 64 tps: 3313.73 qps: 66291.61 (r/w/o: 46404.22/13259.92/6627.46) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 64 tps: 3411.11 qps: 68184.66 (r/w/o: 47709.52/13654.42/6820.71) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 64 tps: 3231.72 qps: 64733.86 (r/w/o: 45304.55/12964.37/6464.94) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 64 tps: 3321.68 qps: 66367.17 (r/w/o: 46475.57/13249.23/6642.37) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 64 tps: 3353.15 qps: 67152.05 (r/w/o: 47018.14/13426.61/6707.30) lat (ms,95%): 29.72 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            1350832
        write:                           385952
        other:                           192976
        total:                           1929760
    transactions:                        96488  (3214.32 per sec.)
    queries:                             1929760 (64286.40 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0166s
    total number of events:              96488

Latency (ms):
         min:                                    5.13
         avg:                                   19.90
         max:                                  251.86
         95th percentile:                       31.94
         sum:                              1919944.77

Threads fairness:
    events (avg/stddev):           1507.6250/17.80
    execution time (avg/stddev):   29.9991/0.00

数据库实例 CPU 使用率

    0[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||           84.0%]   4[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||           84.2%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||        86.1%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||           84.7%]
    2[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||            82.7%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||            83.3%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||          84.6%]   7[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||          84.6%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.03G/7.77G] Tasks: 86, 272 thr, 250 kthr; 8 running
  Swp[||                                                                                             1.39M/4.93G] Load average: 5.56 1.32 0.81 
                                                                                                                  Uptime: 1 day, 07:43:17

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  70129 systemd-co  22   2 10.0G 1132M 20048 S 621.0 14.2  1:40.91 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70292 systemd-co  22   2 10.0G 1132M 20048 S  14.0 14.2  0:02.24 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70370 systemd-co  22   2 10.0G 1132M 20048 S  10.0 14.2  0:01.27 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70371 systemd-co  22   2 10.0G 1132M 20048 R  10.0 14.2  0:01.25 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70378 systemd-co  22   2 10.0G 1132M 20048 S  10.0 14.2  0:01.25 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70398 systemd-co  22   2 10.0G 1132M 20048 R  10.0 14.2  0:01.26 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70400 systemd-co  22   2 10.0G 1132M 20048 R  10.0 14.2  0:01.21 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70404 systemd-co  22   2 10.0G 1132M 20048 S  10.0 14.2  0:01.24 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70406 systemd-co  22   2 10.0G 1132M 20048 R  10.0 14.2  0:01.21 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70413 systemd-co  22   2 10.0G 1132M 20048 R  10.0 14.2  0:01.26 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70420 systemd-co  22   2 10.0G 1132M 20048 R  10.0 14.2  0:01.23 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70425 systemd-co  22   2 10.0G 1132M 20048 R  10.0 14.2  0:01.26 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  70436 systemd-co  22   2 10.0G 1132M 20048 S  10.0 14.2  0:01.23 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试 MySQL8.0

复制示例代码到数据库实例中，编辑 Dockerfile 如下：

dockerfile

FROM mysql:8.0

编译并启动镜像

bash

docker compose down -v && docker compose build && docker compose up -d

创建数据库 sbtest

mysql

create database if not exists sbtest character set utf8mb4 collate utf8mb4_general_ci;

准备测试数据

bash

sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=192.168.1.190 --mysql-port=3306 --table-size=1500000 --threads=64 --report-interval=2 oltp_read_write prepare

运行测试

测试

bash

$ sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=192.168.1.190 --mysql-port=3306 --table-size=1500000 --threads=64 --report-interval=2 --time=30 oltp_read_write run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

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


Initializing worker threads...

Threads started!

[ 2s ] thds: 64 tps: 3394.09 qps: 68218.48 (r/w/o: 47800.99/13598.34/6819.15) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 64 tps: 3605.28 qps: 72275.10 (r/w/o: 50591.92/14472.62/7210.56) lat (ms,95%): 26.20 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 64 tps: 3589.16 qps: 71685.39 (r/w/o: 50188.36/14317.71/7179.32) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 64 tps: 3569.43 qps: 71413.17 (r/w/o: 49997.58/14278.23/7137.36) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 64 tps: 3650.85 qps: 73076.96 (r/w/o: 51130.88/14646.39/7299.70) lat (ms,95%): 26.20 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 64 tps: 3626.75 qps: 72475.51 (r/w/o: 50749.01/14470.00/7256.50) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 64 tps: 3571.99 qps: 71400.27 (r/w/o: 49961.84/14298.95/7139.48) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 64 tps: 2675.61 qps: 53604.07 (r/w/o: 37526.94/10720.91/5356.21) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 64 tps: 1714.64 qps: 34181.84 (r/w/o: 23923.49/6832.07/3426.28) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 64 tps: 1801.45 qps: 36174.18 (r/w/o: 25335.44/7232.83/3605.91) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 64 tps: 2071.05 qps: 41353.43 (r/w/o: 28977.14/8234.19/4142.09) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 64 tps: 856.84 qps: 17287.96 (r/w/o: 12044.35/3529.92/1713.69) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 64 tps: 1020.71 qps: 20389.18 (r/w/o: 14279.92/4067.83/2041.42) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 64 tps: 3501.62 qps: 69911.36 (r/w/o: 48965.15/13942.98/7003.23) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 64 tps: 3545.01 qps: 70869.73 (r/w/o: 49619.15/14160.56/7090.02) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            1182552
        write:                           337872
        other:                           168936
        total:                           1689360
    transactions:                        84468  (2813.89 per sec.)
    queries:                             1689360 (56277.78 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0160s
    total number of events:              84468

Latency (ms):
         min:                                    4.89
         avg:                                   22.73
         max:                                  324.91
         95th percentile:                       49.21
         sum:                              1920326.74

Threads fairness:
    events (avg/stddev):           1319.8125/16.24
    execution time (avg/stddev):   30.0051/0.00

数据库实例 CPU 使用率

    0[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||    90.7%]   4[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||     89.4%]
    1[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||        87.3%]   5[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||       87.2%]
    2[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||   90.7%]   6[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||        86.7%]
    3[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||          85.3%]   7[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||     90.1%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2.23G/7.77G] Tasks: 86, 282 thr, 264 kthr; 8 running
  Swp[||                                                                                             1.39M/4.93G] Load average: 16.03 5.52 2.46 
                                                                                                                  Uptime: 1 day, 07:47:49

  [Main] [I/O]
    PID USER       PRI  NI  VIRT   RES   SHR S  CPU%▽MEM%   TIME+  Command
  70868 systemd-co  22   2 10.1G 1318M 35156 S 644.4 16.6  4:22.90 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71183 systemd-co  22   2 10.1G 1318M 35156 S  10.6 16.6  0:03.59 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71223 systemd-co  22   2 10.1G 1318M 35156 S  10.6 16.6  0:03.53 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71246 systemd-co  22   2 10.1G 1318M 35156 S  10.6 16.6  0:03.59 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71181 systemd-co  22   2 10.1G 1318M 35156 S  10.0 16.6  0:03.62 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71182 systemd-co  22   2 10.1G 1318M 35156 R  10.0 16.6  0:03.48 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71185 systemd-co  22   2 10.1G 1318M 35156 S  10.0 16.6  0:03.55 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71186 systemd-co  22   2 10.1G 1318M 35156 S  10.0 16.6  0:03.60 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71189 systemd-co  22   2 10.1G 1318M 35156 S  10.0 16.6  0:03.63 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71191 systemd-co  22   2 10.1G 1318M 35156 R  10.0 16.6  0:03.64 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71213 systemd-co  22   2 10.1G 1318M 35156 R  10.0 16.6  0:03.57 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71214 systemd-co  22   2 10.1G 1318M 35156 R  10.0 16.6  0:03.57 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g
  71216 systemd-co  22   2 10.1G 1318M 35156 R  10.0 16.6  0:03.54 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --skip-character-set-client-handshake --innodb-buffer-pool-size=5g

测试不同主键类型对主键查询和插入性能的影响

测试说明

本测试使用本站示例测试

准备测试环境

使用 GCE E2 实例

一个数据库实例：8C8G、CentOS8

一个 OpenResty 反向代理实例：8C8G、CentOS8

四个 SpringBoot 应用实例：8C8G、CentOS8

一个 wrk 压力机：10C10G、CentOS8

内核参数文件描述符限制调优（在所有主机中）：参考链接

修改 application.properties 中的配置指向数据库服务

properties

spring.datasource.url=jdbc:mariadb://10.138.0.4:3306/demo?useSSL=false&allowPublicKeyRetrieval=true

修改示例中 db/Dockerfile 基础镜像，如下：

dockerfile

FROM mariadb:11.4

编译并推送镜像

bash

./build.sh && ./push.sh

准备数据库服务

复制示例中的 deployer 目录到实例中

bash

scp -r deployer root@10.138.0.xxx:~/deployer-order-management-app

启动数据库服务

bash

cd ~/deployer-order-management-app/common && docker compose down -v && docker compose pull && docker compose up -d

准备 SpringBoot 应用服务

复制示例中的 deployer 目录到实例中

bash

scp -r deployer root@10.138.0.xxx:~/deployer-order-management-app

启动 SpringBoot 服务

bash

cd ~/deployer-order-management-app/service && docker compose pull && docker compose up -d

准备 OpenResty 反向代理服务

复制代码到 OpenResty 反向代理实例中

修改 nginx.conf 指向各个 SpringBoot 应用，如下：

upstream backend {
    server 10.138.0.187:8080;
    server 10.138.0.188:8080;
    server 10.138.0.193:8080;
    server 10.138.0.194:8080;
}

启动服务
bash
```
docker compose up -d
```

使用示例中的 rest-api.http 测试接口是否正常

准备 wrk 压力机

参考链接编译 wrk

测试 bigint

修改示例中的 t_order、t_order_detail 列数据类型为 bigint，相关源代码也修改为 Long 和 long 类型。

编译并推送镜像

bash

./build.sh && ./push.sh

重启数据库服务

bash

cd ~/deployer-order-management-app/common && docker compose down -v && docker compose pull && docker compose up -d

重启各个 SpringBoot 应用

bash

cd ~/deployer-order-management-app/service && docker compose pull && docker compose restart

安装 Apache Bench 工具：参考链接

初始化5000万数据

bash

ab -n 50000 -c 64 -k http://10.138.0.185/api/v1/order/initInsertBatch

等待数据初始化完毕后，初始化id缓存辅助数据，10.138.0.187 是其中一个 SpringBoot 应用 ip 地址

bash

curl http://10.138.0.187:8080/api/v1/order/init

数据初始化完毕后，分别重启各个 SpringBoot 应用以加载订单 ID

bash

cd ~/deployer-order-management-app/service && docker compose pull && docker compose restart

测试根据订单 ID 查询

wrk 测试

bash

$ wrk -t8 -c2048 -d60s --latency --timeout 30 http://10.138.0.185/api/v1/order/getById
Running 1m test @ http://10.138.0.185/api/v1/order/getById
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    91.27ms   73.99ms 854.86ms   70.03%
    Req/Sec     3.11k   249.89     3.84k    72.17%
  Latency Distribution
     50%   80.61ms
     75%  126.23ms
     90%  189.83ms
     99%  327.69ms
  1486669 requests in 1.00m, 831.85MB read
Requests/sec:  24748.51
Transfer/sec:     13.85MB

测试新增订单

wrk 测试

bash

$ wrk -t8 -c2048 -d60s --latency --timeout 30 http://10.138.0.185/api/v1/order/create
Running 1m test @ http://10.138.0.185/api/v1/order/create
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   430.38ms  331.69ms   4.41s    79.65%
    Req/Sec   653.82    139.47     1.11k    71.74%
  Latency Distribution
     50%  357.04ms
     75%  575.35ms
     90%  856.72ms
     99%    1.58s
  312501 requests in 1.00m, 75.77MB read
Requests/sec:   5199.92
Transfer/sec:      1.26MB

测试 int

修改示例中的 t_order、t_order_detail 列数据类型为 int，相关源代码也修改为 Integer 和 int 类型。

编译并推送镜像

bash

./build.sh && ./push.sh

重启数据库服务

bash

cd ~/deployer-order-management-app/common && docker compose down -v && docker compose pull && docker compose up -d

重启各个 SpringBoot 应用

bash

cd ~/deployer-order-management-app/service && docker compose pull && docker compose restart

安装 Apache Bench 工具：参考链接

初始化5000万数据

bash

ab -n 50000 -c 64 -k http://10.138.0.185/api/v1/order/initInsertBatch

等待数据初始化完毕后，初始化id缓存辅助数据，10.138.0.187 是其中一个 SpringBoot 应用 ip 地址

bash

curl http://10.138.0.187:8080/api/v1/order/init

数据初始化完毕后，分别重启各个 SpringBoot 应用以加载订单 ID

bash

cd ~/deployer-order-management-app/service && docker compose pull && docker compose restart

测试根据订单 ID 查询

wrk 测试

bash

$ wrk -t8 -c2048 -d60s --latency --timeout 30 http://10.138.0.185/api/v1/order/getById
Running 1m test @ http://10.138.0.185/api/v1/order/getById
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    88.13ms   71.16ms 781.01ms   69.75%
    Req/Sec     3.22k   204.24     3.86k    69.88%
  Latency Distribution
     50%   78.08ms
     75%  123.13ms
     90%  181.60ms
     99%  316.64ms
  1537358 requests in 1.00m, 860.32MB read
Requests/sec:  25584.79
Transfer/sec:     14.32MB

测试新增订单

wrk 测试

bash

$ wrk -t8 -c2048 -d60s --latency --timeout 30 http://10.138.0.185/api/v1/order/create
Running 1m test @ http://10.138.0.185/api/v1/order/create
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   367.41ms  281.81ms   3.85s    79.23%
    Req/Sec   764.41    129.44     1.63k    76.75%
  Latency Distribution
     50%  313.77ms
     75%  493.70ms
     90%  733.00ms
     99%    1.34s
  365339 requests in 1.00m, 88.58MB read
Requests/sec:   6079.62
Transfer/sec:      1.47MB

测试 decimal

修改示例中的 t_order、t_order_detail 列数据类型为 decimal(20,0)，相关源代码也修改为 BigInteger 类型。

编译并推送镜像

bash

./build.sh && ./push.sh

重启数据库服务

bash

cd ~/deployer-order-management-app/common && docker compose down -v && docker compose pull && docker compose up -d

重启各个 SpringBoot 应用

bash

cd ~/deployer-order-management-app/service && docker compose pull && docker compose restart

安装 Apache Bench 工具：参考链接

初始化5000万数据

bash

ab -n 50000 -c 64 -k http://10.138.0.185/api/v1/order/initInsertBatch

等待数据初始化完毕后，初始化id缓存辅助数据，10.138.0.187 是其中一个 SpringBoot 应用 ip 地址

bash

curl http://10.138.0.187:8080/api/v1/order/init

数据初始化完毕后，分别重启各个 SpringBoot 应用以加载订单 ID

bash

cd ~/deployer-order-management-app/service && docker compose pull && docker compose restart

测试根据订单 ID 查询

wrk 测试

bash

$ wrk -t8 -c2048 -d60s --latency --timeout 30 http://10.138.0.185/api/v1/order/getById
Running 1m test @ http://10.138.0.185/api/v1/order/getById
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    78.35ms   64.25ms 737.36ms   69.26%
    Req/Sec     3.64k   302.91     4.48k    71.83%
  Latency Distribution
     50%   68.43ms
     75%  108.92ms
     90%  162.37ms
     99%  284.24ms
  1737793 requests in 1.00m, 0.99GB read
Requests/sec:  28930.54
Transfer/sec:     16.86MB

测试新增订单

wrk 测试

bash

$ wrk -t8 -c2048 -d60s --latency --timeout 30 http://10.138.0.185/api/v1/order/create
Running 1m test @ http://10.138.0.185/api/v1/order/create
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   344.80ms  268.29ms   3.42s    79.10%
    Req/Sec   818.98    178.33     1.26k    71.42%
  Latency Distribution
     50%  285.72ms
     75%  460.76ms
     90%  693.15ms
     99%    1.27s
  391416 requests in 1.00m, 94.90MB read
Requests/sec:   6514.96
Transfer/sec:      1.58MB

测试 varchar

注意：初始化数据到3000万左右时，initInsertBatch 接口经常报错导致无法很高效地继续插入数据，所以使用3000万数据测试。

修改示例中的 t_order、t_order_detail 列数据类型为 varchar(36)，相关源代码也修改为 String 类型。

编译并推送镜像

bash

./build.sh && ./push.sh

重启数据库服务

bash

cd ~/deployer-order-management-app/common && docker compose down -v && docker compose pull && docker compose up -d

重启各个 SpringBoot 应用

bash

cd ~/deployer-order-management-app/service && docker compose pull && docker compose restart

安装 Apache Bench 工具：参考链接

初始化3000万数据

bash

ab -n 30000 -c 64 -k http://10.138.0.185/api/v1/order/initInsertBatch

等待数据初始化完毕后，初始化id缓存辅助数据，10.138.0.187 是其中一个 SpringBoot 应用 ip 地址

bash

curl http://10.138.0.187:8080/api/v1/order/init

数据初始化完毕后，分别重启各个 SpringBoot 应用以加载订单 ID

bash

cd ~/deployer-order-management-app/service && docker compose pull && docker compose restart

测试根据订单 ID 查询

wrk 测试

bash

$ wrk -t8 -c2048 -d60s --latency --timeout 30 http://10.138.0.185/api/v1/order/getById
Running 1m test @ http://10.138.0.185/api/v1/order/getById
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    85.36ms   68.44ms 809.89ms   70.47%
    Req/Sec     3.31k   301.23     4.28k    71.92%
  Latency Distribution
     50%   74.62ms
     75%  117.74ms
     90%  175.12ms
     99%  304.52ms
  1582330 requests in 1.00m, 0.95GB read
Requests/sec:  26351.39
Transfer/sec:     16.26MB

测试新增订单

wrk 测试

bash

$ wrk -t8 -c2048 -d60s --latency --timeout 30 http://10.138.0.185/api/v1/order/create
Running 1m test @ http://10.138.0.185/api/v1/order/create
  8 threads and 2048 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency     2.47s     3.40s   21.22s    84.56%
    Req/Sec   528.00    231.38     0.99k    69.86%
  Latency Distribution
     50%  663.91ms
     75%    3.50s
     90%    7.88s
     99%   13.86s
  109789 requests in 1.00m, 26.61MB read
Requests/sec:   1828.52
Transfer/sec:    453.84KB

总结

插入性能：bigint 和 int 自増主键类型时的插入性能一般；decimal 主键类型的插入性能较 bigint 和 int 自増主键类型好；varchar 主键类型时的插入性能差，在数据量3000万时插入数据经常报错。
主键查询性能：bigint、int、decimal、varchar 主键类型时的查询性能相差不大。

基准测试 ​

使用 SpringBoot 应用测试 ​

业务场景设计 ​

不同版本的 MariaDB、MySQL 测试对比 ​

测试说明 ​

准备测试环境 ​

测试 MariaDB10.4 ​

测试 MariaDB11.4 ​

测试 MySQL5.7 ​

测试 MySQL8.0 ​

使用 Sysbench 测试 ​

准备测试环境 ​

测试 MariaDB10.4 ​

测试 MariaDB11.4 ​

测试 MySQL5.7 ​

测试 MySQL8.0 ​

测试不同主键类型对主键查询和插入性能的影响 ​

测试说明 ​

准备测试环境 ​

测试 bigint ​

测试 int ​

测试 decimal ​

测试 varchar ​

总结 ​

基准测试

使用 SpringBoot 应用测试

业务场景设计

不同版本的 MariaDB、MySQL 测试对比

测试说明

准备测试环境

测试 MariaDB10.4

测试 MariaDB11.4

测试 MySQL5.7

测试 MySQL8.0

使用 Sysbench 测试

准备测试环境

测试 MariaDB10.4

测试 MariaDB11.4

测试 MySQL5.7

测试 MySQL8.0

测试不同主键类型对主键查询和插入性能的影响

测试说明

准备测试环境

测试 bigint

测试 int

测试 decimal

测试 varchar

总结