mysql-InnoDB并发线程配置

一 Innodb并发线程控制

       InnoDB使用操作系统线程来处理来自用户事务的请求。(事务在提交或回滚之前可能会向InnoDB发出很多请求。)在具有多核处理器的现代操作系统和服务器上，上下文切换非常有效，大多数工作负载运行良好，并发线程的数量没有任何限制。同时mysql5.5及以上版本的可伸缩性有了提高，这也降低了对InnoDB内进行并发线程数量限制的要求。（不建议设置并发控制）

       InnoDB可以使用一系列技术去限制并发的线程数量（从而限制每次处理的并发请求数），这有助于降低线程之间的上下文切换。若并发线程数已经达到或者超过限制，新接收的用户请求会进入一个短暂的休眠，然后再次尝试进入innodb，若是还不能进入innodb，则该请求被放入一个请求队列中继续进行等待。等待锁的线程不会计算在并发执行的线程数中。

       可以通过设置配置参数innodb_thread_concurrency来限制并发线程的数量。一旦并发线程的数量达到这个限制，其他线程在被放入队列之前就会休眠几微秒(由配置参数innodb_thread_sleep_delay设置)。

       以前，需要进行实验才能找到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 5.7中innodb_thread_concurrency的默认值是0，也就是默认情况下，并发执行的线程数量没有限制。

       InnoDB只有在限制并发线程数量情况下(当innodb_thread_concurrency>0时)才会导致线程休眠。 当线程数量没有限制时，所有线程平等对待执行。也就是说，如果innodb_thread_concurrency为0，则忽略innodb_thread_sleep_delay的值。

       当限制并发线程数量情况下(当innodb_thread_concurrency>0时)，InnoDB允许在执行单个SQL语句期间发出的多个请求进入InnoDB，而无需观察innodb_thread_concurrency设置的限制，从而减少上下文切换开销。由于一个SQL语句(比如join)可能包含InnoDB中的多个行操作，InnoDB分配了指定数量的"tickets"，允许线程以最小的开销重复调度。

       当一个新的SQL语句开始时，该线程线程没有"tickets"，它必须观察 innodb_thread_concurrency。一旦线程被授予进入InnoDB的权限，它就会被分配一些"tickets"，用于随后进入InnoDB执行的行操作。如果"tickets"用完，线程将被逐出，并再次观察innodb_thread_concurrency，这可能会将线程放回先入/先出等待线程队列中。当线程再次被授予进入InnoDB的权限时，"tickets"将再次被分配。分配的"tickets"数量由全局选项innodb_concurrency_tickets指定，默认值为5000。等待锁的线程在锁可用时才会得到"tickets"。

       这些变量的正确值取决于您的环境和工作负载。尝试一系列不同的值，以确定哪些值适合您的应用程序。在限制并发执行线程的数量之前，需要配合可以提高InnoDB在多核多处理器计算机上性能的配置选项，比如innodb_adaptive_hash_index一起使用。

二 相关参数解释

       （1）innodb_concurrency_tickets ：（默认值5000）决定可以同时进入Innodb的线程数。当一个thread试图进入innodb时，若是并发线程数量已经达到了并发限制的数量，该线程就会被放入一个等待队列中。一旦该线程被允许进入innodb中，就给分配给它和innodb_concurrency_tickets 相同数量的 tickets，在这些tickets 被用完之前，该线程可以自由出入innodb。之后当线程再次试图进入innodb时，将会再次对其做并发检查。

       若是将 innodb_concurrency_tickets 设置一个比较小的值，小事务（操作很少行）和大事务（操作很多行）有相同的机会进入innodb层。但是这样的设置对于大事务而言必须要经过多次的等待队列循环才能完成，这会延长事务执行时间。

       若是 innodb_concurrency_tickets 设置一个比较大的值，大事务花费更少的时间去等待队列末尾的位置(由innodb_thread_concurrency控制)，有更多的时间去检索需要的行。也就会有更少在的等待队列中循环次数去完成事务操作。但是这将会导致同时运行的大事务很多，而小事务却要花费大量时间等待，从而导致小事务长时间无法运行。

       将 innodb_thread_concurrency  设置为非零时，需要尝试调整innodb_concurrency_tickets  值得大小，以找到对于大小事务的平衡点。SHOW ENGINE INNODB状态报告显示当前通过队列等待的在执行事务的剩余tickets 数。这个数据也可以从INFORMATION_SCHEMA.INNODB_TRX的TRX_CONCURRENCY_TICKETS列中获得。

　　说明：对于innodb_concurrency_tickets  这个参数有其他文章说是事务运行该参数设置的数值对应的毫秒后后被逐出innodb，有的说是扫描都该参数设置的行数后被逐出innodb。本人测试发现这两种解释均不正确。如有哪位大神明白该参数的具体含义还请指点。

     （2） innodb_thread_concurrency ：（默认值为 0）Innodb试图在innodb保持操作系统线程数（并发线程数）小于或者等于该参数的限制（InnoDB使用操作系统线程来处理用户事务）。一旦线程数量达到该参数的限制，其他线程就会被放入到一个等待队列中（FIFO，先进先出），注意等待锁的线程不计入并发线程数量里。

       该参数范围是0-1000，设置为零（默认）表示无限并发，也就是不检查并发限制。禁用并发检查使得innodb能根据需要创建任意多的线程。0的值还禁用了InnoDB内部的查询和在SHOW ENGINE InnoDB STATUS输出的行操作部分的队列计数器的查询。

       如果您的MySQL实例与其他应用程序共享CPU资源，或者您的工作负载或并发用户数量正在增长，请考虑设置此变量。该变量的正确设置取决于工作负载、计算环境和正在运行的MySQL版本。为确定提供最佳性能的设置，需要对innodb_thread_concurrency的设置不同的值进行测试。innodb_thread_concurrency是一个动态变量，可以在不重启的情况下试验不同的设置。如果某个特定的设置执行得不好，可以快速地将innodb_thread_concurrency设置回0。

       以下指南可以帮助查找和维护适当的设置:

• 如果工作负载的并发连接数小于64  ，则可以将innodb_thread_concurrency  设置为64；
• 如果你的工作量一直很重，或者偶尔会激增，那可以先设置innodb_thread_concurrency=128，然后将值降低到96、80、64等等，直到找到提供最佳性能的线程数量。假设您的系统通常有40到50个用户并发，但是用户数量会周期性地增加到60、70甚至200个。您会发现在80个并发用户时性能是稳定的，但在这个数字之上开始出现回归。在这种情况下，可以将innodb_thread_concurrency设置为80，以避免影响性能。
• 如果不希望InnoDB为用户线程使用超过虚拟cpu数(例如，20个虚拟cpu)，可以将innodb_thread_concurrency设置为这个数量(或者更低，取决于性能结果)。如果您的目标是将MySQL与其他应用程序隔离，那么可以考虑将mysqld进程专门绑定到虚拟cpu。但是，要注意，如果mysqld进程不是一直处于繁忙状态，则排它绑定可能会导致非最佳的硬件使用。在这种情况下，可以将mysqld进程绑定到虚拟cpu，但也允许其他应用程序使用部分或全部虚拟cpu。
• innodb_thread_concurrency设置太高有可能会导致性能退化，因为系统内部和资源争用会增加；
• 在有些情况下 innodb_thread_concurrency 的最佳设置可以小于cpu核数；
• 定期的监控和分析系统情况。对工作负载、用户数量或计算环境的更改可能需要调整innodb_thread_concurrency的设置

   （3）innodb_thread_sleep_delay ：定义InnoDB线程在加入InnoDB队列之前的睡眠时间(以微秒为单位)。默认值是10000。值0禁用睡眠。您可以将innodb_adaptive_max_sleep_delay设置为允许innodb_thread_sleep_delay的最大值，而InnoDB会根据当前线程调度活动自动向上或向下调整innodb_thread_sleep_delay。这种动态调整有助于线程调度机制在系统负载较轻或接近满负荷运行时平稳工作。（可动态调整）

   （4）innodb_adaptive_max_sleep_delay ：允许InnoDB根据当前工作负载自动调整innodb_thread_sleep_delay的值。任何非零值都可以自动动态调整innodb_thread_sleep_delay值，最大可以设置到innodb_adaptive_max_sleep_delay选项中指定的最大值。该值表示微秒数（默认值150000，最小0，最大1000000）。这个选项在有超过16个InnoDB线程的繁忙系统中非常有用。(实际上，它对于具有数百或数千个并发连接的MySQL系统最有价值。)

三 实验：

       3.1 相关参数设置如下

       mysql> show  global variables like  ‘%concurrency%‘;

       +----------------------------+-------+

       | Variable_name              | Value |

       +----------------------------+-------+

       | innodb_commit_concurrency  | 0     |

       | innodb_concurrency_tickets | 1000  |

       | innodb_thread_concurrency  | 1     |

       +----------------------------+-------+

　　3.2 分别在三个窗口执行

　　select  count(1)  se1 from  t_test1 a ,t_test2 b where a.id=b.col1;

　　select  count(1)  se2 from  t_test1 a ,t_test2 b where a.id=b.col1;

　　select  count(1)  se3 from  t_test1 a ,t_test2 b where a.id=b.col1;

（语句执行时间在7min左右）

　　观察当前在执行事务状态，查询INFORMATION_SCHEMA.INNODB_TRX

　　一开始只有一个事务在执行如下：

过几秒会依次发现两到三个事务，但是有两个是处于sleeping before entering InnoDB 状态

　　查看innodb status 也会发现有一个查询在Innodb执行，有两个在队列中排队

四 个人小结

       在限制并发线程数量情况下(当innodb_thread_concurrency>0时)，当请求的并发数超过 innodb_thread_concurrency 所设置的值时，会先尝试进入innodb中，若是innodb_thread_concurrency 已经达到限制，会进行休眠，innodb_thread_sleep_delay时间后会再次尝试进入innodb，若还是不行 ，那么该线程会进入一个队列（先进先出）排队，按照先进先出原则进入innodb。当innodb中有线程花费完 tickets后，若此时这个线程事务还未执行完，会进入队列进行等待，会看到处于一种 sleeping before entering InnoDB 的状态，队列最前面的的线程会进行进入innodb执行事务。

       另对于 innodb_concurrency_tickets 的设置，设置的越大线程被逐出innodb的频次就越底，对于执行时间较长的事务越有利，设置的较小时线程被析出innodb的频次就越高，对于执行时间较短的事务越有利，因为等待时间变短，一旦进入innodb 可以一次就能执行完毕。

　　另根据官方文档的解释 一般情况下 不建议设置并发控制。

mysql-InnoDB并发线程配置

原文：https://www.cnblogs.com/ivan17/p/13173714.html