首页W3C技术头条如何drop掉mysql库中的1TB表单

如何drop掉mysql库中的1TB表单

w3cschool小编 2020-08-24 11:19:21 浏览数 (278)
反馈

如果问你怎么从MySQL数据库空drop一张表,很多人估计会说,很简单啊,用drop table t_test语句不就行了。这可真是初生牛犊不怕虎,你要真觉得这么简单的话,去线上业务库中drop掉一张1TB大小的表,造成长时间的业务无法访问数据库,更严重,导致数据库崩溃,宕机都是可能的。

下面就先聊聊,drop table语句背后的事情,语句执行之后,主要做2两件事情

1、清除Buffer Pool缓冲

drop table时,innodb引擎会清理该表在每个buffer pool实例中中对应的数据块页面,为了避免对系统的影响,这里的清除操作并不是真正的flush,而是将涉及到的页面从flush队列中摘除。但在摘除过程中,删除进程会持有每个buffer pool的全局锁,然后搜索这个buffer pool里对应的页面以便从flush list中删除。如果在buffer pool中需要被搜索并删除的页面过多,那么遍历时间就会增大,这就导致了其他事务操作被阻塞,严重时可导致数据库锁住。

(推荐课程:MySQL教程)

在这里还需要注意一件事情,如果数据库的buffer pool设置的很大,就会导致遍历时间变长 清理buffer pool时,还包含清理AHI包含此表的数据,AHI的功能在这里就不多说了,主要是当b+tree的层级变高时,为避免b+tree逐层搜索,AHI能根据某个检索条件,直接查询到对应的数据页,跳过逐层定位的步骤。其次AHI会占用 1/16 的buffer pool的大小,如果线上表数据不是特别大,不是超高并发,不建议将开启AHI,可以考虑关闭AHI功能

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name                   | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)


mysql> SET GLOBAL innodb_adaptive_hash_index=OFF;
Query OK, 0 rows affected (0.00 sec)


mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name                   | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

2、删除对应的磁盘数据文件ibd

在删除数据文件时,如果数据文件过大,删除过程会产生大量的IO并耗费更多的时间,造成磁盘IO开销飙升,CPU负载过高,影响其他程序运行。我的一个好伙伴,就曾在线上库删除了一张 1TB 大小的表,结果20分钟,数据库无响应,最后库崩溃,重启了。

既然知道drop table做了2件事情,那就针对以上 2 个事情进行优化

在清除Buffer Pool缓冲上,为减少当个buffer pool的大小,可以合理设置innodb_buffer_pool_instances参数,减少buffer pool数据块列表扫描时间,同时关闭AHI功能

在步骤2上,可以巧妙的利用linux的硬连接特性,延迟删除真正的物理文件。

首先看看linux系统的硬链接示意图

硬链接示意图

当多个文件名同时指向同一个INODE时,这个INODE的引用数 N>1, 删除其中任何一个文件名都会很快.因为其直接的物理文件块没有被删除.只是删除了一个指针而已;当INODE的引用数 N=1 时, 删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时;

如果给数据库表的.ibd文件创建一个硬链接,当删除表时,删除物理文件时,其实删除的就是物理文件的一个指针,所以删除操作响应速度会非常快,大约不到1秒左右

下面就来演示一下具体的操作

先创建表文件的硬链接
ln t_test.ibd t_test.ibd.bak
删除表
drop table t_test;

最后就是要真正删除掉物理文件,释放文件所占用的磁盘空间,那么问题来了,如果优雅的删除物理文件呢,在这里推荐大家coreutils工具集中的truncate命令

当然需要你先安装相关的软件包

wget http://ftp.gnu.org/gnu/coreutils/coreutils-8.29.tar.xz


使用非root进行解压
tar -xvJf coreutils-8.29.tar.xz
cd coreutils-8.29
./configure
make
使用root进行make install

安装好之后,就可以写一个脚本,非常优雅的分布删除大文件,${i}G 表示,每次删除 10G

#!/bin/bash


TRUNCATE=/usr/local/bin/truncate
for i in `seq 2194 -10 10 `; 
do 
  sleep 2
  $TRUNCATE -s ${i}G /data/mysql/t_test.ibd.hdlk 
done
rm -rf /data/mysql/t_test.ibd.hdlk ;

最后,给大家一个建议,不要在业务高峰期做drop table操作,一定要在业务低峰期做。

(推荐微课:MySQL微课)

文章来源:www.toutiao.com/a6863864032139411975/

以上就是W3Cschool编程狮关于 如何drop掉mysql库中的1TB表单 的相关介绍了,希望对大家有所帮助。

0 人点赞

相关课程 更多»

{{ relateCourse.bookname }}

{{ relateCourse.description }}

{{ relateCourse.watch_count }} 人参与

立即学习
相关文章 更多»
{{ relateArticle.title }} {{ relateArticle.postdate }}

最新文章

热门文章

更多»