MySQL 简单性能测试记录

机器配置:

• 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

总结