MySQL:一個innodb_thread_concurrency設定不當引發的故障
原始碼版本：5.7.22
一、問題來源
歡迎關注我的《深入理解MySQL主從原理 32講 》，如下：
這是一個朋友問我的典型案例。整個故障現象表現為，MySQL資料庫頻繁的出現大量的請求不能響應。下面是一些他提供的證據：
1、show processlist
從狀態資訊來看出現如下情況：
- insert操作：狀態為update
- update/delete操作：狀態為updating
- select操作：狀態為sending data
因此可以推斷應該是語句執行期間出現了問題，由於篇幅原因只給出一部分，並且我將語句部分也做了相應截斷：
show processlist----------------------------
......
11827639 root dbmis Execute 9 updating UPDATE
17224594 root dbmis Execute 8 Sending data SELECT sum(exchange_coin) as exchange_coin FROM
17224595 root dbmis Execute 8 update INSERT INTO
17224596 root dg Execute 8 update INSERT INTO
17224597 root dbmis Execute 8 update INSERT INTO
17224598 root dbmis Execute 7 update INSERT INTO
17224599 root dbmis Execute 7 Sending data SELECT COUNT(*) AS tp_count FROM
17224600 root dg Execute 7 update INSERT INTO
17224601 root dbmis Execute 6 update INSERT INTO
17224602 root dbmis Execute 6 Sending data SELECT sum(exchange_coin) as exchange_coin FROM
17224606 root dbmis Execute 5 update INSERT INTO
17224619 root dbmis Execute 2 update INSERT INTO
17224620 root dbmis Execute 2 update INSERT INTO
17224621 root dbmis Execute 2 Sending data SELECT sum(exchange_coin) as exchange_coin
17224622 root dg Execute 2 update INSERT INTO
17224623 root dbmis Execute 1 update INSERT INTO
17224624 root dbmis Execute 1 update INSERT INTO
17224625 root dg Execute 1 update INSERT INTO
17224626 root dbmis Execute 0 update INSERT INTO
2、系統IO/CPU
從vmstat來看，CPU使用不大，而IO也在可以接受的範圍內（vmstat wa%不高且b列為0）如下：
vmstat--------------------------------------
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
2 0 927300 3057100 0 53487316 0 0 5 192 0 0 3 1 96 0 0
iostat--------------------------------------
Linux 3.10.0-693.el7.x86_64 (fang-data1) 09/23/2019 _x86_64_ (32 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
2.72 0.00 0.52 0.45 0.00 96.31
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sdb 9.73 11.28 3.93 264.54 415.23 2624.20 22.64 0.25 0.93 3.25 0.90 0.80 21.61
sda 10.13 11.59 6.34 264.22 450.68 2624.20 22.73 0.01 0.05 2.55 1.00 0.93 25.19
sdc 11.60 11.36 5.03 263.12 453.02 2592.44 22.71 0.17 0.62 5.08 0.53 0.81 21.60
sde 0.01 0.10 0.11 160.45 6.69 920.23 11.55 0.16 1.01 1.80 1.01 0.83 13.32
sdd 11.26 11.30 2.23 263.18 412.90 2592.44 22.65 0.17 0.65 10.37 0.56 0.82 21.78
md126 0.00 0.00 11.30 468.80 164.79 5216.64 22.42 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.11 58.80 6.69 920.23 31.47 0.15 2.56 1.96 2.56 2.16 12.74
dm-1 0.00 0.00 0.06 0.08 0.24 0.31 8.00 0.01 41.80 1.20 72.78 0.83 0.01
dm-2 0.00 0.00 11.24 408.66 164.55 5216.33 25.63 0.14 0.32 1.02 0.30 0.46 19.29
這就比較奇怪了，一般來說資料庫不能及時響應請求很大可能是由於系統負載過高。如果說DML還可能是Innodb鎖造成的堵塞，但是大量sending data狀態下的select操作一般可能都和系統負載過高有聯絡，但是這裡系統負載還在可以接受的範圍內。
二、pstack分析
藉助pstack檢視執行緒的棧幀，檢視pstack發現如下（由於篇幅限制只給出部分說明問題的部分）：
1、insert 執行緒：
Thread 85 (Thread 0x7fbb0d42b700 (LWP 20174)):
#0 0x00007fbfae164c73 in select () from /lib64/libc.so.6
#1 0x0000000000987c0f in os_thread_sleep (tm=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/os/os0thread.cc:287
#2 0x00000000009e4dea in srv_conc_enter_innodb_with_atomics (trx=trx@entry=0x7fba4802f9c8) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:276
#3 srv_conc_enter_innodb (trx=trx@entry=0x7fba4802f9c8) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:511
#4 0x000000000093b948 in innobase_srv_conc_enter_innodb (trx=0x7fba4802f9c8) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:1280
#5 ha_innobase::write_row (this=0x7fb8440ab260, record=0x7fb8440ab650 "") at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:6793
#6 0x00000000005b440f in handler::ha_write_row (this=0x7fb8440ab260, buf=0x7fb8440ab650 "") at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:7351
#7 0x00000000006dd3a8 in write_record (thd=thd@entry=0x1d396c90, table=table@entry=0x7fb8440aa970, info=info@entry=0x7fbb0d429400, update=update@entry=0x7fbb0d429480) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_insert.cc:1667
#8 0x00000000006e2541 in mysql_insert (thd=thd@entry=0x1d396c90, table_list=<optimized out>, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_REPLACE, ignore=false) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_insert.cc:1072
#9 0x00000000006fa90a in mysql_execute_command (thd=thd@entry=0x1d396c90) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_parse.cc:3500
2、update執行緒
Thread 81 (Thread 0x7fbb24b67700 (LWP 27490)):
#0 0x00007fbfae164c73 in select () from /lib64/libc.so.6
#1 0x0000000000987c0f in os_thread_sleep (tm=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/os/os0thread.cc:287
#2 0x00000000009e4dea in srv_conc_enter_innodb_with_atomics (trx=trx@entry=0x7fb94003c608) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:276
#3 srv_conc_enter_innodb (trx=trx@entry=0x7fb94003c608) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:511
#4 0x000000000093ae4e in innobase_srv_conc_enter_innodb (trx=0x7fb94003c608) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:1280
#5 ha_innobase::index_read (this=0x7fb95c05b540, buf=0x7fb95c2ae4f0 "\377\377\377", key_ptr=<optimized out>, key_len=<optimized out>, find_flag=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:7675
#6 0x00000000005ab6e0 in ha_index_read_map (find_flag=HA_READ_KEY_EXACT, keypart_map=3, key=0x7fb940017048 "7\307\017e\257h", buf=<optimized out>, this=0x7fb95c05b540) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:2753
#7 handler::read_range_first (this=0x7fb95c05b540, start_key=<optimized out>, end_key=<optimized out>, eq_range_arg=<optimized out>, sorted=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:6717
#8 0x00000000005aa206 in handler::multi_range_read_next (this=0x7fb95c05b540, range_info=0x7fbb24b65240) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:5871
#9 0x0000000000804acb in QUICK_RANGE_SELECT::get_next (this=0x7fb94000f720) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/opt_range.cc:10644
#10 0x000000000082ae2d in rr_quick (info=0x7fbb24b65410) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/records.cc:369
#11 0x0000000000766e1b in mysql_update (thd=thd@entry=0x1d1f2250, table_list=<optimized out>, fields=..., values=..., conds=0x7fb9400009c8, order_num=<optimized out>, order=<optimized out>, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=found_return@entry=0x7fbb24b65800, updated_return=updated_return@entry=0x7fbb24b65d60) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_update.cc:744
3、select執行緒
Thread 66 (Thread 0x7fbb3c355700 (LWP 16028)):
#0 0x00007fbfae164c73 in select () from /lib64/libc.so.6
#1 0x0000000000987c0f in os_thread_sleep (tm=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/os/os0thread.cc:287
#2 0x00000000009e4dea in srv_conc_enter_innodb_with_atomics (trx=trx@entry=0x7fb988354858) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:276
#3 srv_conc_enter_innodb (trx=trx@entry=0x7fb988354858) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:511
#4 0x000000000093ae4e in innobase_srv_conc_enter_innodb (trx=0x7fb988354858) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:1280
#5 ha_innobase::index_read (this=0x7fb9880e33a0, buf=0x7fb988351b50 "\377\377\377\377", key_ptr=<optimized out>, key_len=<optimized out>, find_flag=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:7675
#6 0x00000000005ab6e0 in ha_index_read_map (find_flag=HA_READ_AFTER_KEY, keypart_map=7, key=0x7fb988134a48 "", buf=<optimized out>, this=0x7fb9880e33a0) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:2753
#7 handler::read_range_first (this=0x7fb9880e33a0, start_key=<optimized out>, end_key=<optimized out>, eq_range_arg=<optimized out>, sorted=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:6717
#8 0x00000000005aa206 in handler::multi_range_read_next (this=0x7fb9880e33a0, range_info=0x7fbb3c353400) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:5871
#9 0x0000000000804acb in QUICK_RANGE_SELECT::get_next (this=0x7fb988002050) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/opt_range.cc:10644
#10 0x000000000082ae2d in rr_quick (info=0x7fb98809c210) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/records.cc:369
#11 0x00000000006d44fd in sub_select (join=0x7fb98809a728, join_tab=0x7fb98809c180, end_of_records=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_executor.cc:1259
#12 0x00000000006d2823 in do_select (join=0x7fb98809a728) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_executor.cc:936
#13 JOIN::exec (this=0x7fb98809a728) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_executor.cc:194
好了有了這些棧幀視乎發現一些共同點他們都處於innobase_srv_conc_enter_innodb函式下，本函式正是下面引數實現的方式：
- innodb_thread_concurrency
- innodb_concurrency_tickets
所以我隨即告訴他檢查這兩個引數，如果設定了可以嘗試取消。過後資料庫故障得到解決。
三、引數和相關說明
實際上涉及到的引數主要是innodb_thread_concurrency和innodb_concurrency_tickets。將高壓力下執行緒之間搶佔CPU而造成執行緒上下文切換的情況儘量阻塞在Innodb層之外，這就需要innodb_thread_concurrency引數了。同時又要保證對於那些（長時間處理執行緒）不會長時間的堵塞（短時間處理執行緒），比如某些select操作需要查詢很久，而某些select操作查詢量很小，如果等待（長時間的select操作）結束後（短時間select操作）才執行，那麼顯然會出現（短時間select操作）飢餓問題，換句話說對（短時間select操作）是不公平的， 因此就引入了innodb_concurrency_tickets引數。
1、innodb_thread_concurrency
同一時刻能夠進入Innodb層的會話（執行緒）數。如果在Innodb層幹活的會話（執行緒）數量超過這個引數的設定，新會話（執行緒）將不能從MySQL層進入到Innodb層，它們將進入一個短暫的睡眠狀態。休眠多久則通過引數innodb_thread_sleep_delay引數指定，如果還設定了引數innodb_adaptive_max_sleep_delay那麼Innodb將會自動調整休眠時間，具體的演算法實際上就在srv_conc_enter_innodb_with_atomics函式中，感興趣的可以執行檢視。
其次這種休眠實際上是一個定時醒來的時鐘，通過::nanosleep或者select（多路IO轉接函式）進行實現，定時喚醒後會話（執行緒）重新判斷是否可以進入Innodb層。函式os_thread_sleep部分如下：
#elif defined(HAVE_NANOSLEEP)
struct timespec t;
t.tv_sec = tm / 1000000;
t.tv_nsec = (tm % 1000000) * 1000;
::nanosleep(&t, NULL);
#else
struct timeval t;
t.tv_sec = tm / 1000000;
t.tv_usec = tm % 1000000;
select(0, NULL, NULL, NULL, &t);
關於到底如何設定這個值，官方文件有如下建議：
Use the following guidelines to help find and maintain an appropriate setting:
- 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 then 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 InnoDB to use more than a certain number of virtual CPUs for user threads
(20 virtual CPUs, 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 mysqld process exclusively to the virtual CPUs. Be aware,
however, that exclusive binding could result in non-optimal hardware usage if the mysqld process
is not consistently busy. In this case, you might bind the mysqld process to the virtual CPUs but
also allow other applications to use some or all of the virtual CPUs.
- innodb_thread_concurrency values that are too high can cause performance regression due
to increased contention on system internals and resources.
- In some cases, the optimal innodb_thread_concurrency setting can be smaller than the
number of virtual CPUs.
- Monitor and analyze your system regularly. Changes to workload, number of users, or computing
environment may require that you adjust the innodb_thread_concurrency setting
可以發現要合理的設定這個值並不那麼容易並且要求較高。
2、innodb_concurrency_tickets
實際上這裡的tickets可以理解為MySQL層和Innodb層互動的次數，比如一個select一條資料就是需要Innodb層返回一條資料然後MySQL層進行where條件的過濾然後返回給客戶端，拋開where條件過濾的情況，如果我們一條語句需要查詢100條資料，那麼實際上需要進入Innodb層100次，那麼實際上消耗的tickets就是100。當然對於insert select這種操作，需要的tickets是普通select的兩倍，因為查詢需要進入Innodb層一次，insert需要再次進入Innodb層一次，後面我們就使用insert select的方式來模擬堵塞的情況，最後還會給出說明。
這樣我們也就理解為什麼innodb_concurrency_tickets可以避免（長時間處理執行緒）長時間堵塞（短時間處理執行緒）的原因了。假設innodb_concurrency_tickets為5000（預設值），有一個需要查詢100W行資料的大select操作和一個需要查詢100行資料的小select操作，大select操作先進行，但是當查詢了5000行資料後將丟失CPU使用權，小select操作將會進行並且一次性完成。
最後關於這裡涉及的引數可以繼續參考官方文件中的說明，我們線上並沒有設定這些引數，因為感覺很難設定合適，如果設定不當反而會遇到問題，就如本案例一樣。
3、事務操作狀態
實際上如果是處於這種堵塞情況，我們完全可以在information_schema.innodb_trx和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
mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G
*************************** 1. row ***************************
trx_id: 84325
trx_state: RUNNING
trx_query: insert into baguait4 select * from testgp
trx_operation_state: sleeping before entering InnoDB（這裡）
trx_concurrency_tickets: 0
*************************** 2. row ***************************
trx_id: 84319
trx_state: RUNNING
trx_query: insert into baguait3 select * from testgp
trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
我們可以看到事務操作狀態被標記為‘sleeping before entering InnoDB’。但是需要注意一點的是對於只讀事務比如select操作而言，show engine innodb status可能看不到。但是遺憾的是案例中朋友並沒有採集trx_operation_state的值。
四、模擬測試
這裡我們簡單模擬，我們一共啟用3個事務，其中兩個insert select操作，一個單純的select操作，當然這裡的都是耗時操作，涉及的表每個表都有大概100W的資料。
同時為了方便觀察我們需要設定引數：
- innodb_thread_concurrency=1
- innodb_concurrency_tickets=10
操作步驟如下：
| S1 | S2 | S3 |
|---|---|---|
| insert into baguait4 select * from testgp | ||
| insert into baguait3 select * from testgp | ||
| select * from baguait1 |
如果多觀察幾次你可以看到如下的現象：
mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G show processlist;
*************************** 1. row ***************************
trx_id: 84529
trx_state: RUNNING
trx_query: insert into baguait4 select * from testgp
trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
*************************** 2. row ***************************
trx_id: 84524
trx_state: RUNNING
trx_query: insert into baguait3 select * from testgp
trx_operation_state: inserting
trx_concurrency_tickets: 1
*************************** 3. row ***************************
trx_id: 422211785606640
trx_state: RUNNING
trx_query: select * from baguait1
trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
3 rows in set (0.00 sec)
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 3173 | Waiting on empty queue | NULL | 0 | 0 |
| 6 | root | localhost | testmts | Query | 70 | Sending data | insert into baguait3 select * from testgp | 0 | 0 |
| 7 | root | localhost | testmts | Query | 68 | Sending data | insert into baguait4 select * from testgp | 0 | 0 |
| 8 | root | localhost | testmts | Query | 66 | Sending data | select * from baguait1 | 120835 | 0 |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G show processlist;
*************************** 1. row ***************************
trx_id: 84529
trx_state: RUNNING
trx_query: insert into baguait4 select * from testgp
trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
*************************** 2. row ***************************
trx_id: 84524
trx_state: RUNNING
trx_query: insert into baguait3 select * from testgp
trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
*************************** 3. row ***************************
trx_id: 422211785606640
trx_state: RUNNING
trx_query: select * from baguait1
trx_operation_state: fetching rows
trx_concurrency_tickets: 3
3 rows in set (0.00 sec)
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 3177 | Waiting on empty queue | NULL | 0 | 0 |
| 6 | root | localhost | testmts | Query | 74 | Sending data | insert into baguait3 select * from testgp | 0 | 0 |
| 7 | root | localhost | testmts | Query | 72 | Sending data | insert into baguait4 select * from testgp | 0 | 0 |
| 8 | root | localhost | testmts | Query | 70 | Sending data | select * from baguait1 | 128718 | 0 |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
5 rows in set (0.00 sec)
我們可以觀察到trx_operation_state的狀態3個操作都在交替的變化，但是總有2個處於‘sleeping before entering InnoDB’狀態。並且我們可以觀察到trx_concurrency_tickets總是不會大於10的。因此我們有理由相信在同一時刻只有一個操作進入了Innodb層。但是需要注意的是在show engine innodb status中觀察不到select的操作如下：
------------
TRANSACTIONS
------------
Trx id counter 84538
Purge done for trx's n:o < 84526 undo n:o < 0 state: running but idle
History list length 356
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422211785609424, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 422211785608032, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 84529, ACTIVE 103 sec inserting, thread declared inside InnoDB 6
mysql tables in use 2, locked 1
1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 111866
MySQL thread id 7, OS thread handle 140737158833920, query id 80 localhost root Sending data
insert into baguait4 select * from testgp
Trx read view will not see trx with id >= 84529, sees < 84524
---TRANSACTION 84524, ACTIVE 105 sec sleeping before entering InnoDB
mysql tables in use 2, locked 1
1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 105605
MySQL thread id 6, OS thread handle 140737159034624, query id 79 localhost root Sending data
insert into baguait3 select * from testgp
Trx read view will not see trx with id >= 84524, sees < 84524
但是我們還需要注意show engine innodb status有如下輸出第一行說明了有2個會話（執行緒）堵塞在Innodb層以外。
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 2 queries in queue
3 read views open inside InnoDB
2 RW transactions active inside InnoDB
五、實現方法
前面我們已經描述了每次MySQL層和Innodb層的互動都會進行一次這樣的判斷，它用來決定會話（執行緒）是否能夠進入Innodb層，下面就是大概的邏輯，由函式innobase_srv_conc_enter_innodb調入。
->是否設定了引數innodb_thread_concurrency
->是
->是否tickets大於0
->是、直接進入Innodb層並且tickets減1
->否、調入函式srv_conc_enter_innodb
->調入函式srv_conc_enter_innodb_with_atomics
->開啟死迴圈
->是否活躍執行緒數小於innodb_thread_concurrency設定
->是、增加活躍執行緒數，並且自動調整delay引數，退出死迴圈，滿tickets進入Innodb層
->否、自動調整delay引數後設定事務操作狀態為"sleeping before entering InnoDB"，然後進入休眠狀態知道時間達到後重新醒來繼續迴圈
->否、直接進入Innodb層
我們可以看到這個實現方式，在Inndob以外的會話（執行緒）會一直等待直到Inndob層內活躍的執行緒數小於innodb_thread_concurrency為止，並且每次進入Innodb層都會將tickets減1。
其他：關於insert select操作消耗tickets的說明
這裡額外說明一下，因為我在測試的時候看了一下，對於一行資料而言首先需要select查詢出來然後再insert插入到表中，這裡實際上一行資料涉及到進入Innodb層兩次，那麼就需要消耗2個tickets，下面留下兩個棧幀供自己後面參考：
1、insert select查詢資料進入Innodb層
#0 innobase_srv_conc_enter_innodb (prebuilt=0x7ffedcb98d10) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:1740
#1 0x0000000001a53f7c in ha_innobase::general_fetch (this=0x7ffedcb9d760, buf=0x7ffedc9469b0 "\375\n", direction=1, match_mode=0)
at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9846
#2 0x0000000001a545ee in ha_innobase::rnd_next (this=0x7ffedcb9d760, buf=0x7ffedc9469b0 "\375\n")
at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:10083
#3 0x0000000000f836d6 in handler::ha_rnd_next (this=0x7ffedcb9d760, buf=0x7ffedc9469b0 "\375\n") at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:3146
#4 0x00000000014e2a55 in rr_sequential (info=0x7ffedcb4f120) at /mysqldata/percona-server-locks-detail-5.7.22/sql/records.cc:521
#5 0x0000000001581277 in sub_select (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f0d0, end_of_records=false)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1280
#6 0x0000000001580be6 in do_select (join=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950
#7 0x000000000157eaa2 in JOIN::exec (this=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199
#8 0x0000000001620327 in handle_query (thd=0x7ffedc012960, lex=0x7ffedc014f90, result=0x7ffedcc46680, added_options=1342177280, removed_options=0)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185
#9 0x000000000180466d in Sql_cmd_insert_select::execute (this=0x7ffedcc46608, thd=0x7ffedc012960)
2、insert select插入資料進入Innodb層
#0 innobase_srv_conc_enter_innodb (prebuilt=0x7ffedcb9c6f0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:1740
#1 0x0000000001a50587 in ha_innobase::write_row (this=0x7ffedc946470, record=0x7ffedcb78d00 "\375\n")
at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:8341
#2 0x0000000000f9041d in handler::ha_write_row (this=0x7ffedc946470, buf=0x7ffedcb78d00 "\375\n") at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:8466
#3 0x00000000018004b9 in write_record (thd=0x7ffedc012960, table=0x7ffedcb8f940, info=0x7ffedcc466c8, update=0x7ffedcc46740)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:1881
#4 0x00000000018019b9 in Query_result_insert::send_data (this=0x7ffedcc46680, values=...) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:2279
#5 0x00000000015853a8 in end_send (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f248, end_of_records=false)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2925
#6 0x0000000001581f71 in evaluate_join_record (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f0d0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1645
#7 0x0000000001581372 in sub_select (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f0d0, end_of_records=false)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297
#8 0x0000000001580be6 in do_select (join=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950
#9 0x000000000157eaa2 in JOIN::exec (this=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199
#10 0x0000000001620327 in handle_query (thd=0x7ffedc012960, lex=0x7ffedc014f90, result=0x7ffedcc46680, added_options=1342177280, removed_options=0)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185
#11 0x000000000180466d in Sql_cmd_insert_select::execute (this=0x7ffedcc46608, thd=0x7ffedc012960)
實際上插入資料正是在查詢完資料後呼叫函式evaluate_join_record的時候，通過回撥了函式Query_result_insert::send_data來實現，這點和單純的select不一樣單純的select這裡調入是函式Query_result_send::send_data如下：
#0 Query_result_send::send_data (this=0x7ffedcc465f8, items=...) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_class.cc:2915
#1 0x00000000015853a8 in end_send (join=0x7ffedcb4e930, qep_tab=0x7ffedcb4f4b0, end_of_records=false)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2925
#2 0x0000000001581f71 in evaluate_join_record (join=0x7ffedcb4e930, qep_tab=0x7ffedcb4f338) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1645
#3 0x0000000001581372 in sub_select (join=0x7ffedcb4e930, qep_tab=0x7ffedcb4f338, end_of_records=false)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297
#4 0x0000000001580be6 in do_select (join=0x7ffedcb4e930) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950
#5 0x000000000157eaa2 in JOIN::exec (this=0x7ffedcb4e930) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199
#6 0x0000000001620327 in handle_query (thd=0x7ffedc012960, lex=0x7ffedc014f90, result=0x7ffedcc465f8, added_options=0, removed_options=0)
at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185
#7 0x00000000015d1f77 in execute_sqlcom_select (thd=0x7ffedc012960, all_tables=0x7ffedcc45cf0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5445
作者微信：gp_22389860
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2658990/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql interactive_timeout 設定不當一例MySql
- 一個ES設定操作引發的“血案”
- 一次交換空間設定不合理引發的故障
- 一個單引號引發的 MySQL 效能損失MySql
- 【故障公告】redis 伺服器當機引發部落格站點故障Redis伺服器
- MySQL 中一個雙引號的錯位引發的血案MySql
- OGG DDL觸發器引發的故障系列（一）觸發器
- MySQL的配置檔案的引數設定MySql
- 【ASM】RAC19C因引數設定不當，asm無法啟動ASM
- MySQL 8.0因關閉Gtid 引發從庫故障MySql
- ORA-00130 引發的故障
- 事故現場：MySQL 中一個雙引號的錯位引發的血案MySql
- MySQL不區分大小寫設定MySql
- pga_aggregate_limit設定不合理的一個可能的原因是和processes引數不匹配MIT
- [轉]MySQL的datetime設定當前時間為預設值及 triger 一例MySql
- remote_listener引發的故障分析REM
- 關於 MySQL 時區設定的一個常用 sql 命令MySql
- 系統引數nofile設定不生效問題
- 一個commit引發的思考MIT
- 一個排序引發的BUG排序
- MySQL表名不區分大小寫的設定方法MySql
- 由OGG引發的資料庫故障資料庫
- Vue一個案例引發的「程式設計式」建立元件Vue程式設計元件
- 關於MySql 設定一個間隔時間 執行一個事件MySql事件
- 記一次 Redis Cluster 當機引發的事故Redis
- 由一個emoji引發的思考
- 一個 List.of 引發的“血案”
- 這是一個不確定的時代
- 【故障公告】部落格系統升級到 .NET 5.0 引發的故障
- Redis Cluster 當機引發的事故Redis
- 一個引發程式設計師們幹架的問題程式設計師
- 詳述一條SQL引發的高CPU故障處理過程SQL
- mysql怎麼設定密碼都不正確的一個解決方法MySql密碼
- mysql的"雙1設定"-資料安全的關鍵引數（案例分享）MySql
- 一個不讓用加號的需求而引發的Java位運算詳解Java
- Oracle DBLink bug引發的故障（Session Hang Memory leak）OracleSession
- 一個map函式引發的血案函式
- 一個UPDATE語句引發的血案