mysql性能压测

软件安装

安装sysbench

yum install epel-release -y
yum install sysbench
sysbench --version

创建测试库

CREATE DATABASE `demobench`
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

创建测试表（不需要，执行准备阶段的命令即可）

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

INSERT INTO  sbtest1
(id, k, c, `pad`)
VALUES(1, 4993, '83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330', '67847967377-48000963322-62604785301-91415491898-96926520291');

sysbench执行参数示例

sysbench

参数 10个表每个表10000行压测时长6000秒（100分钟）报告周期每10秒报告1次 10个线程

准备阶段

sysbench --db-driver=mysql \
  --mysql-host=your_host \
  --mysql-port=your_port \
  --mysql-user=your_username \
  --mysql-password=your_password \
  --mysql-db=your_database \
  --tables=10 \  # Number of tables
  --table-size=10000 \  # Number of rows per table
  oltp_read_write \
  prepare

执行阶段

sysbench --db-driver=mysql \
  --mysql-host=your_host \
  --mysql-port=your_port \
  --mysql-user=your_username \
  --mysql-password=your_password \
  --mysql-db=your_database \
  --time=6000 \  # 100 minutes in seconds
  --threads=number_of_threads \
  --report-interval=10 \  # Report every 10 seconds
  --tables=10 \  # Number of tables
  --table-size=10000 \  # Number of rows per table
  oltp_read_write \
  run

清理阶段

sysbench --db-driver=mysql \
  --mysql-host=your_host \
  --mysql-port=your_port \
  --mysql-user=your_username \
  --mysql-password=your_password \
  --mysql-db=your_database \
  --tables=10 \  # Number of tables
  oltp_read_write \
  cleanup

sysbench执行参数实战

实战

准备阶段

sysbench --db-driver=mysql \
  --mysql-host=192.168.11.10 \
  --mysql-port=13306 \
  --mysql-user=root \
  --mysql-password=root \
  --mysql-db=demobench \
  --tables=10 \
  --table-size=10000 \
  oltp_read_write \
  prepare

运行阶段

sysbench --db-driver=mysql \
  --mysql-host=192.168.11.10 \
  --mysql-port=13306 \
  --mysql-user=root \
  --mysql-password=root \
  --mysql-db=demobench \
  --time=6000 \
  --threads=10 \
  --report-interval=10 \
  --tables=10 \
  --table-size=10000 \
  oltp_read_write \
  run

清理阶段

sysbench --db-driver=mysql \
  --mysql-host=192.168.11.10 \
  --mysql-port=13306 \
  --mysql-user=root \
  --mysql-password=root \
  --mysql-db=demobench \
  --tables=10 \
  oltp_read_write \
  cleanup

报告解读

tps 472 qps 9441

[ 5960s ] thds: 10 tps: 493.49 qps: 9873.72 (r/w/o: 6911.77/1974.96/986.98) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 5970s ] thds: 10 tps: 449.93 qps: 8997.46 (r/w/o: 6299.89/1797.71/899.86) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 5980s ] thds: 10 tps: 461.38 qps: 9226.08 (r/w/o: 6456.48/1846.84/922.77) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 5990s ] thds: 10 tps: 491.41 qps: 9832.79 (r/w/o: 6882.80/1967.16/982.83) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 6000s ] thds: 10 tps: 445.78 qps: 8913.09 (r/w/o: 6240.21/1781.32/891.56) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            39652886
        write:                           11329369
        other:                           5664687
        total:                           56646942
    transactions:                        2832338 (472.05 per sec.)
    queries:                             56646942 (9441.06 per sec.)
    ignored errors:                      11     (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          6000.0575s
    total number of events:              2832338

Latency (ms):
         min:                                    5.28
         avg:                                   21.18
         max:                                  231.83
         95th percentile:                       29.19
         sum:                             59992494.60

Threads fairness:
    events (avg/stddev):           283233.8000/251.50
    execution time (avg/stddev):   5999.2495/0.01

最终的tps qps 查看

transactions: 2832338 (472.05 per sec.)

queries: 56646942 (9441.06 per sec.)

压测结果对比

阿里云99计划ECS 笔记本服务器压测结果对比

项目	阿里云99计划 2c2g Ecs ESSD Entry云盘 docker版 mysql8 本机压测(带工作负载)	thinpad t420 4c8g ssd docker版 mysql8 局域网压测(带工作负载)	dell t140 12c64g hdd 宿主机版 mysql8 局域网压测(带工作负载)
tps	472	38	40
qps	9441	767	819