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”, which results in processors invalidating portions of each other's cache. InnoDB minimizes this issue by waiting a random time between subsequent polls. The delay is implemented as a busy loop.
You can control the maximum delay between testing a mutex or
rw-lock using the
variable. The duration of the delay loop depends on the C compiler
and the target processor. (In the 100MHz Pentium era, the unit of
delay was one microsecond.) 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 with multiple processor chips, the effect of cache
invalidation can be more significant and you might increase the
The default value of
6. The spin wait delay is a dynamic, global
parameter that you can specify in the MySQL option file
my.ini) or change
at runtime with the
is the desired
maximum delay. Changing the setting at runtime requires privileges
sufficient to set global system variables. See
Section 220.127.116.11, “System Variable Privileges”.
For performance considerations for InnoDB locking operations, see Section 8.11, “Optimizing Locking Operations”.