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”, different processors invalidating portions of each others' 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 parameter
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 maximum delay.
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 command
desired maximum delay. Changing the setting requires the
For performance considerations for InnoDB locking operations, see Optimizing Locking Operations.