一、背景

随着业务量的不断增长，数据库主库与从库之间的数据同步延迟时间越来越长，个别实例长达30+分钟，在JX集群中，主从延迟比较明显。

二、原因分析

经过数据特征分析，发现主从延迟主要集中在每天的05:00-07:00，系每日库存快照自动生成worker任务，该任务会每日保存一份库存快照数据，库存快照会用于库存分析、追溯、对账等使用场景，是WMS不可或缺的数据。

在JX集群，云仓数据巨大，个别单层的库存行可达到几百万行。JX集群和KA集群，每日05:00需要生成的库存快照数据为6.9亿行，4842亿件。

快照数据生成是通过insert into... select from xxx limit yyy方式实现的，单个数据库实例上会有多个逻辑库，JX集群和KA集群共有152个数据源，多个数据源之间有一定的并行度，单个数据源的执行是串行执行，基于id翻页滚动执行。为了降低单个数据库实例下的多个数据源并行执行所带来的负载压力，程序特定做了间隔梯度处理。

定时任务worker执行期间会产生大量的binlog数据，主从同步数据量大，负载高，磁盘IO也高，同步时间长，主库和从库的延迟时间也会很长，这就是我们从数据库监控中看到的主从延迟高的原因。

三、影响分析

主从延迟，主要是每日05:00-07:00个别高延迟数据库实例，从库中的数据不准确，相应的依赖于从库的报表查询和大数据抽数是不准确的，对于报表查看、导出、数据分析有影响，对单据生产无直接影响。

亿级库存快照的记录，除了主从延迟外，另一个显而易见的问题是，磁盘利用率的居高不下，因为要保留20天的库存快照数据，相当于要保留6.9亿行*20=138亿行数据，大概是5TB，虽然这5TB数据是分库分表承载的，但每个数据库实例中的硬盘利用率也不低。

业务量的持续增长，系统方面对此也应该进行必要的演进变革，使其不断满足对应业务量的系统发展。

四、解决方案

方案1：独立单独的库存快照数据库实例，与生产库隔离。

方案2：通过分库分表，细化数据拆分粒度，将数据拆分到更多的分片实例中。

方案3：每日通过SQL管理软件导入和导出方式实现库存快照记录。

方案4：通过SQL管理软件的writeset减少主从延迟，writeset可以提高并行复制效率。

• writeset通过计算事务修改数据的哈希值（如XXHASH64算法），识别无冲突的事务，允许这些事务在从库上并行执行。相比基于组提交的LOGICAL_CLOCK机制，writeset能进一步降低事务间的依赖关系（last_committed值），从而提升从库的并行度。适用于主键明确的表，且需设置参数transaction_write_set_extraction=XXHASH64和binlog_transaction_dependency_tracking=WRITESET。

方案5：通过大数据抽取实现库存快照留存。

• 通过BDP对每日库存进行离线抽数，每天一个数据版本（dt分区），即每天一个库存快照版本。同时通过hive2ES从fdm中写入ES数据中，WEB页面通过后端程序查询ES中的数据，支撑业务系统中的报表查询和导出业务。ES中的库存快照，每天写入一个独立的分片中，保留20天的历史数据，20天的历史数据按分片进行删除处理。

前四种方案，均是对SQL管理软件数据进行优化处理，尽可能提高并行度，或通过数据库实例隔离，降低主从延迟，虽然可以降低主从延迟，但SQL管理软件同步数据的瓶颈仍较难突破。在方案选择上，决定采用方案5进行治理。

五、专项治理

按照步骤，治理工作分为下面几个步骤实施落地。

1、基于库存表使用BDP工作流建立5点离线抽数任务

用工作流拆分采集任务，10个任务，每个任务负责16个库。

2、基于原有库存快照fdm表的大数据任务，切换为新的fdm离线表

梳理依赖旧表的任务

替换为新表 fdm_jdl_scm_wms_stock_st_stock_st_stock_dayly_st

3、进行磁盘容量和分片规划，申请ES资源

评估数据量，每天写入一个新的分片。

4、配置hive2ES抽数任务，写入ES

每天库存快照都会写入一个新的ES分区，保留20天的分区数据，20天前的历史数据进行删除。

5、业务端的库存快照报表查询、导出依赖的数据源从SQL管理软件，开发支持按ES数据源查询导出

ES的访问借助EasyData实现，WEB -> Controller -> EasyData -> ES，EasyData封装了对ES的查询访问，可通过SQL实现，简单方便。

6、ES写入20天数据，停用老库存快照定时任务

因为最近20天的fdm数据是存量已存在的，该项工作可以直接进行，无需一天只同步一天的数据。

7、库存快照报表查询和导出切换为ES数据源

等20天库存快照ready后，WEB报表的查询和导出正式切换为ES数据源。

8、清理SQL管理软件老库存快照数据的数据，清理磁盘碎片，释放资源

清理SQL管理软件生产库中库存快照表中的数据，清理对应的碎片，释放空间。

六、落地效果

在SQL管理软件库存快照停写的第二天，之前每日必出现的长时间主从同步延迟不见了，对于监控平台上出现的分中间延迟情况，经过与监控平台和DBA确认，是统计口径差异，并非真正的延迟，至此主从延迟问题得以解决。

同时，每日的库存快照数据从SQL管理软件单独的快照分表，转移至ES存储方式，SQL管理软件生产库的磁盘利用率也降低至60%以下，处于健康水位。

作者丨郭忠强

来源丨公众号：京东技术（ID：jingdongjishu）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn