1. innodb_thread_concurrency
innodb有一系列的计数器来统计和控制内部的工作线程。其中最重要的一个是innodb_thread_concurrency,和它相关的innodb_thread_sleep_delay 和innodb_concurrency_tickets。
由于MySQL是插件式db,读取行的时候可以有很多方式,比如说顺序读or随机读,而DML(insert,delete,update)语句是要判断是否已经进入到了innodb线程里,如果超过了 innodb_thread_concurrency的值,首先要等innodb_thread_sleep_delay ms后尝试再次进入工作线程,如果失败,则会进入到FIFO队列等待唤醒。这里要提下为什么需要两次尝试?因为需要减少等待线程的个数和上下文切换的次数。
如果一个线程能够进到innodb层,则会发放一个innodb_concurrency_tickets 票,下次的时候如果在有效期内,则不会检查tickets,代码很简单,在 srv_conc_enter_innodb function in innobase/srv/srv0srv.c里:
if (thread->n_tickets_to_enter_innodb > 0) { thread->n_tickets_to_enter_innodb--; ENTER; } retry: if (entered_thread < innodb_thread_concurrency) { entered_threads++; thread->n_tickets_to_enter_innodb = innodb_concurrency_tickets; ENTER; } if (innodb_thread_sleep_delay > 0) { thread_sleep(innodb_thread_sleep_delay); } goto retry; // (only once) WAIT_IN_FIFO_QUEUE; thread->n_tickets_to_enter_innodb = innodb_concurrency_tickets; ENTER;
那么innodb_thread_concurrency最佳值是多少呢?在MySQL5.6之前建议要比cpu的核数小
理论上可以设置2*(NumCPUs+NumDisks),这样就有每个cpu和磁盘分配2倍的active threads,但是这仅仅是理想情况。根据实践,一般在8核cpu推荐设置1,2,4;如果这个值远远比cpu个数少的话就会不能充分利用cpu,这样线程就会在进入innodb的队列之前sleep一段时间。
2. innodb_commit_concurrency
innodb_thread_concurrency限制行的并发度,但是在提交阶段(innodb的结构和锁争用很严重的),缺没有得到很好的保护。MySQL从5.0就开始引进的innodb_commit_concurrency,
Command-Line Format | --innodb_commit_concurrency=# | ||
Option-File Format | innodb_commit_concurrency | ||
System Variable Name | innodb_commit_concurrency | ||
Variable Scope | Global | ||
Dynamic Variable | Yes | ||
Permitted Values | |||
Type | numeric | ||
Default | 0 | ||
Range | 0 .. 1000 |
这个参数设置了同一时刻允许同时commit的线程数。默认是0,也即是不限制。这个值可以在0到1000随意设置,如果刚开始是0,要想设置>0,必须在配置文件里添加:
innodb_commit_concurrency=n(n>0),在n>0的情况下可以设置(0~1000]的范围变化,但是不能动态的设置成0,也不能动态的设置为n,必须重启MySQL;
这个值一般场景下,不限制(为0)就能满足需求,但是0并不是适用于所有的场景,对于大量写场景,对性能提升还是很明显的。
参考:http://www.mysqlperformanceblog.com/2006/06/05/innodb-thread-concurrency/