Bug #114903 MySQL 8.4.0 is 10% slower for write workloads than MySQL 8.0.37

• Submitted: 7 May 7:22
• Modified: 21 Oct 17:19
• Reporter: Przemysław Skibiński (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S5 (Performance)
• Version: 8.4.0
• OS: Any
• CPU Architecture: Any

[7 May 7:22] Przemysław Skibiński

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. Please check full results at https://perconadev.atlassian.net/browse/PS-9228

How to repeat:
MySQL Config File: https://github.com/Percona-QA/perf-tests/blob/main/cnf/stable-innodb.cnf						
MySQL Command line options: "--innodb-buffer-pool-size=96G --sync_binlog=1024 --innodb_flush_log_at_trx_commit=0"						

sysbench: https://github.com/akopytov/sysbench/commits/master/
sysbench options: --time=300 --table-size=10000000 --tables=16 --threads=8/16/32/64 --mysql-ssl=DISABLED
sysbench workloads:
oltp_update_index.lua
oltp_update_non_index.lua
oltp_delete.lua
oltp_insert.lua

[8 May 5:24] MySQL Verification Team

Hello Przemysław,

Thank you for the report and feedback.
I'm still analyzing your report but in the meantime wanted to share lefred's article where he nicely explained about several InnoDB variable default values have been modified in MySQL 8.4 to match current workloads and hardware specifications - https://lefred.be/content/mysql-8-4-lts-new-production-ready-defaults-for-innodb/

Sincerely,
Umesh

[29 May 6:11] Przemysław Skibiński

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 test server.
Even with these added parameters MySQL 8.4.0 is still 7% slower on average for write workloads than MySQL 8.0.37.

[29 May 7:11] MySQL Verification Team

Hello Przemysław,

Thank you for the details,  will let you know if anything further needed. 

Sincerely,
Umesh

[5 Aug 6:56] MySQL Verification Team

Hello Przemysław,

Thank you for the report and feedback.

regards,
Umesh

[21 Oct 17:19] Przemysław Skibiński

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.0.1 at with https://github.com/mysql/mysql-server/commit/cb257cbc86e7d18