案例分析总结连载：第八章

案例分析总结

 

第7章结尾提到的两个性能优化方案：硬件扩容和业务软件优化，都被否决。这个项目出现了我们最不愿见到的“一高一低”：目标高——客户要求扩展性和标准化程度高，不但总体性能要提高50%，还要一并解决当前客户的迫切需要；支撑度低——数据库整体架构已无法保证有效的支撑。最后只剩下一个可行方案——大规模优化SQL语句。下文就是在4周的工作中，性能优化小组的工作要点回顾，分享给大家。

2010年7月8日   笔记整理                            地点：广州

SQL优化第一周

在项目推进会上，我开门见山地要求大家一开始就上紧发条，争取主动。虽然期限从3周延长到4周，但是这种以优化SQL为主的调优方式，对于如此庞大复杂的信贷系统来讲，好比要把5%的希望变成100%的现实。

我讲到：“这个生产系统是OLTP、OLAP混合型数据库，基本上是六成负载做事务，四成负载做分析，这使得我们前期要更多地考虑内存和并发操作的效率，现在暂且不要把太多精力放在分区和磁盘I/O上。用户并发数很大，我们要多关注系统对用户操作的反应速度，这是重中之重。”

costly SQL也是我重点关注的，Larry报告了通过监控得到的初步分析：“现在已查出对系统性能影响较大的costly SQL有30多个，这些SQL占有了超过70% 的CPU资源，还有60%以上的I/O 资源。”我插了一句：“这些costly SQL，你做个优化步骤文档，可以作为减压的优先方法。”

越是复杂的项目，越不能把摊子铺的太大，否则每件事情都做得不够到位，也许在找到关键问题前就把团队的精力耗光了。

在这次会上，我分解了任务，交给合适的人选来操作。有的着手搭建测试环境，有的调整监控方向，经验最丰富的老A开始攻坚几块SQL硬骨头，我负责收集客户需求、协调资源和制订详细计划。总之，貌似大家都忙了起来。

SQL优化第二周

尽管抢先一步做足准备，第一周还是很狼狈。我们遇到了如下几个问题：

（1）由于客户将大部分性能问题都归于数据库本身，这消耗掉我们一部分精力和热情；

（2）老A分析SQL上来就挑硬骨头啃，结果崩了牙。原来，他发现一个对表的关联查询异常频繁的SQL，这个SQL是由查询生成器生成的，完全不符合SQL的优化编写规则。只要改一下SQL的写法，就能得到一个很好的查询计划，但是这个提议被业务开发商否决——他们认为改SQL查询生成器不可行。除此之外，还出了两个类似的问题，一时还想不出替代方案；

（3）楼下的智能网在周三出现一次宕机，银行方面损失惨重。这连带着给硬件、数据库、业务厂商新的精神压力，而对于正在现场准备优化数据库的我们，更是雪上加霜。

尽管情况很不乐观，但我们的计划仍在有条不紊地进行当中：银行的DBA把生产系统的监控数据，架到“PAT树”上，定位了瓶颈；Larry调整方案转到优化编码阶段；测试环境搭建也按照方案进展顺利。

在项目推进会上，Larry提出加大对业务层的压力，我不得不又一次强调：“许多性能优化项目，业务层都是不愿更改自己的代码的，因为他们不得不面对业务逻辑的改变，在金融系统里工作量是非常大的，而且时间这么紧张，安全系数太低了。我们还是坚持以优化SQL为主，专注在这个点上出成绩。”

 

SQL优化第三周

真不习惯带着上一周的压力，来到新的一周。按照既定计划，实施优化操作最晚必须在第三周开始。上周末，优化小组集中火力攻克了DB2存储过程优化，这为团队奠定了心理上的优势。

本周前三天，我们对6个大SQL按照优化方案进行优化调整。最大的SQL语句有4页多，1000多行。期间，我得到消息：生产系统发生了严重的死锁。DBA们收集了大量日志，却一直无法确定问题点。我建议运维人员启用db2support收集问题发生时段的数据库日志，用于后面的定位分析。

到了周五，这些天优化操作的效果出来了。现在主机的CPU利用率平稳维持在70%～90%之间，各项优化指标都有改善。不过，此刻一点也不能放松，因为优化小组已初现疲态。我在项目推进会上说：“银行这次之所以要把系统清扫一遍，一方面是老问题太多，另一方面是下个月仅新业务就要增加30%，用户量岂不更大？ 这几天的监控数据表明系统是健康的，但并不等于下个月还这么平稳，最后几天我们要挖出更多的隐患，彻底解决性能问题。”

对着白板上的分析图，我接着说，“现在我们冲的很猛，平均每天都要完成3～5个SQL，不少都是多表连接的超大SQL。不过我们遗漏的问题还不少。比如昨天做的新业务量模拟测试，系统表现不容乐观。调优后的系统面对业务量增加30%的状况，响应时间倒是可以接受，但CPU时间降不下来。我发现业务层的应用程序大量使用相似的SQL，这些SQL只有一些常量不同，但每个SQL都要重新编译。这些SQL的编译要消耗大量的CPU资源。我担心将来业务更大规模地扩充后，系统很快就会吃不消而崩掉。”

Larry说：“不过目前的监控和测试数据，还比较符合要求。这个时候他们可能不太愿意接受对应用程序做改动，这有不小的风险。”

SQL优化第四周

新业务的模拟压力测试结果困扰着我们，迟迟找不到解决办法。我始终感觉那是个隐患,后面可能出问题。回到酒店我又翻了翻工作笔记，然后又看了看纸板上的PAT树。“思路”，我一下站起来，“一定是思路有问题！”现在应该退出画面来看画，换个场景想问题。

我迅速给Larry打电话，告诉他想办法通过减少SQL编译，来降低CPU时间。放下电话，我嘀咕自己又犯了错误：对至关重要的技术和业务问题的讨论，仅仅依靠口头传达，会严重影响效率和结果。我立刻打开电脑给小C发邮件，写下这句话：“DB2 V9.7中新引入的语句集中器，可以将这些相似的SQL集中起来保证它们只编译一次，这样减少了SQL的编译次数，从而大大降低CPU时间。你今天务必拟出执行方案，明天完成环境测试，我要检查，下周执行。”

我长舒了一口气，心想：人们只做被检查的，不做被要求的。

当优化小组发出系统性能优化报告时，我们已经鏖战40余天。停不下思考的我总结了如上的笔记，后面还附加了一些分析，回顾了如何通过日志分析解除了锁的问题。在下一章会将这次解锁的原委分享给大家。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25714482/viewspace-705673/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25714482/viewspace-705673/