Question 37: When the spin lock is spinning, people are confused

problem

When pursuing the performance of MySQL, I always hear that the parameters of spin locks should be adjusted: innodb_spin_wait_delay and innodb_sync_spin_loops, are they true?

experiment

First of all, we must know the advantages of spin locks: when the spin lock is to be locked, if you need to wait for other threads to release the lock, then:

• In the process of waiting for the lock, the first thread will spin for a period of time

  • During the spin phase, the thread will not give up the CPU

• After spinning:

  • If the lock can be acquired, the response will be faster (the spin does not have a context switch)
  • If you still need to wait for the lock, use a more costly way to wait for the lock

The innodb_spin_wait_delay parameter determines the length of the spin phase. Now we try to adjust the innodb_spin_wait_delay parameter to test:

Let me start a database first, ignore the steps here

Build a table and put some data:

Configure performance_schema:

Check the relevant parameters:

Clean up the statistics of performance_schema:

Some pressure:

Check the lock with the highest lock wait cost:

It can be seen that the lock_mutex with the highest lock waiting cost is the lock that protects the MySQL lock system

Let's adjust innodb_spin_wait_delay to make the spin time longer:

Redo the pressure (remember to clean up the statistics first) and check the statistics:

It can be seen that the average waiting time of lock_mutex has increased from 751267 to 1399041. We have increased the spin phase by a factor of 10, and the lock wait time will increase accordingly.

The current experiment seems to be as short as possible for the spin phase. Is the spin phase meaningless? of course not.

You can set innodb_spin_wait_delay to 1, and then test. As the spin phase decreases, the lock waiting time will increase (most locks use high-cost methods to wait).

So how to choose the spin parameters? We suggest "no problems and no blind adjustments".

In the previous statistics, the unit of time is cycle. According to the following conversion table, the average lock time is 1399041 cycle, which is about 0.5ms (1399041/ 2385353233 = 0.00058 s), which accounts for a very low overall SQL time. No need to adjust.

A famous CPU problem

In the spin phase, MySQL will call the PAUSE instruction of the CPU, which not only takes up the CPU, but also the PAUSE instruction (compared to other instructions that take up the CPU) is also more energy-efficient.

However, since the CPU is occupied, it will be reflected in the CPU usage rate.

Now let's zoom in and compare innodb_spin_wait_delay:

It can be seen that the CPU usage will increase as the spin phase becomes longer

Intel’s Skylake series CPUs have increased the cycle time of the PAUSE instruction. Increase from 10 cycles to 140 cycles (perhaps because making the PAUSE cycle longer is more conducive to energy saving)

Similar to today's experiment, the adjustment of this instruction is equivalent to lengthening the spin phase.

As a result, under the Skylake series CPU, MySQL's lock performance and CPU usage performance may be worse.

(Recommended article: https://tech.meituan.com/2020/04/16/intel-pause-mysql.html )

Tips

Seeing here, is everyone getting anxious and ready to change the CPU?

In fact, there is no need to be anxious. In most cases, you do not need to pursue the ultimate performance. Compared to squeezing a little bit of the server's extreme performance, the benefits of a business adjustment to SQL will be orders of magnitude larger.

As the saying goes: The business is well written, the operation and maintenance go home early.

---

What else do you want to know about the technical content of MySQL? Hurry up and leave a message to tell the editor!