一个MySQL服务CPU 100%的优化案例反思
- 2019 年 11 月 13 日
- 筆記
昨天在快下班的时候和同事处理了一起性能故障导致的服务器CPU 100%的案例。虽然问题解决了，但是在事后我做了一些反思，总体来说不够乐观。
其实这几年救火处理性能故障的情况已经越来越少了，我不知道是不是一种错觉，所以会看到很多DBA在不断的思考人生，思考DBA存在的价值，这个过程可长可短，但是归根结底的问题其实还是一样的：处理问题的价值和个人的价值是否持久。
我们来看下这个案例，我会弱化一些问题的描述，而更多是在问题本身的思考上面。
快下班的时候，突然收到了一堆MySQL慢日志报警，同事反馈说晚上有一波活动，这个时候已经距离活动的开启时间很近了，然后就发现服务器的CPU瞬间到了100%，确切的说系统的负载在70倍左右。
这是当时的一个系统负载情况：
top - 19:11:13 up 137 days, 2:19, 3 users, load average: 76.87, 45.35, 34.26 Tasks: 228 total, 2 running, 226 sleeping, 0 stopped, 0 zombie Cpu(s): 99.2%us, 0.5%sy, 0.0%ni, 0.1%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st Mem: 16318504k total, 9758308k used, 6560196k free, 286764k buffers Swap: 5242876k total, 0k used, 5242876k free, 6967804k cached
这是一个很熟悉的场景，我们处理问题的思路也很自然，查看慢日志，查看瓶颈SQL,很可能找出一些全表扫描的表，然后添加索引，接着系统负载瞬间跌下来，然后业务活动继续，我们送了一口气。
实际上却不是这样的。
一方面产生的慢日志过多，没几分钟就产生了上G的慢日志，所以很快抓取慢日志的方法不大可行，而且从补救的角度，我们关闭了参数log_queries_not_using_indexes，防止产生过多的慢日志。
另外一方面，我们发现产生慢日志的表还挺多，不是添加一个两个索引就能够快速解决的。
事情到了这里似乎有一些失控的状态。
我们需要快速定位问题，然后根据SQL的执行情况添加合适的索引，尽可能给活动争取时间。 如果是你该如何处理？当时背后已经站了好几个人，大家都希望奇迹出现的时刻。
当时我是临时开启了参数log_queries_not_using_indexes，得到了一些补充的慢日志信息，然后快速关闭，拿pt工具做了下分析，很快得到了一个简单的报告内容：
然后针对这几条语句开始优化，很快按照查询情况添加了索引，业务再次尝试，负载还是很高。
这个时候我们查看show processlist的结果，发现有大量的线程锁定在同一张表上，而且返回的事件是sending data,那么很显然和一个全表扫描相关了。
这个语句的逻辑是select count(1) from xxxx where xxxx;
再次添加了索引之后，问题得到了解决，后续做了些收尾，算是告一段落。
当然如果是在10年前，我觉得是一个很有意思的案例了。但是我的内心的成就感其实不是很高，一方面来说，添加索引这种优化方法简直就是DBA优化的送分题，而且这种优化是属于规范化流程之外的救火行为，有什么可以值得宽慰和满足的。
为什么会出现这个问题，除了业务方的准备不够充分外，我觉得还有很多补充的细节。
- 我们是否对慢日志给予了高度的关注，慢日志的可视化平台其实是一种后知后觉的处理模式，（目前是周期性采集慢日志分析推送分析结果入库做可视化），如何让慢查询处理方式更加具备实时性。
- 其中有几条SQL优化的时候有点犯嘀咕，因为本身是业务SQL中采用了半连接，当时准备尝试关闭这个优化器参数，所幸添加索引后解决，但是优化器参数部分是否也需要全面考虑。
- SQL规范问题其实已经很早就提过了，而且通过SQL自动化上线是明确要求业务要有主键的，但是有了主键不一定能够解决很多业务场景的问题，很多场景还是可能使用普通索引的条件。 。。。。
这种潜在的小问题其实可以举出很多。
当然我觉得最主要的问题，刚好在双十一的背景下，其实很多业务的瓶颈部分在于缺少一种行而有效的全链路压测模式。
业务问题是最直观的问题，出现业务阻塞，如何定位问题瓶颈，其实是当务之急。 这个问题的思路可以分为两个层面。
1）快速定位瓶颈的层面，如果是业务服务出现问题，那么我们需要定位出一些边界，如果是数据库，则应该也有相应的辅助信息，最直观的，如果没有问题，用数字说话。
2）解决具体层面的问题，需要形成一套快速的应急策略。比如这个问题，虽然解决了，但是发现还是走了一些弯路，怎么更快更高效的定位问题应该是我们着力去完善的地方。比如这次花了20分钟，但是下次能不能更快，我觉得理想的方式最好是在5-10分钟以内。 这些优化思路是不是可以形成脚本或者其他可以快速调用的方式，也就意味着在紧急情况下，处理问题的步骤还是那么多，但是通过工具加快了试错效率，降低了试错成本。
最后是开发和DBA之间的一道鸿沟：索引。
索引是优化利器，但是在优化中应该成为过去式，开发创建索引，但是其实索引对于开发是不透明的，而索引维护的成本和质量是DBA需要关注的，这就好比拿着一把尺子去衡量一个变化的事物，数据处理方式的解耦反而不是一件好事。最好的方式是什么呢，索引的问题交给智能化解决。
业务场景是变化的，数据量是变化的，那么相关的索引应该只有最合适的，而没有最好的。
这一点在Oracle中已经开始有了雏形，我们可以高大上的说自动化索引，智能索引，不管怎么叫，都是用时间可以去解决的问题。
而除此之外，DBA更硬核的技能是什么,欢迎留言。
