MySQL自治平台建设的内核原理及实践（下）

见贤思齐

总第566篇2023年 第018篇本文整理自美团技术沙龙第75期的主题分享《美团数据库攻防演练建设实践》，系超大规模数据库集群保稳系列（内含4个议题的PPT及视频）的第4篇文章。本文作者在演讲后根据同学们的反馈，补充了很多技术细节，跟演讲（视频）相比，内容更加丰富。文章分成上、下两篇，上篇将介绍数据库的异常发现跟诊断方面的内容，下篇将介绍内核可观测性建设、全量SQL、异常处理以及索引优化建议与SQL治理方面的内容。希望能够对大家有所帮助或启发。0 前文回顾1 内核可观测性建设1.1 内核可观测性建设2 全量SQL2.1 实现方式2.2 内核实现方式2.3 全量SQL总体架构3 异常处理3.1系统架构3.2 产品展示4 索引优化建议与治理4.1 索引优化建议4.2 单SQL索引优化建议4.3 基于Workload的索引优化建议4.4 SQL治理0 前文回顾在《MySQL自治平台建设的内核原理及实践（上）》一文中，我们主要介绍了数据库的异常发现与诊断方面的内容，在诊断方面经常会碰到一些难以找出根因的Case。针对这些疑难杂症Case，通过本篇可以了解到，如何通过内核可观测性以及全量SQL来解决这些问题。除此之外，在得出根因后，如何处理异常，如何对SQL进行优化，以及如何进行SQL治理等相关方面问题，也将在本篇中给予解答。1 内核可观测性建设| 1.1 内核可观测性建设1.1.1 性能诊断挑战在自治性能诊断平台的建设过程中，我们发现如下两大挑战：很多SQL性能抖动的问题找不出根因，比如SQL的执行时长莫名其妙的突然变大，其执行计划良好、扫描跟返回的行数都很少，也没有行锁、MDL锁相关锁阻塞；查看慢查询日志，也没有哪个字段的耗时比较高，但是SQL的执行时长就是突然变长，有时候达到几十秒长，而平时往往是几毫秒，各种分析后找不出原因。有时候在诊断一些指标异常的根因时，凭借的是不太严格的经验，而不是量化分析，比如thread_running或者slow_queries值突然升高的时候，可能会通过表information_schema.processlist查看当前的活跃会话中线程的状态，看一下状态是不是有行锁或者MDL锁之类的阻塞，或者通过慢查询日志里的相关数据来诊断根因。这里的挑战是：我们看到的是整个SQL某个时间点的瞬时状态，或者只是整个SQL执行过程中的部分数据，而不是整体的数据，所以得出的根因诊断可能是片面的，也许一瞬间看到的是行锁，但是大部分时间被MDL锁阻塞。1.1.2 解决思路如果使用的是社区版本的MySQL，基本上都会面临上面两大问题。我们先从内核的角度分析一下这两个挑战。对于第一个挑战，主要是对MySQL在内核层面执行细节不够了解，比如一条SQL执行了10s，而从内核层面来看的话，这十秒的时间可能会有几百个步骤组成，检查后可能发现row或者MDL锁等待时间加起来只有1秒，那么其他的9秒的耗时在哪里呢？可能发生在这几百个步骤中的任何一个或者多个，所以如果没有这几百个步骤的明细数据，就无法诊断出突然出现的性能问题的根因。第二个问题跟第一个问题从本质上来说是一样的。由于采集的数据是某个时间点的快照数据（通过活跃会话），或者只是部分指标的数据（通过慢查询日志），所以我们看到的只是片面的信息，而没有办法获取到整个SQL的完整的耗时分布信息。1.1.3 Wait耗时量化分析法在分析完原因之后，我们参考了TSA的思想，同时结合MySQL自身的特点来做基于Wait的内核可观测性的建设。从TSA可以看出，SQL执行的总耗时无非就是由Off-CPU跟ON-CPU两大部分组成，如果SQL有耗时长的问题，首先要确认是在OnCPU还是在OffCPU上耗时多。如果是前者，那么说明SQL本身有问题，比如消耗资源太多（比如无效索引造成扫描行数过多）；如果是后者，那么说明SQL本身没有问题，而是受到干扰或者系统资源不足，进而造成OffCPU层面耗时过多。无论是何种情况，我们都需要把OnCPU跟OffCPU的耗时做进一步的拆分，从而来查看耗时瓶颈点到底在哪里，并且对耗时比较多的关键代码路径交由内核团队来进行埋点；相对MySQL performance_schema库中统计的Wait信息，实现非常轻量，所以对总体的性能的影响很小，并且耗时指标都是SQL明细粒度的，而不是类似performance_schema中的SQL模版级别的聚合数据。耗时在OnCPU在分析一条SQL执行耗时，如果发现像下图一样，耗时分布大部分都在OnCPU，这说明SQL本身有严重的性能问题（全表扫描、过滤效果不佳或者查询优化器的bug等原因），我们可以把诊断的方向集中在如何优化SQL本身就可以了，而不需要去关注其他的方面，如锁阻塞、磁盘或者网络延迟等OffCPU方面的问题。通过使用getrusage方法来获取某条SQL的OnCPU耗时时长，比如在SQL执行前，获取当前线程的ru_utime、ru_stime时间，然后在SQL执行完毕时，再次获取ru_utime、ru_stime值就可以得到SQL执行的CPU Time。耗时在OffCPU如果发现OnCPU的耗时占总耗时的比率比较低，通过OffCPU的相关指标发现其占总耗时的比率比较高，说明SQL本身没有问题，可能是被锁住了、硬件资源不足或者是被内核层面某个内核锁给卡住了。我们就需要查看到底是OffCPU相关的哪个，或者哪些指标耗时比较高，需要在内核层面对执行过程中的可能的、耗时比较长的执行代码路径进行埋点，只有获取到了跟性能相关的关键的数据，才能做出准确的判断。如何选择合适而全面的OffCPU相关的代码路径进行埋点？经过探索，这里提供了如下几个方式：分析setup_instruments表中包含的关键埋点信息，大致知道有哪些关键的指标可以埋点，比如wait/IO、Mutex类等，再结合自身的经验来判断哪些指标可能有性能瓶颈的问题。根据实际疑难case来判断选择哪些Wait指标，比如看到"Thread xxx has waited at trx0trx.cc line 1193 for 241.00 seconds the semaphore: xxxxx mutex REDO_RSEG created trx0rseg.cc:211, lock var 1"，则对mutex REDO_RSEG进行埋点，看到"Thread xxx has waited at dict0dict.cc line 1239 for 245.00 seconds the semaphore: xxxxxx Mutex DICT_SYS created dict0dict.cc:1173, lock var 1"则对 Mutex DICT_SYS进行埋点。源码分析，在内核层面对SQL的执行过程进行逐步的Debug，根据经验分析可能的瓶颈点来埋点；下面举两个例子分别就纯源码的角度来分析，SQL在读、写过程中哪些地方可能会造成性能抖动并做埋点。例子1，在SQL读操作的执行过程中，需要从buffer pool中请求内存资源，如果一直满足不了内存资源的请求则一直会循环尝试获取，如果在2000ms内还是没有从Buffer pool中请求到需要的内存，会打印日志“Difficult to find free blocks in the buffer pool......”，来表明内存很紧张；这种长时间等待内存资源的获取会生产了性能抖动，这个时候就需要在对应的内核代码处进行埋点获取BP内存资源等待时间。例子2，在SQL写操作的执行过程中是需要写redo log的，如果redo log空间不够，则需要刷盘redo log跟BP中的脏页，而刷脏页可能是个很耗时的操作，并会引起包括活跃会话突增、慢查询等性能抖动问题。这个场景跟上面的类似，也需要对源码进行分析，并且在关键的位置进行埋点来统计刷脏的耗时。1.1.4 Wait指标层次图通过上面的三种方式我们不断地迭代累计的关键耗时指标，整理成如下的OffCPU跟OnCPU两大类的分层架构图。截止目前为止，我们内核团队在内核层面埋点了100多个关键指标来辅助诊断，指标分为Statement跟Wait两大层级。StatementSQL语句级别的指标，如QUERY_TIME、 Row_time行锁时间、ROWS_EXAMINED、ROWS_SENT、Bytes_sent、NO_INDEX_USED、Full_SCAN等，但是这个层面的数据不足于判断出SQL性能问题的根因，比如QUERY_TIME很长，但是ROWS_EXAMINED、ROWS_SENT都很小，就需要进入下一个Wait级别的指标进行进一步的分析。WaitWait层面主要是MySQL内核层面的指标，比如Latch（Mutex、rw_lock、sx_lock、cond）的指标，这里需要说明的是上面的分层图跟MySQL自带的performance_schema中的wait type分层图看上去很像，那为什么不直接使用而需要自研呢？这里主要从如下几个原因且内核团队都很好的解决了：performance_schema相关的统计信息是自实例启动后总体的wait时间的聚合数据，而更需要的是任何时间段、单SQL例子的明细数据，因为诊断的往往是某个时间点或者时间段的异常SQL问题。performance_schema中其有不少Bug，除此之外，它只支持OffCPU类型的指标，OnCPU耗时的统计不支持，甚至有些非常重要OffCPU的指标，比如row lock的wait统计信息都不支持。开启performance_schema相关的wait后，对总体的性能影响比较大，而基于内核埋点的实现方式很轻量级，对数据库整体性能影响很小。2 全量SQL全量SQL，指的是把应用程序或者用户访问DB的所有SQL集合，需要捕获到这些SQL并且发至后端进行分析。在内核可观测性建设之前，原来采用的方式是根据MySQL的协议，来解析TCP报文来提取出SQL。此种实现方式的缺陷也很明显，就是除了SQL文本外，其他的能获取到的关键SQL指标信息非常少，这样做数据库的故障诊断跟SQL性能分析，会因为缺少关键指标而带来挑战。针对此挑战，我们的解决方案是：改造为直接从MySQL内核吐数据，来作为全量SQL的数据来源（前面的内核可观测性分析介绍可知，单条SQL维度上能从内核层面输出100多个关键指标，包含故障诊断跟SQL性能分析需要的重要指标）。| 2.1 实现方式全量SQL其实有好几个实现方式，初版使用了抓TCP包的方式，现在逐步从TCP抓包过渡到了基于内核输出SQL文本跟关键指标的方案。| 2.2 内核实现方式从内核层面来说，MySQL对于用户的链接将创建独立的thd结构体，所以采集的全量SQL对应的SQL文本跟关键指标数据，可以存于thd结构体中。然后用户线程将thd保存的指标数据，复制到一个自定义内存池中（无锁队列）；输出模块有一个线程从内存池中依次获取保存的信息后，写入全量SQL文件。具体流程如下图所示：| 2.3 全量SQL总体架构如何把海量信息（日均PB级别）上报到后端系统，来赋能故障诊断、SQL审核、索引优化建议等场景是一个较大的挑战。我们首先通过数据采集器（rds-agent）读取Full SQL File文件内的内容，再往后端传输。但是由于数据量特别大，为了节省存储资源，分析后选用Snappy压缩算法进行压缩，默认情况下压缩效果只有1～2倍，为此分析了Snappy的源码，发现如果让相似的SQL文本聚合在一起，那么压缩效果会有很大的提高，所以按照SQL文本的前N个字符（N取50，可以根据SQL文本的实际情况做调整）进行了排序后再压缩，发现压缩比提高到了7～8倍。后端的SQL存储分成了两种形式，一种是存明细SQL，另一种是存聚合后的SQL模版信息。产品展示采集到的全量SQL既可以模版化展示，点击模版后也可以看到明细的SQL。3 异常处理根因分析后，就需要根据具体根因来进行相应的处理，但是如何安全、可靠的进行处理其实是一个很大的挑战。具体的策略是根据其操作是“无损”还是“有损”来采取不同的处理策略。如果是对业务“无损”的操作，比如磁盘空间清理、参数值调整、缺失索引添加等，目标是让操作尽量的自动化。如果是对业务是“有损”的操作，比如需要Kill或者限流，把相关操作进行包装，发送相关的聊天群让DBA或者用户确认后再进行相关的操作。对于MySQL Hang的情况，高可用团队会进行周期性探测，如果发现主库MySQL Hang了，则会自动进行主从切换，如果发现从库MySQL Hang了则会进行相应的MySQL实例下线替换动作。| 3.1 系统架构整个异常处理的系统架构图如下，由“异常发现与分析”系统以及DBA团队开发的“预案服务”系统组成，前者提供精确的故障根因，后者根据具体的根因来安全、可靠的执行对应的预案，使故障快速恢复。| 3.2 产品展示下图是两个异常发现、分析、给出建议以及处理的例子：1）异常发现、确认根因后，发现缺索引，系统会自动调用索引优化建议系统给出建议，用户可一键添加索引；2）对于慢查询或者主从延迟问题，分析根因后提供用户进行限流、Kill、参数调优等操作的建议，避免异常被进一步放大。4 索引优化建议与治理众所周知，很大一部分的数据库的异常都是跟SQL性能有关。很显然，日常的SQL性能问题的治理就很有必要，一个低成本但效果显著的SQL性能优化方案是提供索引优化建议，并且可以将这种索引优化建议的能力应用于SQL生命周期的三个阶段，这三个阶段下文会进一步阐述：应用程序“发布前”的性能问题SQL审核（事前）；SQL执行过程中的实时性能问题SQL发现（事中）；SQL“执行完毕”后批量的SQL治理（事后）。| 4.1 索引优化建议建立适合的索引对SQL的性能提升效果很明显，如何添加适合的索引是一个有挑战性的任务。索引的添加不能光凭经验，因为索引是否被用到不但跟表的数据的分布有关，还跟MySQL查询优化器的Cost逻辑有关系，所以“人肉”进行索引建议的评估并不是一个好的方案。解决方式是尽量要基于MySQL的Cost模型，利用MySQL自身提供的查询优化器的能力，来给出最佳的索引；索引优化建议的建设一般分为三个阶段：1）单SQL维度的优化建议；2）基于workload的整体优化建议；3）索引自维护。这几个阶段的建设需要按顺序来进行，并且不可跳跃。下面我们将分别进行阐述。| 4.2 单SQL索引优化建议4.2.1 实现思路单SQL索引优化建议，就是指输入一条SQL语句后，优化建议系统给出一个索引优化建议的过程。想要利用查询优化器本身的能力来实现索引建议的目标，就必须先要了解MySQL的查询优化器是如何工作的。这里举个简单的单表SQL查询的例子，帮助我们来初步了解其工作原理。我们先了解查询优化器是如何从众多的候选执行计划中选择最终的执行计划的；在有了这个基础之后，再来讲是如何利用查询优化器自身的能力来做索引优化建议。比如有SQL语句“select * from test_db.table1 where c2=3 and c3=4 and c4