MySQL drop table 影响及过程

在一个高负载的生产数据库上删除大表需要一些技巧,倘若直接drop table,将产生大量磁盘I/O,严重的会卡库,这是高可用服务所不能接受的。要优化删除表,需要了解其内部执行过程。

一、表删除过程
表删除原理上分为内存和磁盘两部分操作:

清除表相关的buffer pool页面。
删除表相关的磁盘文件。
1. buffer pool清除
删除表时,MySQL会将表在buffer pool中对应的页面清除,这个过程中会对buffer pool上一个全局排它锁。在MySQL 5.5.23以后的版本,实现了一个lazy drop table方式,以减轻清理内存结构的操作对系统吞吐量产生的波动,具体的过程:

1. 持有buffer pool mutex;
2. 持有buffer pool中的flush list mutex;
3. 开始扫描flush list;
1. 如果dirty page属于drop table,那么就直接从flush list中remove掉;
2. 如果删除的page个数超过了#define BUF_LRU_DROP_SEARCH_SIZE 1024 这个数目的话,释放buffer pool mutex,flush list mutex,释放cpu资源;
. 释放flush list mutex;
. 释放buffer pool mutex;
. 强制通过pthread_yield进行一次OS context switch,释放剩余的cpu时间片;
3. 重新持有buffer pool mutex;
4. 重新持有flush list mutext;
4. 释放flush list mutex;
5. 释放buffer pool mutex;

上面的过程中持有了buffer pool mutex和flush list mutex,从锁的保护范围来看,buffer pool mutex直观上瓶颈会比较明显。如果buffer pool很大,或者表有很多脏页面需要被清除,则持有mutex的时间会相对较长,导致其它事务在用到相应buffer pool实例时被阻塞,从而影响整个数据库性能。

这部分的优化的难点在于涉及源码,作为应用能做是先要保证被删除表上没有活动事务,最好是没有任何访问,然后在业务低峰期执行表删除操作。

2. 删除表相关的磁盘文件
这里只讨论采用独立表空间(innodb_file_per_table=1 )的innodb表删除。独立表空间在性能和运维上都大大强于共享表空间,也是当前绝大多数情况下的表存储方式。相对于内存扫描,删除磁盘文件对系统的影响要大得多。问题在于如果表文件过大,直接删除会瞬时占用大量I/O,造成IO阻塞。通常可以使用以下三个步骤删除大表:

创建表文件的硬链接。
drop table删除表。
删除表文件释放磁盘空间。
http://mysql.taobao.org/monthly/2016/01/07/

上一篇:HBase工作中的一些优化方法


下一篇:如何强制正在运行的程序使用外部方法将其I / O缓冲区的内容刷新到磁盘?