原创

快速安全删除MySQL大表

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://wxy0327.blog.csdn.net/article/details/102318556

目录

一、表删除过程

1. buffer pool清除

2. 删除表相关的磁盘文件

二、创建硬链接

三、删除表

四、删除文件释放空间

参考:


        在一个高负载的生产数据库上删除大表需要一些技巧,倘若直接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删除表。
  • 删除表文件释放磁盘空间。

二、创建硬链接

        一个磁盘上的存储文件,可以由多个文件名引用。这多个文件是完全相同的,都指向同一个磁盘上的inode index。当我们删除任何一个文件的时候,都不会影响真实的存储文件,只是会将其引用数据减1,只有当被引用数目变为1时,再次删除文件,才会真正被删除。

        例如,在没有硬链接时,t1表对应的磁盘文件如下:

-rw-r----- 1 mysql mysql        17973 Jul  3  2018 t1.frm
-rw-rw---- 1 mysql mysql 498115543040 Oct  7 17:14 t1.ibd

其中第二个字段的“1”表示文件只有一个inode指针,此时删除t1表会实际删除磁盘文件。创建硬链接后:

ln t1.frm t1.frm.h
ln t1.ibd t1.ibd.h

每个表文件有两个inode引用:

-rw-r----- 2 mysql mysql        17973 Jul  3  2018 t1.frm
-rw-r----- 2 mysql mysql        17973 Jul  3  2018 t1.frm.h
-rw-rw---- 2 mysql mysql 498115543040 Oct  7 17:14 t1.ibd
-rw-rw---- 2 mysql mysql 498115543040 Oct  7 17:14 t1.ibd.h

再删除表时,只是删除了inode引用,此操作非常快,不会影响正常服务。可以使用以下脚本对一个数据库内的所有表创建硬链接:

# MySQL数据目录
datadir=`mysql -uroot -p123456 -S /data/mysqldata/mysql.sock -e "show variables like 'datadir'" -N -B | awk '{print $2}'`
# 数据库名
dbname='dbname'

cd $datadir/$dbname
# 创建硬链接
ls *.{ibd,frm} | awk '{print "ln "$0" "$0".h"}' | bash

三、删除表

drop table t1;

        MySQL的drop table操作会删除系统表中t1的相关记录,同时删除表的t1.frm与t1.ibd文件。如上所述,此操作可以瞬间完成,之后剩下两个.h文件:

-rw-r----- 1 mysql mysql        17973 Jul  3  2018 t1.frm.h
-rw-rw---- 1 mysql mysql 498115543040 Oct  7 17:14 t1.ibd.h

四、删除文件释放空间

        t1表虽然秒删,但并没有释放磁盘空间。下面我们需要删除t1.frm.h与t1.ibd.h文件以真正释放空间。此时不能直接rm这个接近500G的文件,原因同样是会造成I/O冲击。相应的对策就是使用coreutils的truncate工具,逐次缩减文件大小,最终执行一个不会对系统产生影响的小文件删除操作。例如要实际删除t1的两个剩余.h文件:

rmtablefile.sh t1

rmtablefile.sh脚本文件内容如下:

#!/bin/bash
# 表定义文件很小,可直接删除
rm $1.frm.h

# 表数据文件大小,单位M
filesize=`ls -l $1.ibd.h | awk '{print int($5/1024/1024)}'`
if (( $filesize < 100 ))
then
    # 小于100直接删除
    rm $1.ibd.h
else
    # 大于等于100,每次截断100M
    for i in `seq $filesize -100 0`
    do
        sleep 2
        echo $i
        truncate -s ${i}M $1.ibd.h
    done

    # 删除小于100M的文件
    rm $1.ibd.h
fi

使用truncate每次将文件大小缩小100M,当文件小于100M时再将其删除。需要说明的一点是,这步实际删除磁盘文件的操作,目标是不影响线上服务,在此基础上只要能够安全删除文件即可。

参考:

文章最后发布于: 2019-10-07 18:33:58
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览