ORACLE 数据库性能分析——大量的IO类等待

作者简介:孙显鹏,Oracle 十年从业经验,拥有11G ocp认证,精通内部原理,擅长调优,解决疑难问题,致力于帮助客户解决生产过程过出现的性能问题,提高生产效率!爱好书法!

现象:

通过分析故障时间段的AWR报告以及平时的AWR报告,发现故障时间段数据库出现大量的IO类等待,IO等待最为严重的等待为 LOG FILE SYNC,该事件为日志同步,平均等待时间为1419MS,ORACLE 建议值为20MS以下,很显然该等待非常异常。我们首先处理日志同步等待问题。

相关AWR报告信息:

2018年1月15日故障时AWR报告的TOP等待信息如下:

2018年1月15日非高峰期AWR报告的TOP信息如下:

故障时间段的日志后台写进程信息:

用户提交频率信息:

综合上面的信息可以看到log file sync 平均等待时间非常高,ORACLE建议该平均等待时间小于20ms,故障时期该等待平均时间为1419ms,但是真正的写事件log file parallel write 平均等待时间只有10ms,另外用户事每次调用事务提交频率为33符合标准值,也就是日志写正常,事务提交频率正常,那么再结合11.2.0.3版本就可以判断该问题为自适应日志写导致,查询自适应log是否开启:

_use_adaptive_log_file_sync =true 表示该功能开启,开启状态表示后台log写进程不需要通知前台事物进程,前台进程自己定期检测log是否写入成功,没有成功继续等待,在高并发时该功能会导致大量的log file sync等待,需要关闭该功能。

知识点:ORACLE从11G开始,为lgwr写日志引入了polling机制,而在以前只有post/wait机制。同时引入了一个隐含参数,”_use_adaptive_log_file_sync”,即在两个机制之间自适应的切换。在11.2.0.3以下,该参数的默认值为false,即只启用post/wait机制。从11.2.0.3开始,该参数的默认值为true,即Oracle会在post/wait机制和polling机制自适应。

  • Post/wait进制下,lgwr进程在写完log buffer中的改变向量后,立刻通知待commit的进程,因此log file sync等待时间短,但lgwr相对来说,负担要重一些。毕竟12C以下lgwr进程只有1个,当同时commit的进程比较多的时候,通知待commit的进程也是一种负担。
  • Polling模式下,待commit的进程,通知lgwr进程进行写入操作后,会进入sleep环节,并在timeout后去看是否log buffer中的内容被写入了磁盘,lgwr进程不再单独通知待commit的进程写已经完成。Polling机制下,解放了一部分lgwr的工作,但是会带来待commit的进程长时间处于log file sync等待下。对于交易型的系统而言,该机制是极度不适用的!

我们看看故障时段自适应log信息:

存在大量的自适应log synch。

解决方式:

禁用该功能,该参数可以动态调整:

关闭该功能后下午采集的AWR报告信息:

从上面的信息可以看到log file sync 平均等待为19MS,符合ORACLE建议值,但是下午业务量不高并不能说明问题,注意自适应LOG在低并发是完全满足系统要求的,高并发事务操作或者大量gc 会导致问题,该功能在OLTP环境是需要关闭的。

关闭后自适应log信息:

可以看到自适应LOG写为0,证明该功能已经关闭。但是我们发现关闭LOG自适应后user I/O占用还有很高,单块读占用了63%,但是平均响应时间正常13ms,那么需要重点关注IO的问题了。

IO问题分析:

既然问题主要反映到IO上面我们看看系统故障时间段IO信息统计信息:

从上图可以看到4个小时的采样IO读数据量共373.9GB,IO写共102.9GB,IO读写量并不是很高,但是IO的AV Rd(ms)非常高,ORACLE建议该值低于20,另外平均IO等待都在200ms据此判断故障时间段IO处理能力很低,存在大量的IO等待。

存储工程师统计数据显示故障期间raid组iops为20000,那么通过AWR计算数据库部分贡献的IOPS和吞吐量:

参考AWR中的Instance Activity Statistics 环节

    IOPS = “physical reads total I/O requests” + “physical writes total I/Orequests”
    MBytes/s = “physical reads total bytes” + physical writes total bytes”

physical read total IO requests

25,301,327

1,759.86

23.04

physical write total IO requests

9,192,691

639.41

8.37


IOPS = “physical reads total I/O requests” + “physical writes total I/Orequests” =  1759.86+639.41 = 2399.27

physical read total bytes

401,005,080,064

27,892,398.48

365,215.76

physical write total bytes

110,058,011,136

7,655,219.48

100,235.44


MBytes/s=
27,892,398.48 + 7,655,219.48 = 34 MB/s 

大致计算其 IOPS 2399.27 而吞吐量为 34 MB/s

那么20000IOPS是怎么产生的,经过分析存储没有划分pool,业务交叉访问,也许导致了20000IOPS,但是生产库大概才贡献了2400IOPS 20000这个值是怎么产生的,总觉得这个值有点虚,上面的计算也明显看到吞吐量太小了,也就是序列读太高,索引不合理吧。但是如果IOPS真的到了20000,那基本上也就只能等待了,数据库再怎么优化也是杯水车薪了。

但是从数据库的层面我们最大程度的减少物理读,降低IOPS,可以通过以下几方面:

1:考虑操作系统目前内存为96GB ORACLE SGA=24GB,增大SGA48GB,尽量缓存数据到内存中,可以减少物理读。

2:在系统范围设置10949事件避免频繁的直接路径读增加IO负担。

3:针对性优化IO使用率高的SQL合理使用索引或者干脆不要使用索引减少单数据块读提IO高吞吐量。

其他优化建议:

  1. sga_target=0,memory_target=0  会触发BUG 1269139.1,建议修改 sga_target=sga_max。
  2. 目前操作系统内存96GB,sga=24GB,建议增大SGA,建议SGA增大到48GB。
  3. 关注OGG内存使用,防止OGG大量使用操作系统swap导致系统不可用。
  4. 目前应用只连接在一号节点建议应用以集群方式连接RAC两个节点,分发连接,高峰期把二号节点内存用起来,降低一号节点的压力。需要业务评估。
  5. 强烈建议关闭 DRM 功能,经查询目前DRM已经关闭。
  6. 建议检查索引的合理性。
  7. 建议用户在条件允许下隔离生产库的raid组。
  8. 如果业务无法降低单块读,有必要购买SSD作为raid组单独存放索引数据。
  9. 如果以上办法不理想建议用户以较大数据块重构数据库。

未经允许不得转载:Oracle一体机用户组 » ORACLE 数据库性能分析——大量的IO类等待

相关推荐