Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Research: MySQL 8.4.0 is 10% slower for write workloads than MySQL 8.0.37

Description

Comparing MySQL 8.0.37 with MySQL 8.4.0 everything looks good for READ workloads but for WRITE workloads there is 10% decrease in QPS on average.
We did experiments on the same machine and environment, compiled MySQL with the same compiler and used the same MySQL parameters. Moreover 8.0.37 and 8.4.0 runs were repeated 3-5 times and interleaved. The Sysbench results look stable and reproducible for 8, 16, 32, and 64 threads:

8 THREADS, 16 THREADS, 32 THREADS, 64 THREADS DELETE node4_MS8037_300sec_16x10M-96G_DELETE_mysql-8.0.37@6dcee9fa4b1, 33507.75, 59158.90, 77000.37, 77656.50 node4_MS8037_300sec_16x10M-96G_DELETE_mysql-8.0.37@6dcee9fa4b1, 33374.01, 59270.72, 77716.59, 76216.93 node4_MS8037_300sec_16x10M-96G_DELETE_mysql-8.0.37@6dcee9fa4b1, 33441.00, 59142.12, 77122.09, 76484.70 node4_MS840_300sec_16x10M-96G_DELETE_mysql-8.4.0@dc86e412f18, 30634.69, 54067.49, 68775.27, 70518.60 node4_MS840_300sec_16x10M-96G_DELETE_mysql-8.4.0@dc86e412f18, 30937.53, 53855.19, 68928.66, 70194.53 node4_MS840_300sec_16x10M-96G_DELETE_mysql-8.4.0@dc86e412f18, 30781.75, 54099.50, 68947.93, 70747.56 node4_MS840_300sec_16x10M-96G_DELETE_mysql-8.4.0@dc86e412f18, 30527.48, 53586.95, 68456.05, 70284.11 node4_MS840_300sec_16x10M-96G_DELETE_mysql-8.4.0@dc86e412f18, 30532.78, 53680.12, 68990.03, 70793.5 INSERT node4_MS8037_300sec_16x10M-96G_INSERT_mysql-8.0.37@6dcee9fa4b1, 35959.13, 53043.26, 58869.38, 48913.10 node4_MS8037_300sec_16x10M-96G_INSERT_mysql-8.0.37@6dcee9fa4b1, 36211.54, 52905.33, 58634.94, 48912.01 node4_MS8037_300sec_16x10M-96G_INSERT_mysql-8.0.37@6dcee9fa4b1, 36156.88, 53200.00, 59062.02, 49028.97 node4_MS840_300sec_16x10M-96G_INSERT_mysql-8.4.0@dc86e412f18, 34028.75, 52157.98, 48683.64, 48621.80 node4_MS840_300sec_16x10M-96G_INSERT_mysql-8.4.0@dc86e412f18, 33936.90, 52168.50, 48825.64, 48530.86 node4_MS840_300sec_16x10M-96G_INSERT_mysql-8.4.0@dc86e412f18, 33908.46, 52049.26, 48869.09, 48622.28 node4_MS840_300sec_16x10M-96G_INSERT_mysql-8.4.0@dc86e412f18, 33953.70, 52199.43, 48738.85, 48512.68 node4_MS840_300sec_16x10M-96G_INSERT_mysql-8.4.0@dc86e412f18, 33985.22, 52329.22, 49126.98, 48746.90 UPDATE_INDEX node4_MS8037_300sec_16x10M-96G_UPDATE_INDEX_mysql-8.0.37@6dcee9fa4b1, 21949.18, 47171.67, 58359.11, 50434.87 node4_MS8037_300sec_16x10M-96G_UPDATE_INDEX_mysql-8.0.37@6dcee9fa4b1, 21741.64, 47073.61, 57857.32, 49961.32 node4_MS8037_300sec_16x10M-96G_UPDATE_INDEX_mysql-8.0.37@6dcee9fa4b1, 21878.42, 47290.07, 58533.68, 49562.03 node4_MS840_300sec_16x10M-96G_UPDATE_INDEX_mysql-8.4.0@dc86e412f18, 18011.06, 41108.61, 50750.85, 46992.09 node4_MS840_300sec_16x10M-96G_UPDATE_INDEX_mysql-8.4.0@dc86e412f18, 18145.79, 40796.80, 50091.39, 47462.09 node4_MS840_300sec_16x10M-96G_UPDATE_INDEX_mysql-8.4.0@dc86e412f18, 17807.48, 41265.61, 50831.83, 46763.57 node4_MS840_300sec_16x10M-96G_UPDATE_INDEX_mysql-8.4.0@dc86e412f18, 17678.21, 40524.55, 50503.31, 47222.73 node4_MS840_300sec_16x10M-96G_UPDATE_INDEX_mysql-8.4.0@dc86e412f18, 17975.75, 40490.31, 50630.35, 47217.74 UPDATE_NON_INDEX node4_MS8037_300sec_16x10M-96G_UPDATE_NON_INDEX_mysql-8.0.37@6dcee9fa4b1, 30843.93, 50709.81, 55397.74, 49710.12 node4_MS8037_300sec_16x10M-96G_UPDATE_NON_INDEX_mysql-8.0.37@6dcee9fa4b1, 31032.61, 50806.97, 55647.44, 49968.06 node4_MS8037_300sec_16x10M-96G_UPDATE_NON_INDEX_mysql-8.0.37@6dcee9fa4b1, 30937.15, 50797.00, 55392.56, 49880.94 node4_MS840_300sec_16x10M-96G_UPDATE_NON_INDEX_mysql-8.4.0@dc86e412f18, 28408.64, 46252.97, 47310.90, 46316.78 node4_MS840_300sec_16x10M-96G_UPDATE_NON_INDEX_mysql-8.4.0@dc86e412f18, 28471.57, 46437.13, 47177.52, 46355.68 node4_MS840_300sec_16x10M-96G_UPDATE_NON_INDEX_mysql-8.4.0@dc86e412f18, 28525.04, 46167.56, 46830.02, 46561.01 node4_MS840_300sec_16x10M-96G_UPDATE_NON_INDEX_mysql-8.4.0@dc86e412f18, 28582.02, 46241.34, 47037.46, 46272.39 node4_MS840_300sec_16x10M-96G_UPDATE_NON_INDEX_mysql-8.4.0@dc86e412f18, 28452.23, 46318.34, 46963.26, 46347.69

 

Command line options: "--innodb-buffer-pool-size=96G --sync_binlog=1024 --innodb_flush_log_at_trx_commit=0"

Config File: https://github.com/Percona-QA/perf-tests/blob/main/cnf/stable-innodb.cnf

sysbench options: --time=300 --table-size=10000000 --tables=16

HARDWARE: smblade04; 2 x Intel Xeon E5-2683 v3 @ 2.00GHz @ 2*28 threads (2*14 cores); 256 GB RAM

STORAGE: Intel Optane DC P4800X 375GB

COMPILER: gcc version 10.5.0 (Ubuntu 10.5.0-1ubuntu1~20.04)

OS: Ubuntu 20.04.1 LTS (5.4.0-42-generic)

Environment

None

Activity

Show:

Przemyslaw Skibinski October 21, 2024 at 5:14 PM
Edited

The issue was partially fixed in July with Percona Server 8.4.0-1 at https://github.com/percona/percona-server/pull/5353
Later it was also fixed in MySQL 8.4.3 and MySQL 9.1.0 at with https://github.com/mysql/mysql-server/commit/cb257cbc86e7d18

marc reilly October 15, 2024 at 4:06 PM

FYI on this change in 8.4.3, may be related.

Performance; Replication: The data structure used in tracking binary log transaction dependencies has been changed from Tree to ankerl::unordered_dense::map, which uses approximately 60% less space, and which should thus contribute to better dependency tracking performance. (Bug #tel:37008442)

https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-3.html

Jean-François Gagné August 13, 2024 at 1:25 PM

I am discovering this bug report just now.

I found a commit that causes perf regression for write workloads in MySQL 8.3.0 or newer: WL#15861: Change default binlog_transaction_dependency_tracking to WRITESET

If this change is the source of the problem, it is probably possible to show it “just” with 8.0. Benchmark could be done with 8.0 binaries, one with binlog_transaction_dependency_tracking set to COMMIT_ORDER and the other with WRITESET. If we are able to show that setting to WRITESET with 8.0 incur a performance regression, we might have a strong case for claiming that this deprecation and removal was a bad choice performance-wise (and maybe have it re-introduced).

Przemyslaw Skibinski July 9, 2024 at 2:35 PM

I found a commit that causes perf regression for write workloads in MySQL 8.3.0 or newer: https://github.com/mysql/mysql-server/commit/50e61aae9fd

Changed binlog_transaction_dependency_tracking default to WRITESTET from COMMIT_ORDER.

Moreover from Oracle:
binlog_transaction_dependency_tracking: Source of dependency information (commit timestamps or transaction write sets) from which to assess which transactions can be executed in parallel by replica's multithreaded applier. Removed in MySQL 8.4.0.

Przemyslaw Skibinski May 29, 2024 at 6:10 AM

I followed Lefred's article about changed default values of InnoDB variables in MySQL 8.4: https://lefred.be/content/mysql-8-4-lts-new-production-ready-defaults-for-innodb/
I used default values from MySQL 8.0.37 in MySQL 8.4.0 with:

--innodb_buffer_pool_instances=8 --innodb_change_buffering=all --innodb_page_cleaners=4 --innodb_parallel_read_threads=4 --innodb_read_io_threads=4 --innodb_doublewrite_files=16 --innodb_doublewrite_pages=4 --innodb_log_buffer_size=16M --innodb_use_fdatasync=OFF

It improved performance in MySQL 8.4.0 by 3.5% for write workloads on our smblade04 server.
Even with these added parameters MySQL 8.4.0 is still 7% slower on average for write workloads than MySQL 8.0.37.

Done

Details

Assignee

Reporter

Labels

Upstream Bug URL

https://bugs.mysql.com/bug.php?id=114903

Needs QA

Yes

Priority

Smart Checklist

Created May 7, 2024 at 6:49 AM
Updated October 21, 2024 at 5:23 PM
Resolved July 9, 2024 at 2:37 PM