MySQL 5.7.41 vs. 8.0.33 Performance Issue on amd64 Image

jcua-helpscout commented 1 year ago

Issue Summary

When running a Sysbench test, it looks like the performance of MySQL 8.0.33 (amd64) is slower compared to MySQL 5.7.41 (amd64) when running in Mac (arm64) or in AWS EC2 Instance (amd64). I would have expected that 8.0.33 to be faster given that it is a newer version.

Environment

	Environment 1	Environment 2
Hardware	Mac Pro (M1 Max / 64 GB)	AWS EC2 (t2.2xlarge: 8 CPU / 32 GB)
OS	MacOS Monterey (12.6.5)	Ubuntu 22.04 (amd64)
Docker	20.10.24, build 297e128 (installed	24.0.1, build 6802122
Sysbench	1.0.20 (installed via brew)	1.0.20
MySQL client	8.0.32 (installed via brew)	8.0.33-0ubuntu0.22.04.2

Steps to Reproduce (applicable for both environments)

Setup of the 2 MySQL versions

$ docker run --platform=linux/amd64 --name=mysql1 -p 3309:3306/tcp -e MYSQL_ROOT_PASSWORD="test" -d mysql:5.7.41
$ docker run --platform=linux/amd64 --name=mysql2 -p 3310:3306/tcp -e MYSQL_ROOT_PASSWORD="test" -d mysql:8.0.33

$ mysql -h 127.0.0.1 -u root -ptest -P3309 -e 'create database sb1'
$ mysql -h 127.0.0.1 -u root -ptest -P3310 -e 'create database sb1'

$ sysbench oltp_read_write --table-size=100000 --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=test --mysql-port=3309 --mysql-db=sb1 prepare
$ sysbench oltp_read_write --table-size=100000 --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=test --mysql-port=3310 --mysql-db=sb1 prepare

Run the tests

$ sysbench oltp_read_write --table-size=100000 --threads=4 --max-requests=0 --time=30 --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=test --mysql-port=3309 --mysql-db=sb1 run
$ sysbench oltp_read_write --table-size=100000 --threads=4 --max-requests=0 --time=30 --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=test --mysql-port=3310 --mysql-db=sb1 run

Sysbench Results on Mac Pro

For 5.7.41 on Mac Pro:

SQL statistics:
    queries performed:
        read:                            85120
        write:                           24320
        other:                           12160
        total:                           121600
    transactions:                        6080   (202.58 per sec.)
    queries:                             121600 (4051.55 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0129s
    total number of events:              6080

Latency (ms):
         min:                                   14.70
         avg:                                   19.74
         max:                                  129.73
         95th percentile:                        0.00
         sum:                               120028.52

Threads fairness:
    events (avg/stddev):           1520.0000/1.58
    execution time (avg/stddev):   30.0071/0.00

For 8.0.33 on Mac Pro:

SQL statistics:
    queries performed:
        read:                            64848
        write:                           18528
        other:                           9264
        total:                           92640
    transactions:                        4632   (154.31 per sec.)
    queries:                             92640  (3086.13 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0179s
    total number of events:              4632

Latency (ms):
         min:                                   19.10
         avg:                                   25.92
         max:                                  149.70
         95th percentile:                        0.00
         sum:                               120045.87

Threads fairness:
    events (avg/stddev):           1158.0000/1.00
    execution time (avg/stddev):   30.0115/0.00

Sysbench Results on AWS EC2

For 5.7.41 on AWS EC2:

 SQL statistics:
    queries performed:
        read:                            149100
        write:                           42600
        other:                           21300
        total:                           213000
    transactions:                        10650  (354.86 per sec.)
    queries:                             213000 (7097.24 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0098s
    total number of events:              10650

Latency (ms):
         min:                                    5.21
         avg:                                   11.27
         max:                                  160.54
         95th percentile:                       16.71
         sum:                               119983.74

Threads fairness:
    events (avg/stddev):           2662.5000/8.79
    execution time (avg/stddev):   29.9959/0.00

For 8.0.33 on AWS EC2:

SQL statistics:
    queries performed:
        read:                            75082
        write:                           21452
        other:                           10726
        total:                           107260
    transactions:                        5363   (178.66 per sec.)
    queries:                             107260 (3573.29 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0150s
    total number of events:              5363

Latency (ms):
         min:                                    9.19
         avg:                                   22.38
         max:                                  126.07
         95th percentile:                       49.21
         sum:                               120010.00

Threads fairness:
    events (avg/stddev):           1340.7500/4.21
    execution time (avg/stddev):   30.0025/0.00

Summary of the Results

	5.7.41 (Mac Pro)	8.0.33 (Mac Pro)	5.7.41 (AWS EC2)	8.0.33 (AWS EC2)
transactions/sec	202.58/sec	154.31/sec	354.86/sec	178.66/sec
queries/sec	4051.55/sec	3086.13/sec	7097.24/sec	3573.29/sec

Expected Behavior

Based on the results, I expected that MySQL 8.0.33 should be faster given that it is a newer version, but it looks like MySQL 5.7.41 is faster. Is this a known issue?

tianon commented 1 year ago

This is a really interesting and detailed summary, but I'm afraid I'm struggling to understand what we as the image packagers could do about it? :sweat_smile:

We take the binaries published by Oracle (for both Debian and Oracle Linux-based images) and package them pretty faithfully with very minimal configuration differences from the stock packages provided by upstream. :bow:

jcua-helpscout commented 1 year ago

I see what you are saying about taking the binaries published by Oracle. So, using the same specifications as defined in my "Environment 2" above, I installed the 2 different MySQL versions natively (eg. using apt-get install), then I ran the same Sysbench test. This was the results:

	5.7.42	8.0.33 (vanilla)	8.0.33 (skip-log-bin)
transactions/sec	483.60/sec	306.87/sec	520.02/sec
queries/sec	9671.90/sec	6137.38/sec	10400.32/sec

This is what the columns represent:

5.7.42 - this version was readily available which is why I chose this. I am hoping there is not much difference between this and 5.7.41.
"8.0.33 (vanilla)" - there are no configuration changes to MySQL when the Sysbench was run.
"8.0.33 (skip-log-bin)" - I added the variable "skip-log-bin" to the MySQL configuration which disables the binary logging.

Based on these results:

The performance related to the docker image for 5.7.41 and 8.0.33 does not seem related to docker itself but to the native MySQL packages.
Adding the variable "skip-log-bin" to the MySQL configuration made the performance of MySQL 8.0.33 to be similar to MySQL 5.7.42.

So, I applied the same variable ("skip-log-bin") to the docker image for MySQL 8.0.33 running on a freshly installed AWS EC2 (t2.2xlarge) instance. The command is:

$ docker run --platform=linux/amd64 --name=mysql2 -p 3310:3306/tcp -e MYSQL_ROOT_PASSWORD="test" -d mysql:8.0.33 --skip-log-bin

Then I ran the same Sysbench against this and here's the result:

	5.7.41 (AWS EC2)	8.0.33 (AWS EC2- vanilla)	8.0.33 (skip-log-bin)
transactions/sec	354.86/sec	178.66/sec	276.67/sec
queries/sec	7097.24/sec	3573.29/sec	5533.94/sec

The two columns called "5.7.41 (AWS EC2)" and "8.0.33 (AWS EC2 - vanilla)" were from my original testing, I just added them here for easier comparison.

In the case of 8.0.33 with the skip-log-bin variable (last column), the performance is still not on par with 5.7.41. The query rate for 8.0.33 is 5533.94 queries/sec, while 5.7.41 is 7097.24 queries/sec.

If we examine the native test, 8.0.33 with the skip-log-bin variable performs significantly better, reaching 10400.32 queries/sec, which is comparable to 5.7.42 9671.90 queries/sec.

Given these results, I expected the performance of MySQL 8.0.33 with the skip-log-bin variable to have improved in a Docker environment, but it did not. Do you have some possible insights as to why this apparent performance change is not visible in a Docker environment?

robsontenorio commented 7 months ago

@jcua-helpscout

So MySQL will ways be slower on docker ? Have used volumes or bind mount ?

docker-library / mysql