Mysql线程参数详解

Mysql线程参数详解
一、innodb_thread_concurrency
1、 innodb_thread_concurrency限制行的并发度,但是在提交阶段innodb的结构和锁争用很严重的。MySQL从5.0就开始引进的innodb_commit_concurrency,这个参数设置了同一时刻允许同时commit的线程数。默认是0即不限制，取值范围0-1000，不可动态修改。
2、 innodb_thread_concurrency，如果参数设置大于0，则表示检查机制开启，允许进入的线程数就是参数的值.
3、 在新的MySQL线程调用Innodb接口前，Innodb会检查已经接受的请求线程数；
如已经超过innodb_thread_concurrency设置的限制，则该请求线程会等待innodb_thread_sleep_delay微秒后尝试重新请求，如果第二次请求还是无法获得，则该线程会进入FIFO队列休眠。重试两次的机制是为了减少CPU的上下文切换的次数，以降低CPU消耗。
如果请求被Innodb接受，则会获得一个次数为innodb_concurrency_tickets(默认500次)的通行证，在次数用完之前，该线程重新请求时无须再进行前面的检查
4、 InnoDB使用操作系统线程来处理用户事务请求，是这样工作的：当InnoDB收到一个用户的请求时，如果已经超过innodb_thread_concurrency预先设置的并发线程数量，那么就会按照innodb_thread_sleep_delay预先设定的值休眠N秒，之后再次尝试连接，重试两次的机制是为了减少CPU上下文切换的次数，以降低CPU消耗。如果请求被接受，则会获得一个innodb_concurrency_tickets默认500次的通行证，在这些次数用完之前，该线程重新请求时无须再次进行前面所说的innodb_thread_concurrency的检查。如果还没有接受，那么就会进入队列中，知道最终被处理掉。
5、
6、 从5.5.x版本开始，innodb_thread_concurrency被默认设置为0，表示不限制并发数。
7、 nnodb_thread_concurrency=0时，innodb_thread_sleep_delay参数就无效了；同样，innodb_concurrency_tickets 也没有意义。这里推荐设置innodb_thread_concurrency为0，这样就可以更好的发挥CPU多核处理能力，提高并发量。

二、thread_handling=one-thread-per-connection
1、mysql常用（目前线上使用）的线程调度方式是one-thread-per-connection（每连接一个线程），server为每一个连接创建一个线程来服务，连接断开后，这个线程进入thread_cache或者直接退出（取决于thread_cache设置及系统当前已经cache的线程数目），one-thread-per-connection调度的好处是实现简单，而且能够在系统没有遇到瓶颈之前保证较小的响应时间，比较适合活跃的长连接的应用场景，而在大量短连接或者高并发情况下，one-thread-per-connection需要创建/调度大量的线程，产生较高的的context-switch代价，从而使得系统性能下降

2、在MySQL5.6出现以前，MySQL处理连接的方式是One-Connection-Per-Thread,即对于每一个数据库连接，MySQL-Server都会创建一个独立的线程服务，请求结束后，销毁线程。再来一个连接请求，则再创建一个连接，结束后再进行销毁。这种方式在高并发情况下，会导致线程的频繁创建和释放。当然，通过thread-cache，我们可以将线程缓存起来，以供下次使用，避免频繁创建和释放的问题，但是无法解决高连接数的问题。One-Connection-Per-Thread方式随着连接数暴增，导致需要创建同样多的服务线程，高并发线程意味着高的内存消耗，更多的上下文切换(cpu cache命中率降低)以及更多的资源竞争，导致服务出现抖动。相对于One-Thread-Per-Connection方式，一个线程对应一个连接，Thread-Pool实现方式中，线程处理的最小单位是statement(语句)，一个线程可以处理多个连接的请求。这样，在保证充分利用硬件资源情况下(合理设置线程池大小)，可以避免瞬间连接数暴增导致的服务器抖动。
调度方式实现
MySQL-Server同时支持3种连接管理方式，包括No-Threads，One-Thread-Per-Connection和Pool-Threads。No-Threads表示处理连接使用主线程处理，不额外创建线程，这种方式主要用于调试；One-Thread-Per-Connection是线程池出现以前最常用的方式，为每一个连接创建一个线程服务；Pool-Threads则是本文所讨论的线程池方式。MySQL-Server通过一组函数指针来同时支持3种连接管理方式，对于特定的方式，将函数指针设置成特定的回调函数，连接管理方式通过thread_handling参数控制
Thread Pool根据系统的CPU创建出thread_pool_size(该参数不建议调整)，也可以把这个参数理解为Thread Pool共分了多少Group,　每个Group里同时可以运行多少个任务由：thread_pool_oversubscribe控制，默认是3. 如果把thread_pool_oversubscribe设置为1，则thread_pool_size是同时允许的活跃任务数．每个Group都是一个epoll的对象．

Thread_pool_size X Thread_pool_oversubscribe = MySQL内部同时支持运行的最大任务数．　如MySQL-5.5在128个任务时运行比较好时，CPU是16个核，可以把Thread_pool_oversubscribe更改为: 8或是10都可以．
每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。
如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了（上述状态值可以使用SHOW GLOBAL STATUS LIKE ‘Open%tables’获得）。
注意，不能盲目地把table_open_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

查询mysql当前打开的表的数量：
show global status like 'open%tables%';
查看打开表的缓存：
show variables like '%table%cache%';