InnoDB
mutexes and
rw-locks are typically
reserved for short intervals. 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 period of time before it sleeps.
If the mutex or rw-lock becomes available during this period, the
thread can continue immediately, in the same time slice. However,
too-frequent polling of a shared object such as a mutex or rw-lock
by multiple threads can cause “cache ping pong”,
which results in processors invalidating portions of each
other's cache. InnoDB
minimizes this issue
by forcing a random delay between polls to desynchronize polling
activity. The random delay is implemented as a spin-wait loop.
The duration of a spin-wait loop is determined by the number of
PAUSE instructions that occur in the loop. That number is
generated by randomly selecting an integer ranging from 0 up to
but not including the
innodb_spin_wait_delay
value, and
multiplying that value by 50. For example, an integer is randomly
selected from the following range for an
innodb_spin_wait_delay
setting of
6:
{0,1,2,3,4,5}
The selected integer is multiplied by 50, resulting in one of six possible PAUSE instruction values:
{0,50,100,150,200,250}
For that set of values, 250 is the maximum number of PAUSE
instructions that can occur in a spin-wait loop. An
innodb_spin_wait_delay
setting of
5 results in a set of five possible values
{0,50,100,150,200}
, where 200 is the maximum
number of PAUSE instructions, and so on. In this way, the
innodb_spin_wait_delay
setting
controls the maximum delay between spin lock polls.
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.
In the 100MHz Pentium era, an
innodb_spin_wait_delay
unit was
calibrated to be equivalent to one microsecond. That time
equivalence did not hold, but PAUSE instruction duration remained
fairly constant in terms of processor cycles relative to other CPU
instructions until the introduction of the Skylake generation of
processors, which have a comparatively longer PAUSE instruction.
The
innodb_spin_wait_pause_multiplier
variable provides a way to account for differences in PAUSE
instruction duration.
The
innodb_spin_wait_pause_multiplier
variable controls the size of PAUSE instruction values. For
example, assuming an
innodb_spin_wait_delay
setting of
6, decreasing the
innodb_spin_wait_pause_multiplier
value from 50 (the default and previously hardcoded value) to 5
generates a set of smaller PAUSE instruction values:
{0,5,10,15,20,25}
The ability to increase or decrease PAUSE instruction values
permits fine tuning InnoDB
for different
processor architectures. Smaller PAUSE instruction values would be
appropriate for processor architectures with a comparatively
longer PAUSE instruction, for example.
The innodb_spin_wait_delay
and
innodb_spin_wait_pause_multiplier
variables are dynamic. They can be specified in a MySQL option
file or modified at runtime using a
SET GLOBAL
statement. Modifying the variables at runtime requires privileges
sufficient to set global system variables. See
Section 7.1.9.1, “System Variable Privileges”.