MySQL 纯insert_Presto Insert Mysql

故事的开始

一个咖啡劲儿已经消退了的午后，业务大大突然笑眯眯的对我说: "Presto insert Mysql 特别慢，跑了几十分钟了，一直 13% 左右， 你，帮忙看看？"。第一反应就是: "嗯？难道出大问题了？"，老实讲，有一点点小兴奋。

一顿操作猛如虎

紧接着，大概进行了如下几个猛如虎的操作：Presto 监控看起来，嗯～有一个 Stage 很慢，但是又没有死，大概十几K Row/s 的速度在爬行；

Mysql 监控看起来，哦？CPU 低很，内存不大，磁盘占用率和 IO 都不高，网络更别说了，不忙；

Presto 进程在搞啥这么慢？Jstack 一下，stack 就是在 write 的地方，没毛病；

Mysql 的信息再稍微捞一捞。

果然不出所料，并没那么 easy 就看到原因，再来一杯咖啡提提神吧。

来自大自然的猜想

提神醒脑之后开始整理思路(& YY)。基本确定的是：两个系统都没有发生什么异常，他们在正常的工作，只是慢；

资源不紧张，看起来不是瓶颈，深度怀疑是有竞争了。

难道是有锁竞争？Show Create Table 还真有一点小小的惊喜，内容是这样的( 已脱敏)：| mock_id_cross_index | CREATE TABLE `mock_id_cross_index` (`id` bigint(20) DEFAULT NULL,`c1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL ,`c2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,`c3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,`c4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,`c5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,`c6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,`c7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,`c8` bigint(20) DEFAULT NULL,PRIMARY KEY (`c1`),KEY `AK_c1` (`c2`) USING BTREE,KEY `AK_c2` (`c3`) USING BTREE,KEY `AK_c3` (`c4`) USING BTREE,KEY `AK_c4` (`c5`) USING BTREE,KEY `AK_c5` (`c6`) USING BTREE

先卖个关子，不说明这里主要的坑。但是满眼的索引很可疑。

探一探 InnoDB 的状态

查看 InnoDB 状态后，居然没有看到想象中的锁竞争 。想想我们的数据是预先去重的，所以理论也并不会竞争 Insert Lock。这里稍微贴一下 InnoDB 的事物信息(已裁减无用信息 & 脱敏 & 不看也没关系)：------------TRANSACTIONS---------------TRANSACTION 2820204, ACTIVE 0 sec1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 327MySQL thread id 99133, OS thread handle 139965631731456, query id 107575094 100.64.22.0 root startingINSERT INTO `studydb`.`tmp_presto_509cb3802fc84d6697019cad2c55c982` (`c1`, `c2`, `c3`) VALUES ("37008735_xxxx","717460_R0073xxx","717xxx")---TRANSACTION 2820201, ACTIVE 0 sec1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 457MySQL thread id 99138, OS thread handle 139965657954048, query id 107575093 100.64.22.0 root initINSERT INTO `studydb`.`tmp_presto_509cb3802fc84d6697019cad2c55c982` (`c1`, `c2`, `c3`) VALUES ("35922969_xxx_xxx","21918025_Q1829xxx","21918xxx")---TRANSACTION 2820200, ACTIVE 0 sec inserting

发现没有 Lock Waiting 后是有点迷茫的，干脆来个笨办法吧。对比了有索引和没有索引下的 innodb 的状态， 发现了一些有用的信息。(右图是有索引)

从上面的对比看，有几点的差异比较明显(无索引 vs 有索引)：Modified db pages，45594 vs 127588， meaning 有索引时修改的 Page 页比较多；

writes/s， 210 vs 1474 ，meaning 有索引时每秒改写的 Page 数增了不少；

youngs/s 和 non-youngs/s 的速度，0 vs 1467 & 5996，meaning 有索引时导致 Page 在 young 和 non-young 之间转换频繁。

到这里，我们可以将慢的原因范围限定了，是什么东西导致了频繁的 Page 操作。再回顾下创表语句。哦，是它：C1 是自建字符串类型的主键索引

主键索引的自建绝对是 Performance killer，Btree 结构的情况下，据我不多的 DB 知识，Page 里面都是要排序的。

赶紧验证自己的猜想，删除 C1 的主键索引，采用自增 Primary Key 后测试，完美。世界又恢复了美好。

Presto Insert Mysql 的流程

严格意义上来说，属于创建表的 DDL 有问题，但是 Presto 作为一个框架，兼容性要强。来看看 Presto 是如何写 Mysql 数据的。Presto 通过先将数据写入临时表的方式达到了 Atom 的效果，具体流程如下：

文字版说明：在 Presto Coordinator 解析作业时如果发现是 Insert 操作，则创建一个临时表；

Presto Worker 上开始运行作业，读取数据，写入数据；

Presto Coordinator 在数据写完后，执行 SQL 将数据从临时表拷贝到目标表；

Presto Coordinator 删除临时表；

临时表的做法带来了原子性，也伴随着两个问题，稍微列一列不多说：写放大，整个过程 Mysql 需要至少 2 倍于数据量的空间；

临时表有些情况会被残留，必现的场景是执行操作过程中 Mysql 发生重启；

社区贡献

明白 Presto 的 insert 流程后，基本有了一个思路：在创建临时表的时候不要索引，先写了临时表，然后通过 INSERT SELECT 的模式将数据倒腾到目标表。然后去社区一顿忽悠，搞了个 Issues 上去了。

截图上有一个细节，这个 Issues 是 22 day ago 的。Presto 的代码严谨程度应该是 Top 级的，在提交 Pull request 的时候 Reviewer 就敏锐的发现了 CREATE TABLE AS SELECT 在 GTID 模式下不支持，并且这个人马上去补充了一堆 GTID 的测试用例，花了不少时间等这个测试用例。代码和评论也是扣的很细，20 天后才 Merged。具体细节琐碎而丢人，我就不放出来了，有兴趣的可以去看 PR 共勉。

后记

本质上来说是表结构设计问题，很遗憾没有第一时间发现，当然发现了也就没有这么长的故事了。

PR 的改法严格来说并不完美，因为误伤的约束检查会很多，有不少可以在插入临时表的时候就做掉，比如 Primary key 的 Unique 检查。单纯过滤掉 Primary Key 的做法应该是更好的，只是代码逻辑上会繁杂一点点。

其实，整个过程并没有如上面的操作般行云流水，研究了不少 Mysql 的锁机制和 SHOW ENGINE INNODB STATUS\G 的输出信息，反复准备环境和验证。不是很确定这种没有边界的探索方式是否适合每个人，应该不是。

祝大家中秋快乐！！