Oracle DBA 应知应会 -- 如何优化LOG FILE SYNC

在一个提交十分频繁的系统中，我们经常会看到LOG FIEL SYNC等待事件排在TO EVENTS中。这种情况下，我们可能就需要针对LOG FILE SYNC等待事件进行优化了。
首先我们会看一下这个等待事件平均的等待时长，正常情况下这个等待事件的平均等待时间不会超过10毫秒，如果等待时间太长，那说明LOG WRITER每次写入的时间过长，如果能够优化一下REDO LOG文件的存储，使之存放到更快的磁盘上，可以减少这个等待事件的单次等待时间。不过往往理论上的事情是最不靠靠谱的，系统管理员可能会和你说，目前的存储优化已经做的好到不能再好了，想要放到更快磁盘上的可能性几乎不存在。也许你很幸运，当前的REDO LOG是放在RAID 5上的，而存储上正好有个磁盘组是RAID 10的，而且可以划些空间给你，那样你可以通过提升REDO LOG写性能来缓解这个等待事件。
如果正好不凑巧，我们无法通过优化REDO LOG的IO性能来解决这个问题，或者优化了REDO LOG的IO性能还是无法达到我们的预期，那么我们由该如何是好呢？有经验的DBA可能会建议你加大LOG BUFFER。提到加大LOG BUFFER可能有些朋友会感到疑惑了，REDO LOG文件写等待严重怎么会和LOG BUFFER直接关联起来呢？实际上这个问题解释起来一点也不难，如果我们发现数据文件的IO性能有问题，平均单块读的等待时间偏长，那么我们可以通过加大DB CACHE来减少总的IO次数，从而达到优化IO的效果。加大LOG BUFFER的原理也是一样的，可以使LOG BUFFER中存储更多的REDO LOG数据，从而减少由于REDO LOG BUFFER不足而导致的LGWR写操作的数量，使平均每次写入REDO LOG 文件的REDO 字节数增加，减少REDO IO的次数，从而达到优化LOG FILE SYNC等待的目的。
如果前面两个招数都不灵了，那我们又该如何处理呢？那么我们就有下一招了，就是减少提交的次数，有些时候，提交过于频繁，我们如论如何去优化都无法彻底解决问题。通过加大一次提交记录的数量，减少提交批次的方法，可以很有效的减少LOG FILE SYNC等待。不过一旦需要这么解决问题，将是十分痛苦的事情了。因为这意味着应用需要做较大的调整，甚至要求应用架构作出调整才能完成，这种修改的代价就十分巨大了。
优化LOG FILE SYNC还有一个方案，就是把部分经常提交的事务设置为异步提交。异步提交是10G以后的新特性，通过设置COMMIT_WRITE参数，可以控制异步提交。COMMIT_WRITE的缺省值是IMMEDIATE,WAIT，我们可以设置为IMMEDIATE,NOWAIT来实现异步提交。这个参数可以系统级设置也可以会话级设置。
如果觉得设置COMMIT_WRITE参数太麻烦，也可以直接使用COMMIT WRITE命令。比如COMMIT WRITE IMMEDIATE NOWAIT。COMMIT WRITE的语法如下：

其中IMMEDIATE的意思是提交时LGWR马上开始REDO LOG写操作，而BATCH的意思是提交后LGWR不需要马上开始写REDO LOG，可以按照其原有的计划启动写操作。BATCH可以让REDO LOG写操作更加迟后，一般我们还是希望尽快写入REDO LOG数据，因此IMMEDIATE NOWAIT是较为常用的优化方案。
不过使用异步提交带来一个问题，从REDO LOG的基本原理来看，一旦提交成功的数据，客户端就可以认为其已经被正常存入数据库了，这些数据就不会丢失了，哪怕实例故障。而采用异步提交技术的数据，哪怕已经提交成功，数据还是有可能丢失。因此一旦数据库实例故障，采用异步提交的系统需要做一些额外的校验和处理。清理不一致的数据，重新插入刚才由于异步提交而丢失的数据。这就需要我们的应用对当前最后一笔提交的数据进行特殊的处理。建立校验机制和错误数据处理机制。这就需要我们在应用层面做一些特殊的设置。特别重要的，无法后续重新完全补充的数据就不适合使用这个方法了。
老白曾经参加过一个项目投标工作，客户在进行压力测试，而测试的存储系统性能不佳，REDO LOG放在RAID 5上，一旦系统负载增加到800TPS，LOG FILE SYNC的等待就十分严重。LOG FILE SYNC成为系统的一个主要瓶颈。由于仅仅是压力测试，实际生产系统不可能达到800TPS，而且实际生产系统的存储系统要远好于本次测试的测试平台。而由于目前这个问题的存在，客户的很多测试项目无法完成，为了完成本次测试，老白就建议客户使用异步提交。采用异步提交后，系统并发处理能力明显提高，最终完成了1200tps的测试工作。
最后，老白要说的是LOG FILE SYNC这个等待事件十分关键的。我们日常做数据库维护的时候，应该对此指标建立基线，一旦这个指标有异常变化，一定要尽快进行分析并解决问题。一旦这个指标恶化，可能导致系统性能急剧下降，甚至导致短暂的HANG住。去年老白的一个客户的系统LOG FILE SYNC的指标平时是2－3毫秒，突然有一次做巡检的时候发现该指标增长到7毫秒了，当时老白在巡检报告中建议客户关注这个指标，并尽快检查存储系统和操作系统，查出变慢的原因。客户检查了一下存储，没有发现故障，于是就不了了之了。下个月巡检的时候，老白发现该指标增长到13毫秒了，再次预警，又没发现问题。随后两个月这个指标一直持续恶化，增长到了20多毫秒。由于前面几个月的检查工作没有发现问题，而从目前系统来看还是很正常的，所以客户也没有再去认真核查。终于有一天，系统突然HANG住了，5分钟后才恢复正常。后来检查原因，就是LOG FILE SYNC等待导致的。根据老白的建议，客户从头到尾检查了一遍 最终发现LVM的一条链路存在闪断的现象，修复了链路后，系统一切都恢复正常了。