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

作者微信：