多索引的表并发插入时 buffer busy waits

bamuta

1.问题，基本环境，解决过程：
        有一台开发用的机器上做压力测试，该机器redhat64位，数据库1120164位。测试跑1个只有insert的存储过程，测试并发300，只针对1张表做操作。表上有14个索引（每个索引只有1个COLUMN_NAME，跟业务开发沟通过，索引暂时不让变）。表有12379662行数据，统计dba_extents，该表segment大小4821M。实际业务可能读写都有，insert的并发量很大。
        压力测试重复做了多次（大概5、6次），两次间隔有一定时间，是无压力的时间段。
        第一次AWR(附件：awrrpt_1_4428_4429索引调整前.html)有buffer busy waits和enq: TX - row lock contention。针对这个情况，找了AWR的Segments by Buffer Busy Waits。全是索引，对列出的5个索引，调整了pctfree,数据连续的做了 reverse,其中有3个试着做了分区，重建了表，rebuild了余下的所有索引，并将所有索引放到了单独的表空间。重新收集表的统计信息。
       再次进行几次压力测试，得到AWR(附件：awrrpt_1_4504_4508索引调整后)。根据压力测试人员的反馈和AWR的报告看，该insert执行速度并没有改善，且原等待事件依旧。
2.关于磁盘
        用orion测了存有数据分区的IO性能，是run –simple模式下运行的，最大IOPS： 107，最大MBPS：41.53。
3.要解决的问题。
        暂时在不调整硬件环境下的情况下。怎么优化压力测试并发insert的执行时间？这种情况下buffer busy waits怎么减少？

        另外附件中有两次AWR，orion数据，以及5个index修改的SQL

saup007

1张表14个索引！！！

Maclean Liu(刘相兵

NO RAC 11.2.0.1

log file parallel write        13,890        0        285        21        6.48        26.99

LGWR        1M        0.09        .000996        2.3G        10.17        2.37066        7141        40.38
DBWR        0M        0.03        0M        2G        27.56        1.99116        20.2K        22.68



io非常差， lz 的想法是在 IO非常差的情况下 ，不优化 IO 而优化性能

==》Io是这里性能最大的瓶颈，这本身是矛盾的

Maclean Liu(刘相兵

IO非常差的情况下 优化性能==》主要以 牺牲持久性为代价 ，愿意丢失数据


1、 创建非常大的redo logfile
2、 减少checkpoint
3、 使用 commit_write='batch,nowait';
4、 尽可能使用nologging
5、 加大buffer cache
6、 关闭后台的一些维护操作例如 smon 维护undo

欢迎补充

545459983

使用异步io也可以

bamuta

14个索引我也想砍掉一些，有些做复合索引，人家业务暂时不同意。
谢谢刘大，领导是想不花钱的情况办事，提了几次IO差，听不进去，也提过nologging，加大buffer cache，领导不同意。有了刘大的指导，下次跟领导说，底气大大的了。

aaron8219

IO正常的情况是不是应该在70M~100M/s?

dla001

把OEM停掉，把审计关掉。

bamuta

dla001 发表于 2013-9-27 11:55
把OEM停掉，把审计关掉。

OEM影响不大，是想看看实时的变化 ，临时打开的，关掉OEM的时候性能也一样的。
开发环境开审计我也纳闷，但这个是这么要求的，在我接手之前一直是开着的。除非能通过数据说明审计造成了严重影响，才有可能关掉。

qq7343696

增大log_buffer的大小。

Maclean Liu(刘相兵

qq7343696 发表于 2013-9-28 00:21
增大log_buffer的大小。

增大log buffer在这里有什么益处呢？

不了峰

把表放在keep 池中～

不了峰

可能可以把表 或索引，变成稀疏些，加么pctfree参数，以减少并发插入时对数据块的争用

不了峰

Maclean Liu(刘相兵 发表于 2013-9-28 23:45
增大log buffer在这里有什么益处呢？

top 5 event 出现 log buffer space
Event        Waits        Time(s)        Avg wait (ms)        % DB time        Wait Class
log buffer space        34,668        9,854        284        17.53        Configuration
虽然 redo size每秒不到1M。但redo log buffer也只有4M
但是log file sync 有15,693 waits，并且user commits=5,983 次

增大 log buffer 可能可以减小log file sync 产生的次数，并且也可以减小log buffer space的等待次数

Maclean Liu(刘相兵

"增大 log buffer 可能可以减小log file sync 产生的次数" 的依据是什么？

这里的log buffer space       ，我认为是因为redo写的慢导致申请log buffer space变慢， 增大log buffer的效果不大

bamuta

不了峰 发表于 2013-9-29 09:16
可能可以把表 或索引，变成稀疏些，加么pctfree参数，以减少并发插入时对数据块的争用 ...

Segments by Buffer Busy Waits中的索引加过pctfree,有连续值的做过reverse ，有的还试着分了区，一点效果都没有

bamuta

不了峰 发表于 2013-9-29 09:07
把表放在keep 池中～

Segments by Buffer Busy Waits 中全是索引，把表放keep中的依据是什么？

不了峰

bamuta 发表于 2013-9-29 17:38
Segments by Buffer Busy Waits 中全是索引，把表放keep中的依据是什么？

那就把索引放在keep池中，

依据就是想尽量减少IO的发生.

但是你的io很差的话，不应该做这么大的压力测试 ，因为这应该跟你实际的生产环境不太一致。

另外，刘大说讨论的是在IO很差的情况下，可以做哪些优化。所以我说的主要针对这个。

你把log buffer加大到30M, 索引放在keep池中,pctfree给个50看看效果 如何 ：）