故障分析 | DROP 大表造成数据库假死

客户数据库出现假死，导致探测语句下发不下去，出现切换。后来经过排查发现是一个大表drop导致的数据库产生假死，也参考过类似的数据库假死的案例，这里将测试一下不同版本droptable的影响

根据，对于大的bufferpool中的大表drop会占用mutex锁，导致其它查询无法进行。提供的临时解决方案为释放AHI(自适应哈希),预期解决版本是8.0.23。暂未从5.7的后期版本中找到解决方式

使用不同版本测试影响效果

测试配置

bufferpool表空间占用5.7.29128

关闭binlog、调整双一，使用benchmark导入300个库的数据

ls-lhbmsql_*.ibd-rw-r-----1rootroot96KOct1917:49bmsql_:05bmsql_:46bmsql_:28bmsql_:34bmsql_:32bmsql_new_:48bmsql_:44bmsql_order_:43bmsql_:57bmsql_[localhost:5729]{root}(test)FLUSHTABLESbmsql_customerFOREXPORT;QueryOK,0rowsaffected(0.01sec)[root@R820-04test]cpbmsql_order_line.{ibd,cfg}/data/sandboxes/mysql[localhost:5729]{root}(test)UNLOCKTABLES;QueryOK,0rowsaffected(0.00sec)mysql[localhost:5729]{root}(test)FLUSHTABLESbmsql_stockFOREXPORT;QueryOK,0rowsaffected(0.00sec)[root@R820-04test]ls-lhbmsql_*-:06bmsql_:06bmsql_:12bmsql_order_:12bmsql_order_:14bmsql_:14bmsql_

改回binlog及双一参数。调整bufferpool到128G

mysql[localhost:5729]{root}((none))setglobalinnodb_buffer_pool_size=128*1024*1024*1024QueryOK,0rowsaffected(0.00sec)mysql[localhost:5729]{root}((none))showvariableslike'innodb_buffer_pool_size';+-------------------------+-------------+|Variable_name|Value|+-------------------------+-------------+|innodb_buffer_pool_size||+-------------------------+-------------+1rowinset(0.00sec)

数据库预热

mysql[localhost:5729]{root}(test)showvariableslike'%hash%';+----------------------------------+-------+|Variable_name|Value|+----------------------------------+-------+|innodb_adaptive_hash_index|ON||innodb_adaptive_hash_index_parts|8||metadata_locks_hash_instances|8|+----------------------------------+-------+/opt/sysbench-x86_64//bin/sysbencholtp_read_=10.186.17.104--mysql-port=5729--mysql-user=test--mysql-password=123456--mysql-db=test--table-size=10000000--tables=5--threads=5--db-ps-mode=disable--auto_inc=off--report-interval=3--max-requests=0--time=300--percentile=95--skip_trx=on--mysql-ignore-errors=6002,6004,4012,2013,4016,1062,1213--create_secondary=()Runningthetestwithfollowingoptions:Numberofthreads:5Reportintermediateresultsevery3second(s)InitializingrandomnumbergeneratorfromcurrenttimeInitializingworkerthreadsThreadsstarted![179s]thds:5tps:17.00qps:330.01(r/w/o:257.01/73.00/0.00)lat(ms,95%):350.33err/s:0.00reconn/s:0.00[180s]thds:5tps:5.00qps:78.99(r/w/o:63.99/15.00/0.00)lat(ms,95%):272.27err/s:0.00reconn/s:0.00[181s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[182s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[183s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[184s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[185s]thds:5tps:3.00qps:68.96(r/w/o:56.97/11.99/0.00)lat(ms,95%):5312.73err/s:0.00reconn/s:0.00[186s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[187s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[188s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[189s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[190s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[191s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[192s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[193s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[194s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[195s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[196s]thds:5tps:10.00qps:139.00(r/w/o:99.00/40.00/0.00)lat(ms,95%):16519.10err/s:0.00reconn/s:0.00[197s]thds:5tps:19.00qps:362.00(r/w/o:286.00/76.00/0.00)lat(ms,95%):303.33err/s:0.00reconn/s:0.00[198s]thds:5tps:21.00qps:358.00(r/w/o:274.00/84.00/0.00)lat(ms,95%):442.73err/s:0.00reconn/s:0.00[199s]thds:5tps:22.00qps:395.97(r/w/o:307.97/87.99/0.00)lat(ms,95%):308.84err/s:0.00reconn/s:0.00[200s]thds:5tps:16.00qps:303.02(r/w/o:237.02/66.00/0.00)lat(ms,95%):502.20err/s:0.00reconn/s:0.00[201s]thds:5tps:21.00qps:379.03(r/w/o:297.02/82.01/0.00)lat(ms,95%):325.98err/s:0.00reconn/s:0.00

关闭AHI，时间缩短至2s，TPS/QPS过程中降为0

mysql[localhost:5729]{root}(test)droptablebmsql_stock;QueryOK,0rowsaffected(2.60sec)[47s]thds:5tps:17.00qps:283.99(r/w/o:215.99/68.00/0.00)lat(ms,95%):502.20err/s:0.00reconn/s:0.00[48s]thds:5tps:15.00qps:278.02(r/w/o:217.02/61.01/0.00)lat(ms,95%):493.24err/s:0.00reconn/s:0.00[49s]thds:5tps:14.00qps:275.92(r/w/o:214.93/60.98/0.00)lat(ms,95%):502.20err/s:0.00reconn/s:0.00[50s]thds:5tps:13.00qps:245.92(r/w/o:197.94/47.99/0.00)lat(ms,95%):539.71err/s:0.00reconn/s:0.00[51s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[52s]thds:5tps:0.00qps:0.00(r/w/o:0.00/0.00/0.00)lat(ms,95%):0.00err/s:0.00reconn/s:0.00[53s]thds:5tps:17.00qps:280.95(r/w/o:210.96/69.99/0.00)lat(ms,95%):2728.81err/s:0.00reconn/s:0.00[54s]thds:5tps:17.00qps:312.01(r/w/o:246.01/66.00/0.00)lat(ms,95%):458.96err/s:0.00reconn/s:0.00[55s]thds:5tps:22.00qps:366.99(r/w/o:277.99/89.00/0.00)lat(ms,95%):331.91err/s:0.00reconn/s:0.00[56s]thds:5tps:22.00qps:401.00(r/w/o:316.00/85.00/0.00)lat(ms,95%):411.96err/s:0.00reconn/s:0.00

8.0.28

开启AHI，drop执行了2min34,未影响TPS/QPS

mysql[localhost:8028]{root}(test)droptablebmsql_stock;QueryOK,0rowsaffected(2)[113s]thds:50tps:26.00qps:511.99(r/w/o:415.99/96.00/0.00)lat(ms,95%):2728.81err/s:0.00reconn/s:0.00[114s]thds:50tps:34.00qps:552.02(r/w/o:411.02/141.01/0.00)lat(ms,95%):2279.14err/s:0.00reconn/s:0.00[115s]thds:50tps:25.00qps:538.94(r/w/o:444.95/93.99/0.00)lat(ms,95%):2320.55err/s:0.00reconn/s:0.00[116s]thds:50tps:28.00qps:452.00(r/w/o:323.00/129.00/0.00)lat(ms,95%):2449.36err/s:0.00reconn/s:0.00[117s]thds:50tps:34.00qps:580.06(r/w/o:456.05/124.01/0.00)lat(ms,95%):2985.89err/s:0.00reconn/s:0.00[118s]thds:50tps:24.00qps:508.00(r/w/o:409.00/99.00/0.00)lat(ms,95%):2539.17err/s:0.00reconn/s:0.00[119s]thds:50tps:34.00qps:580.93(r/w/o:449.95/130.98/0.00)lat(ms,95%):2585.31err/s:0.00reconn/s:0.00[120s]thds:50tps:37.00qps:669.08(r/w/o:525.06/144.02/0.00)lat(ms,95%):2539.17err/s:0.00reconn/s:0.00[121s]thds:50tps:50.99qps:918.87(r/w/o:705.90/212.97/0.00)lat(ms,95%):1938.16err/s:0.00reconn/s:0.00[122s]thds:50tps:42.00qps:747.92(r/w/o:586.93/160.98/0.00)lat(ms,95%):2585.31err/s:0.00reconn/s:0.00[123s]thds:50tps:40.01qps:730.16(r/w/o:566.12/164.04/0.00)lat(ms,95%):1803.47err/s:0.00reconn/s:0.00[124s]thds:50tps:46.00qps:778.02(r/w/o:599.02/179.01/0.00)lat(ms,95%):2120.76err/s:0.00reconn/s:0.00[125s]thds:50tps:38.00qps:759.00(r/w/o:593.00/166.00/0.00)lat(ms,95%):1648.20err/s:0.00reconn/s:0.00[126s]thds:50tps:43.99qps:802.89(r/w/o:638.91/163.98/0.00)lat(ms,95%):2009.23err/s:0.00reconn/s:0.00[127s]thds:50tps:45.00qps:768.00(r/w/o:585.00/183.00/0.00)lat(ms,95%):2120.76err/s:0.00reconn/s:0.00[128s]thds:50tps:42.00qps:791.00(r/w/o:622.00/169.00/0.00)lat(ms,95%):1869.60err/s:0.00reconn/s:0.00[129s]thds:50tps:53.01qps:880.09(r/w/o:675.07/205.02/0.00)lat(ms,95%):1903.57err/s:0.00reconn/s:0.00[130s]thds:50tps:59.99qps:1088.90(r/w/o:838.92/249.98/0.00)lat(ms,95%):1589.90err/s:0.00reconn/s:0.00[131s]thds:50tps:35.88qps:723.51(r/w/o:575.02/148.49/0.00)lat(ms,95%):1561.52err/s:0.00reconn/s:0.00[132s]thds:50tps:62.20qps:1000.22(r/w/o:764.46/235.76/0.00)lat(ms,95%):1708.63err/s:0.00reconn/s:0.00[133s]thds:50tps:41.01qps:879.29(r/w/o:704.23/175.06/0.00)lat(ms,95%):1213.57err/s:0.00reconn/s:0.00[134s]thds:50tps:49.00qps:849.02(r/w/o:654.02/195.00/0.00)lat(ms,95%):1648.20err/s:0.00reconn/s:0.00[135s]thds:50tps:49.99qps:885.89(r/w/o:690.92/194.98/0.00)lat(ms,95%):1973.38err/s:0.00reconn/s:0.00[136s]thds:50tps:43.00qps:781.08(r/w/o:608.07/173.02/0.00)lat(ms,95%):1618.78err/s:0.00reconn/s:0.00[137s]thds:50tps:42.00qps:726.99(r/w/o:557.00/170.00/0.00)lat(ms,95%):1938.16err/s:0.00reconn/s:0.00[138s]thds:50tps:49.00qps:948.96(r/w/o:749.97/198.99/0.00)lat(ms,95%):2045.74err/s:0.00reconn/s:0.00

关闭AHI，执行时间0.58s完成，不影响业务

mysql[localhost:8028]{root}(test)droptablebmsql_stock;QueryOK,0rowsaffected(0.58sec)[35s]thds:10tps:37.01qps:668.19(r/w/o:522.15/146.04/0.00)lat(ms,95%):331.91err/s:0.00reconn/s:0.00[36s]thds:10tps:36.00qps:623.98(r/w/o:481.98/142.00/0.00)lat(ms,95%):344.08err/s:0.00reconn/s:0.00[37s]thds:10tps:35.00qps:659.96(r/w/o:513.97/145.99/0.00)lat(ms,95%):344.08err/s:0.00reconn/s:0.00[38s]thds:10tps:43.00qps:758.02(r/w/o:583.01/175.00/0.00)lat(ms,95%):297.92err/s:0.00reconn/s:0.00[39s]thds:10tps:38.00qps:671.96(r/w/o:523.97/147.99/0.00)lat(ms,95%):434.83err/s:0.00reconn/s:0.00[40s]thds:10tps:33.00qps:646.03(r/w/o:508.02/138.01/0.00)lat(ms,95%):369.77err/s:0.00reconn/s:0.00[41s]thds:10tps:45.00qps:742.92(r/w/o:569.94/172.98/0.00)lat(ms,95%):303.33err/s:0.00reconn/s:0.00[42s]thds:10tps:42.00qps:760.09(r/w/o:597.07/163.02/0.00)lat(ms,95%):287.38err/s:0.00reconn/s:0.00

打印5.7.29的堆栈信息，droptable过程中持续进行btr_search_drop_page_hash_index，在AHI的删除时占用了大量的时间

Thread69(Thread0x7fa088139700(LWP397558)):10x0000000001441090inha_remove_all_nodes_to_page(table=0xdbce6e08,fold=940915631,page=0x7faae06b8000"G\242",incompletesequence\332)at/export/home/pb2/build/sb_0-37309218-1576676677.02//storage/innobase/ha/:43430x00000000013a9de9inbtr_search_drop_page_hash_when_freed(page_id=,page_size=)at/export/home/pb2/build/sb_0-37309218-1576676677.02//storage/innobase/btr/:139550x0000000001431f8binfseg_free_step(header=optimizedout,ahi=true,mtr=0x7fa088133b70)at/export/home/pb2/build/sb_0-37309218-1576676677.02//storage/innobase/fsp/:389870x000000000138354cinbtr_free_if_exists(page_id=,page_size=,index_id=183,mtr=0x7fa0881341a0)at/export/home/pb2/build/sb_0-37309218-1576676677.02//storage/innobase/btr/:120890x0000000001319b7ainrow_upd_clust_step(node=0x7fa054aa4730,thr=0x7fa054aa7fc0)at/export/home/pb2/build/sb_0-37309218-1576676677.02//storage/innobase/row/:2894110x000000000131b513inrow_upd_step(thr=0x7fa054aa7fc0)at/export/home/pb2/build/sb_0-37309218-1576676677.02//storage/innobase/row/:320013que_run_threads_low(thr=0x7fa054aa7fc0)at/export/home/pb2/build/sb_0-37309218-1576676677.02//storage/innobase/que/:1119150x00000000012af8eeinque_eval_sql(info=0x7fa054a8e218,sql=0x7fa054b9d7b0"PROCEDUREDROP_TABLE_PROC()IS\nsys_foreign_idCHAR;\ntable_idCHAR;\nindex_idCHAR;\nforeign_idCHAR;\nspace_idINT;\nfoundINT;\nDECLARECURSORcur_fkIS\nSELECTIDFROMSYS_FOREIGN\nWHEREFOR_NAME=:table",reserve_dict_mutex=0,trx=optimizedout)at/export/home/pb2/build/sb_0-37309218-1576676677.02//storage/innobase/que/:1236170x0000000001217672inha_innobase::delete_table(this=optimizedout,name=0x7fa0881371c0"./test/bmsql_stock")at/export/home/pb2/build/sb_0-37309218-1576676677.02//storage/innobase/handler/ha_:12597190x0000000000db5547inmysql_rm_table_no_locks(thd=0x7fa0540128a0,tables=0x7fa05492d360,if_exists=false,drop_temporary=false,drop_view=false,dont_log_query=false)at/export/home/pb2/build/sb_0-37309218-1576676677.02//sql/sql_:2553

超过32gbufferpool中drop大表、dropAHI中占用大量页面的表、drop临时表空间，

之前版本会立即的释放脏页和AHI，这样会对性能产生很大的问题。如今的修复方式采用惰性删除的方式，对业务影响比较小

droptable过程大概分为三部分：

1、遍历lru，驱逐属于该表的脏页

2、清理AHI中的内容

3、文件系统的删除

其中前两部分属于最耗时，也是最影响业务的。大的bufferpool会导致遍历时间过长，通过hash运算找到AHI对应的位置并删除，这个时间也是比较长的，此阶段持有内部latche不释放，影响其它查询

8.0.23的修复版本主要是对应第一部分，对于脏页采用惰性删除方式，在关闭AHI的时候，是瞬间完成。当开启AHI的是，时间比历史版本的还要长，区别是不影响业务，猜测是降低锁的持有时间和粒度，使其它事务能够同时执行