Investigating MySQL InnoDB Spin‑Lock Parameters: innodb_spin_wait_delay and innodb_sync_spin_loops

When seeking MySQL performance improvements, many hear that tuning the spin‑lock parameters innodb_spin_wait_delay and innodb_sync_spin_loops can help; this article investigates whether that claim holds true.

Spin locks allow a thread that cannot immediately acquire a lock to spin for a short period instead of yielding the CPU. During the spin phase the thread keeps the CPU, and if it acquires the lock quickly the response is fast because no context switch occurs. If the lock is still unavailable after spinning, the thread falls back to a higher‑cost waiting method.

The innodb_spin_wait_delay variable controls the length of the spin phase. By increasing this value, the spin duration becomes longer, which can be measured with performance_schema .

The experiment proceeds as follows: a MySQL instance is created, a test table is populated, performance_schema is enabled, and its statistics are cleared. A load is then applied to generate lock contention, after which the lock with the highest wait cost (typically lock_mutex ) is identified.

When innodb_spin_wait_delay is increased tenfold, the average wait time for lock_mutex rises from 751,267 cycles to 1,399,041 cycles, confirming that a longer spin phase leads to higher lock‑wait times. Conversely, setting the delay to 1 reduces the spin phase and causes most locks to use the more expensive waiting path, again increasing overall wait time.

The takeaway is that a shorter spin phase is generally better, but eliminating spinning entirely is not advisable. The recommendation is to avoid adjusting these parameters unless a concrete problem is observed, as the lock‑wait time typically represents a tiny fraction of total SQL execution time (e.g., 0.5 ms in the test).

On CPUs such as Intel’s Skylake, the spin phase invokes the PAUSE instruction, which consumes CPU cycles but is more power‑efficient than other busy‑wait instructions. Extending the PAUSE period (e.g., from 10 to 140 cycles) raises CPU utilization, potentially worsening MySQL lock performance on such hardware.

In practice, most workloads benefit more from SQL‑level optimizations than from aggressive low‑level spin‑lock tuning; focusing on proper indexing and query design yields far greater performance gains.