Mysql参数innodb_thread_concurrency

0 长求总

innodb_thread_concurrency

- innodb_thread_concurrency是动态参数可以随时修改
- 64个活跃连接以内直接配0
- 高压场景需要从高到低测试，找到最优值
- 高压场景下较低的值可以明显提高写入QPS的占比（高频率的读被限制了）

innodb_thread_sleep_delay（微秒）

- 定义在开始排队前，等多久加入队列

innodb_adaptive_max_sleep_delay（微秒）

- 配置innodb_thread_sleep_delay允许的最大值，配了之后innodb会自动调整innodb_thread_sleep_delay的值到一个合适的范围内（自适应算法）

innodb_concurrency_tickets（默认5000）

- 使用小的值时小事务可以和大事务竞争，缺点是大事务要多次才能跑完
- 使用大的值时大事务有优势，缺点是可能让小事务一直得不到运行
- 调整这个值可以参考队列长度，长度从SHOW ENGINE INNODB STATUS来看（ `ROW OPERATIONS` section of `SHOW ENGINE INNODB STATUS` output），也可以从INFORMATION_SCHEMA.INNODB_TRX的TRX_CONCURRENCY_TICKETS来看。

1 官方解释

InnoDB使用操作系统线程来处理用户的事务请求。（在事务提交或回滚之前可能给InnoDB引擎带来很多的请求）。在现代化操作系统和多核处理器的服务器上，上下文切换是非常高效的，大多数工作负载运行没有任何并发线程数量的限制。在MySQL 5.5及以上版本中，MySQL做了可伸缩性的改进，它减少了这种在InnoDB内部限制并发执行线程数量的需要。

它有助于在最小化的情况下进行线程之间的上下文切换，InnoDB可以使用各种技术来限制操作系统并发执行线程的数量（因此大批量的请求可以在任何一个时间得到处理）。当InnoDB从用户会话收到一个新的请求，如果线程并发执行的数量达到预定义的限制，那么新的请求会先睡眠一段时间后再次尝试。在睡眠后不能按计划执行的请求会被放入先入/先出队列，并最终处理。但那些等待获取锁的线程则不会被计入到并发执行线程的数量中。 我们可以通过设置配置参数innodb_thread_concurrency来限制并发线程的数量，一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒，可以通过设定参数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。

2 野史

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）

如果宰相的儿子不持久，那就用小时间快点排到他。

3 官方使用建议

在官方文档上，对于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参数的设置进行调整。

4 笔记

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操作将会进行并且一次性完成。

5 测试和总结

5.1 总结

innodb_thread_concurrency

• innodb_thread_concurrency是动态参数可以随时修改
• 64个活跃连接以内直接配0
• 高压场景需要从高到低测试，找到最优值
• 高压场景下较低的值可以明显提高写入QPS的占比（高频率的读被限制了）

innodb_thread_sleep_delay（微秒）

• 定义在开始排队前，等多久加入队列

innodb_adaptive_max_sleep_delay（微秒）

• 配置innodb_thread_sleep_delay允许的最大值，配了之后innodb会自动调整innodb_thread_sleep_delay的值到一个合适的范围内（自适应算法）

innodb_concurrency_tickets（默认5000）

• 使用小的值时小事务可以和大事务竞争，缺点是大事务要多次才能跑完
• 使用大的值时大事务有优势，缺点是可能让小事务一直得不到运行
• 调整这个值可以参考队列长度，长度从SHOW ENGINE INNODB STATUS来看（ ROW OPERATIONS section of SHOW ENGINE INNODB STATUS output），也可以从INFORMATION_SCHEMA.INNODB_TRX的TRX_CONCURRENCY_TICKETS来看。
• innodb_thread_concurrency限制后活跃连接状态不会变，从innodb_trx能看出来事务是不是在排队，show engine innodb status的row部分也能看出来
• 对于长短事务的场景应该非常有帮助，适当减少ticket可以让短事务更容易被执行，按下面测试场景来说，一个5读SQL的事务，一个5读3写SQL的事务，两个事务长度基本相同，如果用系统线程调度的话，执行快的事务执行的频率会更高。如果打开排队，每个事务拿着相同的ticket进入innodb，执行次数会更公平。所以可以看到写的次数明显升高了。
• sysbench的测试场景来说，读影响不是很大

横轴为innodb_thread_concurrency，纵轴为QPS

5.3 90c读写9:1测试

(tickets=5000不变cc变)

sysbench只读模型

sysbench oltp_read_only --mysql-host=rm-wz9w4eq0md96sc89x.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql  --tables=64 --table-size=10000000 --rand-type=uniform --report-interval=1 --threads=1024 --time=12000 run

BEGIN
SELECT c FROM sbtest%u WHERE id=?
SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ?
SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ?
SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c
SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c
COMMIT

sysbench读写模型

sysbench oltp_read_write --mysql-host=rm-wz9w4eq0md96sc89x.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql  --tables=64 --table-size=10000000 --rand-type=uniform --report-interval=1 --threads=1024 --time=12000 run

BEGIN
SELECT c FROM sbtest%u WHERE id=?
SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ?
SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ?
SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c
SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c
UPDATE sbtest%u SET k=k+1 WHERE id=?
UPDATE sbtest%u SET c=? WHERE id=?
DELETE FROM sbtest%u WHERE id=?
COMMIT

innodb_thread_sleep_delay=10000

innodb_adaptive_max_sleep_delay=150000

innodb_concurrency_tickets=5000

两客户端每个1024，cc=innodb_thread_concurrency

读写qps

5.4 无事务测试

(tickets=5000不变cc变)

sysbench只读模型（无事务无范围查询）

sysbench oltp_read_only --mysql-host=rm-wz9w4eq0md96sc89x.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql  --tables=64 --table-size=10000000 --rand-type=uniform --report-interval=1 --threads=1024 --time=12000 --range_selects=off --skip_trx=on run


SELECT c FROM sbtest%u WHERE id=?

sysbench读写模型（无事务无范围查询）

sysbench oltp_read_write --mysql-host=rm-wz9w4eq0md96sc89x.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql  --tables=64 --table-size=10000000 --rand-type=uniform --report-interval=1 --threads=1024 --time=12000 --skip_trx=on --range_selects=off run


UPDATE sbtest%u SET k=k+1 WHERE id=?
UPDATE sbtest%u SET c=? WHERE id=?
DELETE FROM sbtest%u WHERE id=?

两客户端每个1024，cc=innodb_thread_concurrency

读写qps

innodb_concurrency_tickets=10

innodb_thread_concurrency=10

mysql> select trx_operation_state,count(*) from information_schema.innodb_trx group by trx_operation_state;
+---------------------------------+----------+
| trx_operation_state             | count(*) |
+---------------------------------+----------+
| NULL                            |       25 |
| sleeping before entering InnoDB |     1932 |
| starting index read             |       10 |
+---------------------------------+----------+