故障分析 ｜ Federated 存储引擎表导致监控线程处于 Opening table 状态

1、故障分析| Federated存储引擎表 导致监控线程处于Opening table状态最近发现客户的一台MySQL 5. 7. 32实例的监控线程状态一直处于 Opening table 状态，且都是在对 informalion_schema. tables 表做相 关查询，如图：127.00,l:5S004I NULL1 Query164I Opening tablesI SELECT TABLE_SCMMAt TABLE JAME.TABLEJOWS. DATAJ.ENGTM. INOOCLEHGTH, DAT127.00l:S5008| NULL1 Query193I Opening

2、tablesI SELECT TASLE.SCHEMA, TABLE.NAME,TABLEJOWS. DATA_LENGTH, INDEX.LENGTH, DATA.F127.001:SSO1O| NULL1 Qury223I Opening tablesI SELECT TA8LE_SCHEM, TABLE JAME.TABLE-ROWS. DATA-LENGTH, INOGLLEWGTH. DATA_F127.00l：5S0121 NULL1 Query2091 Opening tables1 SELECT TASLE.SCMEMA, TABLE_NAME.TA8LE_R0WS. DATj

3、EMHH. INOX_LENGTH, DATA.F127.00l:SS016I NULL1 Query154I Opening tables1 SELECT TABLE.SCHeMA, TABLE JAME,TABLEJOWS, DATAJLEM5TW, INDeX_LEMjTH, DAT127.001:S5O2OI NULL1 Sleep011 NULL127.00l：S$042| NULL| Qury178| Opening tables| SELECT TABIE_SCHEKA. TABLE JAME,TABLEJOWS, DATA_LENGTH, INOEX.LENGTM, DATA_

4、F127.00l：5S044I NULL1 Query1481 Opening tables1 SELECT TABIJSCMEMA. TABLE.NAME.TABLEJOWS. DATAJLENGTH. INOeX_LENGTH, DATA.F127.001:SSO52I NULL1 Qury119I Opening tables1 SELECT TASLE.SOCMA, TABLE JAME.TABLE JOWS, OATA_LENGD4. IheX_LEM5TH, DATA.F通过show open tables ;语句发现opened tables并不算太多:相关参数也没有太大的不合理

5、性:mysql show variables like *table_open_cache1;+-+_ -+I Variable_name | Value |+一一一一一 一一 一一一一一一一一一 一+-+| tabie_open_cache | 2000 |+一一一 一一 一一一一 一一 一一一 一一 一+一+1 row in set (0,01 sec)虽然ulimit设置不是很大，但是也不会对此产生什么影响rootC# ulimit -n1024- A查看MySQL Error日志也没有发现与此相关的异常。因此只能使用pstack工具对MySQL打堆栈来进行分析，堆栈日志 如下：winw

6、iCJa mn ，rs ，X4八fu-.Q 1 OsXMMM 14422f m laliiw ay”I wtoee ccm*ct UM-2C. *MrfinSaMiW. Bnqi ockf. arctrc n：sltc4?a 小“4/加/9Cl.rv*nq：3，.12/vU/TlogBt：Ll t Zw/iUM加/ ：od/*yi27FaU*rll aoctot.bxW92 vlo toetoK cntwct /var/：l*/t2/6 l-4004-ie5mrril.?fql-S.T.2/vU/viooekt.c：40” 。而c8修R2).k./M/*J*4M4-IMM9.7.U/ir9*/

7、rr*t4/h.r4rl4.cc:ns a*frX88811M” in Ka 1:.l 3B C. Mzyxcptiaxz*! eot. 】MUycialEy 8t1 at.13/，t8A2=td/he fdatetd.oe:32?4*“ QaXWMllO*2e2:Ufo lthm22,S39 fUv212 t 7vr/ll/f*l/bJ-4OO4-14OM4m.Y/ayql5.t./tecR/f*tatdrM_fStcatd.cc:l.7 OaMCOaMCOM4M) in 6_“hM_3k,jC3d QUte4B7ru2lMM9,5MC* 4 W rat, 9_n-杂W4 oet. CaMj

8、MMctuuMd cutl at /er7“b，而/2 1彳8104T8l，”0L“/aryl$.1.1“八/y】stMv.cc:lll2 OaXOJMCMtftaOra m fill scMaa tbls toy 加c32aX14fC. m root-. is a&ov rielAt 8 IwyvfalM. t91CsTf】B，M0 chM 31Os】”l8 ochoM u， 0-68S.C2W ift tt ill ule 4li7rU*12Al4r.CC*3V)I 4t /Vr/llb7p2/b*l-4&JO4-iemunO.T/ql.5.1.2/qX/aXbcv.Ca”OF3 in *

9、_fin_C4lat:111 0,OX888-19 in9cbM 31” t9ult2334Xj1ctOCSMC BT A3：V tXtCl t”wCC；l12 OaMOSOC0MarM inru：C，tM血”1212U4S at148104“3141力).：/7 M：*ct.cc:B11 OMCOJWC0Jd2SX4O in X：l: :mc ltlaC*7n212tA4M At /var/：Xh/pfc2/Bb 1-4OM4-1C341791.?/sql-5.T. 12/q：/l xC4tor.CC：lSl14 0W68KHQO id handle tyJ2：2a：4fC. lK-3K?f

10、S212aSC44. t*lt-3a?tS2125MOI. OMd optioewl. ziani 衣&cEt /wr/iil/ptC/ib l-4OO4-ieO：e4JHl.?/yB OaOMOICeM5t fct emcute g，M elct (tM-0an2412414f0. 11 C31.ioCiaitd outl l ，vat/l、b7g1/，b l-4t)04*-5.1.)2/尸ix.c，3139114 Q8C38gMs44 ，n ay，ql.*i*C48.c*MM2 (CMairUalUim. Clm_lrrlcrwl at“，0*M38COW0“ in *yqlj*rM 7f

11、 12412A14fOt,“ti彳P3Md wt| at /” 13孰/陶，& &T&SCX21,03./y3_r” Q*MS8CQ皿” in dltpatcM ccmmaI l-40M4.1MM31.7aql.3.?.32/aq：/91_rw.cc：X4X Q，868gXM4“ in 4ofthiHCxir2al24Uf01 atbCSK-1G10X.，行卬r.,.M/gX/印W 0WB8CO”a肾 in，ai，cri 1/Vt/u5/1*2/t (4K4.i40l4)T0i.Y/ivti.5.).)vqi/oMM_haM：(/6MmctiC*Mnf5M in ff fM.lhr*1at /

12、var/liW|*MZJ-4OO4-14MNl.*?/yMl*S.?.2/M4*/f(f select tabie_name,tabie.schema,engine from information_schema.tables v*here engine=*federated,;I tablt.nanw| tablt.schema | engineFEDERATED IFEDERATED |2 rows in $t (0.17 sec)通过在实例服务器上Telnet Feferated服务端的实例IP和端口发 现是不通的： c# telnet .Trying厂“AC所以推测问题原因为：监控线程

13、在查询informalion_schema. tables 表时，当需要获取Federated存储引擎表的信息时需要连接远端 Server ,而由于网络或其他原因无法连接时，就会导致本地监控线程处 于 Opening table 状态。接下来设计实验验证我们的想法:1.启用Federated存储引擎ql how engSc”；EnQineSupport1 CowrnntTransactionsXA1 SavepointsXnnooe WG_XYISAM.MEMIMY LACKMOLt “3 CSV MOlVt 麒窗!髀OCFAULTYiS nsVtsYiS YSYt5 卷Supports tr

14、ansactions,locZg. Md foreign kyiCol ifctior of identical MylSAM tabi” Hash based, stored in memory, useful for temporary tables /dev/null storaQe engine (anything you write to It dHappeart) MylSAW srorag CSv storage enoine Archive storage engine Ptrformince Schema Federated MySQL storage engineYIS N

15、O NO 2 NO NO1 NO NO NOVISYfiSrows in set (0.00 1 create create table action.t(id 1nt primary kty.nan varchar(10)enginefcurated COKNECTXOb-nyiql:/ac11 on:p&s90:J30t/db/tab 1 6iay OK. 0 rows(0.05 SC)1. rowTable: terw Tablt1. rowTable: terw Tablt： CREATE TABLE t ( idNOT NULL.nam varcnar(lO) C

16、OLLATE utf8gLbln DEFAULT null. PRIMARY KEY ( id )ENCIhFDRATED DC FAULT CHARSETutf 8fb4 COLLATE-utf Snb4 Jin CONNCTION * myql ： Z/Ct i on ： pas50:330S/db/tabl 1 row in set (0.01 sec)查询 information_schema. tables 表，线程卡住mysql select * from information_schema.tabies;线程状态处于Opening table状态ysql shg processlist;I I