最近，某商业图书馆的负荷比平时高很多。最直观的印象是原来的负载是100%。现在不是翻倍，也不是指数增长，而是一下子增长了100倍。这导致业务后端的数据写入量急剧增加，导致严重的性能阻塞。读写分离的引入，优化初见成效，引起了我的兴趣和好奇。与商家沟通后，商家记录回执数据。简单的说，就像你发了一条微博，想看看有多少人看了，有多少人留言等等，所以这种场景是没有交易的，会有数据的密集写入，会有明确的统计要求。目前的统计频率是每7分钟统计一次。将有几种类型的统计场景。目前基本都是全表扫描级别的查询语句。现在的数据库架构很简单，就是主从，加上MHA高可用。问题的改进方向是减轻主库的压力，也就是分别读取和写入的压力。写的压力来自于业务的并发写压力，而读的压力来自于全表扫描的压力，给CPU和IO带来了很大的压力。解决这两个问题还是有重点的。首先，统计SQL导致系统资源成为瓶颈。这样一来，原本简单的Insert也变成了慢日志SQL。相比之下，写作要求是硬性要求。统计需求属于辅助需求，所以在这种场景下，与业务方沟通时，快速响应的方式是将主库的统计需求传递给从库。转移了读请求的负载，大大缓解了写压力。后来经过业务端应用层面的优化，整体负载情况还是比较乐观的。主库的监控负载如下图所示：可以看出有明显的下降趋势，CPU负载从90%以上下降到10%以下。IO的压力也从近100%下降到25%左右。从库的监控负载如下图所示：可以看出压力明显增加了。CPU层面的表现还不够明显。主要压力在IO层面，即扫描整张表数据的成本极高。这可以看作是优化的第一个改进。在此基础上开始进行索引优化，但通过对比发现效果非常有限。因为从数据库端是统计需求，增加的索引只能从全表扫描降级为全索引扫描，对整个系统的负载提升非常有限，所以我们需要对现有的进行一些改进和优化建筑学。方案一考虑到资源成本和使用场景，所以我们暂时将架构调整为如下方式：即增加两个数据节点，然后计划启用中间件进行分布式架构设计。对于从库，为了暂时节省成本，对原服务器做了资源扩展，即单机多实例模式，这样写的压力就完全可以支撑了。但是这种方式有一个潜在的隐患，就是库的中间件层起到了数据统计的作用。一旦出现性能问题，中间件的压力会非常大，可能会导致原本的统计任务被阻塞。同时，除了磁盘空间外，库侧的资源瓶颈是IO压力，目前无法通过空间扩展来解决。与商科学生进一步交流后发现，他们创建这类表的方式是动态配置的方式，目前的中间件方案很难实现。对于企业而言，统计要求变得更加不透明。改进解决方案2的一个有效方法是在应用程序级别进行数据路由。比如有10个业务：业务1和业务2在第一个节点，业务3和业务5在第二个节点，以此类推。按照这种路由配置方式映射数据源相对可控，也更容易扩展，所以架构改成这样：而整体提升最关键的部分是统计SQL性能的提升。如果SQLstatistics的性能提升可以达到初步的效果，后续的架构改进也会比较容易。引入列式存储并优化统计性能后，业务开始爆发式增长，使得统计需求的优化成为本次优化的关键。原主库读写压力大。通过读写分离，读节点的压力开始急剧增加，而且随着业务的扩大，统计查询的需求也越来越多。比如以前有10个query，现在可能有30个query。从而导致统计压力增加，导致系统响应下降，进而导致从库延迟增加。袭击发生时，有3小时的延误。按照这种情况，其实统计意义不大。对此我做了几点改进：第一，和业务方进行了详细的沟通，对业务场景有了更清晰的认识。其实这种业务场景还是挺适合Redis这样的方案的。但是考虑到成本和性价比，我还是选择了关系型MySQL。结论：暂时保持现状。对于阅读压力，目前不仅支撑不住指数级的压力，就连现在的情况也堪忧。业务的每个统计需求涉及到5条SQL，需要权衡取舍，才能对每个场景进行优化。***初步实现的效果是5个字段3个索引，不可控的是一旦某个表的数据量过大造成延迟，整个系统的延迟就会增加，导致统计要求全部崩溃。因此，添加索引来解决硬统计需求被认为是强大但还不够。结论：指标优化效果有限，需要寻求其他可行方案。对于写入压力，后续可以通过分片策略来解决。这里的分片策略和我们传统的逻辑不一样。它基于应用层面的分片，数据路由在应用端完成。这样，对于业务的爆发式增长，分片很容易扩展。有了这一层保障，业务的统计需求迁移到从库，写压力就可以顺利对接。目前写压的空间很大，完全可以支持指数级的压力。结论：业务数据路由在统计压力缓解后开始好转。为了快速改善现状，我写了一个自动采集管理的脚本，会定时kill超时查询的session。但是延迟依然存在，查询还是很慢。很难想象这种延迟在指数压力下会有多大。做了很多对比测试，按照单表3500万的数据量，8张相同数据量的表，5条统计SQL，统计完成大概需要17~18分钟，每张表耗时平均约2分钟。很多分钟。因为没有事务关联，根据业务场景和技术实现，这个场景下的延迟肯定是存在的。我们的改进方法是在保证系统压力在可控范围内的同时，提高统计查询的效率。一种有效的方法是使用数据仓库解决方案。MySQL本身不支持数据库仓库，但是有第三方的解决方案：一种是ColumnStore，它是在InfiniDB的基础上改造的。一个是Infobright，此外还有其他大规模的解决方案，比如Greenplum的MPP解决方案。ColumnStore的方案有点类似于这个MPP的方案，需要分布式节点，所以Infobright在资源和架构上更轻量。我们的表结构很简单，字段类型也是基本类型，我们团队内部有很多实践经验。改进后的整体架构如下，不影响原有的主从架构：需要在此基础上扩展一个数据仓库节点，数据量可以根据需要继续扩展。表结构如下：CREATETABLE`receipt_12149_428`(`id`int(11)NOTNULLCOMMENT'自增主键',`userid`int(11)NOTNULLDEFAULT'0'COMMENT'用户ID',`action`int(11)NOTNULLDEFAULT'0'COMMENT'动作',`readtimes`int(11)NOTNULLDEFAULT'0'COMMENT'阅读次数',`create_time`datetimeNOTNULLCOMMENT'创建时间');导出的语句类似于：select*from${tab_name}wherecreate_timebetweenxxxandxxxxintooutfile'/data/dump_data/${tab_name}.csv'FIELDSTERMINATEDBY''ENCLOSEDBY'\"'；Infobright社区版不支持DDL和DML。后来Infobright官方宣布不再发布ICECommunityEdition，将专注于IEE的开发，所以后续的支持其实非常有限，对于我们目前的需求来说已经绰绰有余了。下面简单感受下Infobright的强大：>selectcount(id)fromtestxxxwhereid>2000;+------------+|count(id)|+------------+|727686205|+------------+1rowinset(6.20sec)>selectcount(id)fromtestxxxxwhereid<2000;+------------+|count(id)|+------------+|13826684|+------------+1rowinset(8.21sec)>selectcount(distinctid)fromtestxxxxwhereid<2000;+--------------------+|count(distinctid)|+--------------------+|1999|+--------------------+1rowinset(10.20sec)所以对于几千万的表来说，这不是问题。我将3500万条数据导入Infobright，5条查询语句的总执行时间维持在14秒，相比原来的2分钟有了很大的提升。我运行了一个用了18分钟的批量查询，现在只用了不到3分钟。引入动态调度来解决统计延迟问题。通过引入Infobright解决方案，可以完全支持现有的统计需求，但随之而来的一个难点是如何平滑支持数据的流动。我们可以设置传输频率，比如10分钟或半小时，但目前，这需要额外的脚本或工具。在落地的过程中，我发现有很多事情需要提前做好。一：比如最头疼的就是全同步。第一次同步必须是完整的。如何将这么多数据同步到Infobright。第二个问题，也是比较关键的，同步策略是怎么设置的，能不能更灵活的支持。第三个问题是基于现有的增量同步方案，需要在时间字段上增加一个索引。这对在线运营来说是另一个巨大的挑战。第二：从目前的业务需求来看，最多可以允许一个小时的统计延迟。如果后期要做大量的运营活动，就需要更精准的数据支持。获取半小时的统计数据，按照现有方案是否可以支持。这两个主要问题都无法解决，实现数据传输是一个难题。我只剩下一天的时间来解决这个问题。所以我会把前期的准备和测试做的扎实一些，后期的衔接会顺畅很多。部分脚本实现如下：脚本有两个输入参数，一个是开始时间，一个是结束时间。对于第一次全量同步，可以提前给定开始时间，这样deadline就固定了，逻辑上就全量了。另外，在全量同步的时候，一定要保证主从延迟已经过去或者暂时停止查询服务，这样全量数据的提取会更加顺畅。所以需要对上面的脚本再做一层保证，通过计算当前时间和上次执行时间来获取任务的执行时间。这样脚本就不需要参数了，是一个动态调度的迭代过程。考虑到每天放盘的数据量在10G左右，日志量在30G左右，考虑使用客户端导入Infobright进行操作。从实际来看，涉及到的表有600多张。我先导出一个列表，按照数据量排序，这样小表可以快速导入，大表放在***。整个数据量约为150G。通过网络传输到Infobright，从导出到导入，这个过程大约需要1个小时。将数据导入Infobright后的性能提升也非常明显。过去持续半小时的一组查询现在可以在70秒内完成。大大提升了业务体验。第一次同步完成后，后续的同步可以根据实际情况灵活控制。因此，数据增量同步暂时由“手动档”控制。从整个数据结构分离后的效果来看，从库的压力大大降低，效率也大大提高。业务路由的引入和对业务扩展的平滑支持，实现了对现状最大程度的优化，但仍然存在一个问题。目前的架构暂时可以支持数据密集写入，但无法支持指数级的压力请求，存储容量难以扩展。以我的理解，业务层面的数据路由是最好的方式，而且在扩展方面也比较友好。因此，进一步的改进方案是：通过数据路由来实现负载均衡，从目前来看效果是明显的，也是业务在后续不断扩展中的一种可控方式。以下是最近一些优化期间从库的IO压力情况：经过连续几次解决问题，补充和跟进方案，我们完成了从最初的失败到落地成功，架构优化分享MySQL的性能扩展也基本结束了。如果有更好的实现方法，欢迎在留言区分享！作者：杨建荣简介：竞技界资深DBA，前搜狐畅游数据库专家，OracleACE，YEP会员。拥有近十年的数据库开发和运维经验，目前专注于开源技术、运维自动化和性能调优。拥有Oracle10gOCP、OCM、MySQLOCP认证，对Shell、Java有一定基础。连续1800多天，他每天都通过微信和博客分享技术。
