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

作者：岳明强

爱可生北京分公司 DBA 团队成员，人称强哥，朝阳一哥等，负责数据库管理平台的运维和 MySQL 问题处理。擅长对 MySQL 的故障定位。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

---

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

关于drop 大表的历史bug描述

根据https://bugs.mysql.com/bug.php?id=91977中的描述，对于大的buffer pool中的大表 drop 会占用mutex 锁，导致其它查询无法进行。提供的临时解决方案 为释放AHI(自适应哈希),预期解决版本是8.0.23。暂未从5.7的后期版本中找到解决方式

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

准备流程

测试配置

关闭 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

导入数据后使用表空间迁移的方式保留较大表的备份，方便后续连续测试

[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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

改回 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)
1
2
3
4
5
6
7
8
9
10
11

数据库预热

 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

测试结果

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

关闭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.00
1
2
3
4
5
6
7
8
9
10
11
12
13
14

8.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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

关闭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
1
2
3
4
5
6
7
8
9
10
11
12

打印堆栈

打印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", ) 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=, space=131, page_size=..., page=926336, ahi=, mtr=) 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=, 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=, pcur=, 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=) 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=) 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=, nonatomic=, handler=) 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=, 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=, 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

修复说明：

超过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的是，时间比历史版本的还要长，区别是不影响业务，猜测是降低锁的持有时间和粒度，使其它事务能够同时执行