MYSQL INNODB innodb_thread_concurrency相關(guān)參數(shù)理解

原創(chuàng)水平有限請(qǐng)諒解

雖然這幾個(gè)參數(shù)我以前也有學(xué)習(xí)過(guò)，但是一直沒(méi)有在源碼級(jí)別進(jìn)行證明，所以一直也沒(méi)有寫(xiě)，但是今天群里有
朋友問(wèn)到，所以先按照官方手冊(cè)的加上我自己的理解進(jìn)行一下解釋?zhuān)院笠欢ㄒ谠创a級(jí)別進(jìn)行下補(bǔ)充
使用MYSQL版本：5.7.14
OS平臺(tái)： CentOS release 6.5 (Final) 64bit
一、理論基礎(chǔ)
首先要理解幾個(gè)參數(shù)我們必須要先知道下面的內(nèi)容，注意下面內(nèi)容并不深入，而且只是我自己的理解
1、什么是多線程
實(shí)際上MYSQL中的多線程就是POSIX那一套，比如也就是我們說(shuō)的pthread族函數(shù)比如pthread_create、pthread_join、pthread_mutex_lock等等，相信有多線程編程基礎(chǔ)
的朋友不會(huì)陌生，線程也叫輕量級(jí)進(jìn)程(LWP)那么多線程有什么好處，相對(duì)于進(jìn)程而言多線程共享了很多東西比如
1.文件描述符表
2.每種信號(hào)的處理方式
3.當(dāng)前工作目錄
4.用戶(hù)ID和組ID
5.除棧以外的內(nèi)存空間

其實(shí)我們?cè)诰幊痰臅r(shí)候多線程通信都是通過(guò)非棧以外的內(nèi)存進(jìn)程的，比如堆空間，既然線程能夠共享這麼多資源，不管是線程的創(chuàng)建、上下文切換、線程間通信都
變得方便了(注意共享是方便了但是對(duì)臨界區(qū)的管理需要使用類(lèi)似mutex rwlock之類(lèi)的鎖來(lái)實(shí)現(xiàn))。接下來(lái)我們就要來(lái)講講線程間上下文切換
同時(shí)要記住一點(diǎn)線程是CPU調(diào)度的最小單位、進(jìn)程是資源分配的最小單位。配上一張圖


2、線程的上下文切換
我們知道LINUX是一個(gè)多批道多用戶(hù)分時(shí)操作系統(tǒng)，它允許多個(gè)任務(wù)同時(shí)進(jìn)入內(nèi)存CPU通過(guò)時(shí)間輪片的方式進(jìn)行調(diào)度，我們舉例如果我有2核的CPU，但是我當(dāng)前有4
個(gè)同等優(yōu)先級(jí)的MYSQL線程進(jìn)入了就緒隊(duì)列，那么我們同一時(shí)刻能夠并行(注意用詞的準(zhǔn)確性不是并發(fā)是并行)執(zhí)行的MYSQL線程其實(shí)是2個(gè)，另外2個(gè)呢？當(dāng)然就處
于就緒隊(duì)列，等待獲得CPU時(shí)間來(lái)完成工作，等到正在執(zhí)行的2個(gè)線程時(shí)間輪片用完以后這個(gè)時(shí)候需要保留處理器現(xiàn)場(chǎng)，其實(shí)就是保存寄存器的值到內(nèi)存，然后放棄
CPU，進(jìn)入就緒態(tài)，這個(gè)時(shí)候在就緒隊(duì)列的2個(gè)線程可以進(jìn)入CPU進(jìn)行工作了，這種4個(gè)線程并發(fā)執(zhí)行但是只有2個(gè)線程獲得時(shí)間輪片并行執(zhí)行(獲得CPU輪片)在這種不斷
需要獲得CPU輪片-->>工作-->>保存寄存器值到內(nèi)存-->>放棄CPU輪片的方式中我們將保存寄存器值到內(nèi)存這種動(dòng)作叫做線程上下文切換，這是有一定代價(jià)的，當(dāng)然
我的理解也許很片面因?yàn)槲耶吘共皇歉鉒INUX內(nèi)核的。如果同時(shí)需要并發(fā)執(zhí)行的線程越多這種上下文切換的頻率就越大，這也是為什么我們?cè)贚INUX負(fù)載高的時(shí)候能夠觀察
到更多上下文切換的原因(vmstat就可以看到)，那么我們說(shuō)如果限制同一時(shí)刻并發(fā)執(zhí)行的線程數(shù)上下文切換將會(huì)減少，某種意義說(shuō)就是長(zhǎng)痛不如短痛，與其讓你不斷的
進(jìn)行上文切換還不如把你處于睡眠態(tài)放棄CPU使用權(quán)
這里簡(jiǎn)單說(shuō)一下線程的缺點(diǎn)：
線程不穩(wěn)定（庫(kù)函數(shù)實(shí)現(xiàn)）
線程調(diào)試比較困難（gdb支持不好）
信號(hào)使用非常困難
3、小事物線程饑餓問(wèn)題
如果有過(guò)多線程編程使用過(guò)MUTEX，這種搶占試鎖的朋友，一定不會(huì)忘記在某個(gè)線程釋放MUTEX后，其他線程會(huì)以搶占的方式來(lái)獲得，某些線程可能運(yùn)氣不好老是搶不到，如果換成
同優(yōu)先級(jí)線程之間，OS在調(diào)度的時(shí)候如果不均衡，那么某些可能任務(wù)量小的線程老是得不到CPU輪片，而大任務(wù)線程老是獲得CPU輪片，這依賴(lài)于OS的線程調(diào)度策略，這樣就可能形成小
任務(wù)線程饑餓問(wèn)題，與其依賴(lài)OS的調(diào)度策略不如自己設(shè)置一種規(guī)則，讓用到了一定時(shí)間輪片的線程先處于睡眠態(tài)放棄CPU的使用。
二、參數(shù)解釋
好了有了上面的理論知識(shí)可以進(jìn)行這幾個(gè)參數(shù)的解釋了
其實(shí)這三個(gè)參數(shù)就是來(lái)解決上面的問(wèn)題
1、innodb_thread_concurrency
同一時(shí)刻能夠進(jìn)入innodb層次并發(fā)執(zhí)行的線程數(shù)(注意是并發(fā)不是并行)，如果超過(guò)CPU核數(shù)，某些線程可能處于就緒態(tài)而沒(méi)有獲得CPU時(shí)間輪片，如果SERVER層的線程大于這個(gè)值，對(duì)不起多余的
線程將會(huì)被放到一個(gè)叫做wait queue的隊(duì)列中，而不能進(jìn)入INNODB層次，進(jìn)不到innodb層當(dāng)然也就不能干活了，談不上獲得CPU。既然是一個(gè)隊(duì)列那么它必然滿(mǎn)足先進(jìn)入先出的原則。這也是前面說(shuō)的長(zhǎng)痛不如短痛，與其讓你不斷的進(jìn)行上文切換還不如把你處于睡眠態(tài)放棄CPU使用權(quán)，默認(rèn)這個(gè)值是0，代表不限制。
2、innodb_concurrency_tickets
這個(gè)參數(shù)設(shè)置為一種tickets,默認(rèn)是5000，我也不清楚到底它代表多久，從官方文檔來(lái)看它和事物處理的行數(shù)有關(guān)，大事物需要處理的行數(shù)自然更多，小事物當(dāng)然也就越少至少我們可以想成獲得CPU的時(shí)間，干活期間他會(huì)不斷減少，如果減少到0，這個(gè)線程將被提出innodb層次，進(jìn)入前面說(shuō)的等待隊(duì)列，當(dāng)然也就在隊(duì)尾部了，這里假設(shè)有一個(gè)小的事物正在排隊(duì)進(jìn)入innodb層，又或者它已經(jīng)進(jìn)入了innodb層沒(méi)有獲得CPU時(shí)間輪片，突然一個(gè)大的事物tickets耗盡被提出了innodb層，那么這個(gè)小事物就自然而然能夠獲得CPU輪片干活，而小事物執(zhí)行非?？欤瑘?zhí)行完成后
另外的事物又能盡快的獲得CPU干活，不會(huì)由于OS線程調(diào)度不均勻的問(wèn)題而造成的小事物饑餓問(wèn)題，這很好理解。也就是前面我說(shuō)的與其依賴(lài)OS的調(diào)度策略不如自己設(shè)置一種規(guī)則，讓用到了一定時(shí)間輪片的線程先處于睡眠態(tài)放棄CPU的使用。
3、innodb_thread_sleep_delay
這個(gè)參數(shù)從官方手冊(cè)來(lái)看，是代表當(dāng)事物被踢出innodb層次后自己睡眠的時(shí)間，等待睡眠完成后再次進(jìn)入wait que隊(duì)列5.6.3以后可以設(shè)置innodb_adaptive_max_sleep_delay，來(lái)自動(dòng)調(diào)整innodb_thread_sleep_delay，這就更為方便，因?yàn)檫@個(gè)值很難講多少合適，其單位是microseconds，從某種意義上來(lái)講這個(gè)值加劇了大事物執(zhí)行的時(shí)間，小事物也就更加容易進(jìn)入INNODB
層次獲得CPU時(shí)間來(lái)干活。

關(guān)于這幾個(gè)值如果一旦innodb_thread_concurrency設(shè)置為0，其他值的設(shè)置均沒(méi)有效果，這很好理解，設(shè)置為0
后表示不限制，如果不限制也就談不上等待隊(duì)列，沒(méi)有等待隊(duì)列睡眠多久進(jìn)入等待隊(duì)列自然沒(méi)有意義。
如果設(shè)置為0后show engine status的下面值始終為0
0 queries inside InnoDB, 0 queries in queue

這里配上一張自己根據(jù)理解畫(huà)的圖：


下面是官方對(duì)于innodb_thread_concurrency的一個(gè)建議設(shè)置值：
? 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.

至少我們知道如果要設(shè)置innodb_thread_concurrency不應(yīng)該高于CPU核數(shù)很多，比如我們可以設(shè)置1.5倍*CPU核數(shù)。
關(guān)于這一塊也可以參考MYSQL官方手冊(cè)
Section 15.4.6, “Configuring Thread Concurrency for InnoDB”.

三、如何觀察
現(xiàn)在知道的觀察方式主要是show engine innodb status和innodb_trx，其事物狀態(tài)會(huì)為
sleeping before entering InnoDB

為了更好的觀察我這里設(shè)置如下：
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 1     |
+---------------------------+-------+
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_concurrency_tickets | 10    |
+----------------------------+-------+
言外之意我將同一時(shí)刻能夠進(jìn)入innodb干活的線程數(shù)設(shè)置了1，同時(shí)tickets設(shè)置為了10來(lái)盡可能的觀察到這種不斷進(jìn)入innodb
層次，然后tickets到被提出innodb層次的現(xiàn)象，隨后我做了2個(gè)大事物，
好了我在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
這里也明顯的說(shuō)了1個(gè)線程在innodb里面另外一個(gè)在等待隊(duì)列

在innodb_trx中能夠觀察到:

時(shí)間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)

時(shí)間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中可以看到他們不斷的在進(jìn)行輪換的進(jìn)入的innodb層次，同時(shí)我們還能看到
活躍事物trx_concurrency_tickets這個(gè)tickets不斷的減少，而處于sleeping before entering InnoDB
的事物其trx_concurrency_tickets為0。

四、事物等待進(jìn)入innodb層堆棧
雖然沒(méi)有研究源碼但是還是將堆棧打出來(lái)，方便以后研究
#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

作者微信：