一、thread_concurrency
首先，最重要的一点，这个参数已经在最新版本的MySQL中被移除了，官方MySQL 5.7版本的doc上面对thread_concurrency有这样的说明：
thread_concurrency变量是针对于Solaris 8及低版本的系统，设置了这个变量MySQL会调用thr_setconcurrency()函数。这个函数允许应用程序给同一时间运行的线程系统提示所需数量的线程。当前的Solaris版本中这个参数已经没有作用了，这个参数在MySQL 5.6.1中已经被标记为过时，在5.7.2版本的MySQL中被移除。
二、innodb_thread_concurrency参数介绍
MySQL的两种存储引擎：MyISAM和InnoDB，InnoDB支持事务，也是MySQL默认的存储引擎，在InnoDB中，我们可以通过设置参数innodb_thread_concurrency限制线程的数量。
先来看一下官方Configuring Thread Concurrency for InnoDB对innodb_thread_concurrency参数的配置说明，翻译如下：
InnoDB使用操作系统线程来处理用户的事务请求（在事务提交或回滚之前可能给InnoDB引擎带来很多的请求）。在现代化操作系统和多核处理器的服务器上，上下文切换是非常高效的，大多数工作负载运行没有任何并发线程数量的限制。在MySQL 5.5及以上版本中，MySQL做了可伸缩性的改进，它减少了在InnoDB内部限制并发执行线程数量的需要。
在有助于最小化线程之间的上下文切换的情况下，InnoDB可以使用许多技术来限制并发执行的操作系统线程的数量（以及因此在任何时间处理的请求的数量）。
InnoDB可以使用各种技术来限制操作系统并发执行线程的数量。当InnoDB从用户会话收到一个新的请求，如果线程并发执行的数量达到预定义的限制，那么新的请求会先睡眠一段时间后再次尝试重新判断是否可以进入InnoDB层。在睡眠后不能按计划执行的请求会被放入先入/先出队列，并最终处理。但那些等待获取锁的线程则不会被计入到并发执行线程的数量中。
我们可以通过设置配置参数innodb_thread_concurrency来限制同一时刻能够进入InnoDB层的会话（线程）数，一旦执行线程的数量达到这个限制，新会话（线程）将不能从MySQL层进入到InnoDB层，它们将进入一个短暂的睡眠状态，可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间。
关于InnoDB层线程等待队列的查看，可以通过innodb status观察到，如下：
|
1 2 3 4 5 6 7 8 |
-------------- ROW OPERATIONS -------------- 8 queries inside InnoDB, 934 queries in queue // 8个线程在innodb内部（说明innodb_thread_concurrency设置为8），934个线程在队列等待; 63 read views open inside InnoDB Main thread process no. 9045, id 140552469030656, state: sleeping Number of rows inserted 1405689533, updated 2174378506, deleted 1850571936, read 4780412837263 9.48 inserts/s, 29.91 updates/s, 0.00 deletes/s, 712443.02 reads/s |
在MySQL 5.6.3之前的版本中，MySQL要求通过测试和实验找到innodb_thread_sleep_delay的最优值，这个最优值可能会因工作负载情况不同而发生改变。在MySQL 5.6.3及更高版本中，你可以通过设置参数innodb_adaptive_max_sleep_delay为innodb_thread_sleep_delay设置最大允许的值，InnoDB会根据当前线程调度活动自动调整innodb_thread_sleep_delay的值，这种动态调整机制有助于工作的线程，在系统负载低时或系统接近满负荷运转时，都能够顺利的调度。
|
1 2 3 4 5 6 7 |
mysql> show global variables like '%innodb_adaptive_max%'; +---------------------------------+--------+ | Variable_name | Value | +---------------------------------+--------+ | innodb_adaptive_max_sleep_delay | 150000 | +---------------------------------+--------+ 1 row in set (0.00 sec) |
在当前最新版本的MySQL中，innodb_thread_concurrency的默认值为0，它表示默认情况下不限制线程并发执行的数量。另外，只有当并发线程数有限时，InnoDB才会导致线程休眠。当线程数没有限制时，所有线程都同等地进行调度。也就是说，如果innodb_thread_concurrency为0，则忽略innodb_thread_sleep_delay的值。
我们知道了innodb_thread_concurrency的引入主要是将高压力下线程之间抢占CPU而造成线程上下文切换的情况尽量阻塞在InnoDB层之外，这就需要innodb_thread_concurrency参数了。同时又要保证对于那些（长时间处理线程）不会长时间的堵塞（短时间处理线程），比如某些select操作需要查询很久，而某些select操作查询量很小，如果等待（长时间的select操作）结束后（短时间select操作）才执行，那么显然会出现（短时间select操作）饥饿问题，换句话说对（短时间select操作）是不公平的， 因此就引入了innodb_concurrency_tickets参数。因此InnoDB会分配指定数量的“tickets”，这些“tickets”允许以最小的开销重复调度线程。
当一个新的SQL语句开始，当前线程没有“tickets”时，它就必须遵守innodb_thread_concurrency参数设置，一旦这个线程有权进入InnoDB，它会被分配“tickets”，它可以通过这个“tickets”用于随后进入InnoDB执行行操作，如果“tickets”使用完毕，该线程将会被驱逐，并且再次观察innodb_thread_concurrency，这可能将线程放回到等待线程的先进/先出队列中。一旦这个线程再次有权进入InnoDB，“tickets”又会被重新分配，我们可以通过设置全局参数innodb_concurrency_tickets来指定“tickets”的数量，默认情况下是5000。正在等待获取锁的线程，一旦获取到锁，会被立即分配一个“tickets”。
实际上这里的“tickets”可以理解为MySQL层和InnoDB层交互的次数，比如一个select一条数据就是需要InnoDB层返回一条数据然后MySQL层进行where条件的过滤然后返回给客户端，抛开where条件过滤的情况，如果我们一条语句需要查询100条数据，那么实际上需要进入InnoDB层100次，那么实际上消耗的“tickets”就是100。当然对于insert select这种操作，需要的“tickets”是普通select的两倍，因为查询需要进入InnoDB层一次，insert需要再次进入InnoDB层一次。
这样我们也就理解为什么innodb_concurrency_tickets可以避免（长时间处理线程）长时间堵塞（短时间处理线程）的原因了。假设innodb_concurrency_tickets为5000（默认值），有一个需要查询100W行数据的大select操作和一个需要查询100行数据的小select操作，大select操作先进行，但是当查询了5000行数据后将丢失CPU使用权，小select操作将会进行并且一次性完成。
这些参数的正确值取决于当前系统环境和负载情况。尝试各种不同的值，以确定哪些值适用于当前应用程序。在限制并发执行的线程数之前，在多核及多处理器的计算机上，检查一下InnoDB的配置参数是否可以改善性能，比如innodb_adaptive_hash_index。我们线上并没有设置这些参数，因为感觉很难设置合适，如果设置不当反而会遇到问题。
三、innodb_thread_concurrency&innodb_thread_sleep_delay&innodb_concurrency_tickets
这三个参数的配合使用就是这样的一个故事（看网上一个哥们写的，摘抄下来）
一个屋子内有一个头牌妓女叫Innodb，大家都想接近她。
老鸨（MySQL）不可能允许那么多人同时进屋去，就限制每次只能进去几个（上下和手嘛），这个限制的名字就叫（innodb_thread_concurrency）。
其他的人怎么办，只能在外面排成长队依次进入，同时老鸨说，大爷你们可以睡一会，这样就不用苦苦等待。
这里老鸨就会个一段时间（innodb_thread_sleep_delay）叫醒一位大爷，以免睡不醒了。
老鸨也怕总是叫醒大爷不好交代，就看快到了再叫，老鸨自己发明了一个自适应的叫醒算法，能够尽量减少唤醒次数。
但是大爷会规定一个最长唤醒时间，就是必须在这样的时间（innodb_adaptive_max_sleep_delay）时唤醒我。
如此，当有人从内部出来以后，等待的大爷（排在最前面的）就可以进入享受鱼水之欢了。
但是每位大爷能够支持的时间不一样，有的几秒（速SQL），有的大爷需要几分钟（慢SQL）。这样外面等待的大爷就会有意见，哎呀，怎么还不出来。
老鸨又想了一个办法，规定每个人不能在姑娘房里呆10分钟以上（innodb_concurrency_tickets），有特别持久的人就需要在10分钟时出来，然后继续排队（排在队尾）。等到下一次轮到他再进行鱼水之欢。
人物对应：老鸨（MySQL），大爷（threads），姑娘（innodb）
如何优化innodb_concurrency_tickets，那就得看哪位大爷重要，比如宰相的儿子在这里等，而宰相的儿子又十分持久，最好就用多点时间（增大innodb_concurrency_tickets）。如果宰相的儿子不持久，那就用小时间快点排到他。
四、innodb_thread_concurrency使用建议
在官方文档上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
- 如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
- 如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置innodb_thread_concurrency参数为80，以避免影响性能。
- 如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），建议通过设置innodb_thread_concurrency参数为这个值（也可能更低，这取决于性能体现），如果你的目标是将MySQL与其他应用隔离，你可以考虑绑定mysqld进程到专有的虚拟CPU。但是需要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置mysqld进程绑定的虚拟CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。可以发现要合理的设置这个值并不那么容易并且要求较高。
五、如何观察
实际上如果是处于这种堵塞情况，我们完全可以在information_schema.innodb_trx和show engine innodb status中看到如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
---TRANSACTION 162307, ACTIVE 133 sec sleeping before entering InnoDB （这里） mysql tables in use 2, locked 2 767 lock struct(s), heap size 106968, 212591 row lock(s), undo log entries 15451 MySQL thread id 14, OS thread handle 140736751912704, query id 1077 localhost root Sending data insert into testui select * from testui ---TRANSACTION 162302, ACTIVE 320 sec, thread declared inside InnoDB 1 mysql tables in use 2, locked 2 2477 lock struct(s), heap size 336344, 609049 row lock(s), undo log entries 83582 MySQL thread id 13, OS thread handle 140737153779456, query id 1050 localhost root Sending data insert into testti3 select * from testti3 mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G *************************** 1. row *************************** trx_id: 84325 trx_state: RUNNING trx_query: insert into baguait4 select * from testgp trx_operation_state: sleeping before entering InnoDB（这里） trx_concurrency_tickets: 0 *************************** 2. row *************************** trx_id: 84319 trx_state: RUNNING trx_query: insert into baguait3 select * from testgp trx_operation_state: sleeping before entering InnoDB trx_concurrency_tickets: 0 |
我们可以看到事务操作状态被标记为‘sleeping before entering InnoDB’。但是需要注意一点的是对于只读事务比如select操作而言，show engine innodb status可能看不到。
这里我们简单模拟，我们一共启用3个事务，其中两个insert select操作，一个单纯的select操作，当然这里的都是耗时操作，涉及的表每个表都有大概100W的数据。
同时为了方便观察我们需要设置参数：
- innodb_thread_concurrency=1
- innodb_concurrency_tickets=10
操作步骤如下：
| S1 | S2 | S3 |
|---|---|---|
| insert into baguait4 select * from testgp | ||
| insert into baguait3 select * from testgp | ||
| select * from baguait1 |
如果多观察几次你可以看到如下的现象：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G show processlist; *************************** 1. row *************************** trx_id: 84529 trx_state: RUNNING trx_query: insert into baguait4 select * from testgp trx_operation_state: sleeping before entering InnoDB trx_concurrency_tickets: 0 *************************** 2. row *************************** trx_id: 84524 trx_state: RUNNING trx_query: insert into baguait3 select * from testgp trx_operation_state: inserting trx_concurrency_tickets: 1 *************************** 3. row *************************** trx_id: 422211785606640 trx_state: RUNNING trx_query: select * from baguait1 trx_operation_state: sleeping before entering InnoDB trx_concurrency_tickets: 0 3 rows in set (0.00 sec) +----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 3173 | Waiting on empty queue | NULL | 0 | 0 | | 6 | root | localhost | testmts | Query | 70 | Sending data | insert into baguait3 select * from testgp | 0 | 0 | | 7 | root | localhost | testmts | Query | 68 | Sending data | insert into baguait4 select * from testgp | 0 | 0 | | 8 | root | localhost | testmts | Query | 66 | Sending data | select * from baguait1 | 120835 | 0 | | 9 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 | +----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+ 5 rows in set (0.00 sec) mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G show processlist; *************************** 1. row *************************** trx_id: 84529 trx_state: RUNNING trx_query: insert into baguait4 select * from testgp trx_operation_state: sleeping before entering InnoDB trx_concurrency_tickets: 0 *************************** 2. row *************************** trx_id: 84524 trx_state: RUNNING trx_query: insert into baguait3 select * from testgp trx_operation_state: sleeping before entering InnoDB trx_concurrency_tickets: 0 *************************** 3. row *************************** trx_id: 422211785606640 trx_state: RUNNING trx_query: select * from baguait1 trx_operation_state: fetching rows trx_concurrency_tickets: 3 3 rows in set (0.00 sec) +----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 3177 | Waiting on empty queue | NULL | 0 | 0 | | 6 | root | localhost | testmts | Query | 74 | Sending data | insert into baguait3 select * from testgp | 0 | 0 | | 7 | root | localhost | testmts | Query | 72 | Sending data | insert into baguait4 select * from testgp | 0 | 0 | | 8 | root | localhost | testmts | Query | 70 | Sending data | select * from baguait1 | 128718 | 0 | | 9 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 | +----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+ 5 rows in set (0.00 sec) |
我们可以观察到trx_operation_state的状态3个操作都在交替的变化，但是总有2个处于‘sleeping before entering InnoDB’状态。并且我们可以观察到trx_concurrency_tickets总是不会大于10的。因此我们有理由相信在同一时刻只有一个操作进入了Innodb层。但是需要注意的是在show engine innodb status中观察不到select的操作如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
------------ TRANSACTIONS ------------ Trx id counter 84538 Purge done for trx's n:o < 84526 undo n:o < 0 state: running but idle History list length 356 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422211785609424, not started 0 lock struct(s), heap size 1160, 0 row lock(s) ---TRANSACTION 422211785608032, not started 0 lock struct(s), heap size 1160, 0 row lock(s) ---TRANSACTION 84529, ACTIVE 103 sec inserting, thread declared inside InnoDB 6 mysql tables in use 2, locked 1 1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 111866 MySQL thread id 7, OS thread handle 140737158833920, query id 80 localhost root Sending data insert into baguait4 select * from testgp Trx read view will not see trx with id >= 84529, sees < 84524 ---TRANSACTION 84524, ACTIVE 105 sec sleeping before entering InnoDB mysql tables in use 2, locked 1 1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 105605 MySQL thread id 6, OS thread handle 140737159034624, query id 79 localhost root Sending data insert into baguait3 select * from testgp Trx read view will not see trx with id >= 84524, sees < 84524 |
但是我们还需要注意show engine innodb status有如下输出第一行说明了有2个会话（线程）堵塞在InnoDB层以外。
|
1 2 3 4 5 6 |
-------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 2 queries in queue 3 read views open inside InnoDB 2 RW transactions active inside InnoDB |
<延伸>
MySQL 大量 sleeping before entering InnoDB 故障诊断
MYSQL INNODB innodb_thread_concurrency相关参数理解
MySQL：一个innodb_thread_concurrency设置不当引发的故障
