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

Bug #114903	MySQL 8.4.0 is 10% slower for write workloads than MySQL 8.0.37
Submitted:	7 May 2024 7:22	Modified:	2 Jun 10:09
Reporter:	Przemysław Skibiński (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any

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

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

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.

Hello Przemysław,

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

Sincerely,
Umesh

Hello Przemysław,

Thank you for the report and feedback.

regards,
Umesh

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

Hello Przemysław,

Thank you, agree - observed this during the weekend with 8.4.5 build.
Closing the report for now but please feel free to open. Thank you.

Sincerely,
Umesh