InnoDB Drop Table的机制及瓶颈

一、背景

在使用 MySQL 时，如果有大表的存储引擎是 InnoDB，并且系统参数 innodb_file_per_table 设置为 1，即每个文件对应一个独立的表空间，当对这些大表进行 DROP TABLE 时，有时会发现整个数据库系统的性能会有显著下降，包括一些只涉及几行数据的简单 SELECT 查询和 DML 语句，而且这些语句和正在删除的大表没有关系。造成这种现象的原因是什么呢？通过什么方式能缓解和避免这个问题呢？

二、已知的瓶颈

Percona 曾经在 MySQL 官方 5.5.23 之前的版本中遇到过这个问题，并且提供了一种叫 Percona Lazy Drop 的补丁。简单来说，他们认为这个问题的瓶颈在 CPU。在删除一个有独立表空间的大表时，需要对 Buffer Pool 中所有和这个表空间有关的数据页做清理工作，包括从 AHI，flush list 和 LRU list 上移除，而在这个清理过程中，会一直持有 buffer pool 的 mutex。如果 buffer pool 配置特别大，比如 500 GB 大小，持有这个 mutex 的时间会较长，导致其他连接被阻塞住，从而导致系统性能的下降。Percona Lazy Drop 就是在清理 buffer pool 这里做了优化，尽量短时间和小粒度的持有 mutex。

MySQL 官方在 5.5.23 版本中也实现了一个 lazy drop 的功能，但和 Percona 的实现方式不一样：在移除 flush list 时，会有一个条件判断，如果已经处理了超过一定数量的 page，会强制释放当前持有的 buffer pool mutex 和 flush list mutex，并且让出 CPU，过一会儿再重新拿回锁继续清理 flush list；对于 LRU list，则不做处理，因为当这个表被删除后，这些数据页最终会在 LRU 算法调度下被回收。

相关的函数调用图为：

buf_LRU_flush_or_remove_pages(BUF_REMOVE_FLUSH_NO_WRITE) --> buf_LRU_remove_pages --> buf_flush_dirty_pages --> buf_pool_mutex_enter
                                                                                                            |__ buf_flush_or_remove_pages --> buf_flush_list_mutex_enter
                                                                                                            |                             |__ buf_flush_try_yield
                                                                                                            |                             |__ buf_flush_list_mutex_exit
                                                                                                            |__ buf_pool_mutex_exit

buf_LRU_flush_or_remove_pages(BUF_REMOVE_FLUSH_NO_WRITE) --> buf_LRU_remove_pages --> buf_flush_dirty_pages --> buf_pool_mutex_enter

|__ buf_flush_or_remove_pages --> buf_flush_list_mutex_enter

| |__ buf_flush_try_yield

| |__ buf_flush_list_mutex_exit

|__ buf_pool_mutex_exit

Percona 在后续版本中移植了 MySQL 官方 5.5.23 中的 lazy drop，并且移除了自己之前实现的 Percona Lazy Drop 方案。

三、IO 问题

尽管已经有了上述的 Buffer Pool 层面的优化，我们在使用 MySQL 5.6 或者 5.7 时依然发现删除大表对系统性能还是会产生显著的影响，说明 DROP TABLE 还有其他的性能瓶颈，尤其是对于这样一种业务场景：并发地删除多个大表。在这种场景下，数据库实例几乎处于不可服务的状态。通过抓取这种状态下的 mysqld 进程的堆栈，我们发现此时性能瓶颈不在上面介绍的 buffer pool mutex 上，而是在删除 ibd 文件的 IO 上。IO 的瓶颈是怎样具体地导致系统性能下降的呢？

通过阅读 MySQL 5.7 的源码可以看到，整个 DROP TABLE 过程可以简单地概括为：

获取 dict_sys->mutex 这个数据字典锁
启动一个 InnoDB 事务
更新数据字典，包括内存中的数据和 mysql 库下的数据字典表
lazy drop 逻辑，清理 buffer pool 的 flush list，会多次持有和释放 buffer pool mutex 以及 flush list mutex
写入 MLOG_FILE_DELETE 类型的 redo 日志
unlink ibd 文件
提交 InnoDB 事务
释放 dict_sys->mutex

这个过程表明，删除 ibd 文件时是持有 dict_sys->mutex 的，如果文件大小很大，比如 50GB 以上，unlink 操作会比较耗时，dict_sys->mutex 会较长时间被当前连接持有，此时，并发的其他 DROP TABLE 会阻塞在 dict_sys->mutex 上，更糟糕的是，并发的被阻塞的 DROP TABLE 会持有所有的 table cache lock，因为它是在清理 table cache 中和该表相关的数据时被 dict_sys->mutex 阻塞的。于是，会导致接下来的几乎所有语句都阻塞在 table cache lock 上，从 show processlist 结果上看，就表现为 SELECT 和 DML 语句状态是 ”Opening tables”，而 DROP TABLE 语句状态为 ”Checking permissions”。被阻塞的 DROP TABLE 语句（持有所有 table cache lock）的相关函数调用为：

mysql_execute_command --> mysql_rm_table --> tdc_remove_table --> table_cache_manager.lock_all_and_tdc
                                                              |__ Table_cache_manager::free_table --> intern_close_table --> closefrm --> ha_innobase::close --> row_prebuilt_free --> dict_table_close --> mutex_enter(&dict_sys->mutex)
                                                              |__ table_cache_manager.unlock_all_and_tdc

mysql_execute_command --> mysql_rm_table --> tdc_remove_table --> table_cache_manager.lock_all_and_tdc

|__ Table_cache_manager::free_table --> intern_close_table --> closefrm --> ha_innobase::close --> row_prebuilt_free --> dict_table_close --> mutex_enter(&dict_sys->mutex)

|__ table_cache_manager.unlock_all_and_tdc

DROP TABLE 的源代码调用关系大致为：

row_drop_table_for_mysql --> row_mysql_lock_data_dictionary
                         |__ trx_start_for_ddl
                         |__ clean up data dictionary
                         |__ row_drop_table_from_cache
                         |__ row_drop_single_table_tablespace --> fil_delete_tablespace --> buf_LRU_flush_or_remove_pages
                         |                                                              |__ os_file_delete --> unlink
                         |__ row_mysql_unlock_data_dictionary

row_drop_table_for_mysql --> row_mysql_lock_data_dictionary

|__ trx_start_for_ddl

|__ clean up data dictionary

|__ row_drop_table_from_cache

|__ row_drop_single_table_tablespace --> fil_delete_tablespace --> buf_LRU_flush_or_remove_pages

| |__ os_file_delete --> unlink

|__ row_mysql_unlock_data_dictionary

四、AHI（Adaptive Hash Index）

AHI 默认开启，在 Buffer Pool 较大的情况下，意味着 AHI 所占用的空间也变大。当 DROP TABLE 时，InnoDB 引擎还会删除表对应的 AHI（自适应哈希索引），这期间会持有 AHI X 锁。查询请求使用 AHI 定位的时候，有 AHI S 锁的等待；UPDATE 操作的时候也需要 AHI X 锁。

处理 AHI 的这个过程也是在持有全局字典互斥锁的保护下做的，另外把锁又分别和 Master Thread 和用户线程互斥，所以导致在删除 AHI 时，会有大量的 Opening tables 用户线程状态显示。

对于这个问题，可以在 DROP TABLE 的时候关闭 AHI 功能，推荐直接关闭 AHI 功能。在当前硬件条件下，此功能除了增加 CPU 使用率，对性能基本没有任何帮助。即便是主键查询的压测，依然如此。

五、TRUNCATE

https://bugs.mysql.com/bug.php?id=68184，在这个 bug 中，分析了 truncate table 会比 drop table 在删除 buffer pool page 慢的本质原因，是因为 truncate table 需要复用 space id，这导致必须把 buffer pool 中的老的表中的页全部删除，而 drop table 因为新旧表的页可用通过 space id 区分，只需要把 flush list 中的脏页删除就可以了。

六、解决方案

从上面介绍可以看到，DROP TABLE 可能存在两个性能瓶颈，一个是 buffer pool 的清理，另一个是 ibd 文件的删除，怎么缓解或者解决这个问题呢？

1. 不改动源码情况

尽可能在低峰期进行删除表的操作；
对于 buffer pool 问题，适当增大 innodb_buffer_pool_instances 参数，尽量减小持有 buffer pool mutex 对其他语句的影响；
对于 IO 问题，删除表之前对 ibd 文件手动创建一个硬链接，让 DROP TABLE 可以快速执行结束，等到低峰期再真正从磁盘上删除文件；

2. 改动源码方案

考虑在 unlink 文件前释放 dict_sys->mutex，dict_sys->mutex 是用来保护内存中的数据字典，以及 mysql 库下的数据字典表，从源码可以看到，在 unlink ibd 文件之前，所有的数据字典操作已经结束，其实是可以释放掉 dict_sys->mutex 的。这个方案可以快速简单地解决 dict_sys->mutex 是性能瓶颈的问题，但是，这个方案是不安全的，因为提前释放 dict_sys->mutex 意味着需要提前提交 innodb 事务，于是上述的 DROP TABLE 过程变为：那么就会存在这样一个情况：如果系统 crash 发生在释放 dict_sys->mutex 后和 unlink 文件前，那么当数据库重新启动后，从 InnoDB 数据字典的角度看，DROP TABLE 事务已经成功提交了，数据字典里已经没有这个表了，但是磁盘上还存在 ibd 文件；这个问题一方面会导致磁盘空间的浪费，另外一方面会导致 CREATE TABLE 失败，如果表名和之前删除的表名一样。可能有人会问，不是在提交事务前写入了一条 MLOG_FILE_DELETE 类型的 redo 日志吗，那么数据库启动后重做 redo 日志时难道不会删除磁盘上遗留的 ibd 文件？遗憾的是，现在 crash recovery 的逻辑中，对于 MLOG_FILE_DELETE 类型的日志，只把它当作是一条“告知”含义的日志记录，不会去删除本应被删除的文件（在 UNIV_HOTBACKUP 代码分支的 crash recovery 逻辑中会，但这个分支不在正常的 server 编译路径中）。如果想要用这个方案，就需要修改 MLOG_FILE_DELETE 日志的语义，也就是说在 crash recovery 中，当执行这条日志时，如果磁盘上该文件还存在，则将该文件删除，同时，将 DROP TABLE 过程调整为：

获取 dict_sys->mutex 这个数据字典锁
启动一个 innodb 事务
更新数据字典，包括内存中的数据和mysql库下的数据字典表
lazy drop逻辑，清理buffer pool的flush list，会多次持有和释放buffer pool mutex以及flush list mutex
启动一个mini-transaction
写入MLOG_FILE_DELETE类型的redo日志
提交innodb事务
释放dict_sys->mutex
提交mini-transaction
unlink ibd文件
获取dict_sys->mutex这个数据字典锁
启动一个innodb事务
更新数据字典，包括内存中的数据和mysql库下的数据字典表
lazy drop逻辑，清理buffer pool的flush list，会多次持有和释放buffer pool mutex以及flush list mutex
写入MLOG_FILE_DELETE类型的redo日志
提交innodb事务
释放dict_sys->mutex
unlink ibd文件

假设已经没有 dict_sys->mutex 瓶颈，单纯地 unlink 一个大文件也会影响系统其他的 IO 操作，因为需要涉及大量的文件系统日志操作（详见之前推送的文章”Linux删除文件过程解析”），所以另一个可以考虑的方案是将一个大文件的删除分成多次的 truncate 操作。具体来说，在 DROP TABLE 中，将之前的 unlink 调用替换为 rename，将 ibd 文件加上一个后缀，比如 .trash，然后将这个重命名后的文件加入到一个队列中，DROP TABLE 就继续后面的提交事务释放锁。真正的文件删除可以在 innodb master thread 中进行，或者重新启动一条专门的线程负责在后台从队列中拿文件并小批量 truncate。这个方案需要注意的一个问题是重命名的文件名不能重复，因为有 dict_syc->mutex 的保护，用一个简单的计数器就可以实现，但考虑到重启的情况，用时间戳会更好一些；另一个问题是，如果重命名的文件依然在之前的数据库目录下，那么在后台线程真正删除掉文件之前，DROP DATABASE 会失败，一种方案是 DBA 可以手动删除掉 trash 文件，另一个方案是让用户指定一个其他的临时目录来存放 rename 后的文件，注意临时文件目录需要和数据目录在同一个挂载点下。

五、总结

本文介绍了 MySQL DROP TABLE 可能存在的性能瓶颈，导致瓶颈的具体原因，以及相关的解决方案和思路。

<转载>

http://mysql.taobao.org/monthly/2020/08/01/

https://cloud.tencent.com/developer/article/1006978

https://mp.weixin.qq.com/s/nW9FARyeqfjQktpSaQkASQ

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。