达梦数据库监控分析方法

最近有个项目偶发性出现异常，等故障发生时已经处于不可用状态，由于特殊原因，没法部署相关监控工具。现利用脚本进行数据库会话及服务器资源的监控。根据收集的数据分析故障期间sql相关会话和服务器资源使用情况。

--监控cpu和内存
for rs in {1..5000}; do
top -b -d 1 -n 1 | grep -E "average|Cpu|Mem|Swap|dmserver|PID"|grep "$2"|awk '{print strftime("%Y-%m-%d %H:%M:%S"), $0}'|tee -a top2.`date +%F`.log;
iostat -xm 1 2 | tr -s '\n' | tail -n 5|grep "$2"|awk '{print strftime("%Y-%m-%d %H:%M:%S"), $0}' |tee -a io.`date +%F`.log;
sleep 2;
done 2>&1 &

--监控cpu也可以用如下命令
top -b -d 1 -n 1 |head -20| tr -s '\n'|grep "$2"|awk '{print strftime("%Y-%m-%d %H:%M:%S"), $0}'|tee -a top1.`date +%F`.log;
--过滤日志

--dmserver 进程参数
cat top2.2026-05-26.log|grep "14:"|grep dmserver|awk '{print $1,$2,$11,$12}'|tr -s ' ' ','

--sda磁盘io参数
cat io.2026-05-26.log|grep "14:"|grep sda|awk '{print $1,$2,$11,$12}'|tr -s ' ' ','

cat monitor.sh
#!/bin/bash
# 持续监控脚本 monitor_loop.sh

LOG_DIR="/dmdata/monitor"
mkdir -p "$LOG_DIR"

# 防止多个实例同时跑
exec 200>"$LOG_DIR/monitor.lock"
flock -n 200 || { echo "监控已在运行中，退出。"; exit 1; }

KEYWORD=${1:-""}

for rs in {1..20000}; do
/usr/bin/top -b -d 1 -n 1 | grep -E "average|Cpu|Mem|Swap|dmserver|PID" | grep "$KEYWORD" | awk '{print strftime("%Y-%m-%d %H:%M:%S"), $0}' >> "$LOG_DIR/top2.$(date +%F).log"
/usr/bin/iostat -xm 1 2 | tr -s '\n' | tail -n 5 | grep "$KEYWORD" | awk '{print strftime("%Y-%m-%d %H:%M:%S"), $0}' >> "$LOG_DIR/io.$(date +%F).log"
sleep 2
done

 nohup /dmdata/monitor/monitor.sh   >/dev/null 2>&1 &

--监控数据库会话及活动会话
CREATE or REPLACE PROCEDURE dm_monitor
as
i int;
sdt datetime;
BEGIN
for i in 1..5 loop
sdt:=SYSDATE;
insert into DM_SESSION
--CREATE table dm_session as
select SYSDATE() sdt ,STATE,USER_NAME,LTRIM(SUBSTR(CLNT_IP, 1, LENGTH(CLNT_IP) - 6),'::ffff:') clnt_ip,count(*) ct from v$sessions
where SESS_ID<>sessid
GROUP by STATE,USER_NAME ,clnt_ip;
INSERT into DM_SLOWSQL
--CREATE table dm_slowsql as
select SYSDATE() sdt ,
DATEDIFF(ss,LAST_RECV_TIME,SYSDATE()) ss,
SESS_ID,
SESS_SEQ,
SF_GET_SESSION_SQL(SESS_ID) sqltxt,
SEQ_NO,
TRX_ID,
CLNT_IP,
LAST_RECV_TIME
from SYS.V$SESSIONs
where STATE='ACTIVE' and SESS_ID<>sessid;
COMMIT;
sleep(10);
end LOOP;
end;

--设置定时任务调用存储过程
call dm_monitor();

--活动会话记录
select sdt,sum(ct) from dm_session where STATE='ACTIVE'
--and sdt BETWEEN '2026-06-04 16:08:08' and '2026-06-04 16:33:07'
group by sdt ORDER by sdt;

--慢sql记录查询
select count(DISTINCT(SEQ_NO)),TO_CHAR(SQLTXT) sqltxt ,max(ss) from dm_slowsql
--where sdt =''
GROUP by TO_CHAR(SQLTXT) order by 3 desc;

将所需数据获取出来，就可以对某个时间点进行具体分析