【腾讯云CDB】如何快速删除InnoDB中的大表

1. 背景

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

2. 已知的瓶颈

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方案。

3. 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

4. 解决方案

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

4.1 不改动源码情况

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

4.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后的文件,注意临时文件目录需要和数据目录在同一个挂载点下。

5. 总结

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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

YH4:Oracle Flex Clusters

从Oracle数据库12c开始,可以将Oracle Clusterware和Oracle RAC配置在大型集群中,称为Oracle Flex集群。 这些集群包含...

2595
来自专栏Java进阶架构师

「mysql优化专题」什么是慢查询?如何通过慢查询日志优化?(10)

日志就跟人们写的日记一样,记录着过往的事情。但是人的日记是主观的(记自己想记的内容),而数据库的日志是客观的,根据记录内容分为以下好几种日志:

813
来自专栏Java Edge

Jedis连接池1. 什么是连接池2. Jedis的连接池3. 源码解析4. 总结

3325
来自专栏散尽浮华

Mysql优化系列(0)--总结性梳理

对于一个网站来说,在运行很长一段时间后,数据库瓶颈问题会越来越暴露出来。作为运维人员,对数据库做必要的优化十分重要! 下面总结以往查阅到的以及自己工作中的一些优...

1908
来自专栏MYSQL轻松学

Mysql5.5&Mysql5.6&Mysql5.7特性

Mysql5.5 特性,相对于Mysql5.1 性能提升 默认InnoDB plugin引擎。具有提交、回滚和crash恢复功能、ACID兼容。 行级锁(一致性...

3525
来自专栏PHP在线

php连接mysql数据库的几种方式(mysql、mysqli、pdo)

php与mysql的连接有三种API接口,分别是:PHP的MySQL扩展 、PHP的mysqli扩展 、PHP数据对象(PDO) ,下面针对以上三种连接方式做下...

3878
来自专栏张秀云的专栏

MySQL 压缩解决方案(一)

本文描述 mysql 压缩的使用场景和解决方案,包括压缩传输协议、压缩列解决方案和压缩表解决方案。

2.4K1
来自专栏idba

MySQL常见slave延迟原因以及解决方法

一 序言 在运维线上M-M 架构的MySQL数据库时,接收的比较多关于主备延时的报警:

873
来自专栏Spark学习技巧

Kafka源码系列之如何删除topic

本文依然是以kafka0.8.2.2为例讲解 一,如何删除一个topic 删除一个topic有两个关键点: 1,配置删除参数 delete.topic.enab...

3359
来自专栏令仔很忙

Mybaits深入了解(一)----带你入门

MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google ...

691

扫码关注云+社区