数据库系统常见异常现象主要有：主机 CPU 利用率异常、主机 IO 异常、数据库连接异常、数据库响应慢、数据库归档日志产生量异常等。
作为一名合格的 DBA，必须有一套成熟的排查步骤、方法、工具。
本文以数据库运行突发变慢的现象为例，为大家介绍下该问题的排查思路。
确认系统资源状况
对于任何故障，必须先确认硬件系统的资源使用情况。sar , nmon , top 等都是很好用的工具。为什么要先检查硬件系统资源？因为，大部分的数据库问题都会影响硬件资源的利用率。比如：数据库 IO 变大，操作系统层面的 IOWait 就会变高，IOSize 也会变大；数据库 Active 状态的会话变多，对于操作系统最直观的影响就是 CPU 利用率变高；资源 IO 突然变小，可能意味数据库层面有堵塞；系统 CPU 利用率突然变低，可能意味着前端堵塞，请求无法到达数据库。
作为一名 DBA，在排查故障是，必须检查系统资源利用率，然后再根据资源利用率的情况，有针对性的进行下一步排查。
排查硬件问题
在确定硬件资源利用率异常，比如 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 能力。
网络故障排查过程如下：
1.检查操作系统日志，确认是否有相关的 error 信息。
grep -i error /var/log/messages | egrep -i ‘up|down’
复制代码
2.主机网卡的故障通常会伴随 up down 信息，但网络堵塞或网关故障不并会显示在操作系统日志里，因此，还需要 ping 网关，确认网络是否超时。
ping -s 4096 192.168.237.1
复制代码
硬件确认没问题后，再从数据库层面去查找问题的根源。
排查数据库问题
从数据库连接情况来判断异常
数据库的连接数会占用主机的内存资源，活动连接更会占用 CPU、IO 资源。数据库连接数变多可能是应用问题，比如：应用连接配置修改、业务量突增等，也可能是数据库自身响应变慢导致。从数据库连接着手来定位问题是个常见的排查问题的方法。
检查数据库连接
统计各 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),
()
)
复制代码
检查会话状态
会话连接的状态主要有以下几种：
active：后端正在执行一个查询。
idle：后端正在等待一个新的客户端命令。
idle in transaction：后端在一个事务中，但是当前没有正在执行一个查询。
idle in transaction (aborted)：这个状态与 idle in transaction 相似，不过在该事务中的一个语句导致了一个错误。
fastpath function call：后端正在执行一个 fast-path 函数。
disabled：如果在这个后端中 track_activities 被禁用，则报告这个状态。
这里必须关注的 active 状态的会话。我们可以统计 active 状态的会话数量，通过比较历史数据，判断数据库系统的状态。active 状态的会话数量增多，可能是因为业务的繁忙，也可能是由于数据库系统发生了会话阻塞。会话阻塞情况需要结合后面的等待事件进行分析。以下 SQL 按用户统计不同状态的会话数量。
select
usename,
state,
count(*)
from
sys_stat_activity
group by
rollup(
usename
state,
)
复制代码
检查耗时 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 是更好的方法。
从阻塞情况来判断异常
用户的活动连接数增长、SQL 耗时变长，在数据库现象上都会显示特定的等待事件变多。比如，大量的 wait_event_type=’Lock’类型的等待事件，表示系统有大量的锁；大量的 WALWrite、WALSync 等待事件，表示大量数据库修改操作，或者事务 commit 过于频繁。通过分析等待事件，可以大致了解引发系统问题的可能原因，给解决问题一个思路。
查看数据库阻塞情况
对当前数据库的等待事件进行归类，确认异常的等待事件。这里的异常要综合考虑事件、等待数量，比如：锁等待事件，零星 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
复制代码
查看阻塞其他进程的进程
对于大部分的等待事件，只是反映当前的系统状态，而并没有堵塞会话。比如：ClientRead 等待事件，只是说明会话正在进行 IO，并没有相关会话堵塞。而有些等待事件，则是由于特定会话堵塞导致的。以下例子按层次显示不同会话间的堵塞关系。
该例子可以看出，会话 2504 是源头，堵塞了会话 3065 和 3227，而 3065 又堵塞了 3182。通过结束会话 2504 的事务，就可以解决整个堵塞的问题。
查看时间段内的等待事件
视图 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"
复制代码
结语
本篇所讲的故障处理主要是通过 sys_stat_activity 视图来窥视数据库当前的状态，解决当前时间点的问题。实际上故障处理过程中可能还需要收集全日志、跟踪 SQL 执行计划、分析 sys_stat_statements 视图、分析 kwr 报告、分析 ksh 报告等。DBA 们要学会使用这些工具来协助定位问题。
评论