故事的开始
一个咖啡劲儿已经消退了的午后,业务大大突然笑眯眯的对我说: "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 的输出信息,反复准备环境和验证。不是很确定这种没有边界的探索方式是否适合每个人,应该不是。
祝大家中秋快乐!!