MySQL Bugs: #113485: Let innodb_dedicated_server set innodb_log_writer

Bug #113485	Let innodb_dedicated_server set innodb_log_writer_threads based on server size
Submitted:	21 Dec 2023 19:15	Modified:	7 Nov 8:45
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	8.0.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using innodb_log_writer_threads=ON on small servers (<= 8 cores) is bad for performance because there is too much spinning and not enough CPU cores.

For example, see https://bugs.mysql.com/bug.php?id=102238

It would help if innodb_dedicated_server=ON also set innodb_log_writer_threads based on the number of CPU cores.

How to repeat:
Run write-heavy benchmarks on a servers with <= 8 cores and innodb_log_writer_threads =ON and =OFF.

Hello Mark,

Thank you for the feature request!

Thanks,
Umesh

When I set innodb_log_writer_threads =ON for some benchmarks, the rate of fsyncs/commit increases by between 3X and 200X depending on fsync latency. And it turns out that too many fsyncs is bad for performance. The feature doesn't work for me, nor for a large web-scaler who disabled it long ago. Please make the default =OFF in all cases or fix the feature.

https://smalldatum.blogspot.com/2024/01/innodblogwriterthreads-and-insert.html

Hello Mark,

Confirmed internally from Innodb lead that this feature request has been implemented as  part of 
"WL#16396 : Change default value for innodb_log_writer_threads based on available cores"

From Changes in MySQL 9.1.0  

Functionality Added or Changed

Important Change; InnoDB: The default value of innodb_log_writer_threads is now OFF on systems with fewer than 32 logical processors. (WL #16396) , please see https://dev.mysql.com/doc/relnotes/mysql/9.1/en/news-9-1-0.html

Also https://dev.mysql.com/doc/refman/9.1/en/innodb-parameters.html#sysvar_innodb_log_writer_th...

Sincerely,
Umesh