Research measuring MySQL (InnoDB) lock contention with performance_schema and perf, and verifying how skipping spin-waits and tuning spin parameters affect throughput (Kono Lab, Keio University).
When an InnoDB lock is contended, it first spin-waits, then hands off to an OS-level sleep if the lock still isn't free. Spinning is efficient for brief contention but wastes CPU cycles when it drags on, while an OS sleep carries a few microseconds of wake-up latency. Intel's Skylake generation has an unusually long PAUSE instruction latency — used during the spin — at about 140 cycles, versus roughly 10 on Broadwell and 50 on Ice Lake, so at the default setting a single spin alone burns about 7,000 cycles. The goal was to quantitatively determine which locks benefit from skipping the spin phase, and what the optimal wait parameters are and why.
performance_schema was used to measure per-lock wait time and wait count, identifying which locks were the real bottlenecks. For the locks with the largest wait times, separate build variants skipped the spin phase entirely and handed off to the OS immediately, then were compared against normal spin-waiting. perf-based CPU and context-switch flamegraphs confirmed the spin-to-sleep transition at the call-stack level. The parameter controlling how many PAUSE instructions are inserted per spin was also swept across several values, compared on both a simple, contention-heavy workload and a more realistic OLTP workload.
The server ran Intel Skylake (8 cores, with its long PAUSE latency) and MySQL 8.4.9, with sysbench generating load. Two workload patterns were tested across varying thread counts: a simple, contention-heavy one, and a more realistic OLTP workload with more tables and data. Each condition was run multiple times until throughput variation converged.
Compared against the default setting, skipping the spin phase had mixed effects depending on the lock. For locks acquired extremely often but rarely actually contended, skipping the spin helped; for locks with genuine contention, it made throughput worse instead (by a few percent at most). Reducing the number of PAUSE instructions per spin improved throughput by over 10% versus the default, with latency improving too; increasing it made things worse. At high concurrency — thread counts well beyond the core count — this trend sometimes reversed.
The divergent results come down to how much genuine contention each lock actually has. For locks acquired often but rarely contended, spinning was pure waste, so skipping it helped directly. For genuinely contended locks, the coarse granularity of switching to sleep far exceeded the typical hold time, so skipping the spin backfired instead. Skylake's long per-PAUSE latency meant the default insertion count made each spin take too long; cutting it let the code detect a lock release sooner. At high thread counts well beyond the core count, though, spinning less increased cache-line contention instead, reversing the benefit. CPU profiling also showed the actual spin hotspot sometimes wasn't the lock initially targeted, meaning fixes aimed at one specific lock had limited effect on their own.
Next steps: targeting fixes at the lock actually identified as the bottleneck via profiling, repeating the experiment on CPU generations beyond Skylake, and adding a way to measure each lock's contention rate more directly.