MySQL spin lock

Spin (spin) is a wait operation that checks to see if a resource becomes available by nonstop testing, for a scenario that only needs to wait a short time to wait for the required resources. It is much more efficient to use spin, the idle Loop (Busy-loop), to complete the resource wait in a way that makes the thread go to sleep through context switching. However, if it is still unable to acquire resources after a short period of time, it will still be transferred to the second resource waiting mode described above.

Innodb_sync_spin_loops The parameter is the rotation number of the spin lock, which can be viewed by the show engine InnoDB status. The spin lock is a low-cost wait compared to the system wait; but it is an active wait that wastes some CPU resources. So if you see a lot of spin waits and spin rotations, it's obvious that it wastes a lot of CPU resources. This value can be used to balance wasted CPU time and meaningless context switching.

Innodb_spin_wait_delay

Vadim Tkachenko in http://www.mysqlperformanceblog.com/2011/12/02/kernel_ The parameter is tested in mutex-problem-or-double-throughput-with-single-variable/and the conclusion is given.

Peter Zaitsev in http://www.mysqlperformanceblog.com/2011/07/28/how-innodb-contention-may-manifest-itself/ The effect of this value is also explained in different situations. And explained the show InnoDB in detail in http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/. The output of the status is explained in detail.

Innodb_thread_concurrency

about the innodb_thread_concurrency parameter

Http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_concurrency

Command-Line format--innodb_thread_concurrency= #Option-file formatinnodb_thread_concurrencyoption Sets VariableYes , innodb_thread_concurrencyvariable nameinnodb_thread_concurrencyvariable scopeglobaldynamic VariableYes Permitted Valuestypenumericdefault0range0. 1000

InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit giv En by this variable. Once the number of threads reaches this limit, additional threads is placed into a wait state within a FIFO queue for EXE Cution. Threads Waiting for locks is not counted in the number of concurrently executing Threads.

The correct value for this variable are dependent on environment and workload. Try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks.

The range of this variable are 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables InnoDB to create as many threads as it needs.

The meaning of the parameter is: the number of concurrent threads inside the InnoDB.
can be dynamically modified
Specific parsing: InnoDB attempts to keep the number of threads within the InnoDB internal operating system less than the Innodb_thread_concurrency set value
If the number of InnoDB concurrent threads is approaching Innodb_thread_concurrency=x, the other InnoDB threads are set to wait, and the queue algorithm is FIFO
Number of threads waiting to be locked out, not counted into the number of concurrent threads being executed

Innodb_thread_concurrency=x,x the value to be set, depending on the server configuration and the server load situation.
The default recommended value is (number of CPUs + number of disks) X2 (I understand that the disk array for RAID10 should be the total number of disks/2)

Parameter value range 0-1000 when it is the default value, it is not said that 0 concurrent threads. Instead, it is interpreted as infinite concurrency (no concurrency check)
When innodb_thread_concurrency=0, it can be understood to disable thread concurrency checking, allowing InnoDB to create as many threads as the request requires.

http://www.dbathink.com/2012/10/trouble-shooting-the-high-sys-cpu-in-mysql-server/

MySQL spin lock