MySQL 简单性能测试记录

本贴最后更新于 2336 天前,其中的信息可能已经事过境迁

机器配置:

  • CPU: Intel(R) Core(TM) i5-4590 CPU @ 3.30GHz
  • Memory: 8GB
  • MySQL: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

准备工作:

sysbench --mysql-db=db_test \
--mysql-port=3306 --db-driver=mysql --mysql-user=root \
--mysql-password=root --test=oltp --oltp-table-size=10000000 \
--num-threads=100 --max-time=60 \
--oltp-test-mode=nontrx --oltp-nontrx-mode=select \
--max-requests=0 --percentile=95 prepare

1. 读取性能测试

sysbench --mysql-db=db_test \
--mysql-port=3306 --db-driver=mysql --mysql-user=root \
--mysql-password=root --test=oltp --oltp-table-size=10000000 \
--num-threads=100 --max-time=60 \
--oltp-test-mode=nontrx --oltp-nontrx-mode=select \
--max-requests=0 --percentile=95 run

结果:

OLTP test statistics:
    queries performed:
        read:                            4248201
        write:                           0
        other:                           0
        total:                           4248201
    transactions:                        4248201 (70801.29 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 4248201 (70801.29 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          60.0017s
    total number of events:              4248201
    total time taken by event execution: 5998.0902
    per-request statistics:
         min:                                  0.03ms
         avg:                                  1.41ms
         max:                                266.43ms
         approx.  95 percentile:               2.72ms

Threads fairness:
    events (avg/stddev):           42482.0100/686.55
    execution time (avg/stddev):   59.9809/0.00

可以看到差不多 7 万的 QPS

2. 写入性能测试

sysbench --mysql-db=db_test \
--mysql-port=3306 --db-driver=mysql --mysql-user=root \
--mysql-password=root --test=oltp --oltp-table-size=10000000 \
--num-threads=100 --max-time=60 \
--oltp-test-mode=nontrx --oltp-nontrx-mode=insert \
--max-requests=0 --percentile=95 run
OLTP test statistics:
    queries performed:
        read:                            0
        write:                           49227
        other:                           0
        total:                           49227
    transactions:                        49227  (817.14 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 49227  (817.14 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          60.2432s
    total number of events:              49227
    total time taken by event execution: 6022.6811
    per-request statistics:
         min:                                 29.59ms
         avg:                                122.35ms
         max:                               1234.17ms
         approx.  95 percentile:             357.95ms

Threads fairness:
    events (avg/stddev):           492.2700/8.26
    execution time (avg/stddev):   60.2268/0.03

可以看到就只有 800 多的 QPS 了, 远远低于简单查询.

3. 更新(带索引)

sysbench --mysql-db=db_test \
--mysql-port=3306 --db-driver=mysql --mysql-user=root \
--mysql-password=root --test=oltp --oltp-table-size=10000000 \
--num-threads=100 --max-time=60 \
--oltp-test-mode=nontrx --oltp-nontrx-mode=update_key \
--max-requests=0 --percentile=95 run

结果

OLTP test statistics:
    queries performed:
        read:                            0
        write:                           21942
        other:                           0
        total:                           21942
    transactions:                        21942  (363.68 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 21942  (363.68 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          60.3338s
    total number of events:              21942
    total time taken by event execution: 6012.0994
    per-request statistics:
         min:                                 33.29ms
         avg:                                274.00ms
         max:                               2546.19ms
         approx.  95 percentile:             629.71ms

Threads fairness:
    events (avg/stddev):           219.4200/6.41
    execution time (avg/stddev):   60.1210/0.04

4. 更新(不带 key)

sysbench --mysql-db=db_test \
--mysql-port=3306 --db-driver=mysql --mysql-user=root \
--mysql-password=root --test=oltp --oltp-table-size=10000000 \
--num-threads=100 --max-time=60 \
--oltp-test-mode=nontrx --oltp-nontrx-mode=update_nokey \
--max-requests=0 --percentile=95 run

结果:

OLTP test statistics:
    queries performed:
        read:                            0
        write:                           28065
        other:                           0
        total:                           28065
    transactions:                        28065  (464.81 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 28065  (464.81 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          60.3791s
    total number of events:              28065
    total time taken by event execution: 6034.7862
    per-request statistics:
         min:                                 32.02ms
         avg:                                215.03ms
         max:                               1426.55ms
         approx.  95 percentile:             574.59ms

Threads fairness:
    events (avg/stddev):           280.6500/8.18
    execution time (avg/stddev):   60.3479/0.06

可以看到, update_nokey 比 update_key 略微高一些, 这也证明了对于更新频繁的表, 不要设置太多的索引的正确性.

4. 删除

sysbench --mysql-db=db_test \
--mysql-port=3306 --db-driver=mysql --mysql-user=root \
--mysql-password=root --test=oltp --oltp-table-size=10000000 \
--num-threads=100 --max-time=60 \
--oltp-test-mode=nontrx --oltp-nontrx-mode=delete \
--max-requests=0 --percentile=95 run

结果:

OLTP test statistics:
    queries performed:
        read:                            0
        write:                           8062
        other:                           0
        total:                           8062
    transactions:                        8062   (119.45 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 8062   (119.45 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          67.4955s
    total number of events:              8062
    total time taken by event execution: 6290.0099
    per-request statistics:
         min:                                 33.34ms
         avg:                                780.20ms
         max:                              16901.57ms
         approx.  95 percentile:            3838.98ms

Threads fairness:
    events (avg/stddev):           80.6200/12.56
    execution time (avg/stddev):   62.9001/2.30

总结

操作类型 性能(QPS)
select 70801/s
insert 817/s
update 363/s
update_nokey 464/s
delete 119/s
  • B3log

    B3log 是一个开源组织,名字来源于“Bulletin Board Blog”缩写,目标是将独立博客与论坛结合,形成一种新的网络社区体验,详细请看 B3log 构思。目前 B3log 已经开源了多款产品:SymSoloVditor思源笔记

    1083 引用 • 3461 回帖 • 257 关注
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    675 引用 • 535 回帖
  • benchmark
    2 引用 • 14 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...