MySQL Bugs: #116463: behavior related to innodb_spin_wait

Bug #116463	behavior related to innodb_spin_wait_delay changed in 8.0.30
Submitted:	23 Oct 2024 20:38	Modified:	14 Apr 15:09
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Too much information is here:
https://smalldatum.blogspot.com/2024/10/innodb-busy-wait-loops-gone-bad-in.html

In MySQL 8.0.28 and previous releases, ut_rnd_interval returns a value between 0 and (innodb_spin_wait_delay-1) and that behavior is documented:
https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-spin_lock_polling.html

In MySQL 8.0.30+, ut::random_from_interval_fast return a value beteen 0 and innodb_spin_wait_delay.

Thus, when innodb_spin_wait_delay=6 (the default value) the average busy-wait time has increased by 1.2X assuming the busy-waiter doesn't get the lock.

How to repeat:
Read the source

Suggested fix:
Either revert to the old behavior (remove the +1) from:
https://github.com/mysql/mysql-server/blob/mysql-8.0.40/storage/innobase/include/ut0rnd.h#...

Or change the default for innodb_spin_wait_delay from 6 to 5

Hi Mr. Callaghan,

Thank you for your bug report.

We have analysed the entire matter, based on the information that you have provided us with and we agree that this is a performance degradation problem.

Verified for 8.0 and all higher supported versions.

Posted by developer:
 
Added the following note to the MySQL Server 8.0.42, 8.4.5, and 9.3.0 release notes:
	
 Changes made to innodb_spin_wait_delay in MySQL 8.0.30 negatively impacted performance.