热搜词:家常手机

数据库面试题有哪些,生产环境mysql数据库大表如何平滑删除,mysql

作者:利哥 下载:word格式

概述

生产环境一张日志表占用达到166G,该表一直有数据写入,如果是直接delete/truncate时间过久,故下面采取drop 硬链方式进行删除。


一、删除表原理与优化

drop表时所有的mysql的相关进程都会停止,直到drop结束,mysql才会恢复执行。出现这个情况的原因就是因为,在drop table的时候,innodb维护了一个全局锁,drop完毕锁就释放了。这意味着,如果在白天,访问量非常大的时候,如果你在不做任何处理措施的情况下,执行了删大表的命令,整个mysql就挂在那了,在删表期间,QPS会严重下滑,然后产品经理就来找你喝茶了。

删除表原理上分为2部分:

1、buffer pool页面清除过程

在删除表的时候,Innodb 会将文件在buffer pool中对应的页面清除。

这会涉及到table_cache的lock,如果持有table_cache的lock,这将导致其他查询都无法执行。这种情况在没有innodb_per_table之前尤为严重。

另外,mysql5.5.23之后添加lazy drop table功能,这个功能用来解决mutex on the LRU list。

其中心思想就是加锁,找到需要被删除的page,删除1024个page之后释放锁让其他thread工作,之后loop。而percona的lazy drop处理起来更优雅一些,其会先加锁,然后找到需要被删除的page,标记,释放锁,后台慢慢删除。

对于删除表的页面清除,只需要将页面从flash队列中删除即可,而不需要去做flush操作,减小对系统的冲击。

数据库面试题有哪些,生产环境mysql数据库大表如何平滑删除,mysql-第1张-知识-醋百科

1)问题1:如果buffer pool很大,或者是在buffer pool中有很多需要被flush的页面,那么此时遍历扫描页面时就会占用比较长的时间,导致其他事务在用到相应buffer pool实例时被阻塞,从而影响整个数据库性能。

优化:涉及源码,优化困难

2、删除ibd磁盘文件的过程

这步在大容量表的时候更为消耗时间,那就是在os上删除物理文件。

1)问题1:表文件过大,直接删除会瞬时占用大量IO,造成IO阻塞

优化:使用硬链接删除

原理:一个磁盘上的文件,可以由多个文件系统的文件引用,这多个文件的完全相同的,都指向同一个磁盘上的文件,当我们删除任何一个文件的时候,都不会影响真实的文件,只是会将其引用数据减1,只有当被引用数目变为1的时候,再次删除文件,才会真正被删除。删除时,这两种情况的区别很明显,一个是在减少被引用数目,一个是真正做IO来删除它

2)问题2:做完硬链,真正的大文件删除问题,直接rm 删除,会造成IO瞬时高峰

优化:使用工具,多次少量的删除

原理:利用系统文件的truncate,脚本工具为slowrm


二、drop大表实例(以下操作同时执行以下命令观察IO:iostat -d -x -k 5 > /tmp/iostat.txt)

基于以上,我们对于大表的删除,应当先建立硬链,drop table后,再删除表数据文件。

对于大表的数据文件,可能会达到10G,也可以是100G级别,甚至更大。在linux下,这样的大文件在使用rm时,无疑会导致IO资源被强行占用,表现为硬盘的io_util基本上是100%左右,会对其它IO操作造成阻塞。更可怕的是,rm单个文件的过程是个原子过程,无法使用kill或kill -9来杀死rm进程,只能乖乖的等待它结束。如果是在繁忙的线上服务所在的机器上做这样的删除操作,很可能会对线上服务产生影响。

1、查看表大小

可以看到需删除的表已经达到166G。

SELECT t1.table_schema t1.table_name `ENGINE` table_rows CAST( data_length / 1024.0 / 1024.0 AS DECIMAL ( 10 2 ) ) `data_size(M)` CAST( index_length / 1024.0 / 1024.0 AS DECIMAL ( 10 2 ) ) `index_size(M)` t2.ct col_COUNT t3.ct idx_count create_time table_comment FROM information_schema.TABLES t1 LEFT JOIN -- 字段总数 ( SELECT table_name COUNT( 1 ) ct FROM information_schema.COLUMNS GROUP BY table_name ) t2 ON t1.table_name = t2.table_name LEFT JOIN -- 索引总数 ( SELECT table_name COUNT( DISTINCT index_name ) ct FROM information_schema.STATISTICS GROUP BY table_name ) t3 ON t1.table_name = t3.table_name WHERE t1.table_schema NOT IN ( 'mysql' 'information_schema' 'performance_schema' ) ORDER BY t1.data_length DESC;

数据库面试题有哪些,生产环境mysql数据库大表如何平滑删除,mysql-第2张-知识-醋百科

2、备份表结构

--查询是否有其他表外键关联 select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME='FSL_RATE_LOG' --备份表结构 create table fsl_rate_log_bak like fsl_rate_log;

3、建硬链接(如果是主从需在从库也设置硬链接)

在DB server上,找到fsl_rate_log表对应的文件,建立硬链接(一个 inode 号对应多个文件名)

cd /tmsdata/datafile/tms_prod ls -lh fsl_rate_log.* ln fsl_rate_log.frm fsl_rate_log.frm.h ln fsl_rate_log.ibd fsl_rate_log.ibd.h --可以发现多了fsl_rate_log.ibd.h/fsl_rate_log.ibd.h文件,且文件的innode均为2。 ls -lh fsl_rate_log.*

数据库面试题有哪些,生产环境mysql数据库大表如何平滑删除,mysql-第3张-知识-醋百科

4、删除大表(这里不做rename切换)

1)删除大表时需在业务低峰时间再做删除

2)如果是主从实时同步,删完后需检查从库是否也删除了

drop table fsl_rate_log; alter table fsl_rate_log_bak rename to fsl_rate_log; cd /tmsdata/datafile/tms_prod ls -lh fsl_rate_log.*

数据库面试题有哪些,生产环境mysql数据库大表如何平滑删除,mysql-第4张-知识-醋百科

数据库面试题有哪些,生产环境mysql数据库大表如何平滑删除,mysql-第5张-知识-醋百科

5、删除真正的数据文件

如果是生产环境在需要低IO负载删除大文件时,可以使用slowrm。这个后面再介绍吧~

rm -f fsl_rate_log.frm.h rm -f fsl_rate_log.ibd.h


其实想做到对生产环境基本没影响的话可以先对表做rename切换,把要删的表换个名字,然后在用drop 硬链方式删除即可。我这里是因为生产可以停机,所以就直接这样操作了。

觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~

数据库面试题有哪些,生产环境mysql数据库大表如何平滑删除,mysql-第6张-知识-醋百科

TAG: 页面 只需 中心思想 数据库面试题有哪些 生产环境mysql数据库大表如何平滑删除mysql

大家都在看