负载突然翻了100倍,如何拯救MySQL架构?数据库

来源:互联网 / 作者:SKY / 2019-01-10 15:03 / 点击:
最近有一个业务库的负载比往常高了很多,最直观的印象就是原来的负载最高是 100%,现在不是翻了几倍或者指数级增长,而是突然翻了 100 倍,导致业务后端的数据写

最近有一个业务库的负载比往常高了很多,最直观的印象就是原来的负载最高是 100%,现在不是翻了几倍或者指数级增长,而是突然翻了 100 倍,导致业务后端的数据写入剧增,产生了严重的性能阻塞。

负载突然翻了100倍,如何拯救MySQL架构?

引入读写分离,优化初见成效

这类问题引起了我的兴趣和好奇心,经过和业务方沟通了解,这个业务是记录回执数据的。

简单来说就好比你发送了一条微博,想看看有多少人已读,有多少人留言等。所以这类场景不存在事务,会有数据的密集型写入,会有明确的统计需求。

目前的统计频率是每 7 分钟做一次统计,会有几类统计场景,目前基本都是全表扫描级别的查询语句。当前数据库的架构很简单,是一个主从,外加 MHA 高可用。

负载突然翻了100倍,如何拯救MySQL架构?

问题的改进方向是减少主库的压力,分别是读和写的压力。写入的压力来自于业务的并发写入压力,而读的压力来自于于全表扫描的压力,对于 CPU 和 IO 压力都很大。

这两个问题的解决还是存在优先级,首先统计的 SQL 导致了系统资源成为瓶颈,结果原本简单的 Insert 也成为了慢日志 SQL,相比而言,写入需求是硬需求。

而统计需求是辅助需求,所以在这种场景下和业务方沟通,快速的响应方式就是把主库的统计需求转移到从库端。

转移了读请求的负载,写入压力得到了极大缓解,后来也经过业务方的应用层面的优化,整体的负载情况就相对乐观了。

主库的监控负载如下图:

负载突然翻了100倍,如何拯救MySQL架构?

负载突然翻了100倍,如何拯救MySQL架构?

可以看到有一个明显降低的趋势,CPU 负载从原来的 90% 以上降到了不到 10%。IO 的压力也从原来的近 100% 降到了 25% 左右。

从库的监控负载如下图:

负载突然翻了100倍,如何拯救MySQL架构?

负载突然翻了100倍,如何拯救MySQL架构?

可以看到压力有了明显的提升。CPU 层面的体现不够明显,主要的压力在于 IO 层面,即全表数据的扫描代价极高。

这个算是优化的第一步改进,在这个基础上,开始做索引优化,但是通过对比,发现效果很有限。

因为从库端的是统计需求,添加的索引只能从全表扫描降级为全索引扫描,对于系统整体的负载改进却很有限,所以我们需要对已有的架构做一些改进和优化。

方案 1

考虑到资源的成本和使用场景,所以我们暂时把架构调整为如下的方式:即添加两个数据节点,然后打算启用中间件的方式来做分布式的架构设计。

对于从库,暂时为了节省成本,就对原来的服务器做了资源扩容,即单机多实例的模式,这样一来写入的压力就可以完全支撑住了。

负载突然翻了100倍,如何拯救MySQL架构?

但是这种方式有一个潜在的隐患,那就是从库的中间件层面来充当数据统计的角色,一旦出现性能问题,对于中间件的压力极大,很可能导致原本的统计任务会阻塞。

同时从库端的资源瓶颈除了磁盘空间外就是 IO 压力,目前通过空间扩容解决不了这个硬伤。

在和业务同学进一步沟通后,发现他们对于这一类表的创建是动态配置的方式,在目前的中间件方案中很难以落实。而且对于业务来说,统计需求变得更加不透明了。

方案 2

一种行之有效的改进方式就是从应用层面来做数据路由,比如有 10 个业务:业务 1、业务 2 在第一个节点,业务 3、业务 5 在第二个节点等等。

按照这种路由的配置方式来映射数据源,相对可控,更容易扩展,所以架构方式改为了这种:

负载突然翻了100倍,如何拯救MySQL架构?

而整个的改进中,最关键的一环是对于统计 SQL 性能的改进,如果 SQL 统计性能的改进能够初见成效,后续的架构改进就会更加轻松。

引入列式存储,优化统计性能

后续又开始有了业务的爆发式增长,使得统计需求的优化成为本次优化的关键所在。

原来的主库读写压力都很大,通过读写分离,使得读节点的压力开始激增,而且随着业务的扩展,统计查询的需求越来越多。

比如原来是有 10 个查询,现在可能变成了 30 个,这样一来统计压力变大,导致系统响应降低,从而导致从库的延迟也开始变大。

最大的时候延迟有 3 个小时,按照这种情况,统计的意义其实已经不大了。

对此我做了几个方面的改进:

首先是和业务方进行了细致的沟通,对于业务的场景有了一个比较清晰的认识,其实这个业务场景是蛮适合 Redis 之类的方案来解决的,但是介于成本和性价比选择了关系型的 MySQL,结论:暂时保持现状。

对于读压力,目前不光支撑不了指数级压力,连现状都让人担忧。业务的每个统计需求涉及 5 个 SQL,要对每个场景做优化都需要取舍。

最后达到的一个初步效果是字段有 5 个,索引就有 3 个,而且不太可控的是一旦某个表的数据量太大导致延迟,整个系统的延迟就会变大,从而造成统计需求都整体垮掉。

所以添加索引来解决硬统计需求算是心有力而力不足。结论:索引优化效果有限,需要寻求其他可行解决方案。

对于写压力,后续可以通过分片的策略来解决,这里的分片策略和我们传统认为的逻辑不同,这是基于应用层面的分片,应用端来做这个数据路由。这样分片对于业务的爆发式增长就很容易扩展了。

有了这一层保障之后,业务的统计需求迁移到从库,写压力就能够平滑的对接了,目前来看写压力的空余空间很大,完全可以支撑指数级的压力。结论:业务数据路由在统计压力减缓后再开始改进。

为了快速改进现状,我写了一个脚本自动采集和管理,会定时杀掉超时查询的会话。

但是延迟还是存在,查询依旧是慢,很难想象在指数级压力的情况下,这个延迟会有多大。

在做了大量的对比测试之后,按照单表 3500 万的数据量,8 张同样数据量的表,5 条统计 SQL,做完统计大约需要 17~18 分钟左右,平均每个表需要大约 2 分多钟。

因为不是没有事务关联,所以这个场景的延迟根据业务场景和技术实现来说是肯定存在的,我们的改进方法是提高统计的查询效率,同时保证系统的压力在可控范围内。

一种行之有效的方式就是借助于数据仓库方案,MySQL 原生不支持数据库仓库,但是有第三方的解决方案:

一类是 ColumnStore,是在 InfiniDB 的基础上改造的。

一类是 Infobright,除此之外还有其他大型的解决方案,比如 Greenplum 的 MPP 方案。

ColumnStore 的方案有点类似于这种 MPP 方案,需要的是分布式节点,所以在资源和架构上 Infobright 更加轻量一些。

我们的表结构很简单,字段类型也是基本类型,而且在团队内部也有大量的实践经验。

改进之后的整体架构如下,原生的主从架构不受影响:


负载突然翻了100倍,如何拯救MySQL架构?

需要在此基础上扩展一个数据仓库节点,数据量可以根据需要继续扩容。

表结构如下:

CREATE TABLE `receipt_12149_428` ( 

阅读延展

1
3