经典故障分析 - ASSM引发的索引争用与 enq HW -contention 等待事件

作者介绍:

孙加鹏 云和恩墨技术顾问

六年Oracle技术顾问经验,所服务的行业包括电信运营商、金融业、制造业等。

擅长Oracle的故障诊断、高可用架构、升级迁移等。目前主要服务于上海金融类客户。

1

故障概述

2017年07月24日11:58左右,客户核心数据库出现大量活动会话,导致数据库负载急剧加大,从而导致业务出现延时,DBA通过查看SESSION信息发现有大量的“enq: HW - contention”等待事件。

一直持续约有3分钟,数据库负载下降:

下面是详细的故障分析诊断过程,以及详细的解决方案描述。

2

故障分析

1

故障现象

ENMODB数据库出现大量活动会话,数据库负载急剧加大,通过V$SESSION能看到大量enq:HW contention的活动会话信息,客户紧急采集了ASH信息,方便后期故障分析。

2

分析过程

从AWR和ASH两个维度来分析此故障,先整体后局部,首先从AWR分析入手。

1、AWR分析

首先看一下故障时间段的AWR报告:

半小时的采样时间,DB Time 215mins,其中等待时间“enq: HW - contention”占据近36%,为TOP 10 events中最主要的非空闲等待事件。

等待事件“enq: HW - contention”的解释:

The HW enqueue is used to manage the allocation of space beyond the high water mark of a segment. The high water mark of a segment is the boundary between used and unused space in that segment. If contention is occurring for "enq: HW - contention" it is possible that automatic extension is occuring to allow the extra data to be stored since the High Water Mark has been reached. Frequent allocation of extents,reclaiming chunks,and sometimes poor I/O performance may be causing contention for the LOB segments high water mark.

The HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment. If lots of data is being added to an object concurrently, then multiple processes may be trying to allocate space above the high water mark at the same time, leading to contention.

简而言之,HW锁是在分配高水位线以上的空闲空间时,多个进程同时为了分配高水位线上空闲空间而修改HWM,修改HWM需要持有HW锁,该锁又属于排他锁(mode=6)。

如果大量数据被并发插入某个对象时,那多个进程可能会试图在高水位线以上同时申请可用空间,大并发的申请HW锁,从而导致HW enqueue争用。HW – contention等待事件的P1,P2,P3参数参考下表;

Event

P1 Parameter

P2 Parameter

Parameter 3

enq: HW - contention

name|mode

table space

# block

发现这个时间段确实有大量的INSERT操作,半小时采用中,该SQL执行了约近24w次。

下一步看看HW竞争是在表段还是在索引段上?

大量的物理读/物理写请求也都发生在表TAB_ENMO的索引上。这里可以猜测HW竞争可能不在表上,而在这几个索引上面,IO读写请求非常高。

2、ASH分析

通过客户采集的ASH分析发现,等待事件“enq: HW - contention”是从07月24日11:57:12秒左右开始的,此类session全部被session id为1191的会话阻塞。

查看1191会话信息,发现11:57:12的时候没有被任何会话阻塞(NOT IN WAIT),Session状态是ON CPU:

这个时间点11:57:12的时候会话1191在执行以下的INSERT操作,这个就是源头,并且这个SQL一直在执行。

INSERT操作从11:57:11开始,后续该会话一直都是HW竞争,并且跟其他SESSION争相持有HW锁。

这个时间点大量的SESSION都在持续申请HW锁,因此都在相互blocking sessions。从p1,p2,p3参数中发现P3值并不代表争用块的RDBA(data block address)(36730这个值太小了,这是为啥?先思考下)。

既然P3找不到RDBA,那就从ash中字段CURRENT_FILE#和CURRENT_BLOCK#上寻找争用块:

发现所有的HW竞争都发生在索引IDX_TAB_ENMO_SEQ上,该索引就是表TAB_ENMO上的索引,HW竞争的SQL语句也是上面AWR中发现的SQL。

既然是大并发持有HW锁,多个进程是持续不断的申请HW锁,说明不断的发现free space不足,一般ASSM管理都是一次性分配多个extent,根据对象大小一个extent下面又会有多个block。除非指定storage参数next size 大小:

表空间ENMO_DATA是ASSM(自动段空间管理),并且是本地管理表空间,获取表空间的定义语句:

表空间自动扩展NEXT SIZE 100M。段管理方式使用自动段空间管理(ASSM)。这里有个地方值得关注下,这个表空间属于bigfile tablespace,这就是为什么通过等待事件中的p1,p2,p3参数无法精确定位到具体发生争用的block了。

具体可以参考Mos文档:ID 2098543.1。

因此上面的P3参数指的datafile中的block number,其实就是这个索引段的段头(segment header)所在的block。

所以HW竞争还是发生在索引的段头上,因为段头会记录HWM信息,进程修改HWM就必须要持有HW锁,并修改索引段头上的HWM。所以P3=36730是准确的,只是这个P3参数代表是bigfile tablespace上的block number,dump出file_id=6 block_id=36730的块,可以看出就是索引IDX_TAB_ENMO_SEQ的Segment header。

现在问题基本明朗了,所有的争用都发生在索引的Segment Header上面,进程为了需要更多的空间(unformatted),通过持有HW锁来修改高水位线(HWM),大量的进程并发从而导致HW锁上的竞争。

那既然是ASSM管理,为何新的extent分配的时候还会出现HWM上的竞争呢?不都是bitmap管理了吗?比之前freelist管理要好很多啊,看看这个索引的DDL语句:

索引的stroage参数中NETX=1M,即每次分配空间以每次1M大小来分配,8k块大小即相当于每次分配

128个blocks。难道是客户创建索引的时候指定extent分配大小?问题是不是发生在这个NEXT 1M上面呢?

显然不是的,自动段管理表空间(ASSM)下这个NEXT扩展字句应该是不生效的,不会按照这样来初始化extent的。

可以检查下索引的extent分布,看看extent下面包含多少个blocks。

上面信息可以看出索引的extent并不是只有128个块,跟ASSM的extent分配机制匹配的,segment后期会按照64M的大小分配extent,即每个extent有64*1024/8=8192个block。

7月24日故障之后几天,又不间断的出过2~3次同样的故障,那为何不间断的会发生这种故障?索引真的有这么需要unformatted空间吗?表上有大量的INSERT操作的同时也需要维护索引,同时索引也会进行分裂,不论是leaf node split还是branch node split,都需要新块来满足分裂,实验证明索引分裂只请求unformatted的块,未满块或空闲块都不会使用。下面来看看索引上unformatted 块的使用情况,这个show_space存储过程来自TOM大师的分享:

同时12点12分左右又出现一次HW竞争严重的情况,导致AAS飙高,系统负载急剧升高。因此每次出现HW竞争都是因为Unformatted Blocks不够用的时候,多个进程修改索引段头的HWM的时候持有HW锁。

所以问题原因主要是多个进程同时修改索引段头上的HWM而导致的争用,针对这种问题一般采用HASH分区索引,通过将索引改造成HASH分区索引来缓解索引段头的争用,这样从原来的在单个段头修改HWM,到现在的在多个分区索引的段头上修改HWM。将原先索引从一个L3位图管理块,到多个L3层位图管理块。

先看一下ASSM的extent三层位图管理结构:

整个位图三级位图结构是一个树形结构,L3往往代表的就是Segment Header,L3中记录了所包含的所有L2位图块的地址,L2位图块中又包含了所属L1位图块地址。L1位图块中记录了具体数据块的地址和使用情况。

L3,L2,L1三层结构均以树形结构,一对多的关系。

下面我们来dump出索引的段头(Segment Header)信息。

索引目前已经有2323个extents,现在的高水位线在extent_id 2322 block_id 8192位置。”L2 Hint for inserts”这表名INSERT插入的记录从这个L2(后面跟的是bigfile的block_id)开始。

Dump出这个L2位图块:

如上,这个L2中包含有1007个L1,free L1有77个,L1共有三个状态值,分别为Free 1,Free 3和Free 5,3和5都代表该L1下面有空块。

ASSM管理表空间的Table Block的状态有7种,分别是:

0 = unformatted

1 = logically full (per pctfree)

2 = 0-25% free

3 = 25-50% free

4 = 50%-75% free

5= 75-100% free

对于Block的DUMP有兴趣的可以研究研究。

4

故障解决

问题原因主要是多个进程同时修改索引段头上的HWM而导致的争用,针对这种问题一般采用HASH分区索引,通过将索引改造成HASH分区索引来缓解索引段头的争用,这样从原来的在单个段头修改HWM,到现在的在多个分区索引的段头上修改HWM。

引入思考,当初设计表的时候,从业务角度出发,知道这是一个业务流水表,流水表的特点就是大量的DML操作,特别是INSERT操作的存在,表级别做了分区设计,索引上未考虑到位采用了普通索引,导致后期性能下降和故障发生。因此好的数据库结构设计是有多么重要。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-09-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle ADDM性能诊断利器及报告解读

性能优化是一个永恒的话题,性能优化也是最具有价值,最值得花费精力深入研究的一个课题,因为资源是有限的,时间是有限的。在Oracle数据库中,随着Oracle功能...

1302
来自专栏更流畅、简洁的软件开发方式

利用虚拟硬盘(把内存当作硬盘)来提高数据库的效率(目前只针对SQL Server 2000)可以提高很多

      虚拟硬盘:就是把内存当作硬盘来用,比如有2G的内存,那么可以拿出来1G的内存当作硬盘来用。       自从知道了“虚拟硬盘”这个东东,我就一直在想...

4625
来自专栏北京马哥教育

为 Zabbix 优化 MySQL

Zabbix 和 MySQL 在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO。 考虑到这一点,我将提...

2963
来自专栏数据和云

基于裸数据的异地数据库性能诊断与优化

作者介绍 杨江, 6年Oracle工作经验,4年Oracle数据库专业服务经验,擅长性能优化、性能问题诊断、故障排查、GOLDENGATE。 影响数据库性能的因...

2885
来自专栏hadoop学习笔记

在Python中调用Java扩展包HanLP测试记录

最近在研究中文分词及自然语言相关的内容,关注到JAVA环境下的HanLP,HanLP是一个致力于向生产环境普及NLP技术的开源Java工具包,支持中文分词(N-...

930
来自专栏数据库

单机数据库优化

数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。...

2147
来自专栏云计算教程系列

Ubuntu 16.04如何使用PostgreSQL中的全文搜索

全文搜索(FTS)是搜索引擎用于在数据库中查找结果的技术。它可用于为商店,搜索引擎,报纸等网站上的搜索结果提供支持。

2296
来自专栏性能与架构

Kafka 流数据 SQL 引擎 -- KSQL

KSQL 是什么? KSQL 是一个 Kafka 的 SQL 引擎,可以让我们在流数据上持续执行 SQL 查询 例如,有一个用户点击流的topic,和一个可持续...

4086
来自专栏北京马哥教育

优化 SQL SELECT 语句性能的 6 个简单技巧

SELECT语句的性能调优有时是一个非常耗时的任务,在我看来它遵循帕累托原则。20%的努力很可能会给你带来80%的性能提升,而为了获得另外20%的性能提升你可能...

45011
来自专栏微信公众号:Java团长

Spring MVC+Spring+Mybatis实现支付宝支付功能(图文详解)

本教程详细介绍了如何使用ssm框架实现支付宝支付功能。本文章分为两大部分,分别是「支付宝测试环境代码测试」和「将支付宝支付整合到ssm框架」,详细的代码和图文解...

1951

扫码关注云+社区