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 Plugin 1.0.4, it is possible to control the
maximum delay between sampling a mutex or rw-lock using the new
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
6. The spin wait delay is a dynamic, global
parameter that can be specified in the MySQL option file
changed at runtime with the command
desired maximum delay. Changing the setting requires the