MYSQL INNODB innodb_thread_concurrency相关参数理解

原创水平有限请谅解

虽然这几个参数我以前也有学习过，但是一直没有在源码级别进行证明，所以一直也没有写，但是今天群里有
朋友问到，所以先按照官方手册的加上我自己的理解进行一下解释，以后一定要在源码级别进行下补充
使用MYSQL版本：5.7.14
OS平台： CentOS release 6.5 (Final) 64bit
一、理论基础
首先要理解几个参数我们必须要先知道下面的内容，注意下面内容并不深入，而且只是我自己的理解
1、什么是多线程
实际上MYSQL中的多线程就是POSIX那一套，比如也就是我们说的pthread族函数比如pthread_create、pthread_join、pthread_mutex_lock等等，相信有多线程编程基础
的朋友不会陌生，线程也叫轻量级进程(LWP)那么多线程有什么好处，相对于进程而言多线程共享了很多东西比如
1.文件描述符表
2.每种信号的处理方式
3.当前工作目录
4.用户ID和组ID
5.除栈以外的内存空间

其实我们在编程的时候多线程通信都是通过非栈以外的内存进程的，比如堆空间，既然线程能够共享这麽多资源，不管是线程的创建、上下文切换、线程间通信都
变得方便了(注意共享是方便了但是对临界区的管理需要使用类似mutex rwlock之类的锁来实现)。接下来我们就要来讲讲线程间上下文切换
同时要记住一点线程是CPU调度的最小单位、进程是资源分配的最小单位。配上一张图


2、线程的上下文切换
我们知道LINUX是一个多批道多用户分时操作系统，它允许多个任务同时进入内存CPU通过时间轮片的方式进行调度，我们举例如果我有2核的CPU，但是我当前有4
个同等优先级的MYSQL线程进入了就绪队列，那么我们同一时刻能够并行(注意用词的准确性不是并发是并行)执行的MYSQL线程其实是2个，另外2个呢？当然就处
于就绪队列，等待获得CPU时间来完成工作，等到正在执行的2个线程时间轮片用完以后这个时候需要保留处理器现场，其实就是保存寄存器的值到内存，然后放弃
CPU，进入就绪态，这个时候在就绪队列的2个线程可以进入CPU进行工作了，这种4个线程并发执行但是只有2个线程获得时间轮片并行执行(获得CPU轮片)在这种不断
需要获得CPU轮片-->>工作-->>保存寄存器值到内存-->>放弃CPU轮片的方式中我们将保存寄存器值到内存这种动作叫做线程上下文切换，这是有一定代价的，当然
我的理解也许很片面因为我毕竟不是搞LINUX内核的。如果同时需要并发执行的线程越多这种上下文切换的频率就越大，这也是为什么我们在LINUX负载高的时候能够观察
到更多上下文切换的原因(vmstat就可以看到)，那么我们说如果限制同一时刻并发执行的线程数上下文切换将会减少，某种意义说就是长痛不如短痛，与其让你不断的
进行上文切换还不如把你处于睡眠态放弃CPU使用权
这里简单说一下线程的缺点：
线程不稳定（库函数实现）
线程调试比较困难（gdb支持不好）
信号使用非常困难
3、小事物线程饥饿问题
如果有过多线程编程使用过MUTEX，这种抢占试锁的朋友，一定不会忘记在某个线程释放MUTEX后，其他线程会以抢占的方式来获得，某些线程可能运气不好老是抢不到，如果换成
同优先级线程之间，OS在调度的时候如果不均衡，那么某些可能任务量小的线程老是得不到CPU轮片，而大任务线程老是获得CPU轮片，这依赖于OS的线程调度策略，这样就可能形成小
任务线程饥饿问题，与其依赖OS的调度策略不如自己设置一种规则，让用到了一定时间轮片的线程先处于睡眠态放弃CPU的使用。
二、参数解释
好了有了上面的理论知识可以进行这几个参数的解释了
其实这三个参数就是来解决上面的问题
1、innodb_thread_concurrency
同一时刻能够进入innodb层次并发执行的线程数(注意是并发不是并行)，如果超过CPU核数，某些线程可能处于就绪态而没有获得CPU时间轮片，如果SERVER层的线程大于这个值，对不起多余的
线程将会被放到一个叫做wait queue的队列中，而不能进入INNODB层次，进不到innodb层当然也就不能干活了，谈不上获得CPU。既然是一个队列那么它必然满足先进入先出的原则。这也是前面说的长痛不如短痛，与其让你不断的进行上文切换还不如把你处于睡眠态放弃CPU使用权，默认这个值是0，代表不限制。
2、innodb_concurrency_tickets
这个参数设置为一种tickets,默认是5000，我也不清楚到底它代表多久，从官方文档来看它和事物处理的行数有关，大事物需要处理的行数自然更多，小事物当然也就越少至少我们可以想成获得CPU的时间，干活期间他会不断减少，如果减少到0，这个线程将被提出innodb层次，进入前面说的等待队列，当然也就在队尾部了，这里假设有一个小的事物正在排队进入innodb层，又或者它已经进入了innodb层没有获得CPU时间轮片，突然一个大的事物tickets耗尽被提出了innodb层，那么这个小事物就自然而然能够获得CPU轮片干活，而小事物执行非常快，执行完成后
另外的事物又能尽快的获得CPU干活，不会由于OS线程调度不均匀的问题而造成的小事物饥饿问题，这很好理解。也就是前面我说的与其依赖OS的调度策略不如自己设置一种规则，让用到了一定时间轮片的线程先处于睡眠态放弃CPU的使用。
3、innodb_thread_sleep_delay
这个参数从官方手册来看，是代表当事物被踢出innodb层次后自己睡眠的时间，等待睡眠完成后再次进入wait que队列5.6.3以后可以设置innodb_adaptive_max_sleep_delay，来自动调整innodb_thread_sleep_delay，这就更为方便，因为这个值很难讲多少合适，其单位是microseconds，从某种意义上来讲这个值加剧了大事物执行的时间，小事物也就更加容易进入INNODB
层次获得CPU时间来干活。

关于这几个值如果一旦innodb_thread_concurrency设置为0，其他值的设置均没有效果，这很好理解，设置为0
后表示不限制，如果不限制也就谈不上等待队列，没有等待队列睡眠多久进入等待队列自然没有意义。
如果设置为0后show engine status的下面值始终为0
0 queries inside InnoDB, 0 queries in queue

这里配上一张自己根据理解画的图：


下面是官方对于innodb_thread_concurrency的一个建议设置值：
? If the number of concurrent user threads for a workload is less than 64, set
innodb_thread_concurrency=0.
? If your workload is consistently heavy or occasionally spikes, start by setting
innodb_thread_concurrency=128, and lowering the value to 96, 80, 64, and so on, until you
find the number of threads that provides the best performance. For example, suppose your system
typically has 40 to 50 users, but periodically the number increases to 60, 70, or even 200. You find that
performance is stable at 80 concurrent users but starts to show a regression above this number. In
this case, you would set innodb_thread_concurrency=80 to avoid impacting performance.
? If you do not want InnoDBto use more than a certain number of vCPUs for user threads (20 vCPUs
for example), set innodb_thread_concurrency to this number (or possibly lower, depending
on performance results). If your goal is to isolate MySQL from other applications, you may consider
binding the mysqldprocess exclusively to the vCPUs. Be aware, however, that exclusive binding
could result in non-optimal hardware usage if the mysqldprocess is not consistently busy. In this
case, you might bind the mysqldprocess to the vCPUs but also allow other applications to use some
or all of the vCPUs.

至少我们知道如果要设置innodb_thread_concurrency不应该高于CPU核数很多，比如我们可以设置1.5倍*CPU核数。
关于这一块也可以参考MYSQL官方手册
Section 15.4.6, “Configuring Thread Concurrency for InnoDB”.

三、如何观察
现在知道的观察方式主要是show engine innodb status和innodb_trx，其事物状态会为
sleeping before entering InnoDB

为了更好的观察我这里设置如下：
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 1     |
+---------------------------+-------+
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_concurrency_tickets | 10    |
+----------------------------+-------+
言外之意我将同一时刻能够进入innodb干活的线程数设置了1，同时tickets设置为了10来尽可能的观察到这种不断进入innodb
层次，然后tickets到被提出innodb层次的现象，随后我做了2个大事物，
好了我在show engine innodb status能够观察到如下：
---TRANSACTION 162307, ACTIVE 133 sec sleeping before entering InnoDB
mysql tables in use 2, locked 2
767 lock struct(s), heap size 106968, 212591 row lock(s), undo log entries 15451
MySQL thread id 14, OS thread handle 140736751912704, query id 1077 localhost root Sending data
insert into testui select * from testui
---TRANSACTION 162302, ACTIVE 320 sec, thread declared inside InnoDB 1
mysql tables in use 2, locked 2
2477 lock struct(s), heap size 336344, 609049 row lock(s), undo log entries 83582
MySQL thread id 13, OS thread handle 140737153779456, query id 1050 localhost root Sending data
insert into testti3 select * from testti3
--------
注意这里的sleeping before entering InnoDB
然后可以观察到
1 queries inside InnoDB, 1 queries in queue
这里也明显的说了1个线程在innodb里面另外一个在等待队列

在innodb_trx中能够观察到:

时间A：
mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G
*************************** 1. row ***************************
                 trx_id: 162612
              trx_state: RUNNING
              trx_query: insert into testti3 select * from testti3
    trx_operation_state: sleeping before entering InnoDB               
trx_concurrency_tickets: 0
*************************** 2. row ***************************
                 trx_id: 422212176322720
              trx_state: RUNNING
              trx_query: insert into testui select * from testui
    trx_operation_state: fetching rows
trx_concurrency_tickets: 2
2 rows in set (0.01 sec)

时间B：

mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G
*************************** 1. row ***************************
                 trx_id: 162612
              trx_state: RUNNING
              trx_query: insert into testti3 select * from testti3
    trx_operation_state: NULL
trx_concurrency_tickets: 10
*************************** 2. row ***************************
                 trx_id: 422212176322720
              trx_state: RUNNING
              trx_query: insert into testui select * from testui
    trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
2 rows in set (0.32 sec)
从trx_operation_state中可以看到他们不断的在进行轮换的进入的innodb层次，同时我们还能看到
活跃事物trx_concurrency_tickets这个tickets不断的减少，而处于sleeping before entering InnoDB
的事物其trx_concurrency_tickets为0。

四、事物等待进入innodb层堆栈
虽然没有研究源码但是还是将堆栈打出来，方便以后研究
#0  0x0000003ca620ef3d in nanosleep () from /lib64/libpthread.so.0
#1  0x0000000001a80c73 in os_thread_sleep (tm=1026) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/os/os0thread.cc:278
#2  0x0000000001b74e81 in srv_conc_enter_innodb_with_atomics (trx=0x7fffeeca15d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0conc.cc:214
#3  0x0000000001b74fcb in srv_conc_enter_innodb (prebuilt=0x7fffb41b7110) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0conc.cc:259
#4  0x000000000199c8c8 in innobase_srv_conc_enter_innodb (prebuilt=0x7fffb41b7110)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:1671
#5  0x00000000019a856d in ha_innobase::write_row (this=0x7fffb41b6b60, record=0x7fffb41af0d0 "\375\001")
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:7920
#6  0x0000000000f72e73 in handler::ha_write_row (this=0x7fffb41b6b60, buf=0x7fffb41af0d0 "\375\001") at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:8228
#7  0x00000000017d0c10 in write_record (thd=0x7fffb402eb20, table=0x7fffb41b61b0, info=0x7fffb40283f0, update=0x7fffb4028468)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:1864
#8  0x00000000017d2117 in Query_result_insert::send_data (this=0x7fffb40283a8, values=...) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:2262
#9  0x000000000155f954 in end_send (join=0x7fffb40286d0, qep_tab=0x7fffb41e4948, end_of_records=false)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2919
#10 0x000000000155c515 in evaluate_join_record (join=0x7fffb40286d0, qep_tab=0x7fffb41e47d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1639
#11 0x00000000015646b7 in QEP_tmp_table::end_send (this=0x7fffb4028ad0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:4710
#12 0x000000000155b508 in sub_select_op (join=0x7fffb40286d0, qep_tab=0x7fffb41e47d0, end_of_records=true)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1063
#13 0x000000000155b640 in sub_select (join=0x7fffb40286d0, qep_tab=0x7fffb41e4658, end_of_records=true)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1220
#14 0x000000000155b1ba in do_select (join=0x7fffb40286d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:946
#15 0x0000000001559060 in JOIN::exec (this=0x7fffb40286d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199
#16 0x00000000015f932a in handle_query (thd=0x7fffb402eb20, lex=0x7fffb4031100, result=0x7fffb40283a8, added_options=1342177280, removed_options=0)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:184
#17 0x00000000017d4d5f in Sql_cmd_insert_select::execute (this=0x7fffb4028330, thd=0x7fffb402eb20) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:3199
#18 0x00000000015a6bea in mysql_execute_command (thd=0x7fffb402eb20, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:3719
#19 0x00000000015ad15a in mysql_parse (thd=0x7fffb402eb20, parser_state=0x7fffec12c600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836
#20 0x00000000015a1019 in dispatch_command (thd=0x7fffb402eb20, com_data=0x7fffec12cd70, command=COM_QUERY)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447
#21 0x000000000159fe4a in do_command (thd=0x7fffb402eb20) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010
#22 0x00000000016e1d9c in handle_connection (arg=0x3a06b60) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312
#23 0x0000000001d72180 in pfs_spawn_thread (arg=0x413d3d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188
#24 0x0000003ca62079d1 in start_thread () from /lib64/libpthread.so.0
#25 0x0000003ca5ee8b6d in clone () from /lib64/libc.so.6

作者微信：