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

作者微信：