mysql被拖垮_分享测试环境中一条sql拖垮整个数据库的解决思路

概述

今天主要简单记录一下最近UAT环境中一条sql拖垮数据库的案例，仅供参考。

1、告警

一大早起来，就冒出这么个告警，看起来又要干活了~

2、检查服务器状态

可以发现缓存了13G，内存已经不够用了，这台服务器上同时部署了Oracle和mysql服务器，初步判断问题在Oracle数据库上。

3、尝试清缓存

无效

4、查看当前等待事件

这台数据库是没有部署DG的，暂时不知道为什么有DG相关进程

--V$sessiong_wait提供了任何情况下session在数据库中当前正在等待什么(如果session当前什么也没在做，则显示它最后的等待事件),注意这里排除了一些常见的IDLE等待事件SELECT inst_id,wait_class,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" , state, sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT FROM GV$SESSION_WAITWHERE event NOTIN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client','gcs remote message') AND event NOT LIKE '%idle%' AND event NOT LIKE '%Idle%' AND event NOT LIKE '%Streams AQ%'GROUP BY inst_id,wait_class,EVENT,stateORDER BY 1,6 desc;说明：1)当state值为Waiting，Second_in_wait值才是实际的等待时间(单位：秒)，当state值为Waiting known time，那么wait_time值就是实际等待时间。Prev代表上一次等待次数，Curr代表当前等待次数

5、查看当前会话基本信息

其实在这里就可以看到第二个会话是问题sql了

--可以获得会话基本信息、执行时间、执行sql、使用的临时表空间大小、undo大小和表空间等SELECT /* use_hash(sess,proc,undo,tmp) use_nl(s)*/distinct sess.inst_id, sess.sid, sess.serial#, sess.username, substr(osuser, 1, 10) osuser, status, sess.process, proc.spid, sess.machine, sess.program, regexp_substr(NUMTODSINTERVAL(nvl((SYSDATE - SQL_EXEC_START) * 24 * 60 * 60, last_call_et), 'SECOND'), '+\d{2} \d{2}:\d{2}:\d{2}') running_sec, TEMP_MB, UNDO_MB, s.sql_id, TSPS.NAME TSPS, decode(sess.action, null, '', sess.action || ', ') || replace(s.sql_text, chr(13), ' ') sql FROM gv$session sess, gv$process proc, gv$sql s, (select ses_addr as saddr, sum(used_ublk / 128) UNDO_MB from v$transaction group by ses_addr) undo, (select session_addr as saddr, SESSION_NUM serial#, sum((blocks / 128)) TEMP_MB from gv$sort_usage group by session_addr, SESSION_NUM) tmp, (select inst_id, sid, serial#, event, t.name from gv$session ls, sys.file$ f, sys.ts$ t where status = 'ACTIVE' and ls.p1text in ('file number', 'file#') and ls.p1 = f.file# and f.ts# = t.ts#) tsps WHERE sess.inst_id = proc.inst_id(+) and sess.saddr = tmp.saddr(+) and sess.serial# = tmp.serial#(+) AND sess.status = 'ACTIVE' and sess.username is not null and sess.sid = tsps.sid(+) and sess.inst_id = tsps.inst_id(+) and sess.serial# = tsps.serial#(+) AND sess.paddr = proc.addr(+) and sess.sql_id = s.sql_id(+) and sess.saddr = undo.saddr(+) ORDER BY running_sec desc, 4, 1, 2, 3;

6、观察awr报告

最快的办法就是看等待事件--》找对应sql

等待事件很明显是CPU占用过高，对应看sql order by CPU time实际上就有结果了。

有兴趣的朋友可以算一下这条sql产生了多少的逻辑读..

7、关于'acknowledge over PGA limit' Wait Event

细心的朋友从AWR应该可以观察到'acknowledge over PGA limit' Wait Event，查询mos文档，发现此类问题是由于PGA大小达到了PGA_AGGREGATE_LIMIT的值，防止ORA-4036错误，后面进程分配pga时需要等待其他进程释放pga。

临时解决方案：

1. Set PGA_AGGREGATE_LIMIT=0 (as SYS user) to revert to 11g behavior of PGA memory management using PGA_AGGREGATE_TARGET parameter and the wait event will be alleviated. ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SID='*' SCOPE=BOTH;2. Set PGA_AGGREGATE_LIMIT to higher than the default value (as SYS user), which was calculated based on the PGA_AGGREGATE_TARGET, PROCESSES or "_pga_limit_target_perc" setting and that will also help to reduce this wait event.ALTER SYSTEM SET PGA_AGGREGATE_LIMIT= &new_value SID='*' SCOPE=BOTH; oracle建议设置PGA_AGGREGATE_LIMIT=0或者增大这个参数的值解决ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SCOPE=BOTH;

上面这个方法只是暂时解决，究竟是什么导致的呢，需要继续检查pga使用情况

首先判断该应用pga设置是否合理，即连接数数量和pga大小，可以通过查询process数量和pga使用，如果明显出现process较小而pga使用很大，则有可能是某些应用进程或者数据库进程异常导致，其实就是下面sql导致了，也就是上面AWR看到的sql。

进一步探究可用以下sql:

--列出占用pga最大的进程select pid, spid, substr(username, 1, 20) "USER", program, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM from v$process where pga_alloc_mem = (select max(pga_alloc_mem) from v$process where program not like '%LGWR%');--检查应用进程分配的pga,查询出大于50m的select a.sid,a.status,a.sql_id,a.last_call_et,b.pga_alloc_mem/1024/1024,a.event,a.state from v$session a,v$process b where a.paddr=b.addr and a.status='INACTIVE' and b.pga_alloc_mem/1024/1024 > 50;--查看当前pga使用情况select * from v$pgastat

8、问题sql分析

两个800万的表全扫后做hash连接，连接是没问题的，问题在于两点：

1)为什么800万的表要全扫

2)为什么同样的表要查两次

ps：看不懂执行计划不要问我，www.baidu.com或者看之前文章..SELECT id FROM xxl_job_log WHERE ID NOT IN (SELECT id FROM xxl_job_log WHERE (trigger_code in (0, 200) and handle_code = 0) OR (handle_code = 200) ) AND alarm_status = 0 ORDER BY id ASC

9、最终建议

1、改写sql，走索引

这个sql存在的问题：

1)NOT IN和OR导致这个表没走索引

2)引用了两次这个表，是不是可以考虑只查一次？例如with xxx

2、这个是日志表，可以删除不必要的数据，只保留1周数据(考虑定时任务解决)

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！

这里引申一个问题，oracle要去找历史的一些问题还是有很多办法的，那么mysql呢？当你重启mysql数据库后怎么去定位历史的一些问题源头？