dba_chongzh
发布于

单表文件大小文件系统限制后删除部分数据仍无法插入新数据

环境信息
MySQL 5.7.21
文件系统 ext3

问题描述
单表idb文件达到2T(ext3文件系统单文件最大限制),当表insert时会报错 the table is full,开发紧急delete掉6万行数据后,ibd文件大小未减小,insert新数据继续报错 the table is full 。 (ps:临时应急:在未改变文件系统ext3的情况下,rename 旧表,建同名新表,确保增量插入可用,并进行存量数据迁移)

疑问1: 单表idb文件达到文件系统上限后,delete 清理6万数据后,为什么仍无法执行insert操作? 是标记为删除的数据不够吗? 还是索引组织表在自增主键的有序性导致无法利用?
疑问2: 单表idb文件达到文件系统上限后,有办法能直接让该表能继续新增数据吗?

应用报错截图:

错误日志报错截图:

数据清理语句:
delete语句:

DELETE FROM release WHERE AppId="120685" AND ClusterName="default" AND NamespaceName="F-HMP.CLIENT" AND id<44425775 (二次确认已删除成功了)

表结构:
CREATE TABLE `release` (

 `Id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '',

 `ReleaseKey` varchar(64) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '',

 `Name` varchar(64) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'default' COMMENT '',

 `Comment` varchar(256) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '',

 `AppId` varchar(500) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'default' COMMENT '',

 `ClusterName` varchar(500) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'default' COMMENT '',

 `NamespaceName` varchar(500) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'default' COMMENT '',

 `Configurations` longtext CHARACTER SET utf8mb4 NOT NULL COMMENT '',

 `IsAbandoned` bit(1) NOT NULL DEFAULT b'0' COMMENT '',

 `IsDeleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '',

 `DataChange_CreatedBy` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'default' COMMENT '',

 `DataChange_CreatedTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',

 `DataChange_LastModifiedBy` varchar(32) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '',

 `DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',

 `Ucm` longtext COLLATE utf8mb4_bin COMMENT '',

 PRIMARY KEY (`Id`),

 KEY `AppId_ClusterName_GroupName` (`AppId`(191),`ClusterName`(191),`NamespaceName`(191)),

 KEY `DataChange_LastTime` (`DataChange_LastTime`),

 KEY `IX_ReleaseKey` (`ReleaseKey`)

) ENGINE=InnoDB AUTO_INCREMENT=46041420 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=''



表ibd大小:

cmc-0018-0:/data/mysql_data/data/apolloconfigdb # ls -l release.ibd 
-rw-r----- 1 mysql mysql 2194719883264 May 13 08:55 release.ibd

执行delete操作后,release 表的统计信息:

SELECT table_name AS '表名',table_rows AS '行数',avg_row_length,
TRUNCATE(data_free/1024/1024,2) AS 'data_free容量MB',
TRUNCATE(data_length/1024/1024,2) AS '数据容量MB',
TRUNCATE(index_length/1024/1024,2) AS '索引容量MB'
FROM information_schema.tables WHERE table_schema='apolloconfigdb'

浏览 (386)
点赞
收藏
1条评论
dazhao
dazhao
首先,你这个数据表文件是ext3格式,表的数据量达到1.8+ TB,根据 https://access.redhat.com/solutions/1532 的信息,这个表的数据文件的size已经达到了最大(2TB),无法再增长了。应该使用ext4,它的size limit是16TB。 至于删掉一些行试图释放磁盘空间,其实并不管用,首先,这些行未必位于相同的extent,即时innodb purge 处理过之后,仍然无法腾出完整的若干个extent,让文件系统可以回收16KB的空间 --- 事实上innodb并没有把不用的page或者extent还给OS的文件系统的功能。而且,你删掉这些行,还会让binlog多占用一些空间,如果刚好binlog目录与innodb数据目录在同一块盘并且没有删掉不用的binlog的话。 如果在删掉大量数据行之后,确实需要缩小innodb数据文件size,有两种方法: 1. ALTER TABLE tbl_name ENGINE=INNODB; --- 迫使innodb内部完成以此数据灌入 2. 逻辑导出这个表之后重新逻辑导入 不过这两种方法都不适用于你目前的情况,因为你的数据盘已经没有空间了。如果服务器上面还有另外一块足够空间的磁盘,你可以创建一个tablespace,在上面创建目标表,把这个表的数据导入目标表来使用。 总的来说对于这个问题,首选方法应该是使用ext4,毕竟ext3在各方面已经不是最优选项了。 2. 使用分区表解决这个问题。
点赞
评论
dba_chongzh
感谢老师的解答。 针对 这些行未必位于相同的extent 的, 如果delete条件只含有自增主键,比如 delete from release WHERE id&lt;44425775(id不是max,但是在范围内连续) , delete 清理逻辑上连续的数据,是不是能在物理上也能保证连续,腾出完整的若干个extent,让文件系统可以回收n个16KB的空间,让新insert得以继续。
点赞
评论
dba_chongzh
感谢老师的解答。 针对 这些行未必位于相同的extent 的, 如果delete条件只含有自增主键,比如 delete from release WHERE id &lt; 44425775 and id &gt;1(id不是max,但是在范围内连续) , delete 清理逻辑上连续的数据,是不是能在物理上也能保证连续,腾出完整的若干个extent,让文件系统可以回收n个16KB的空间,让新insert得以继续。
点赞
评论
dazhao
删除一个连续的巨大的自增主键范围,也无法保证被删掉的这些数据行所在的page位于同若干个extent,最坏情况下可能被删掉的数据行位于N个page,而这N个page位于N个extent --- 如果有过大量的删除和插入被删掉的id,或者更新自增字段为其他值等操作的话。 如果该表只做插入且从未显示指定自增列的值,并且没有做过删除或者更新自增字段值,那么执行了这个大范围删除之后,过一段时间purge发生过之后,此删除操作能够清空若干个完整的extent中的所有page,但是innodb 能否把文件中的这些 extent 返还给文件系统? 不可以,因为文件系统没有这样的功能,也没有这样的POSIX API。那么Innodb能否重复这些extent使用来分配page? 首先这是可行的,完全可以实现,但是看MySQL文档的说法目前是不完全支持的: “If the insertions into an index are always ascending and records are deleted only from the end, the InnoDB filespace management algorithm guarantees that fragmentation in the index does not occur.” 也就是说目前Innodb只支持从尾部删除行来重复使用page;如果从索引key范围的中部做大量删除,是不会重复使用那些已经空了的page的。 这种边缘功能,优先级比较低,所以现在还没有做出来也完全可以理解。
感谢老师的解答。 针对 这些行未必位于相同的extent 的, 如果delete条件只含有自增主键,比如 delete from release WHERE id < 44425775 and id >1(id不是max,但是在范围内连续) , delete 清理逻辑上连续的数据,是不是能在物理上也能保证连续,腾出完整的若干个extent,让文件系统可以回收n个16KB的空间,让新insert得以继续。
点赞
评论