阅读量:38
作者介绍：纪维骁，在公司里被同事称作基维虾，本科就读于北京航空航天大学，在香港科技大学获取硕士学位。曾在人民搜索、百度、京东工作，2016年9月入职脉脉。作为唯一的dba，在公司业务规模迅速发展（日活量增长20倍）期间保证了mysql的稳定和高效运行，在dba日常工作以外会深入了解研发的业务代码并在业务层面提出优化建议。2017年兼任公司的大数据与统计工程师，自学spark与scala，统计公司各类业务指标（日活，月活，留存等）以及产品指标（拉新效果，活动效果，产品性能与可靠性），在此期间产生了利用spark分析mysql日志的想法并加以实践。背景：
2016年9月本人入职脉脉时，所有主要业务数据均存放在一套一主三从集群（下文称之为主集群）上，总共超过500张表。在此之后的两年里，脉脉的日活/月活数与数据库数据量都有了数量级上的升高，业务复杂度急剧升高，同时过快的业务增长也导致研发对数据库的使用不够精细和严谨。随之而来的问题是曾经的单个集群在性能，空间，业务隔离度等多个维度上都不能满足公司的业务发展需要，mysql统计工具则是用来全面掌握业务对mysql使用情况的工具。
为什么需要统计工具：
2017年4月，脉脉进行了我入职以来第一次拆库。这是一次很被动的拆库，原因很简单又很无奈：主集群的硬盘空间告急。但是dba却无法快速的制定拆库计划——拆哪些表？他们在业务上的关联度如何？哪些业务在查询/写入这些表？都是什么样的sql？有多少联合查询？这些联合查询哪些可以低成本的改造哪些不能？这些问题都让dba头疼，挨个研发去问姑且可以解决一部分问题，但是了解到的情况也是片面的。所以我就希望能有一种能够【在任何时间（或时间段），知道有哪些业务（具体体现在访问mysql的用户名和ip）在如何使用（需要知道sql大概张什么样）mysql，qps是多少】的工具。这就是发展mysql统计工具的初衷。
数据问题：
既然是统计，就离不开数据，首先我需要哪些信息？总体而言就是sql，访问的用户名，ip，访问类型，发生时间这些信息。所以要依据哪些数据文件给mysql做统计呢？当时有三个选择，mysql全日志（general log），慢日志和percona审计插件产生的审计日志。全日志记录的信息太少了，除了时间和sql内容以外没有别的信息，首先被排除了；慢日志和审计日志都记录了比较全面的查询信息，慢日志甚至包括很多性能信息，但是慢日志因为记录阈值的原因不能完全记录所以信息，而如果把阈值调到0则数据量太大了，更重要的是统计工具针对的问题更多的偏向业务层面，所以慢日志提供的性能信息相对来讲就比较多余（当然在后续的演进中还是需要这些信息的，不过这是后话），而且审计日志天然的弱碱性json格式对于后续的日志解析工作也非常友好，最终自然选择了审计日志作为数据源。当然，审计日志也有自己的问题，就是不能保存太长时间的，因为会记录mysql上的所有行为，所以导致其必须频繁滚动和删除老数据才能保证磁盘空间不会被迅速占满，这就导致了在有些qps高的时候，可能审计日志最多也就记录半小时内的mysql行为，这对于我在文章开始是提到的【在任何时间（或时间段）】的要求是冲突的，这也是统计工具要解决的一个问题。
日志的存储与计算方式：
1. (shell或者python)脚本 + 审计日志，最直接和最快捷的实现方式，这个办法解决了最基本的“了解数据库在最近的一段时间内做了什么”的需求。但是缺点也很多，首先是可以查询的时间范围太小，2小时以前的操作几乎一定是查不到的。当然也可以一直tail着新生成的日志然后重定向到其他文件里，这样确实可以收集到更多的日志，但是通过脚本统计大量的数据，性能也十分有限，做不了太多的事情。更何况如果想收集更长时间（比如以周或月为单位）的数据，也是不可能的，因为磁盘空间就那么大。
2. spark + hdfs，既然数据量和计算能力是首要问题，那么就把日志用hdfs收集起来，然后写spark脚本进行计算，但是这里面也是经历了好几个阶段才达到令人满意的效果，主要在于上传方式和日志数据的预处理上还需做一些工作，下面介绍一下这几个大的阶段
a. 【实时】的、【全量】的把【原始日志】上传到hdfs上（通过flume上传），spark脚本进行统计。这样做看上去很美，似乎有了原始日志就可以做任何事情，但实际上对于2,3分钟就生成1G的审计日志而言，flume的性能并不足以支撑实时的全量上传，往往是上传一段时间以后就中断上传了，所以这样做基本上很难保证数据完整，更谈不上统计了。后来又做了一些优化，改成了【分时】按照单个的审计日志文件上传，效果仍然不理想，姑且不说数据完整性的问题（这个问题存在了挺久），上传原始日志也不是好主意，因为直接用spark统计原始日志其实挺麻烦的，就算通过正则表达式对sql做归一化处理，但是由于奇怪的sql层出不穷，仍然会经常出现不能匹配或者归一化出奇怪结果的情况。而且这样做只能进行sql级别的统计，想做大级别（比如表级别）的统计仍然做不到。
b. 【分时】的、【全量】的上传【处理过的日志v1】，spark脚本进行统计。这里的分时上传，其实就是上文中提到的分时上传单个日志文件的办法。这一阶段相比上一阶段进步比较大的地方，是会在上传日之前对日志做一次处理，提取出审计日志中的信息（sqltext，ip，用户名，查询类型等），然后再对sqltext做解析，由此可以获得表名（如果是联合查询可以获得多个表的表名），查询或更新或插入的字段名（*或具体的字段名），where条件后的字段，insert...on duplicate key update后面更新的字段。把这些信息收集起来，以tab分隔作为整理过的数据上传hdfs后，就可以比较方便的用spark脚本进行统计了。然而这一方案的分时上传办法仍然不理想，一方面如果日志滚动过快（比如不到一分钟就生成1G的文件），而flume上传日志又需要比较长的时间（超过1分钟）这样一来就有会出现“跳文件”的现象，即文件还没被上传就被滚动走了，导致丢数据。另一方面对于高峰低谷差距比较大的服务，可能还会在qps低谷期出现“日志半天不上传”的情况，影响统计的时效性。
c. 【实时】的、【抽样】的上传【处理过的日志v2】,【spark-sql】进行统计。可调抽样率的抽样上传数据是实时上传实现的前提，对于qps比较高的服务，我一般设置一个1/10的抽样率，这样可以大大减小flume的压力，并且由于是固定频率的抽样，上传的数据也是均衡的。而【处理过的日志v2】则是指处理过的数据由以前的csv格式变成了现在的json格式，以json格式存放后，则可以直接使用spark-sql进行统计，和以前的spark脚本相比，工作量小了很多（对于有些相对复杂的统计逻辑，还是要用spark脚本会更方便些）。这一阶段完成后，之前设想的统计工具需要的功能就都可以实现了。
d. 补充一点，关于【处理过的日志】的问题，不管v1还是v2，都是不关心where后面的条件字段之间的关系（比如是c1 and c2还是c1 or c2），以及字段本身的比较逻辑的（比如是col = xxx还是col in (1,2,3) ），在这个问题上我确实被提出过不同意见，觉得做更细一些的解析会更好。毫无疑问这个建议是有道理的，但是考虑到公司里数据库使用的实际情况，出现这种不同sql被归一化成同样的sql的情况其实并不算多，即使存在（比如select * from users where id = xxx 和 select * from users where id in (1,2,3,4...) ）其实也对统计效果影响有限，因为这种归一化效果一样的sql在实际执行中往往有数量级上的数量差距。与其增加归一化精度，倒不如以后慢慢推动研发把select *的sql改造了。
举个例子，截图为统计某段时间内s数量排名top5的查询sql
3. clickhouse，clickhouse是一种比较新的列数据库，计算性能远远超过spark。同样的统计需求，spark如果需要2分钟，那么clickhouse可能只需要10秒。所以把数据存储到clickhouse里可以大幅增加统计效率，并且使可视化成为可能。
部分基本功能：
1. 某段时间内的排名统计
上文举例中提到的功能（统计某段时间内s数量排名top5的查询sql）是该功能的一部分，除此以外还可以增加其他的聚合字段，比如用户名，ip，实现更具体的排名。同样也可以限定具体的条件，比如表名，用户名，查询类型等，做限定范围的查询。
例图. 查某一天growth_ro账号在db2上查询sql的top5
2. 查某个sql的查询趋势
查询某个sql在以一段时间(比如10秒，1分钟，5分钟之类)为步长的查询趋势，其实也可以是“查某些sql的查询趋势”，不过这个是用于进一步的统计，做可视化之前并不好观察
例图. 观察从18:30到19:30某sql的访问量趋势
3. 比较两个时间段的sql查询量的差值/比值排名
当某一个时间某项性能出现异常（比如io突然升高）而慢日志等传统排查方式又没有线索时，可以根据问题时间和正常时间的sql访问量的差值/比值排名获取线索。不过如果要看比值排行则需要根据情况给分母设置一个offset，防止出现分母为0的情况。
例图. 获取两个时间段内sql查询量的差值排名
基于基本功能，可以衍生出很多业务相关功能，简单举例两个
1. 发现可下线的表
公司在快速发展时期，容易出现研发“管上不管下”的情况，而时间久了可能研发自己都不记得哪些业务已经下线了。这时如果发现有些表长时间（比如3天或1周）没有读写，或者只有写没有读，则可以基本确定这个表可以下线了。
2. “主流sql”解析
通过监控系统我们可以了解到mysql的整体查询情况和qps变化的情况，但是在这段时间内，具体每个sql的变化趋势是什么，哪些sql对qps的贡献最大则无法得知。具体来说，就是可能这段时间有100种sql，但是真正对总qps有影响的可能只有10个，即所谓的“主流sql”。通过统计工具可以了解一段时间内所有sql的变化趋势并通过与总qps变化的曲线做相关性分析获取这些sql，并显示他们的查询变化趋势以及其他基础统计数据（最大值平均数中位数等）。相关性分析的曲线可以是总qps，也可以是其他监控项，可以根据要分析的内容自己选择。
3. 新上线sql的发现
由于业务对于新sql的上线是不会通知dba的，所以有些不合理sql刚上线的时候dba并不知情，但是随着业务量增大新sql因为比较消耗资源而被发现的时候，想要再从业务层面优化就比较麻烦了。根据比较前后两天、两周、两月的sql差异，并累计成趋势图，可以发现新上线的sql以及其变化情况，以便在“君有疾在腠理”的阶段及时发现并处理问题。
由于篇幅有限，就先写这么多吧。统计工具现在还有很多没有提到的功能以及可以继续发展的地方，后面的打算是把纯mysql审计日志的统计和nginx日志的统计相结合，从业务的角度更加深入的分析和优化mysql的使用。而且由于人手问题，易用性上实在是让人抓狂，即使能实现简单的可视化和网页化都可以在操作上省事很多。
