Bug #116463 behavior related to innodb_spin_wait_delay changed in 8.0.30

• Submitted: 23 Oct 20:38
• Modified: 24 Oct 10:14
• Reporter: Mark Callaghan
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S5 (Performance)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[23 Oct 20:38] Mark Callaghan

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

[24 Oct 10:14] MySQL Verification Team

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.