基于clickhouse分析和优化mysql的业务运行

2019-09-10 15:58:28 采集侠

2017年一年里,脉脉发展出了一套mysql统计方法。借用此方法,业务方对数据库的使用效率得到长足提高,同时也解决了一些之前困扰很久的问题。

在此基础上,我们又在基础架构方面和数据解析方面做了进一步的优化,并且依靠更丰富和实时的数据做了更多更深入的实践,本文将介绍最新的基础架构以及部分实践经验。

一. 观察业务的问题

这部分列举一些日常工作中dba可能会好奇或者真正会遇到但又处理起来比较麻烦的问题

1. 先看一个监控图

基于clickhouse分析和优化mysql的业务运行

可以看到,增删改查的数量以及变化的趋势一目了然,根据对业务的基本了解,也大体可以推断出qps的起伏是由于用户在不同时间段的活跃程度不一样导致的。

但是如果再进一步思考这些问题:这一望无际的绿色(select)具体都是些什么sql?他们的数量都是多少?贡献qps最多的sql是什么?他们的数量随着时间变化的趋势又是什么样子?一张简单的监控图所能展现的信息恐怕就不够了,而如果再继续追问:在11点前后qps有较大波动,是为什么?是某个sql的qps突然飙高,还是业务整体性抽风?如果是单条sql突然飙高,他来自哪台服务器,之前的运行规律是什么?上面的这些问题或许通过咨询研发可能可以得到一些线索,但是想掌握详细的情况可能不太容易。

2. 表和字段

在日常的工作中,哪些表查询量大,哪些表查询量小,我们多少会心里有数,但是哪些表已经没有查询了,恐怕不是一件很好确定的事情。

由于公司的快速发展和在业务上的不断试错,导致数据库里有很多“可能”没用了的表,之所以说“可能”,是因为真的没有谁说得清楚这个表到底有没有用。即使不考虑相关研发离职这种极端情况,也可能因为时间太久或者业务的快速迭代导致相关研发也不敢保证这个表到底还有没有用了,如果这是一张占磁盘空间比较大的表,让他这样不明不白的留在磁盘上显然不是一件让人愉快的事情。还有更糟糕的——对于一张明显比较大的表,dba终究会想起来关注一下;而对于更多的单表不那么大但是加在一起所占空间也不容小视的表来讲,他们则是彻底待在被遗忘的角落了。对于前者,我们或许可以有限的通过查询全日志或者审计日志来判断一下是否还有读写,而对于后者,要想一一搞清楚成本就比较高了。

更进一步,由于类似的原因,一张表(尤其是宽表)里的字段也会有同样的问题——哪些字段访问量大,哪些字段访问量小,哪些字段在业务上关联度更大,哪些字段已经不用了。如果能得到这些信息,将对业务上的优化有很大的帮助。

3. 用户权限

这个相对简单些,如果在做权限控制的时候是以表为单位授权的话,一切都是一目了然的。而如果是对库授权甚至图省事对*.*做授权的话,事情就要麻烦多了。再设想一个场景(我相信很多dba都遇到过),如果研发新加了一台服务器,然后对dba说“我新加了一个服务器,帮我按照其他服务器的权限授权”。如果库表的业务所属划分的比较好,这自然不是一个问题,但是如果不是这样,可能会让dba和研发都比较头疼——授权工作会在dba的反复操作和研发的反复报错之间曲折前行。

二. 数据的收集、解析和存储

在上一篇文章里,这一方案最终确定为:原始数据(审计日志) -> flume interceptor解析日志及sqltext ->hdfs,通过hive进行查询。后来在此基础上又添加了从hdfs转存到clickhouse的环节,最终通过clickhouse查询。

在引入了clickhouse后,查询的效率大幅度提升,绝大多数统计需求可以做到在10秒以内得到结果——相比起之前通过spark-sql的查询,速度可以说有了数量级的提升。然而这一方案仍然有很大的改进空间,主要有以下几点:

1.流程繁琐,既然数据最终会存到clickhouse,那么现有的先存到hdfs然后转存至clickhouse的模式就显得没有必要,如果可以直接存到clickhouse显然是最好的;

2.解析sql的功能仍然不够强大和细致——原先的功能只能把一个sql中涉及到的表名,条件字段,查询(更新)字段解析出来,但是不会做更多的解析。参考下面三个sql:

          select id, name, qqfrom users where id = 1 and status = 1;

          select id, name, qqfrom users where id in ( 1000个id ) and status = 1;

          select id, name, qqfrom users where id = 1 or status = 1;

在原先的方案里,这三个sql都会被解析成 {tables: ‘users’, items: ‘id,name,qq’, columns: ‘id,status’}。显而易见的,这三个sql是完全不一样的,其背后的业务含义也完全不一样(不要深究sql的合理性,只是例子),但是在统计结果里,则会被展示为相同的sql样式,这在当时对业务只需要粗粒度分析的背景下是可以接受的,而如果想更加细致的观察和分析就无能为力了。