Performance Impact of Dropping Large Tables in MySQL 5.7 and 8.0: Bug Analysis and Mitigation

Background

The author, a DBA from Aikexing Beijing, observed a client database becoming unresponsive when a large table was dropped, tracing the issue to a known MySQL bug (ID 91977) where dropping big tables in a large buffer pool holds a mutex lock due to Adaptive Hash Index (AHI), blocking other queries. The temporary workaround was to disable AHI, with the permanent fix expected in MySQL 8.0.23.

Test Setup

Two MySQL versions were tested: 5.7.29 and 8.0.28, each configured with a 128 GB buffer pool and 24 GB tablespace usage. The benchmark imported data for 300 databases, disabled binlog, and adjusted "double‑one" settings.

#benchmark parameters
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
runTxnsPerTerminal=0
runMins=1
limitTxnsPerMin=0
terminalWarehouseFixed=true
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS

After data import, large tables were exported via FLUSH TABLES … FOR EXPORT and copied to a sandbox for safe testing.

Test Procedure

The buffer pool size was set to 128 GB, AHI was toggled on/off, and the DROP TABLE bmsql_stock command was executed while monitoring TPS/QPS with sysbench.

Results

MySQL 5.7.29

AHI enabled : DROP took 15.75 s; TPS/QPS dropped to zero during the operation.

mysql> drop table bmsql_stock;
Query OK, 0 rows affected (15.75 sec)
... (sysbench output showing TPS/QPS collapse) ...

AHI disabled : DROP reduced to 2.60 s; TPS/QPS remained stable.

mysql> drop table bmsql_stock;
Query OK, 0 rows affected (2.60 sec)
... (sysbench output showing normal TPS/QPS) ...

MySQL 8.0.28

AHI enabled : DROP took 2 min 34.82 s but did not affect TPS/QPS noticeably.

mysql> drop table bmsql_stock;
Query OK, 0 rows affected (2 min 34.82 sec)
... (sysbench output) ...

AHI disabled : DROP completed in 0.58 s with no impact on workload.

mysql> drop table bmsql_stock;
Query OK, 0 rows affected (0.58 sec)
... (sysbench output) ...

Stack Trace (5.7.29)

Thread 69 (Thread 0x7fa088139700 (LWP 397558)):
#0  ha_delete_hash_node ...
#1  ha_remove_all_nodes_to_page ...
#2  btr_search_drop_page_hash_index ...
#3  btr_search_drop_page_hash_when_freed ...
#4  fseg_free_extent ...
#5  fseg_free_step ...
#6  btr_free_but_not_root ...
#7  btr_free_if_exists ...
#8  dict_drop_index_tree ...
#9  row_upd_clust_step ...
#10 row_upd ...
#11 row_upd_step ...
#12 que_thr_step ...
#13 que_run_threads_low ...
#14 que_run_threads ...
#15 que_eval_sql ...
#16 row_drop_table_for_mysql ...
#17 ha_innobase::delete_table ...
#18 ha_delete_table ...
#19 mysql_rm_table_no_locks ...

Fix Description

Starting with MySQL 8.0.23, the drop‑table process for tables larger than 32 GB in the buffer pool no longer immediately frees dirty pages and AHI entries. Instead, a lazy‑delete mechanism is used, dramatically reducing lock hold time and minimizing impact on concurrent transactions.

Conclusion

Dropping a large table consists of three phases: (1) traversing the LRU list to evict dirty pages belonging to the table, (2) cleaning up AHI entries, and (3) deleting the file from the filesystem. The first two phases are the most time‑consuming and can block other queries. The 8.0.23 fix optimizes the first phase with lazy deletion and improves the second by reducing lock granularity, allowing business workloads to continue with minimal disruption.