當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql 1280_技术分享 | MySQL 一次奇怪的故障分析

發(fā)布時(shí)間：2024/4/18 数据库 31 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql 1280_技术分享 | MySQL 一次奇怪的故障分析小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

一、問題來源

這是一個(gè)朋友問我的典型案例。整個(gè)故障現(xiàn)象表現(xiàn)為，MySQL 數(shù)據(jù)庫頻繁的出現(xiàn)大量的請(qǐng)求不能響應(yīng)。下面是一些他提供的證據(jù)：

1. show processlist從狀態(tài)信息來看出現(xiàn)如下情況：insert 操作：狀態(tài)為 update

update/delete 操作：狀態(tài)為 updating

select 操作：狀態(tài)為 sending data

因此可以推斷應(yīng)該是語句執(zhí)行期間出現(xiàn)了問題，由于篇幅原因只給出一部分，并且我將語句部分也做了相應(yīng)截?cái)?#xff1a;show?processlist----------------------------......11827639????root????dbmis???Execute?9???updating????UPDATE17224594????root????dbmis???Execute?8???Sending?data????SELECT?sum(exchange_coin)?as?exchange_coin?FROM17224595????root????dbmis???Execute?8???update??INSERT?INTO17224596????root????dg??Execute?8???update??INSERT?INTO17224597????root????dbmis???Execute?8???update??INSERT?INTO17224598????root????dbmis???Execute?7???update??INSERT?INTO17224599????root????dbmis???Execute?7???Sending?data????SELECT?COUNT(*)?AS?tp_count?FROM17224600????root????dg??Execute?7???update??INSERT?INTO17224601????root????dbmis???Execute?6???update??INSERT?INTO17224602????root????dbmis???Execute?6???Sending?data????SELECT?sum(exchange_coin)?as?exchange_coin?FROM17224606????root????dbmis???Execute?5???update??INSERT?INTO17224619????root????dbmis???Execute?2???update??INSERT?INTO17224620????root????dbmis???Execute?2???update??INSERT?INTO17224621????root????dbmis???Execute?2???Sending?data????SELECT?sum(exchange_coin)?as?exchange_coin17224622????root????dg??Execute?2???update??INSERT?INTO17224623????root????dbmis???Execute?1???update??INSERT?INTO17224624????root????dbmis???Execute?1???update??INSERT?INTO17224625????root????dg??Execute?1???update??INSERT?INTO17224626????root????dbmis???Execute?0???update??INSERT?INTO

2. 系統(tǒng) IO/CPU

從 vmstat 來看，CPU 使用不大，而 IO 也在可以接受的范圍內(nèi)(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

這就比較奇怪了，一般來說數(shù)據(jù)庫不能及時(shí)響應(yīng)請(qǐng)求很大可能是由于系統(tǒng)負(fù)載過高。如果說 DML 還可能是 Innodb 鎖造成的堵塞，但是大量 sending data 狀態(tài)下的 select 操作一般可能都和系統(tǒng)負(fù)載過高有聯(lián)系，但是這里系統(tǒng)負(fù)載還在可以接受的范圍內(nèi)。

二、pstack 分析借助 pstack 查看線程的棧幀，查看 pstack 發(fā)現(xiàn)如下(由于篇幅限制只給出部分說明問題的部分)：

1. insert 線程：Thread?85?(Thread?0x7fbb0d42b700?(LWP?20174)):#0??0x00007fbfae164c73?in?select?()?from?/lib64/libc.so.6#1??0x0000000000987c0f?in?os_thread_sleep?(tm=)?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=,?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=)?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=,?key_len=,?find_flag=)?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=,?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=,?end_key=,?eq_range_arg=,?sorted=)?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=,?fields=...,?values=...,?conds=0x7fb9400009c8,?order_num=,?order=,?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=)?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=,?key_len=,?find_flag=)?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=,?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=,?end_key=,?eq_range_arg=,?sorted=)?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=)?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

好了有了這些棧幀視乎發(fā)現(xiàn)一些共同點(diǎn)他們都處于 innobase_srv_conc_enter_innodb 函數(shù)下，本函數(shù)正是下面參數(shù)實(shí)現(xiàn)的方式：innodb_thread_concurrency

innodb_concurrency_tickets

所以我隨即告訴他檢查這兩個(gè)參數(shù)，如果設(shè)置了可以嘗試取消。過后數(shù)據(jù)庫故障得到解決。

三、參數(shù)和相關(guān)說明實(shí)際上涉及到的參數(shù)主要是 innodb_thread_concurrency 和 innodb_concurrency_tickets。將高壓力下線程之間搶占 CPU 而造成線程上下文切換的情況盡量阻塞在 Innodb 層之外，這就需要 innodb_thread_concurrency 參數(shù)了。同時(shí)又要保證對(duì)于那些(長時(shí)間處理線程)不會(huì)長時(shí)間的堵塞(短時(shí)間處理線程)，比如某些 select 操作需要查詢很久，而某些 select 操作查詢量很小，如果等待(長時(shí)間的 select 操作)結(jié)束后(短時(shí)間 select 操作)才執(zhí)行，那么顯然會(huì)出現(xiàn)(短時(shí)間 select 操作)饑餓問題，換句話說對(duì)(短時(shí)間 select 操作)是不公平的，因此就引入了 innodb_concurrency_tickets 參數(shù)。

1. innodb_thread_concurrency

同一時(shí)刻能夠進(jìn)入 Innodb 層的會(huì)話(線程)數(shù)。如果在 Innodb 層干活的會(huì)話(線程)數(shù)量超過這個(gè)參數(shù)的設(shè)置，新會(huì)話(線程)將不能從 MySQL 層進(jìn)入到 Innodb 層，它們將進(jìn)入一個(gè)短暫的睡眠狀態(tài)。休眠多久則通過參數(shù) innodb_thread_sleep_delay 參數(shù)指定，如果還設(shè)置了參數(shù) innodb_adaptive_max_sleep_delay 那么 Innodb 將會(huì)自動(dòng)調(diào)整休眠時(shí)間，具體的算法實(shí)際上就在 srv_conc_enter_innodb_with_atomics 函數(shù)中，感興趣的可以執(zhí)行查看。其次這種休眠實(shí)際上是一個(gè)定時(shí)醒來的時(shí)鐘，通過 ::nanosleep 或者 select(多路 IO 轉(zhuǎn)接函數(shù))進(jìn)行實(shí)現(xiàn)，定時(shí)喚醒后會(huì)話(線程)重新判斷是否可以進(jìn)入 Innodb 層。函數(shù) 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);

關(guān)于到底如何設(shè)置這個(gè)值，官方文檔有如下建議：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,?setinnodb_thread_concurrency=0.-?If?your?workload?is?consistently?heavy?or?occasionally?spikes,?start?by?settinginnodb_thread_concurrency=128?and?then?lowering?the?value?to?96,?80,?64,?and?so?on,?untilyou?find?the?number?of?threads?that?provides?the?best?performance.?For?example,?suppose?yoursystem?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?abovethis?number.?In?this?case,?you?would?set?innodb_thread_concurrency=80?to?avoid?impactingperformance.-?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?possiblylower,?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?processis?not?consistently?busy.?In?this?case,?you?might?bind?the?mysqld?process?to?the?virtual?CPUs?butalso?allow?other?applications?to?use?some?or?all?of?the?virtual?CPUs.-?innodb_thread_concurrency?values?that?are?too?high?can?cause?performance?regression?dueto?increased?contention?on?system?internals?and?resources.-?In?some?cases,?the?optimal?innodb_thread_concurrency?setting?can?be?smaller?than?thenumber?of?virtual?CPUs.-?Monitor?and?analyze?your?system?regularly.?Changes?to?workload,?number?of?users,?or?computingenvironment?may?require?that?you?adjust?the?innodb_thread_concurrency?setting

可以發(fā)現(xiàn)要合理的設(shè)置這個(gè)值并不那么容易并且要求較高。

2. innodb_concurrency_tickets實(shí)際上這里的 tickets 可以理解為 MySQL 層和 Innodb 層交互的次數(shù)，比如一個(gè) select 一條數(shù)據(jù)就是需要 Innodb 層返回一條數(shù)據(jù)然后 MySQL 層進(jìn)行 where 條件的過濾然后返回給客戶端，拋開 where 條件過濾的情況，如果我們一條語句需要查詢 100 條數(shù)據(jù)，那么實(shí)際上需要進(jìn)入 Innodb 層 100 次，那么實(shí)際上消耗的 tickets 就是 100。當(dāng)然對(duì)于 insert select 這種操作，需要的 tickets 是普通 select 的兩倍，因?yàn)椴樵冃枰M(jìn)入 Innodb 層一次，insert 需要再次進(jìn)入 Innodb 層一次，后面我們就使用 insert select 的方式來模擬堵塞的情況，最后還會(huì)給出說明。這樣我們也就理解為什么 innodb_concurrency_tickets 可以避免(長時(shí)間處理線程)長時(shí)間堵塞(短時(shí)間處理線程)的原因了。假設(shè) innodb_concurrency_tickets 為 5000(默認(rèn)值)，有一個(gè)需要查詢 100W 行數(shù)據(jù)的大 select 操作和一個(gè)需要查詢 100 行數(shù)據(jù)的小 select 操作，大 select 操作先進(jìn)行，但是當(dāng)查詢了 5000 行數(shù)據(jù)后將丟失CPU使用權(quán)，小 select 操作將會(huì)進(jìn)行并且一次性完成。最后關(guān)于這里涉及的參數(shù)可以繼續(xù)參考官方文檔中的說明，我們線上并沒有設(shè)置這些參數(shù)，因?yàn)楦杏X很難設(shè)置合適，如果設(shè)置不當(dāng)反而會(huì)遇到問題，就如本案例一樣。

3. 事務(wù)操作狀態(tài)

實(shí)際上如果是處于這種堵塞情況，我們完全可以在 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

我們可以看到事務(wù)操作狀態(tài)被標(biāo)記為sleeping before entering InnoDB。但是需要注意一點(diǎn)的是對(duì)于只讀事務(wù)比如 select 操作而言，show engine innodb status 可能看不到。但是遺憾的是案例中朋友并沒有采集 trx_operation_state 的值。

四、模擬測試這里我們簡單模擬，我們一共啟用3個(gè)事務(wù)，其中兩個(gè) insert select 操作，一個(gè)單純的 select 操作，當(dāng)然這里的都是耗時(shí)操作，涉及的表每個(gè)表都有大概 100W 的數(shù)據(jù)。

同時(shí)為了方便觀察我們需要設(shè)置參數(shù)：innodb_thread_concurrency=1

innodb_concurrency_tickets=10

操作步驟如下：

如果多觀察幾次你可以看到如下的現(xiàn)象：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)

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

5 rows in set (0.00 sec)

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)

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+

5 rows in set (0.00 sec)

我們可以觀察到 trx_operation_state 的狀態(tài) 3 個(gè)操作都在交替的變化，但是總有 2 個(gè)處于sleeping before entering InnoDB狀態(tài)。并且我們可以觀察到 trx_concurrency_tickets 總是不會(huì)大于 10 的。因此我們有理由相信在同一時(shí)刻只有一個(gè)操作進(jìn)入了 Innodb 層。但是需要注意的是在 show engine innodb status 中觀察不到 select 的操作如下：------------TRANSACTIONS------------Trx?id?counter?84538Purge?done?for?trx's?n:o?=?84529,?sees?=?84524,?sees?

但是我們還需要注意 show engine innodb status 有如下輸出第一行說明了有 2 個(gè)會(huì)話(線程)堵塞在 Innodb 層以外。--------------ROW?OPERATIONS--------------1?queries?inside?InnoDB,?2?queries?in?queue3?read?views?open?inside?InnoDB2?RW?transactions?active?inside?InnoDB

五、實(shí)現(xiàn)方法前面我們已經(jīng)描述了每次 MySQL 層和 Innodb 層的交互都會(huì)進(jìn)行一次這樣的判斷，它用來決定會(huì)話(線程)是否能夠進(jìn)入 Innodb 層，下面就是大概的邏輯，由函數(shù) innobase_srv_conc_enter_innodb 調(diào)入。->是否設(shè)置了參數(shù)innodb_thread_concurrency??->是?????->是否tickets大于0????????->是、直接進(jìn)入Innodb層并且tickets減1????????->否、調(diào)入函數(shù)srv_conc_enter_innodb???????????->調(diào)入函數(shù)srv_conc_enter_innodb_with_atomics??????????????->開啟死循環(huán)?????????????????->是否活躍線程數(shù)小于innodb_thread_concurrency設(shè)置????????????????????->是、增加活躍線程數(shù)，并且自動(dòng)調(diào)整delay參數(shù)，退出死循環(huán)，滿tickets進(jìn)入Innodb層????????????????????->否、自動(dòng)調(diào)整delay參數(shù)后設(shè)置事務(wù)操作狀態(tài)為"sleeping?before?entering?InnoDB"，然后進(jìn)入休眠狀態(tài)直到時(shí)間達(dá)到后重新醒來繼續(xù)循環(huán)??->否、直接進(jìn)入Innodb層

我們可以看到這個(gè)實(shí)現(xiàn)方式，在 Inndob 以外的會(huì)話(線程)會(huì)一直等待直到 Inndob 層內(nèi)活躍的線程數(shù)小于 innodb_thread_concurrency 為止，并且每次進(jìn)入 Innodb 層都會(huì)將 tickets 減 1。

其他：關(guān)于 insert select 操作消耗 tickets 的說明這里額外說明一下，因?yàn)槲以跍y試的時(shí)候看了一下，對(duì)于一行數(shù)據(jù)而言首先需要 select 查詢出來然后再 insert 插入到表中，這里實(shí)際上一行數(shù)據(jù)涉及到進(jìn)入 Innodb 層兩次，那么就需要消耗 2 個(gè) tickets，下面留下兩個(gè)棧幀供自己后面參考：

1. insert select 查詢數(shù)據(jù)進(jìn)入 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 插入數(shù)據(jù)進(jìn)入 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)

實(shí)際上插入數(shù)據(jù)正是在查詢完數(shù)據(jù)后調(diào)用函數(shù) evaluate_join_record 的時(shí)候，通過回調(diào)了函數(shù) Query_result_insert::send_data 來實(shí)現(xiàn)，這點(diǎn)和單純的 select 不一樣單純的 select 這里調(diào)入是函數(shù) 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

最后推薦高鵬的專欄《深入理解 MySQL 主從原理 32 講》，想要透徹了解學(xué)習(xí) MySQL 主從原理的朋友不容錯(cuò)過。

總結(jié)

以上是生活随笔為你收集整理的mysql 1280_技术分享 | MySQL 一次奇怪的故障分析的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： shiro注销其他用户_Shiro实现互
下一篇： mysql8坑太多_mysql8安装遇到

3atv精品不卡视频,97人人超碰国产精品最新,中文字幕av一区二区三区人妻少妇,久久久精品波多野结衣,日韩一区二区三区精品

数据库

mysql 1280_技术分享 | MySQL 一次奇怪的故障分析

總結(jié)