MySQL--InnoDB并发线程控制

InnoDB并发线程控制

MySQL InnoDB存储引擎提供innodb_thread_concurrency来控制进入InnoDB 存储引擎的线程数，以限制InnoDB存储引擎层的并发量。

当innodb_thread_concurrency>0时，表示开启线程数检查机制，当有新的Mysql线程调用InnoDB接口前，InnoDB会检查已经接收的请求线程数量(处于等待锁状态的线程数量不被计算在内)，如果数量超过innodb_thread_concurrency设置的限制，则会将该线程等待innodb_thread_sleep_delay微秒后，然后重新获取，如果第二次请求仍获取失败，会将该线程放入FIFO队列中休眠，在队列中等待处理。两次请求机制是为了避免线程被放到FIFO队列后又快速被取出处理，减少CPU的上下文却换的次数，降低CPU消耗。
参数innodb_thread_concurrency默认值为0,不进行限制

在MySQL 5.6.3版本后，可以通过参数innodb_adaptive_max_sleep_delay来动态调整innodb_thread_sleep_delay的值，以保障系统在低负荷或高负荷时顺利调度。
参数innodb_adaptive_max_sleep_delay默认值为150000，单位为微妙(microseconds)

当一个线程获得调用InnoDB接口权限时，会为该线程分配一个入场券(Ticket)，在入场券失效前，该进行可以随时调用InnoDB接口而无需考虑当前请求线程数量。
在MySQL 5.6.5之前，参数值innodb_concurrency_tickets默认为500
在MySQL 5.6.6之后，参数值innodb_concurrency_tickets默认为5000

 

相关参数解释：

innodb_thread_concurrency:
InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable (InnoDB uses operating system threads to process user transactions). Once the number of threads reaches this limit, additional threads are placed into a wait state within a “First In, First Out” (FIFO) queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads. 

innodb_thread_sleep_delay:
Defines how long InnoDB threads sleep before joining the InnoDB queue, in microseconds.

innodb_adaptive_max_sleep_delay:
Permits InnoDB to automatically adjust the value of innodb_thread_sleep_delay up or down according to the current workload.

innodb_concurrency_tickets：
Determines the number of threads that can enter InnoDB concurrently. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is permitted to enter InnoDB, it is given a number of “ tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB.

 

innodb_thread_concurrency参数设置建议

官方建议：
当并发用户线程数量小于64时，建议将innodb_thread_concurrency设置0。
在大部分场景下，将innodb_thread_concurrency设置小于或等于逻辑CPU核数的值能获得最佳性能。

民间建议：
对于MySQL5.5及之前版本，建议将innodb_thread_concurrency设置在8至32之间。
对于MySQL5.6版本，建议将innodb_thread_concurrency设置为32。

 

其他

对于秒杀的场景，如果大量并发请求涌入MYSQL，虽然可以通过innodb_thread_concurrency来限制进入Innodb的存储引擎的线程数量，避免Innodb存储引擎耗费大量资源去处理锁等待和线程唤醒操作，但仍会导致MYSQL层次积压大量线程，消耗大量CPU资源来处理这些线程，从而拖慢秒杀性能。