单表文件大小文件系统限制后删除部分数据仍无法插入新数据
环境信息
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'