mysql删除大表更快的drop table办法
来源:互联网 发布:剑灵邀请一直网络忙 编辑:程序博客网 时间:2024/06/05 13:21
这是因为INNODB会维护一个全局独占锁(在table cache上面),直到DROP TABLE完成才释放。
在我们常用的ext3,ext4,ntfs文件系统,要删除一个大文件(几十G,甚至几百G)还是需要点时间的。
下面我们介绍一个快速DROP table 的方法; 不管多大的表,INNODB 都可以很快返回,表删除完成;
众所周知drop table会严重的消耗服务器IO性能,如果被drop的表容量较大,甚至会影响到线上的正常。
首先,我们看一下为什么降容量大的表会影响线上服务
直接执行drop table,MySQL会将表定义和表数据全都删除,包括磁盘上的物理文件,也包括缓冲池中的内存数据。
这么分两步,第一步从缓冲池中删除,这会涉及到table_cache的锁,如果持有table_cache的锁,这将导致其他查询都无法执行。这种情况在没有innodb_per_table之前尤为严重。另外,mysql5.5.23之后添加lazy drop table功能,这个功能用来解决互斥的LRU列表。其中心思想就是加锁,找到需要被删除的页面,删除1024个页之后释放锁让其他线程工作,之后循环而percona的lazy drop处理起来更优雅一些,其会先加锁,然后找到需要被删除的页面,标记,释放锁,后台慢慢删除。
之后就是第二步,这步在大容量表的时候更为消耗时间,那就是在OS上删除物理文件。大家都知道在EXT3上RM一个200G的文件会非常耗时,这是由于EXT3存储数据的结构导致,如果一个很大的文件,EXT3的i_block无法直接存放,需要多层嵌套才能完全存储下,在这种情况下由于映射的层次多,并且由于多层映射也不会是顺序存储的,就导致了很大的随机IO,这就导致了删除物理文件非常慢的现象。在这种情况下,建议升级到EXT4,这是由于EXT4比ext3的使用程度分配存储空间,其最大的优势就是顺序存储。
EXT3:
EXT4:
知道了原因,我们来说说如何解决具体步骤如下:
1,建立硬链接。
ln table.ibd table.idb.hdlk
2,mysql执行drop table操作。
drop table if exists tablename;
3,使用截断删除物理文件。
truncate -s 1024 * 1024 * 4 filename
其实硬链接和drop table就不用多说了,在建立硬链接之后,mysql会认为rm了硬链接文件之后就算操作完毕,不会真正去删除物理文件从而提高了速度。但是对于服务器来说,实际的物理文件还在,如果手动RM,还是会产生很多的IO影响,这时候就用到了截断这个工具。这个工具会根据指定的尺寸大小进行逐步删除,会将对IO造成的影响降到最低。
用法:truncate OPTION ... FILE ...缩小或扩展每个FILE的大小到指定的大小创建不存在的FILE参数。如果文件大于指定的大小,则额外的数据将丢失。如果文件较短,则延长部分(孔)读为零字节。长期期权的强制性论据也适用于 短期期权。 -c,--no创建 做不创建任何文件 -o,--io- 块对待SIZE作为IO块而不是字节数 -r,--reference = 上的RFile的RFile基底大小 -s,--size = SIZE通过SIZE设置或调整文件大小 - 帮助显示此帮助和退出 - 版本输出版本信息并退出SIZE可以是(或可以是可选的后面的整数)以下之一:对于G,T,P,E,Z,Y,KB 1000,K 1024,MB 1000 * 1000,M 1024 * 1024等。SIZE也可以前缀为以下修改字符之一:` + ' 延伸,` - '减少,`< ' 至多,`> ' 至少,` / ' 循环到多个,'%' 轮到多个。报告截断bug - coreutils@gnu.orgGNU coreutils主页: http:// www.gnu.org/software/coreutils/> 一般帮助使用GNU软件:<http:// www.gnu.org/gethelp/> 有关完整的文档,请运行:info coreutils ' 截断调用“
root@127.0.0.1 : test 21:39:34> drop table tt ;
Query OK, 0 rows affected (25.01 sec)
删除一个11G的表用时25秒左右(硬件不同,时间不同);
下面我们来对另一个更大的表进行删除;
但之前,我们需要对这个表的数据文件做一个硬连接:
root@ # ln stock.ibd stock.id.hdlk
root@ # ls stock.* -l
-rw-rw—- 1 MySQL mysql 9196 Apr 14 23:03 stock.frm
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.ibd
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk
你会发现stock.ibd的INODES属性变成了2;
root@127.0.0.1 : test 21:39:34> drop table stock ;
Query OK, 0 rows affected (0.99 sec)
1秒不到就删除完成; 也就是DROP TABLE不用再HANG这么久了。
但table是删除了,数据文件还在,所以你还需要最后数据文件给删除。
root # ll
total 19096666112
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk
root # rm stock.id.hdlk
虽然DROP TABLE 多绕了几步。(如果你有一个比较可靠的自运行程序(自动为大表建立硬链接,并会自动删除过期的硬链接文件),就会显得不那么繁琐。)
这样做能大大减少MYSQL HANG住的时间; 相信还是值得的。
至于原理: 就是利用OS HARD LINK的原理,
当多个文件名同时指向同一个INODE时,这个INODE的引用数N>1, 删除其中任何一个文件名都会很快.
因为其直接的物理文件块没有被删除.只是删除了一个指针而已;
当INODE的引用数N=1时, 删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时;
- mysql删除大表更快的drop table办法
- mysql删除大表更快的drop table办法
- mysql删除大表更快的drop table办法
- mysql删除大表更快的drop table办法
- mysql删除大表更快的drop table办法
- mysql删除大表更快的drop table办法
- mysql删除大表更快的drop table办法(转老金)
- Mysql命令drop table:删除数据表
- MySQL命令drop table:删除数据表
- mysql:sql drop table (删除表)
- mysql删除大表更快的办法
- 恢复被drop table删除的表
- MySQL DROP TABLE操作以及 DROP 大表时的注意事项
- MySQL DROP TABLE操作以及 DROP 大表时的注意事项
- MySql批量drop table
- MySql批量drop table
- MySql批量drop table
- CREATE TABLE、ALTER TABLE、DROP TABLE等,及表内列的添加、删除以及列的修改
- pageEncoding和charset 编码 以及<%@ page contentType="text/html; charset=utf-8"....>作用
- attention 机制入门
- 以开始、中间、最后为基准的递归快排实现
- BZOJ 2442 [Usaco2011 Open]修剪草坪 DP+单调队列优化
- UVA 10815 (字符串处理)
- mysql删除大表更快的drop table办法
- Linux软件安装
- 网络编程和反射--简单讲解
- IDA pro脱壳实战过反调试
- 注册Google Account账号两天仍旧失败后,终于迎来激动人心的时刻!(此电话号码无法用于进行验证)
- GIT 远程仓库:添加远程库、从远程库克隆
- 颜色分类
- Linux下挂载iso文件和配置yum本地源
- c++抛出异常方式与C语言的区别