案例分享-一个因为排序导致的系统故障

白鳝的洞穴 2020-04-14

772

大型排序操作可能会消耗较多的物理内存，在一些物理内存配置十分紧张的环境中，大型排序的风险甚至可能导致宕机，这个说法似乎有点危言耸听。不过老白在10年前还真遇到过一个这样的问题。

客户有一套系统分别部署于华中，华东和华南三地，跑的业务都十分类似。有一天凌晨4点左右，突然华中的系统出现了HANG住的现象，几乎所有业务都受到了影响，后来甚至连登录服务器都比较慢了，运维人员只能重启服务器解决了这个问题。事后老白参与了问题分析。

其实问题的现象还是十分明确的，这是一套10.2.0.4的数据库，从ORACLE的日志中可以十分清楚的发现疑点：

Dumping diagnosticinformation for J003:

OS pid = 4082

loadavg : 99.48 87.6351.87

memory info: freememory = 0.00M

swap info: free = 0.00M alloc = 0.00M total = 0.00M

skgpgpstack: read() forcmd bin/ps -elf | bin/egrep 'PID | 4082' | bin/grep -v grep timed out after60 seconds

skgpgpstack: read() forcmd usr/bin/gdb --batch -quiet -x tmp/stackytxQHC proc/4082/exe 4082 </dev/null 2>&1 timed out after 60 seconds

JOB进程的日志中出现swap free=0M的告警，说明当时出现了内存与SWAP都耗尽的情况，连ps -elf都timeout了。于是我们可以怀疑是SWAP耗尽导致了问题。

从iostat的监控日志也看得出系统盘有较多的IO：

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-szavgqu-sz await svctm %util

sda 11441.29 20.40 1796.02 13.93 105894.53 274.63 52947.26 137.31 58.66 16.69 9.23 0.55 99.50

sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

sda2 11441.29 0.00 1796.02 0.00 105894.53 0.0052947.26 0.00 58.96 16.68 9.29 0.55 99.50

sda3 0.00 4.98 0.00 12.44 0.00 139.30 0.00 69.65 11.20 0.01 0.68 0.04 0.05

十分明确是因为内存耗尽导致了本次故障，那么为什么会出现类似问题呢？为什么三套系统，只有华中出问题，其他地方都没出问题呢？三套数据库实例的参数配置，服务器的配置、操作系统参数都完全相同，半夜时间除了JOB之外，也没其他业务在跑。

在AWR报告中我们看到了一丝蛛丝马迹，在正常的系统中，我们看到PGA的工作区大概使用了11136MB：

PGACache Hit % W/A MB Processed Extra W/A MB Read/Written

--------------------------------- --------------------------

99.2 11,136 91

-------------------------------------------------------------

而在出问题的节点上，居然使用了29438MB

PGACache Hit % W/A MB Processed Extra W/A MB Read/Written

--------------------------------- --------------------------

80.8 29,438 6,998

-------------------------------------------------------------

在看后面的直方图，出现了8次2-4GB的大型排序和大量的256M-512M的排序操作。于是下面怀疑的对象就是这些JOB的执行计划是否出现了偏差，导致了排序操作出现异常（三地数据量，变化量差别不大，不大可能因为数据量导致执行计划异常）。经过分析，很快定位到有一个统计业务，每天会有32个JOB同时进行，在当天的数据中，因为开发人员写了一条测试数据忘记删除，导致了12点的表分析操作后，对该表访问的执行计划出现异常，导致了异常SQL消耗大量的PGA内存，最终导致SWAP耗尽，引发了这次事故。

从这个案例可以有两个收获，第一个是类似问题排查的一条诊断路径，从数据库TRACE到OS监控信息，到AWR报告中的PGA部分（和内存相关的问题PGA是首先要去排查的），再到出问题的SQL的执行计划；第二个收获就是，测试数据一定要按时清除，不要报侥幸心里，类似的悲剧发生的太多了。

最后修改时间：2020-04-14 09:14:58

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

案例分享-一个因为排序导致的系统故障

评论