Description:
Drop table has been optimized in worklog: https://dev.mysql.com/worklog/task/?id=14100, but it not work for general space(innodb_file_per_table=OFF).
We need to free all btr(btr_free_if_exists) when dropping a table in general space and acquire dict_sys mutex all time. The code is :
```
void Log_DDL::replay_free_tree_log(space_id_t space_id, page_no_t page_no,
ulint index_id) {
/* This is required by dropping hash index afterwards. */
dict_sys_mutex_enter();
btr_free_if_exists(page_id_t(space_id, page_no), page_size, index_id, &mtr);
dict_sys_mutex_exit();
}
```
If the table is big, like 500GB for example, it will take several seconds to free btr. Other user threads may need to open table to execute DML and that also need acquire dict_sys mutex. We can see a performance penalty here.
How to repeat:
mysqld configuration:
1. innodb_file_per_table is OFF
2. disable AHI
3. we should have a big enough Buffer Pool, 8GB in my test.
4. table definition cache not big enough to cache all tables.
```
mysql> show variables like "%file_per_table%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
1 row in set (0.004 sec)
mysql> show variables like "innodb_adaptive_hash_index%";
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index | OFF |
| innodb_adaptive_hash_index_parts | 8 |
+----------------------------------+-------+
2 rows in set (0.002 sec)
mysql> show variables like "innodb_buffer_pool_size";
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 8858370048 |
+-------------------------+------------+
1 row in set (0.004 sec)
mysql> show variables like "%open_cache%";
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| table_open_cache | 4000 |
| table_open_cache_instances | 16 |
| table_open_cache_triggers | 524288 |
+----------------------------+--------+
3 rows in set (0.005 sec)
```
step 1. prepare two big tables in database sbtest:
```
sysbench oltp_write_only --db-driver=mysql --mysql-db=sbtest --mysql-socket=/path/to/socket/mysqld.sock --mysql-user=root --mysql-db=sbtest --tables=2 --table-size=1500000000 --threads=2 prepare
```
2. prepare a lot of tables in database mytest, the number of tables should bigger than table open cache.
```
sysbench oltp_write_only --db-driver=mysql --mysql-db=mytest --mysql-socket=/path/to/socket/mysqld.sock --mysql-user=root --mysql-db=mytest --tables=4000 --table-size=100 --threads=32 prepare
```
3. sysbench execute read only DML
```
sysbench oltp_read_only --db-driver=mysql --mysql-db=mytest --mysql-socket=/path/to/socket/mysqld.sock --mysql-user=root --mysql-db=mytest --tables=4000 --table-size=100 --threads=32 --time=600 --report-interval=1 --db-ps-mode=disable run
```
4. drop big table
```
mysql> drop table sbtest1;
Query OK, 0 rows affected (2.133 sec)
```
5. performance penalty in 61s ~ 63s
```
[ 58s ] thds: 32 tps: 5040.00 qps: 80615.00 (r/w/o: 70534.00/0.00/10081.00) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 32 tps: 5061.65 qps: 80949.25 (r/w/o: 70827.96/0.00/10121.29) lat (ms,95%): 28.67 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 32 tps: 5068.12 qps: 81113.95 (r/w/o: 70976.70/0.00/10137.25) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 61s ] thds: 32 tps: 1434.80 qps: 22995.79 (r/w/o: 20125.18/0.00/2870.61) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 62s ] thds: 32 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
[ 63s ] thds: 32 tps: 2782.56 qps: 44478.92 (r/w/o: 38914.80/0.00/5564.11) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 64s ] thds: 32 tps: 5016.98 qps: 80314.76 (r/w/o: 70279.79/0.00/10034.97) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 32 tps: 5061.16 qps: 80928.59 (r/w/o: 70807.27/0.00/10121.32) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 66s ] thds: 32 tps: 4654.85 qps: 74516.62 (r/w/o: 65206.91/0.00/9309.70) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00
```
Suggested fix:
The problem is holding dict_sys mutex to free btr, we need to acquire mutex to drop AHI according to the code comment.
Actually AHI must have been dropped before Log_DDL::replay_free_tree_log, so we can remove the dict_sys_mutex_enter() in Log_DDL::replay_free_tree_log.
drop table code stack is :
```
mysql_rm_table_no_locks
|---> drop_base_table
|---> ha_delete_table
|---> innobase_basic_ddl::delete_impl<dd::Table>
|----> row_drop_table_for_mysql
|----> row_drop_table_from_cache
|----> btr_drop_ahi_for_table
|----> dict_table_remove_from_cache
|---> hton->post_ddl(thd);
|---> Log_DDL::replay_free_tree_log
|----> btr_free_if_exists
```
AHI has been dropped before execute post DDL and dd cache cleared(dict_index is freed), no more AHI add to page as MDL X lock acquired. So we do not need to acquire dict_sys mutex and do not need to drop AHI in Log_DDL::replay_free_tree_log.
© 2025, Oracle Corporation and/or its affiliates