Many InnoDB mutexes and rw-locks are reserved for a short time. On a multi-core system, it can be more efficient for a thread to continuously check if it can acquire a mutex or rw-lock for a while before sleeping. If the mutex or rw-lock becomes available during this polling period, the thread can continue immediately, in the same time slice. However, too-frequent polling by multiple threads of a shared object can cause “cache ping-pong”, the shipping of cache lines between processors. InnoDB minimizes this issue by waiting a random time between subsequent polls. The delay is implemented as a busy loop.

Starting with InnoDB 1.0.4, you can control the maximum delay between testing a mutex or rw-lock using the parameter innodb_spin_wait_delay. In the 100 MHz Pentium era, the unit of delay was one microsecond. The duration of the delay loop depends on the C compiler and the target processor. On a system where all processor cores share a fast cache memory, you might reduce the maximum delay or disable the busy loop altogether by setting innodb_spin_wait_delay=0. On a system that consists of multiple processor chips, the shipping of cache lines can be slower and you might increase the maximum delay.

The default value of innodb_spin_wait_delay is 6. The spin wait delay is a dynamic, global parameter that can be specified in the MySQL option file (my.cnf or my.ini) or changed at runtime with the command SET GLOBAL innodb_spin_wait_delay=delay, where delay is the desired maximum delay. Changing the setting requires the SUPER privilege.

For performance considerations for InnoDB locking operations, see Section 7.10, “Optimizing Locking Operations”.