15.8.8 Configuring Spin Lock Polling

InnoDBmutexes 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 desychronize 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. (The multiplier value, 50, is
hardcoded before MySQL 8.0.16, and configurable thereafter.) 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 was introduced in MySQL 8.0.16 to provide a way to
account for differences in PAUSE instruction duration.