一个buffer busy wait的分析优化思路

白鳝的洞穴 2020-09-29

2973

最近分析过一个BUFFER BUSY WAIT（以下简称BBW）的案例，BBW是十多年前DBA经常遇到的棘手的问题，因为BBW很多情况下和应用有关，大多数BBW是因为某个应用模块频繁访问同一个数据块，再加上当时的IO系统性能一般都存在一些问题，数据文件的单块读响应时间往往要高达20毫秒，再加上有时候CPU资源也会不足，这样就很容易造成BBW了。近些年，由于硬件技术的发展，特别是X86服务器大量替代小型机后，大家都买得起比较强大的硬件设备了。CPU资源瓶颈和IO性能问题都得到了较大的改善，数据库系统的并发处理能力得到了极大的提高。因此近期老白遇到的BBW问题大多数是由于索引引起的。如果一张表上有大量的INSERT操作，而且表上的索引很多（三四个甚至七八个），那么就很容易出现BBW。

正是因为这个原因，看到这个案例的时候，老白第一时间想到的特征就是大量的INSERT语句、BBW等待，较好的IO性能、索引的leaf node split较高、存在大量TX-index contention、存在ITL等待等待。于是拿到数据后，老白就一一验证。

（1）存在性能问题的时候，BBW比较严重

对于一般的系统来说，9毫秒的平均等待时间应该不算啥。不过这是一个制造业的系统，小事务的性能波动会影响业务，因此这9毫秒也不能忽视。

（2）较好的IO性能

IO相关的读写指标均相当好，说明IO总体性能良好。

（3）索引LEAF NODE SPLIT

平均每秒高达30次，一般来说配置差点的系统，超过15就可能会引起较大的TX-index contention等待了。因为这个系统IO性能很好，所以这个系统中TX锁等待不严重，主要体现在BBW上。

（4）TX锁问题、ITL等待问题

这个系统中，index contention等待远高于普通的行锁等待，从这一点上可以进一步确认是索引引起的问题。

不仅仅是行锁冲突主要集中在索引上，ITL等待也十分高，主要也是在存储上。

（5)BBw的对象

（6）INSERT语句

导出ASH数据后进行分析，从上面看，几乎所有的BBW等待都是一条INSERT语句引起的。

通过上面的分析，很快我们就可以初步定位是因为大量并发INSERT，因为索引上的冲突导致了这次的BUFFER BUSY WAIT等待。通过分析这张表上的多个索引，发现一些索引的INIT_TRANS参数设置采用了缺省值，引起了ITL等待，从而加剧了索引的锁冲突等待。

针对这个问题的优化方案也十分清晰了：

（1）调整部分ITL参数不合理的索引的参数（对新增的数据块有效，老数据块需要REBUILD索引后才生效，不过这类INSERT为主的DCS系统中，新增数据块较多，因此修改该参数后很快就会起效）；

（2）如果有可能，经常rebuild一下索引，对于DCS系统来说，基本上是7*24的，如果索引较大，REBUILD的时间较长，分区索引可以按分区进行REBUILD，DCS系统的特点，老数据不怎么会修改，因此主要是REBUILD当前分区（如果是时间范围分区）；

（3）修改索引的参数，把PCTFREE加大到30-40,减小LEAF NODE SPLIT的机会；

（4）可以考虑使用HASH分区或者反转键索引来进一步减少索引单边增长引起的LEAF NODE SPLIT，减少BBW。

使用老白今天介绍的方法，哪怕你以前处置这方面问题的经验不是很丰富，也可以在很快的时间内初步定位问题，并找到解决问题的办法。

今天我们先讨论了一个最近经常遇到的BBW问题的案例，下一期，老白多花点时间，写一篇关于各种BBW问题分析与解决的文章。

文章转载自白鳝的洞穴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论