数据库系统故障将严重影响业务的正常运行。常见的数据库系统异常现象主要包括：主机CPU利用率异常、主机IO异常、数据库连接异常、数据库响应慢、数据库归档日志产生量异常。面对数据库系统异常问题，每一名合格的DBA，都应该有一套成熟的排查方法和工具。本文针对数据库运行突发变慢这一问题，向大家介绍故障的排查思路。
1. 确认系统资源状况
对于任何故障，必须先确认硬件系统的资源使用情况。sar , nmon , top 等都是很好用的工具。为什么要先检查硬件系统资源？因为，大部分的数据库问题都会影响硬件资源的利用率。比如：数据库IO变大，操作系统层面的IOWait就会变高，IOSize也会变大；数据库Active状态的会话变多，对于操作系统最直观的影响就是CPU利用率变高；资源IO突然变小，可能意味数据库层面有堵塞；系统CPU利用率突然变低，可能意味着前端堵塞，请求无法到达数据库。
作为一名DBA，在排查故障时，必须检查系统资源利用率，然后再根据资源利用率的情况，有针对性的进行下一步排查。
2. 排查硬件问题
在确定硬件资源利用率异常情况下，比如IOWait 变高，可以先检查下硬件的状况。实际上，任何的问题，都建议先检查下操作系统的日志。
PC 服务器上可能出现的会影响系统运行，但又不至于导致宕机的硬件问题主要是存储及网络故障，二者都有可能引发数据库IO变慢、数据库活动连接数变多、数据库响应慢等现象。如果发现大量的CPU在等待IO，或者出现网络超时的情况，建议先检查硬件问题。
存储故障排查过程如下：
1. 检查操作系统日志，确认是否有相关的error信息。
grep -i error /var/log/messages | grep -i ‘disk’ |
2. 如果系统是内置盘，并且有配置Raid，可以调用raid卡厂家提供的命令，检查磁盘状态，如MegaCli64命令。
3. 如果使用了多路径软件，可以检查多路径的状态。多活的多路径会轮巡使用每条路径，单条路径的损坏，会因木桶短板效应，导致整个IO变慢（路径非offline状态）。
4. 操作系统日志没有报错，并不一定表示硬件没问题，比如磁盘处于故障的临界状态，就会导致IO缓慢，但是操作系统日志可能并不会报错。如果主机的IO确实变慢，可以通过dd命令，验证磁盘的IO能力。
dd if=/dev/zero of=/dev/xxx oflag=direct --测试写IO。默认写OS cache，direct 表示跳过OS cache . dd if=/dev/xxx of=/dev/null --测试读IO 注意：使用dd命令务必要小心，不当使用，可能导致数据丢失。 |
网络故障排查过程如下：
1. 检查操作系统日志，确认是否有相关的error信息。
grep -i error /var/log/messages | egrep -i ‘up|down’ |
2. 主机网卡的故障通常会伴随up down信息，但网络堵塞或网关故障不并会显示在操作系统日志里，因此，还需要 ping 网关，确认网络是否超时。
ping -s 4096 192.168.237.1 |
硬件确认没问题后，再从数据库层面去查找问题的根源。
3. 排查数据库问题
3.1. 从数据库连接情况来判断异常
数据库的连接数会占用主机的内存资源，活动连接更会占用CPU、IO资源。数据库连接数变多可能是应用问题，比如：应用连接配置修改、业务量突增等，也可能是数据库自身响应变慢导致。从数据库连接着手来定位问题是个常见的排查问题的方法。
3.1.1. 检查数据库连接
统计各IP、应用、数据库用户的连接数，与往常的值比较，找出存在的异常。比如，结果显示特定IP连接数量异常增多，可以检查该IP所在服务器的应用连接配置、应用启停是否异常。以下SQL统计各IP、应用、用户的连接数。
select client_addr, application_name, usename, count(*) from sys_stat_activity where client_addr is not null and application_name is not null and usename is not null group by grouping sets( (client_addr), (application_name), (usename), () ) |
3.1.2. 检查会话状态
会话连接的状态主要有以下几种：
2 active：后端正在执行一个查询。
2 idle：后端正在等待一个新的客户端命令。
2 idle in transaction：后端在一个事务中，但是当前没有正在执行一个查询。
2 idle in transaction (aborted)：这个状态与idle in transaction相似，不过在该事务中的一个语句导致了一个错误。
2 fastpath function call：后端正在执行一个 fast-path 函数。
2 disabled：如果在这个后端中track_activities被禁用，则报告这个状态。
这里必须关注active状态的会话。我们可以统计active状态的会话数量，通过比较历史数据，判断数据库系统的状态。active 状态的会话数量增多，可能是因为业务的繁忙，也可能是由于数据库系统发生了会话阻塞，如果是会话阻塞情况需要结合后面的等待事件进行分析。以下SQL按用户统计不同状态的会话数量。
select usename, state, count(*) from sys_stat_activity group by rollup( usename state, ) |
3.1.3. 检查耗时SQL
数据库活动连接数的增加也可能是由于SQL执行时间变长导致的。可以通过以下SQL查找当前正在执行的SQL耗时的排序。
select query, extract(epoch from now)- extract(epoch from query_start ) as execute_seconds from sys_stat_activity where state = 'active' and execute_seconds > 0 order by execute_seconds desc |
Tips：对于耗时SQL的定位，通过KWR是更好的方法。
3.2. 从阻塞情况来判断异常
用户的活动连接数增长、SQL耗时变长，在数据库现象上都会显示特定的等待事件变多。比如，大量的wait_event_type=’Lock’类型的等待事件，表示系统有大量的锁；大量的WALWrite、WALSync等待事件，表示大量数据库修改操作，或者事务commit过于频繁。通过分析等待事件，可以大致了解引发系统问题的可能原因，给解决问题一个思路。
3.2.1. 查看数据库阻塞情况
对当前数据库的等待事件进行归类，确认异常的等待事件。这里的异常要综合考虑事件、等待数量，比如：锁等待事件，零星1、2可能是正常的，但数量多的就可能有问题。
以下shell对当前的数据库等待事件进行了归类分析，可以很方便看某类等待事件的数量、正在执行的SQL等信息。
CONNSTR="dbname=${DBNAME} user=${USERNAME} host=${DBHOST} port=${DBPORT} password=${PASSWORD}" get_seswait() { ksql "${CONNSTR}" <<HERE \t on select '============================= Session Wait Start: '||current_timestamp||'=============================' from dual; select 'DAT9999'||'#'||wait_event_type||'#'||wait_event||'#'||pid||'#'||query_start||'#'|| usename||'#'||application_name||'#'||client_hostname||'#'||datname||'#'||state||'#'||query from sys_stat_activity where wait_event_type is not null and state is not null order by wait_event_type,wait_event,query,query_start; select '============================= Session Wait Done: '||current_timestamp||'=============================' from dual; \q HERE } format_output(){ grep 'Session Wait Start:' ${TMPFILE} grep '^ DAT9999' ${TMPFILE} | awk -F# 'BEGIN{ v_event=""; v_type=""; v_event_prev=""; v_type_prev=""; v_event_cnt=0; v_type_cnt=0; print " wait_event_type wait_event pid query_start"; print " usename application_name client_hostname datname state"; print " query"; print " -------------------- ------------------------------ ---------- ----------------------------------------"; print " -------------------- -------------------- -------------------- -------------------- -------------------"; print " -------------------------------------------------------------------------------------------------------" } { v_type=$2; v_event=$3; if (v_event != v_event_prev && v_event_prev != "" ) {print "\n *************** Total "v_event_cnt" of wait event "v_event_prev" ***************"; v_event_cnt=0;} if (v_type != v_type_prev && v_type_prev != "" ) {print " ############### Total "v_type_cnt" of wait event type "v_type_prev" ###############\n\n"; v_type_cnt=0;} printf " %-20s %-30s %-10s %-30s\n %-20s %-20s %-20s %-20s %-20s\n %-100s\n", $2, $3, $4, $5, $6, $7, $8, $9, $10, $11; v_event_cnt = v_event_cnt+1; v_type_cnt = v_type_cnt+1; v_event_prev = v_event; v_type_prev = v_type; } END{ print "\n *************** Total "v_event_cnt" of wait event "v_event_prev" ***************"; print " ############### Total "v_type_cnt" of wait event type "v_type_prev" ###############"; }' grep 'Session Wait Done:' ${TMPFILE} } while [ 1 ];do get_seswait 2>&1 > ${TMPFILE} format_output | tee -a $CURRDIR/kbsw_$CURRTIME.log echo -e "\n" sleep 10 done |
该脚本执行的结果如下：
3.2.2. 查看阻塞其他进程的进程
对于大部分的等待事件，只是反映当前的系统状态，而并没有堵塞会话。比如：ClientRead等待事件，只是说明会话正在进行IO，并没有相关会话堵塞。而有些等待事件，则是由于特定会话堵塞导致的。以下例子按层次显示不同会话间的堵塞关系。
该例子可以看出，会话2504是源头，堵塞了会话3065和3227，而3065又堵塞了3182。通过结束会话2504的事务，就可以解决整个堵塞的问题。
3.2.3. 查看时间段内的等待事件
视图Sys_stat_activity记录的是当前时间点的等待事件情况，有时还需要查询过去某一段时间内等待事件，这时就要查询perf.session_history。以下shell脚本查询过去60秒内等待事件。
if [ $# -eq 2 ] ; then EVENT=$1 CNT=$2 elif [ $# -eq 1 ] ; then EVENT=$1 CNT=60 else echo "Usage: $0 event_name [seconds]" exit 1 fi source ./set_conf.sh TMPSQL_waitevent="select 'waitevent'||'#'||backend_type||'#'||wait_cnt||'#'||sss.query from (select backend_type,query_id,count(1) wait_cnt from perf.session_history where event='${EVENT}' and ts > sysdate - round(${CNT}/86400::numeric,5) group by backend_type,query_id) tmp1 left join sys_stat_statements sss on tmp1.query_id=sss.queryid order by tmp1.backend_type,wait_cnt desc;" format_output(){ echo -e " TOP SQL waiting for '${EVENT}' in last ${CNT} seconds" echo -e " =========================================================================================================================================" grep "^ $1" ${TMPFILE} | awk -F# 'BEGIN{ printf " %-20s %-15s %-100s\n","backend_type","wait_cnt","query" print " -------------------- --------------- ----------------------------------------------------------------------------------------------------" } { printf " %-20s %-15s %-100s\n", $2, $3, $4; }' echo -e "\n" } ksql "${CONNSTR}" -q -c "${TMPSQL_waitevent}" 2>&1 > ${TMPFILE} echo "" format_output "waitevent" |
其显示结果类似如下：
4. 结语
本篇所讲的故障处理主要是通过sys_stat_activity视图来窥视数据库当前的状态，解决当前时间点的问题。实际上故障处理过程中可能还需要收集全日志、跟踪SQL执行计划、分析sys_stat_statements视图、分析kwr报告、分析ksh报告等。DBA们要学会使用这些工具来协助定位问题。
