MySQL Innodb 并发涉及参数

灰暗的星星灰暗的星星灰暗的星星灰暗的星星灰暗的星星
 

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版本废除了该参数,所以我们这里不涉及对该参数的测试及描述。

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_TRXTRX_CONCURRENCY_TICKETS查看消费次数情况。

提交评论


安全码
刷新

 

自1996年以来,公司一直专注于域名注册、虚拟主机、服务器托管、网站建设、电子商务等互联网服务,不断践行"提供企业级解决方案,奉献个性化服务支持"的理念。作为戴尔"授权解决方案提供商",同时提供与公司服务相关联的硬件产品解决方案。
 

联系方式

地址:河南省郑州市经五路2号

电话:0371-63520088 

QQ:76257322

网站:800188.com

电邮:该邮件地址已受到反垃圾邮件插件保护。要显示它需要在浏览器中启用 JavaScript。

微信:用企业微信联系