同事在客户现场利用DTS工具,入数从A实例将数据迁移到B实例过程中,据时间膨发现几乎稍大点的为何表在迁移完成后,目标端表空间大小差不多都是表空倍源端的3倍,也就是量导说表空间膨胀了2倍。
对这篇文章 《叶问》第16期 有印象的入数话,应该还能记得,据时间膨数据迁移(导入导出)过程中,为何也包括主从复制场景,表空倍导致表空间膨胀的原因有几种:
顺着上面的思路,逐一排查,看能否定位问题原因。
排查到这里,就显得有点诡异了,似乎遇到了玄学问题。不过没关系,我们还需要先了解DTS工具的工作方式,大致如下:
初看上述工作过程,似乎也没什么特别之处会导致数据写入后产生大量碎片,从而表空间文件急剧膨胀。
首先,读取数据阶段只涉及到源端,可以先排除了。所以,疑点集中在第3、4两步。
了解InnoDB引擎特点的话应该知道,当InnoDB表有自增ID作为主键时,如果写入的数据总是顺序递增的话,那么产生碎片的概率就会很低。但是,如果写入的数据是离散化的(比如插入的顺序是随机离散的,或者比如插入顺序为1、10000、2、3000、3、5000...这种完全离散无序的),则有极大可能会造成碎片率很高。
按照上述疑点,我们需要确认DTS工具构造的SQL是什么样的,这就需要修改选项 binlog_format = statement,这是为了获取其原生的SQL,row模式下可能就相对不好排查了。然后再次运行DTS工具,查看生成的SQL。
经过排查,终于发现问题所在,原来是DTS工具在拼接SQL时,虽然是分段读取数据,但没有将读取出来的结果集先行排序,造成了拼接后的SQL大概像下面这样的:
INSERT INTO t VALUES (100, ...), (99, ...), (98, ...)...(1, ...);
这种方式写入的话,而且还是并发写入,就会极大概率造成InnoDB data page频繁分裂,所以表空间文件才膨胀到原来的3倍之巨。原因不难理解,就好比排队机制,本来我们是按照身高顺序排,但现在有几位高个子的先排在前面了,那么后来的每次都要让这几个人频繁往后移动才行,这就造成了data page分裂,产生大量碎片。
我用几万条sysbench标准表做测试,采用这种方式写入的话,大概会造成约20%的表空间膨胀率。
问题已然明确,只需要在读取数据拼接插入SQL这个阶段,先行对结果集进行排序,就可以完美解决这个问题了。
并顺手给负责SQL优化器的同学提了个feature request(MySQL bug#109087),希望能在遇到上述倒序INSERT的情况下,自动完成SQL改写,改倒序为正序(或者说,INSERT的顺序和表主键定义的顺序一致,通常都是正序的INT),也就可以完美避开这类风险了。
责任编辑:武晓燕 来源: GreatSQL社区 MySQL批量数据(责任编辑:百科)
“双11”全国快件量达47.76亿件 11日当天共处理快件6.96亿件
A股年内1556起并购涉及1131家公司 专家称需提高上市公司质量
隆盛科技上市后业绩遭遇“滑铁卢” 实现净利润同比下降五成左右
财政部、水利部拨付4.9亿元水利救灾资金用于支持地方安全度汛和云南抗旱
安徽光明市财政局多措并举 积极推进国库支付电子化管理改革进程