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` outp ut），也可以从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 --sql.rds.aliyuncs --mysql-port=3306 --mysql-user=server_234 --mysql-password=s erver_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 --sql.rds.aliyuncs --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 --tim e=12000 run
BEGIN下载mysql为什么下载不了
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
cc si active cpu|qps cs
0  3.6185051/38|19.6w50w
500  3.6170050/40|19.7w52w
100  3.4180042/48|18.1w53w
50  2.8190035/53|15.0w51w
100.5200020/6.6|11w74w
读写qps
Cc Select Update Delete Insert
016.6w350017001700
50016.5w400020002000
10014.8w680034003400
5011.7w710036003600
108.7w540027002700
5.4 ⽆事务测试
(tickets=5000不变cc变)
sysbench只读模型（⽆事务⽆范围查询）
sysbench oltp_read_only --sql.rds.aliyuncs --mysql-port=3306 --mysql-user=server_234 --mysql-password=s erver_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读写模型（⽆事务⽆范围查询）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。