补充二十二：MySQL InnoDB配置并发线程

MySQL InnoDB配置并发线程（ innodb_thread_concurrency）

http://www.ywnds.com/?p=9821

一、thread_concurrency

首先，最重要的一点，这个参数已经在最新版本的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_thread_concurrency来限制并发线程的数量，一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒，可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间。

在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的值，这种动态调整机制有助于工作的线程，在系统负载低时或系统接近满负荷运转时，都能够顺利的调度。

在MySQL和InnoDB之前的版本系列中，innodb_thread_concurrency的默认值，以及其隐含的限制并发线程执行的数量都进行过调整。在当前最新版本的MySQL中，innodb_thread_concurrency的默认值为0，它表示默认情况下不限制线程并发执行的数量。

另外，InnoDB只有当并发线程数量有限时，线程才会休眠。当线程数量没有限制时，所有这些都同样被安排。也就是说，如果innodb_thread_concurrency是0，值 innodb_thread_sleep_delay被忽略。

当线程数量有限时（当innodb_thread_concurrency>0时），InnoDB通过允许在执行单个SQL语句期间进行的多个请求进入InnoDB而不需要遵守设置的限制，从而减少上下文切换开销innodb_thread_concurrency。由于SQL语句（例如join）可能包含多个行操作，所以InnoDB分配指定数量的 “ tickets ”，允许以最少的开销重复排列线程。

当一个新的SQL语句开始，当前线程没有“tickets”时，它就必须遵守innodb_thread_concurrency参数设置，一旦这个线程有权进入InnoDB，它会被分配一个“tickets”，它可以通过这个“tickets”用于随后进入InnoDB执行行操作，如果“tickets”使用完毕，该线程将会被驱逐，innodb_thread_concurrency参数会被放回到先入/先出队列中等待的线程等待再次观察。一旦这个线程再次有权进入InnoDB，“tickets”又会被重新分配，我们可以通过设置全局参数innodb_concurrency_tickets来指定“tickets”的数量，默认情况下是5000。正在等待获取锁的线程，一旦锁可用，会被立即分配一个“tickets”。

这些参数的正确值取决于当前系统环境和负载情况。尝试各种不同的值，以确定哪些值适用于当前应用程序。在限制并发执行的线程数之前，在多核及多处理器的计算机上，检查一下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）时唤醒我．

如此当有人从内部出来以后，等待的大爷(排在最前面的)就可以进入享受鱼水之欢了．

但是每位大爷能够支持的时间不一样，有的一分钟（quicker）,有的大爷需要几个小时．这样外面等待的大爷就会有意见，哎呀，怎么还不出来．

老鸨又想了一个办法，规定每个人不能在姑娘房里呆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参数的设置进行调整。

1. innodb_thread_concurrency

innodb有一系列的计数器来统计和控制内部的工作线程。其中最重要的一个是innodb_thread_concurrency，和它相关的innodb_thread_sleep_delay 和innodb_concurrency_tickets。

由于MySQL是插件式db，读取行的时候可以有很多方式，比如说顺序读or随机读，而DML（insert,delete,update）语句是要判断是否已经进入到了innodb线程里，如果超过了 innodb_thread_concurrency的值，首先要等innodb_thread_sleep_delay ms后尝试再次进入工作线程，如果失败，则会进入到FIFO队列等待唤醒。这里要提下为什么需要两次尝试？因为需要减少等待线程的个数和上下文切换的次数。

如果一个线程能够进到innodb层，则会发放一个innodb_concurrency_tickets 票，下次的时候如果在有效期内，则不会检查tickets，代码很简单，在 srv_conc_enter_innodb function in innobase/srv/srv0srv.c里：

if (thread->n_tickets_to_enter_innodb  > 0)
  {
    thread->n_tickets_to_enter_innodb--;
    ENTER;
  }
 
retry:
  if (entered_thread < innodb_thread_concurrency)
  {
    entered_threads++;
    thread->n_tickets_to_enter_innodb = innodb_concurrency_tickets;
    ENTER;
  }
 
  if (innodb_thread_sleep_delay  > 0)
  {
    thread_sleep(innodb_thread_sleep_delay);
  }
 
  goto retry; // (only once)
 
  WAIT_IN_FIFO_QUEUE;
  thread->n_tickets_to_enter_innodb = innodb_concurrency_tickets;
  ENTER;

那么innodb_thread_concurrency最佳值是多少呢？在MySQL5.6之前建议要比cpu的核数小

理论上可以设置2*(NumCPUs+NumDisks)，这样就有每个cpu和磁盘分配2倍的active threads,但是这仅仅是理想情况。根据实践，一般在8核cpu推荐设置1,2,4;如果这个值远远比cpu个数少的话就会不能充分利用cpu，这样线程就会在进入innodb的队列之前sleep一段时间。

2. innodb_commit_concurrency

innodb_thread_concurrency限制行的并发度,但是在提交阶段（innodb的结构和锁争用很严重的），缺没有得到很好的保护。MySQL从5.0就开始引进的innodb_commit_concurrency,

Command-Line Format	`--innodb_commit_concurrency=#`
Option-File Format	`innodb_commit_concurrency`
System Variable Name	`innodb_commit_concurrency`
Variable Scope	Global
Dynamic Variable	Yes
	Permitted Values
	Type	`numeric`
	Default	`0`
	Range	`0 .. 1000`

这个参数设置了同一时刻允许同时commit的线程数。默认是0，也即是不限制。这个值可以在0到1000随意设置，如果刚开始是0，要想设置>0，必须在配置文件里添加：

innodb_commit_concurrency=n(n>0),在n>0的情况下可以设置(0~1000]的范围变化,但是不能动态的设置成0，也不能动态的设置为n，必须重启MySQL；

这个值一般场景下，不限制（为0）就能满足需求，但是0并不是适用于所有的场景，对于大量写场景，对性能提升还是很明显的。

1 参数作用

MySQL的各个插件式引擎中，都会对事务及线程做一定的处理和优化。在Innodb引擎中，总是尝试保持 innodb内操作系统的线程数（暂命名为innodb_thread）应该小于或等于系统可提供给innodb处理事务的线程数(暂命名为system_innodb_thread)。在大多数情况下，innodb_thread都不会指定一个限制值，而是让它想要多少直接申请多少。

当 innodb_thread 大于system_innodb_thread 时，持续时间较长时，会导致服务器的线程资源被数据库使用，CPU可能居高不下，甚至引发宕机。

这个时候，Innodb内部可以提供一个参数来限制并发线程（同一时刻可处理的请求数），当并发数达到并发线程限制数时，再接收到一个新的请求，那么这个请求需要在下次请求前先sleep一段时间，如果sleep后再请求还是没有多余线程提供其执行，那么，它就会进入到先进先出的队列中等待执行。这里注意下，等待线程，不计入 innodb_thread 。innodb_thread_concurrency 参数因此而来。

可以通过innodb_thread_concurrency 来调节并发线程数的限制值，使用innodb_thread_sleep_delay来调整当并发 thread 到达 innodb_thread_concurrency时需要sleep的时间。当请求被innodb接受的时候，会获得一个消费凭证 innodb_concurrency_tickets (默认5000次)，当这个请求中有多个SQL被执行的时候，每执行一次，消费一次tickets，在次数用完之前，该线程重新请求时无须再进行前面 thread 是否达到并发限制值的检查。

同时 innodb_commit_concurrency也控制了多线程并发提交的数量。如果 innodb_thread_concurrency 设置的有点大innodb_commit_concurrency应该做出相应的调整，否则会造成大量线程阻塞。

所以，跟并发相关的有这几个参数设置：innodb_thread_concurrency、innodb_thread_sleep_delay、innodb_concurrency_tickets、innodb_commit_concurrency。

跟innodb_thread_concurrency相似的参数有 thread_concurrency ，但是它在5.6版本的官方文档中已被标识为过时，在5.7.2版本废除了该参数，所以我们这里不涉及对该参数的测试及描述。

回到顶部(go to top)

2 参数设置

2.1 innodb_thread_concurrency

2.1.1 默认值

innodb_thread_concurrency默认是0，则表示没有并发线程数限制，所有请求都会直接请求线程执行。注意：当 innodb_thread_concurrency 设置为0时，则innodb_thread_sleep_delay的设置将会被忽略，不起作用。如果数据库没出现性能问题时，使用默认值即可。

2.1.2 大于0

当innodb_thread_concurrency>0，则表示有并发数限制，当一个新的请求发起时，会检查当前并发线程数是否达到了 innodb_thread_concurrency的限制值，如果有，则需要sleep一段时间（sleep的设置详见下一部分），然后再再次请求，如果再次请求时，当前并发数还是达到限制值，那么就会进入FIFO队列等待执行。当进入到内核执行时，会得到一个消费凭证 ticket，则这个线程，在后面的多次进入innodb执行操作是都不需要重复上面的检查步骤，当把次数消费完，那么这个线程就会被驱逐，等待下次再次进入Innodb，再重新分配ticket。

2.1.3 建议配置（来自官网）

当并发用户线程数量小于64，建议设置innodb_thread_concurrency=0；
如果负载不稳定，时而低，时而高到峰值，建议先设置innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置innodb_thread_concurrency参数为80，以避免影响性能；
如果DB服务器上还允许其他应用，需要限制mysql的线程使用情况，则可以设置可分配给DB的线程数，但是不建议DB上跑其他应用，也不建议这么设置，因为这样可能导致数据库没有对硬件最优使用；
设置过高值，可能会因为系统资源内部争夺导致性能下降；
在大多数情况下，最佳的值是小于并接近虚拟CPU的个数；
定期监控和分析DB，因为随着数据库负载的变化，业务的增加，innodb_thread_concurrency也需要动态的调整。

2.2 innodb_thread_sleep_delay

5.6.3版本前，需要反复测试才能确定innodb_thread_sleep_delay值，并且固定为一个值，在5.6.3版本后，因为 Innodb 自动调整innodb_thread_sleep_delay参数：

Innodb_adaptive_max_sleep_delay：最大sleep的时间，微秒为单位

可以通过设置参数 innodb_adaptive_max_sleep_delay 来限制 innodb_thread_sleep_delay的最大值，不设置 innodb_thread_sleep_delay的取值情况，让Innodb自动跟进负载来调整，当系统负荷较高时，Innodb动态调整slee时间可使得数据库稳定运行。

2.3 innodb_commit_concurrency

该值只能为默认值0，mysql不限制并发提交。大于0表示允许N个事务在同一时间点提交，N的范围是0-1000。

注意事项：mysqld运行时，不许把innodb_commit_concurrency 的值从0改为非0，或非0的值改为0；但允许从N改为M（N及M均大于0）

2.4 innodb_concurrency_tickets

默认是5000（基于5.6，5.7）。

如果innodb_concurrency_tickets设置小些，适用于小事物操作较多的系统，可以快速使用完线程后退出来，提供给其他请求使用；而对于大事务来说，可能会循环进入等待队列中等待执行完成，这会耗费更多时间及资源；如果innodb_concurrency_tickets设置大些，适用于大事务频繁操作的系统，这样大事务则不需要频繁进入queue等待队列，可以通过较少的请求来处理；但是对于小事务来说，则意味着他们要等待更长的时候，才能排队进入到内核执行。所以，当innodb_thread_concurrency>0时，需要上下调整 innodb_concurrency_tickets ，使其达到最佳性能。可以通过show engine innodb status 的queue查看，也可以通过INFORMATION_SCHEMA.INNODB_TRX 的TRX_CONCURRENCY_TICKETS查看消费次数情况。

参考：