MySQL 5.6 vs MySQL 5.7 Performance Comparison

A post from dbpandit

Test Hosts :
host1 - MySQL 5.6 - c4.xlarge - AWS Instance
host2 - MySQL 5.7 - c4.xlarge - AWS Instance
CPU : 4 Core
host3 with sysbench 0.4

Create prepare table in test database :
sysbench --test=oltp --db-driver=mysql --oltp-table-size=10000000 --mysql-db=test --mysql-user=app --mysql-password=password --mysql-host=10.0.0.10 prepare

ReadOnly test with 200 concurrent threads :

sysbench --db-driver=mysql --test=oltp --oltp-table-size=10000000 --mysql-host=10.0.0.10 --mysql-db=test --mysql-user=app --mysql-password=password --max-time=120 --max-requests=0 --oltp-read-only --oltp-skip-trx --oltp-nontrx-mode=select --num-threads=200 run

-- MySQL 5.6 — Results of above test --

    queries performed:
        read:                            2457378
        write:                           0
        other:                           0
        total:                           2457378
    transactions:                        175527 (1462.03 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2457378 (20468.47 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:  
    total time:                          120.0567s
    total number of events:              175527
    total time taken by event execution: 24004.7367
    per-request statistics:
         min:                                  5.45ms
         avg:                                136.76ms
         max:                                360.08ms
         approx.  95 percentile:             168.45ms

Threads fairness:  
    events (avg/stddev):           877.6350/5.27
    execution time (avg/stddev):   120.0237/0.02

--MySQL 5.7 — Results of above test --

    queries performed:
        read:                            2412172
        write:                           0
        other:                           0
        total:                           2412172
    transactions:                        172298 (1434.66 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2412172 (20085.24 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:  
    total time:                          120.0968s
    total number of events:              172298
    total time taken by event execution: 24009.7627
    per-request statistics:
         min:                                  8.34ms
         avg:                                139.35ms
         max:                                384.53ms
         approx.  95 percentile:             172.59ms

Threads fairness:  
    events (avg/stddev):           861.4900/5.05
    execution time (avg/stddev):   120.0488/0.03

ReadOnly test with 100 concurrent threads :

sysbench --db-driver=mysql --test=oltp --oltp-table-size=10000000 --mysql-host=10.0.0.10 --mysql-db=test --mysql-user=app --mysql-password=password --max-time=120 --max-requests=0 --oltp-read-only --oltp-skip-trx --oltp-nontrx-mode=select --num-threads=100 run

--MySQL 5.6 — Results of above test --

    queries performed:
        read:                            2518208
        write:                           0
        other:                           0
        total:                           2518208
    transactions:                        179872 (1498.54 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2518208 (20979.60 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:  
    total time:                          120.0313s
    total number of events:              179872
    total time taken by event execution: 12000.7090
    per-request statistics:
         min:                                  7.13ms
         avg:                                 66.72ms
         max:                                177.88ms
         approx.  95 percentile:              79.32ms

Threads fairness:  
    events (avg/stddev):           1798.7200/12.32
    execution time (avg/stddev):   120.0071/0.01

--MySQL 5.7 — Results of above test --

    queries performed:
        read:                            2516710
        write:                           0
        other:                           0
        total:                           2516710
    transactions:                        179765 (1497.40 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2516710 (20963.55 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:  
    total time:                          120.0517s
    total number of events:              179765
    total time taken by event execution: 12001.9886
    per-request statistics:
         min:                                  5.96ms
         avg:                                 66.76ms
         max:                                180.86ms
         approx.  95 percentile:              79.70ms

Threads fairness:  
    events (avg/stddev):           1797.6500/7.24
    execution time (avg/stddev):   120.0199/0.01

ReadOnly test with 50 concurrent threads :

sysbench --db-driver=mysql --test=oltp --oltp-table-size=10000000 --mysql-host=10.0.42.210 --mysql-db=test --mysql-user=app --mysql-password=password --max-time=120 --max-requests=0 --oltp-read-only --oltp-skip-trx --oltp-nontrx-mode=select --num-threads=50 run

--MySQL 5.6 — Results of above test --

    queries performed:
        read:                            2467024
        write:                           0
        other:                           0
        total:                           2467024
    transactions:                        176216 (1468.21 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2467024 (20554.99 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:  
    total time:                          120.0207s
    total number of events:              176216
    total time taken by event execution: 5999.9411
    per-request statistics:
         min:                                  4.43ms
         avg:                                 34.05ms
         max:                                240.18ms
         approx.  95 percentile:              42.69ms

Threads fairness:  
    events (avg/stddev):           3524.3200/26.53
    execution time (avg/stddev):   119.9988/0.01

--MySQL 5.7 — Results of above test --

    queries performed:
        read:                            2494926
        write:                           0
        other:                           0
        total:                           2494926
    transactions:                        178209 (1484.73 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2494926 (20786.23 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:  
    total time:                          120.0278s
    total number of events:              178209
    total time taken by event execution: 6000.1057
    per-request statistics:
         min:                                  4.79ms
         avg:                                 33.67ms
         max:                                247.08ms
         approx.  95 percentile:              43.05ms

Threads fairness:  
    events (avg/stddev):           3564.1800/24.54
    execution time (avg/stddev):   120.0021/0.01

Mixed test with 50 concurrent threads :

sysbench --db-driver=mysql --test=oltp --oltp-table-size=10000000 --mysql-host=10.0.0.10 --mysql-db=test --mysql-user=app --mysql-password=password --max-time=120 --max-requests=0 --num-threads=8 run

--MySQL 5.7 — Results of above test--

    queries performed:
        read:                            844452
        write:                           301590
        other:                           120636
        total:                           1266678
    transactions:                        60318  (502.60 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1146042 (9549.48 per sec.)
    other operations:                    120636 (1005.21 per sec.)

Test execution summary:  
    total time:                          120.0109s
    total number of events:              60318
    total time taken by event execution: 959.7289
    per-request statistics:
         min:                                  8.15ms
         avg:                                 15.91ms
         max:                                 61.71ms
         approx.  95 percentile:              23.12ms

Threads fairness:  
    events (avg/stddev):           7539.7500/18.93
    execution time (avg/stddev):   119.9661/0.01

--MySQL 5.6 — Results of above test --

    queries performed:
        read:                            977060
        write:                           348950
        other:                           139580
        total:                           1465590
    transactions:                        69790  (581.52 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1326010 (11048.92 per sec.)
    other operations:                    139580 (1163.04 per sec.)

Test execution summary:  
    total time:                          120.0127s
    total number of events:              69790
    total time taken by event execution: 959.6453
    per-request statistics:
         min:                                  6.91ms
         avg:                                 13.75ms
         max:                                 49.30ms
         approx.  95 percentile:              20.19ms

Threads fairness:  
    events (avg/stddev):           8723.7500/9.64
    execution time (avg/stddev):   119.9557/0.01

Conclusion : There is no performance improvements in MySQL 5.7 if you are running it on a low configured machine. Even as per mysql official weblinks, MySQL 5.7 performs well more than MySQL 5.6 on servers having 18 or more cores of cpu. https://www.mysql.com/why-mysql/benchmarks/

Ref Link# https://wiki.mikejung.biz/Sysbench#Sysbench0.5OLTPReadandWriteTest