一 简介:这个系列将会对具体的需求场景进行举例论证,都是本人亲身经历

二 场景介绍:

   1 此DB为分库分表架构,每天生成一张表,每张表的数据量很大,存储了一年的数据量

   2 需要按照某一条件进行查询整个库,库表访问是随机的

   3 磁盘本身为机械盘,性能不是很高,用两台从库提供负载均衡查询

三 问题描述

     研发反应即便两台从库提供查询,但是效率仍然很慢,需要排查

四 分析:

   1 发现两台数据库的磁盘util值一直100% 负载的上升都是iowait导致的,机械硬盘性能低

五 mysql角度 尝试解决

  1 经过与研发的沟通,暂时停止主从复制进程,减少因为从库应用事务所占用的IO消耗

  2 临时加大buffer_pool的大小,增大了约5G,能缓存更多的数据

  3 临时减少buffer_page_dirty参数,触发刷脏(这个感觉意义不太大,只是进行尝试)

  4 调节其他参数 针对 select 具体语句

 

优化阶段1 结果:程序本身抽取数据虽然比之前快了,但是仍然不是理想速度

六 业务角度 尝试解决

  1 由于随机表查询较多而且不固定,尝试对表查询进行整合,针对同一表的查询进行汇总集中查询

  2 查询语句本身包含in集合的ID过多,将近万个,将in集合内部条件降低数量到千个

  3 由于减少了in集合,所以增大调整并发数,保证总量不变

优化阶段2 结果:结果非常不错,可以预期时间内完成抽取数据任务

七 总结:

  1 从数据库来说,减少其他IO操作,加大缓存的数据

  2 从业务角度来说,集中查询,采用in集合少但是并发多的模式效率远远高于in集合大但是并发少的模式

  3 还可以通过横向扩展机器的方式提高查询效率

八 历史问题

  1 机械硬盘的查询效率远远不如SSD,但是硬件问题无法解决

  2 分库分表的纬度制定方案不行,而且集群本身堆积的数据太多,存储了一年数据,超出了硬件本身的承受水平