首页 > 代码库 > 曲演杂坛--使用ALTER TABLE修改字段类型的吐血教训
曲演杂坛--使用ALTER TABLE修改字段类型的吐血教训
--=====================================================================
事件起因:开发发现有表插入数据失败,查看后发现INT类型自增值已经到了最大值,无法继续插入,需要修改INT类型为BIGINT类型。
--=====================================================================
作为一群自认为还算有点经验的老DBA,大家相互商量下,决定删除复制,然后禁止访问,删除索引,再使用ALTER TABLE修改,最后再加回索引,再修改访问,再不初始化订阅,多完美的步骤。
于是在一群人嘻哈围观中,执行了上述步骤,运行修改脚本:
ALTER TABLE TableNameALTER COLUMN ID BIGINT
然后大家开始预估执行时间,有预估十几分钟的,有预估半小时的,有预估两小时的,然后开始坐等。。。
半小时过去,没有完成,有人开始坐不住,一个小时过去,没有完成,大家开始着急,两个小时过去,还是没有完成,老板坐不住啦,杀过来质问。。。
这下玩大啦。。。
--=====================================================================
由于在多台机器上执行,一些取消执行,一些硬抗等着执行成功,无论是取消执行还是继续执行,都不知道啥时能回滚成功或者执行成功,大家一边在惶恐中祈祷早点执行完成,一边还得给老大们解释各种原因。
--=====================================================================
表行数:11亿+
表数据:60GB+
表情况:1个BIGINT列+3个INT列+1个VARCHAR(50)列(VARCHAR(50)列平均每行长度11Byte)
修改操作:将字段类型INT改为BIGINT
对于继续执行等待ALTER TABLE成功的:耗时9.5小时完成
对于执行四小时然后取消执行的:耗时超过10小时未完成
--=====================================================================
扯了这么多水货,咱们来点干货,为什么修改字段类型消耗这么长时间呢?
首先排查是否由硬件资源瓶颈导致,硬件配置为:
CPU: 48CORE
内存:128GB
IO: FIO卡或高级共享存储
CPU使用未超过10%,磁盘每秒日志写入8MB左右,内存未发现等待分配情况
那问题出在那呢?
问题还是出在日志上,可以轻松发现,无论是在FIO卡还是共享存储上,磁盘每秒日志写入8MB左右,这个数值太低,经过肖磊咨询MS工程师了解到,修改数据类型,需要修改每一行的数据,每行数据生成一条日志记录,从而导致大量的日志,而由于未知原因,每秒写入磁盘的日志量较低,因此导致整个修改操作维持很长时间。
让我们来做个测试:
--=======================--使用测试数据库DB2use db2GO--================--创建测试表CREATE TABLE Test001( C1 INT IDENTITY(1,1), C2 BIGINT)GO--================--往测试表中导入2000+行数据INSERT INTO Test001(C2)SELECT OBJECT_ID FROM sys.all_objectsGO--================--备份日志以截断日志BACKUP LOG [db2] TO DISK=‘NUL‘GO--================--查看当前日志情况--由于日志截断,当前日志记录数很少SELECT * FROM sys.fn_dblog(NULL,NULL)ORDER BY [Current LSN] DESCGO--=====================--修改字段类型ALTER TABLE Test001ALTER COLUMN C1 BIGINTGO--================--再次查看当前日志情况--发现增加4000+的日志记录数SELECT * FROM sys.fn_dblog(NULL,NULL)ORDER BY [Current LSN] DESC
由于我们操作的数据有11亿+条,整个ALTER TABLE 操作是一个完整的事务操作,因此是一个极其庞大的事务,再加上其他诸如复制日志读取等操作影响,导致整个修改耗时9.5个小时,对业务造成比较严重的影响。
--=====================================================================
推荐解决办法:
创建新表,将数据导入到新表中,整个导入过程不影响业务访问(在修改前业务已无法插入数据,没有写操作,但是还有读请求),数据导入完成后,建立对于索引,然后使用SP_RENAME修改表名。这样可以将业务影响降低到最低,并且导入数据可以并发进行,提高导入效率,减小整个操作周期。
使用此推荐方法,用SSMS自带的导入导入工具,约4个小时完成导入。
--======================================================================
个人猜想:
整个修改数据类型操作,是单线程操作,并且可能受单个log buffer每秒能产生的日志记录数限制,因此每秒写入的日志量较小,修改的行数比较少,导致整个修改操作持续大量时间。
--======================================================================
夜深了,妹子提神