作者：嶽明強
愛可生北京分公司 DBA 團隊成員，人稱強哥，朝陽一哥等，負責資料庫管理平臺的運維和 MySQL 問題處理。擅長對 MySQL 的故障定位。
本文來源：原創投稿
*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。
客戶資料庫出現假死，導致探測語句下發不下去，出現切換。後來經過排查發現是一個大表drop 導致的資料庫產生假死，也參考過類似的資料庫假死的案例，這裡將測試一下不同版本drop table的影響
關於drop 大表的歷史bug描述
根據https://bugs.mysql.com/bug.ph...中的描述，對於大的buffer pool中的大表 drop 會佔用mutex 鎖，導致其它查詢無法進行。提供的臨時解決方案 為釋放AHI(自適應雜湊),預期解決版本是8.0.23。暫未從5.7的後期版本中找到解決方式
使用不同版本測試影響效果
準備流程
測試配置
| | buffer pool | 表空間佔用 |
| --- | --- | --- |
| 5.7.29 | 128G | 24G |
| 8.0.28 | 128G | 24G |
關閉 binlog 、調整雙一，使用 benchmark 匯入300個庫的資料
#benchmark引數如下
db=mysql
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://10.186.17.104:5729/test?useSSL=false
user=test
password=123456
warehouses=500
loadWorkers=100
terminals=4
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=1
//Number of total transactions per minute
limitTxnsPerMin=0
//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true
//The following five values must add up to 100
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
//osCollectorScript=./misc/os_collector_linux.py
//osCollectorInterval=1
//osCollectorSSHAddr=user@dbhost
//osCollectorDevices=net_eth0 blk_sda匯入資料後使用表空間遷移的方式保留較大表的備份，方便後續連續測試
[root@R820-04 test]# ls -lh bmsql_*.ibd
-rw-r----- 1 root root 96K Oct 19 17:49 bmsql_config.ibd
-rw-r----- 1 root root 12G Oct 20 13:05 bmsql_customer.ibd
-rw-r----- 1 root root 9.0M Oct 20 12:46 bmsql_district.ibd
-rw-r----- 1 root root 1.9G Oct 20 13:28 bmsql_history.ibd
-rw-r----- 1 root root 17M Oct 19 18:34 bmsql_item.ibd
-rw-r----- 1 root root 168M Oct 20 13:32 bmsql_new_order.ibd
-rw-r----- 1 root root 1.5G Oct 20 13:48 bmsql_oorder.ibd
-rw-r----- 1 root root 14G Oct 20 14:44 bmsql_order_line.ibd
-rw-r----- 1 root root 22G Oct 20 11:43 bmsql_stock.ibd
-rw-r----- 1 root root 160K Oct 20 10:57 bmsql_warehouse.ibd
mysql [localhost:5729] {root} (test) > FLUSH TABLES bmsql_customer FOR EXPORT ;
Query OK, 0 rows affected (0.01 sec)
[root@R820-04 test]# cp bmsql_customer.{ibd,cfg} /data/sandboxes/
mysql [localhost:5729] {root} (test) > UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5729] {root} (test) > FLUSH TABLES bmsql_order_line FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)
[root@R820-04 test]# cp bmsql_order_line.{ibd,cfg} /data/sandboxes/
mysql [localhost:5729] {root} (test) > UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5729] {root} (test) > FLUSH TABLES bmsql_stock FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)
[root@R820-04 test]# cp bmsql_stock.{ibd,cfg} /data/sandboxes/
mysql [localhost:5729] {root} (test) > UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)
[root@R820-04 sandboxes]# ls -lh bmsql_*
-rw-r----- 1 root root 1.8K Oct 20 15:06 bmsql_customer.cfg
-rw-r----- 1 root root 12G Oct 20 15:06 bmsql_customer.ibd
-rw-r----- 1 root root 944 Oct 20 15:12 bmsql_order_line.cfg
-rw-r----- 1 root root 14G Oct 20 15:12 bmsql_order_line.ibd
-rw-r----- 1 root root 1.4K Oct 20 15:14 bmsql_stock.cfg
-rw-r----- 1 root root 22G Oct 20 15:14 bmsql_stock.ibd改回 binlog 及雙一引數。調整 bufferpool 到128G
mysql [localhost:5729] {root} ((none)) > set global innodb_buffer_pool_size = 128*1024*1024*1024
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5729] {root} ((none)) > show variables like 'innodb_buffer_pool_size';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| innodb_buffer_pool_size | 17179869184 |
+-------------------------+-------------+
1 row in set (0.00 sec)資料庫預熱
mysql [localhost:5729] {root} (test) > show variables like '%hash%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| metadata_locks_hash_instances | 8 |
+----------------------------------+-------+
#show engine innodb status;的以下列中有AHI的使用情況
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1373, seg size 1375, 10121 merges
merged operations:
insert 10251, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4425293, node heap has 2 buffer(s)
Hash table size 4425293, node heap has 26 buffer(s)
Hash table size 4425293, node heap has 216 buffer(s)
Hash table size 4425293, node heap has 1 buffer(s)
Hash table size 4425293, node heap has 0 buffer(s)
Hash table size 4425293, node heap has 3 buffer(s)
Hash table size 4425293, node heap has 2374 buffer(s)
Hash table size 4425293, node heap has 1137 buffer(s)
218.70 hash searches/s, 398.15 non-hash searches/s
buffer pool的使用情況
mysql [localhost:5729] {root} (performance_schema) > SELECT CONCAT(FORMAT(A.num * 100.0 / B.num,2),"%") BufferPoolFullPct FROM (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_data') A, (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;
+-------------------+
| BufferPoolFullPct |
+-------------------+
| 86.21% |
+-------------------+
1 row in set (0.00 sec)測試結果
5.7.29
開啟AHI，drop執行了15s ,TPS/QPS 過程中降為0
mysql [localhost:5729] {root} (test) > drop table bmsql_stock;
Query OK, 0 rows affected (15.75 sec)
[root@qiang1 sysbench]# /opt/sysbench-x86_64/sysbench-1.0.17/bin/sysbench oltp_read_write.lua --mysql-host=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=off run
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 5
Report intermediate results every 3 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 179s ] thds: 5 tps: 17.00 qps: 330.01 (r/w/o: 257.01/73.00/0.00) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 5 tps: 5.00 qps: 78.99 (r/w/o: 63.99/15.00/0.00) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00
[ 181s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 182s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 183s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 184s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 185s ] thds: 5 tps: 3.00 qps: 68.96 (r/w/o: 56.97/11.99/0.00) lat (ms,95%): 5312.73 err/s: 0.00 reconn/s: 0.00
[ 186s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 187s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 188s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 189s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 191s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 192s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 193s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 194s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 195s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 196s ] thds: 5 tps: 10.00 qps: 139.00 (r/w/o: 99.00/40.00/0.00) lat (ms,95%): 16519.10 err/s: 0.00 reconn/s: 0.00
[ 197s ] thds: 5 tps: 19.00 qps: 362.00 (r/w/o: 286.00/76.00/0.00) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
[ 198s ] thds: 5 tps: 21.00 qps: 358.00 (r/w/o: 274.00/84.00/0.00) lat (ms,95%): 442.73 err/s: 0.00 reconn/s: 0.00
[ 199s ] thds: 5 tps: 22.00 qps: 395.97 (r/w/o: 307.97/87.99/0.00) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 5 tps: 16.00 qps: 303.02 (r/w/o: 237.02/66.00/0.00) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00
[ 201s ] thds: 5 tps: 21.00 qps: 379.03 (r/w/o: 297.02/82.01/0.00) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00關閉AHI，時間縮短至2s，TPS/QPS 過程中降為0
mysql [localhost:5729] {root} (test) > drop table bmsql_stock;
Query OK, 0 rows affected (2.60 sec)
[ 47s ] thds: 5 tps: 17.00 qps: 283.99 (r/w/o: 215.99/68.00/0.00) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 5 tps: 15.00 qps: 278.02 (r/w/o: 217.02/61.01/0.00) lat (ms,95%): 493.24 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 5 tps: 14.00 qps: 275.92 (r/w/o: 214.93/60.98/0.00) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 5 tps: 13.00 qps: 245.92 (r/w/o: 197.94/47.99/0.00) lat (ms,95%): 539.71 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 5 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 53s ] thds: 5 tps: 17.00 qps: 280.95 (r/w/o: 210.96/69.99/0.00) lat (ms,95%): 2728.81 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 5 tps: 17.00 qps: 312.01 (r/w/o: 246.01/66.00/0.00) lat (ms,95%): 458.96 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 5 tps: 22.00 qps: 366.99 (r/w/o: 277.99/89.00/0.00) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 5 tps: 22.00 qps: 401.00 (r/w/o: 316.00/85.00/0.00) lat (ms,95%): 411.96 err/s: 0.00 reconn/s: 0.008.0.28
開啟AHI，drop執行了2min34 ,未影響TPS/QPS
mysql [localhost:8028] {root} (test) > drop table bmsql_stock;
Query OK, 0 rows affected (2 min 34.82 sec)
[ 113s ] thds: 50 tps: 26.00 qps: 511.99 (r/w/o: 415.99/96.00/0.00) lat (ms,95%): 2728.81 err/s: 0.00 reconn/s: 0.00
[ 114s ] thds: 50 tps: 34.00 qps: 552.02 (r/w/o: 411.02/141.01/0.00) lat (ms,95%): 2279.14 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 50 tps: 25.00 qps: 538.94 (r/w/o: 444.95/93.99/0.00) lat (ms,95%): 2320.55 err/s: 0.00 reconn/s: 0.00
[ 116s ] thds: 50 tps: 28.00 qps: 452.00 (r/w/o: 323.00/129.00/0.00) lat (ms,95%): 2449.36 err/s: 0.00 reconn/s: 0.00
[ 117s ] thds: 50 tps: 34.00 qps: 580.06 (r/w/o: 456.05/124.01/0.00) lat (ms,95%): 2985.89 err/s: 0.00 reconn/s: 0.00
[ 118s ] thds: 50 tps: 24.00 qps: 508.00 (r/w/o: 409.00/99.00/0.00) lat (ms,95%): 2539.17 err/s: 0.00 reconn/s: 0.00
[ 119s ] thds: 50 tps: 34.00 qps: 580.93 (r/w/o: 449.95/130.98/0.00) lat (ms,95%): 2585.31 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 37.00 qps: 669.08 (r/w/o: 525.06/144.02/0.00) lat (ms,95%): 2539.17 err/s: 0.00 reconn/s: 0.00
[ 121s ] thds: 50 tps: 50.99 qps: 918.87 (r/w/o: 705.90/212.97/0.00) lat (ms,95%): 1938.16 err/s: 0.00 reconn/s: 0.00
[ 122s ] thds: 50 tps: 42.00 qps: 747.92 (r/w/o: 586.93/160.98/0.00) lat (ms,95%): 2585.31 err/s: 0.00 reconn/s: 0.00
[ 123s ] thds: 50 tps: 40.01 qps: 730.16 (r/w/o: 566.12/164.04/0.00) lat (ms,95%): 1803.47 err/s: 0.00 reconn/s: 0.00
[ 124s ] thds: 50 tps: 46.00 qps: 778.02 (r/w/o: 599.02/179.01/0.00) lat (ms,95%): 2120.76 err/s: 0.00 reconn/s: 0.00
[ 125s ] thds: 50 tps: 38.00 qps: 759.00 (r/w/o: 593.00/166.00/0.00) lat (ms,95%): 1648.20 err/s: 0.00 reconn/s: 0.00
[ 126s ] thds: 50 tps: 43.99 qps: 802.89 (r/w/o: 638.91/163.98/0.00) lat (ms,95%): 2009.23 err/s: 0.00 reconn/s: 0.00
[ 127s ] thds: 50 tps: 45.00 qps: 768.00 (r/w/o: 585.00/183.00/0.00) lat (ms,95%): 2120.76 err/s: 0.00 reconn/s: 0.00
[ 128s ] thds: 50 tps: 42.00 qps: 791.00 (r/w/o: 622.00/169.00/0.00) lat (ms,95%): 1869.60 err/s: 0.00 reconn/s: 0.00
[ 129s ] thds: 50 tps: 53.01 qps: 880.09 (r/w/o: 675.07/205.02/0.00) lat (ms,95%): 1903.57 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 59.99 qps: 1088.90 (r/w/o: 838.92/249.98/0.00) lat (ms,95%): 1589.90 err/s: 0.00 reconn/s: 0.00
[ 131s ] thds: 50 tps: 35.88 qps: 723.51 (r/w/o: 575.02/148.49/0.00) lat (ms,95%): 1561.52 err/s: 0.00 reconn/s: 0.00
[ 132s ] thds: 50 tps: 62.20 qps: 1000.22 (r/w/o: 764.46/235.76/0.00) lat (ms,95%): 1708.63 err/s: 0.00 reconn/s: 0.00
[ 133s ] thds: 50 tps: 41.01 qps: 879.29 (r/w/o: 704.23/175.06/0.00) lat (ms,95%): 1213.57 err/s: 0.00 reconn/s: 0.00
[ 134s ] thds: 50 tps: 49.00 qps: 849.02 (r/w/o: 654.02/195.00/0.00) lat (ms,95%): 1648.20 err/s: 0.00 reconn/s: 0.00
[ 135s ] thds: 50 tps: 49.99 qps: 885.89 (r/w/o: 690.92/194.98/0.00) lat (ms,95%): 1973.38 err/s: 0.00 reconn/s: 0.00
[ 136s ] thds: 50 tps: 43.00 qps: 781.08 (r/w/o: 608.07/173.02/0.00) lat (ms,95%): 1618.78 err/s: 0.00 reconn/s: 0.00
[ 137s ] thds: 50 tps: 42.00 qps: 726.99 (r/w/o: 557.00/170.00/0.00) lat (ms,95%): 1938.16 err/s: 0.00 reconn/s: 0.00
[ 138s ] thds: 50 tps: 49.00 qps: 948.96 (r/w/o: 749.97/198.99/0.00) lat (ms,95%): 2045.74 err/s: 0.00 reconn/s: 0.00關閉AHI，執行時間0.58s完成，不影響業務
mysql [localhost:8028] {root} (test) > drop table bmsql_stock;
Query OK, 0 rows affected (0.58 sec)
[ 35s ] thds: 10 tps: 37.01 qps: 668.19 (r/w/o: 522.15/146.04/0.00) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 10 tps: 36.00 qps: 623.98 (r/w/o: 481.98/142.00/0.00) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 10 tps: 35.00 qps: 659.96 (r/w/o: 513.97/145.99/0.00) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 10 tps: 43.00 qps: 758.02 (r/w/o: 583.01/175.00/0.00) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 10 tps: 38.00 qps: 671.96 (r/w/o: 523.97/147.99/0.00) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 10 tps: 33.00 qps: 646.03 (r/w/o: 508.02/138.01/0.00) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 10 tps: 45.00 qps: 742.92 (r/w/o: 569.94/172.98/0.00) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 10 tps: 42.00 qps: 760.09 (r/w/o: 597.07/163.02/0.00) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00列印堆疊
列印5.7.29的堆疊資訊，drop table 過程中持續進行 btr_search_drop_page_hash_index ，在AHI的刪除時佔用了大量的時間
Thread 69 (Thread 0x7fa088139700 (LWP 397558)):
#0 0x0000000001440f3f in ha_delete_hash_node (table=0xdbce6e08, del_node=0x7fbb56a83310) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/ha/ha0ha.cc:356
#1 0x0000000001441090 in ha_remove_all_nodes_to_page (table=0xdbce6e08, fold=13261624875940915631, page=0x7faae06b8000 "G\242", <incomplete sequence \332>) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/ha/ha0ha.cc:434
#2 0x00000000013a7f93 in btr_search_drop_page_hash_index (block=0x7faadd2ad4d8) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/btr/btr0sea.cc:1334
#3 0x00000000013a9de9 in btr_search_drop_page_hash_when_freed (page_id=..., page_size=...) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/btr/btr0sea.cc:1395
#4 0x000000000142de36 in fseg_free_extent (seg_inode=<optimized out>, space=131, page_size=..., page=926336, ahi=<optimized out>, mtr=<optimized out>) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/fsp/fsp0fsp.cc:3806
#5 0x0000000001431f8b in fseg_free_step (header=<optimized out>, ahi=true, mtr=0x7fa088133b70) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/fsp/fsp0fsp.cc:3898
#6 0x000000000138321d in btr_free_but_not_root (block=0x7fb32312aaa8, log_mode=MTR_LOG_ALL) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/btr/btr0btr.cc:1160
#7 0x000000000138354c in btr_free_if_exists (page_id=..., page_size=..., index_id=183, mtr=0x7fa0881341a0) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/btr/btr0btr.cc:1208
#8 0x00000000013de54d in dict_drop_index_tree (rec=<optimized out>, pcur=<optimized out>, mtr=0x7fa0881341a0) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/dict/dict0crea.cc:1168
#9 0x0000000001319b7a in row_upd_clust_step (node=0x7fa054aa4730, thr=0x7fa054aa7fc0) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/row/row0upd.cc:2894
#10 0x000000000131b21f in row_upd (node=0x7fa054aa4730, thr=0x7fa054aa7fc0) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/row/row0upd.cc:3054
#11 0x000000000131b513 in row_upd_step (thr=0x7fa054aa7fc0) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/row/row0upd.cc:3200
#12 0x00000000012af158 in que_thr_step (thr=0x7fa054aa7fc0) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/que/que0que.cc:1039
#13 que_run_threads_low (thr=0x7fa054aa7fc0) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/que/que0que.cc:1119
#14 que_run_threads (thr=<optimized out>) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/que/que0que.cc:1159
#15 0x00000000012af8ee in que_eval_sql (info=0x7fa054a8e218, sql=0x7fa054b9d7b0 "PROCEDURE DROP_TABLE_PROC () IS\nsys_foreign_id CHAR;\ntable_id CHAR;\nindex_id CHAR;\nforeign_id CHAR;\nspace_id INT;\nfound INT;\nDECLARE CURSOR cur_fk IS\nSELECT ID FROM SYS_FOREIGN\nWHERE FOR_NAME = :table"..., reserve_dict_mutex=0, trx=<optimized out>) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/que/que0que.cc:1236
#16 0x00000000012e56a2 in row_drop_table_for_mysql (name=0x7fa088135db0 "test/bmsql_stock", trx=0x7fc1fa7f0990, drop_db=<optimized out>, nonatomic=<optimized out>, handler=<optimized out>) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/row/row0mysql.cc:4715
#17 0x0000000001217672 in ha_innobase::delete_table (this=<optimized out>, name=0x7fa0881371c0 "./test/bmsql_stock") at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:12597
#18 0x0000000000852278 in ha_delete_table (thd=0x7fa0540128a0, table_type=<optimized out>, path=0x7fa0881371c0 "./test/bmsql_stock", db=0x7fa05492d8e0 "test", alias=0x7fa05492d318 "bmsql_stock", generate_warning=true) at /export/home/pb2/build/sb_0-37309218-1576676677.02/mysql-5.7.29/sql/handler.cc:2601
#19 0x0000000000db5547 in mysql_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/mysql-5.7.29/sql/sql_table.cc:2553修復說明：
超過32g buffer pool中 drop 大表、drop AHI 中佔用大量頁面的表、drop 臨時表空間，
之前版本會立即的釋放髒頁和 AHI，這樣會對效能產生很大的問題。如今的修復方式採用惰性刪除的方式，對業務影響比較小
結論：
drop table 過程大概分為三部分：
1、遍歷lru，驅逐屬於該表的髒頁
2、清理AHI中的內容
3、檔案系統的刪除
其中前兩部分屬於最耗時，也是最影響業務的。大的 buffer pool 會導致遍歷時間過長，透過hash運算找到AHI對應的位置並刪除， 這個時間也是比較長的，此階段持有內部latche不釋放，影響其它查詢
8.0.23的修復版本主要是對應第一部分，對於髒頁採用惰性刪除方式，在關閉AHI的時候，是瞬間完成。當開啟AHI的是，時間比歷史版本的還要長，區別是不影響業務，猜測是降低鎖的持有時間和粒度，使其它事務能夠同時執行