MySQL删除InnoDB大表

ゞ浴缸里的玫瑰 2022-09-08 11:53 286阅读 0赞

> 在使用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

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

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

## **三、解决方案** ##

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

### **3.1 不改动源码情况** ###

*  尽可能在低峰期进行删除表的操作；
 *  对于buffer pool问题，适当增大innodb\_buffer\_pool\_instances参数，尽量减小持有buffer pool mutex对其他语句的影响；
 *  对于IO问题，删除表之前对ibd文件手动创建一个硬链接，相当于有两个文件名指向同一innode;
 *  DROP TABLE可以快速执行结束，这一步只是把文件引用删除了；

# ln /data/mysql/test/test.ibd /data/mysql/test/test.ibd.hdlk
    mysql> drop table test;

*  当表极大时，不要直接rm删除，这样会引起磁盘IO转速上升，MySQL会发生性能抖动，使用下面脚本删除

#!/bin/bash
    
    TRUNCATE=/usr/bin/truncate
    
    for i in `seq 100 -1 0 ` 
    #从100G 开始每次递减1G，最终让文件变成0
    do 
      sleep 2
      echo "$TRUNCATE -s ${i}G /data/mysql/test/test.ibd.hdlk"
      $TRUNCATE -s ${i}G /data/mysql/test/test.ibd.hdlk
    done
    
    注：先ll -h test.ibd.hdlk看一下该文件有多少G，然后输入seq后面，上述例子为100G。

### **3.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可能存在的性能瓶颈，导致瓶颈的具体原因，以及相关的解决方案和思路。

**Reference**

[https://cloud.tencent.com/developer/article/1143416][https_cloud.tencent.com_developer_article_1143416]  
[https://blog.51cto.com/hcymysql/2130142][https_blog.51cto.com_hcymysql_2130142]  
[https://www.jb51.net/article/145889.htm][https_www.jb51.net_article_145889.htm]

[https_cloud.tencent.com_developer_article_1143416]: https://cloud.tencent.com/developer/article/1143416
[https_blog.51cto.com_hcymysql_2130142]: https://blog.51cto.com/hcymysql/2130142
[https_www.jb51.net_article_145889.htm]: https://www.jb51.net/article/145889.htm