Many InnoDB mutexes and rw-locks are reserved for a short amount of time. On a multi-core system, it is often more efficient for a thread to actively poll a mutex or rw-lock for a while before sleeping. If the mutex or rw-lock becomes available during this polling period, the thread may continue immediately, in the same time slice. Alas, if a shared object is being polled too frequently by multiple threads, it may result in “cache ping-pong”, the shipping of cache lines between processors. InnoDB tries to avoid this by making threads busy, waiting a random time between subsequent polls. The delay is implemented as a busy loop.

Starting with InnoDB storage engine 1.0.4, it is possible to control the maximum delay between sampling a mutex or rw-lock using the new parameter innodb_spin_wait_delay. In the 100 MHz Pentium era, the unit of delay used to be 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, it might be useful to 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 it may be useful to 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.