达梦内存等资源持续监控
有些情况下需要监控数据库资源使用情况来定位分析问题
操作系统层监控
--### 监控dmserver的内存\cpu变化情况(2s收集一次)
for rs in {1..1000};do a=`date;top -bc -n 1|grep dmserver|grep -v grep`;echo $a;sleep 2;done | tee -a vm.`date +%F`.log
test.sh
#!/bin/bash
LOG_FILE="/tmp/dm_sql_$(date +"%Y-%m-%d").log"
CONN="SYSDBA/SYSDBA"
IP="localhost"
PORT="5236"
SQL_FILE="/tmp/dm_check.sql"
while true
do
sleep 5
###disql $CONN@$IP:$PORT \`$SQL_FILE |tee -a $LOG_FILE
/opt/dmdbms/bin/disql -S $CONN@$IP:$PORT \`$SQL_FILE >> $LOG_FILE
echo "" >> $LOG_FILE
date >> $LOG_FILE
echo "" >> $LOG_FILE
done
dm_check.sql
select regexp_replace(name,'[0-9]'),count(*),trunc(sum((org_size /1024.0/1024))) 初始,trunc(sum((data_size /1024.0/1024))) 在用, trunc(sum((total_size /1024.0/1024))) 总的, trunc(sum((target_size /1024.0/1024))) 水位 from v$mem_pool group by regexp_replace(name,'[0-9]') order by 总的 desc;
select
(select sum(cast(n_pages as bigint)* page_size)/1024/1024 from v$bufferpool)||'MB' as BUFFER_SIZE,
( select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as mem_pool,
(select sum(cast(n_pages as bigint) * page_size)/1024/1024 from v$bufferpool)+(select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as TOTAL_SIZE
From dual;
select * from v$sessions where STATE='ACTIVE';
exit;
数据库层面监控
创建需要监控的表
CREATE table dm_t1 as select sysdate as stdate, * from V$MEM_POOL ;
CREATE table dm_t2 as
select sysdate as stdate,regexp_replace(name,'[0-9]'),
count(*),
trunc(sum((org_size /1024.0/1024))) 初始,
trunc(sum((data_size /1024.0/1024))) 在用,
trunc(sum((total_size /1024.0/1024))) 总的,
trunc(sum((target_size /1024.0/1024))) 水位
from v$mem_pool
group by regexp_replace(name,'[0-9]')
order by 总的 desc;
CREATE table dm_t3 as
select
sysdate as stdate,
(select sum(cast(n_pages as bigint)* page_size)/1024/1024 from v$bufferpool)||'MB' as BUFFER_SIZE,
( select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as mem_pool,
(select sum(cast(n_pages as bigint) * page_size)/1024/1024 from v$bufferpool)+(select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as TOTAL_SIZE
From dual;
CREATE table dm_t4 as select sysdate as stdate, * from V$MEM_REGINFO ORDER BY REFNUM DESC ;
CREATE table dm_t5 as select sysdate as stdate, * from V$sysstat ;
定时监控使用情况,每隔10s将监控数据写入表中
declare i int:=1;
begin
while i<=720 LOOP
--内存池使用情况
insert into dm_t1 select sysdate as stdate, * from V$MEM_POOL ;
--内存使用情况2
insert into dm_t2
select sysdate as stdate,regexp_replace(name,'[0-9]'),
count(*),
trunc(sum((org_size /1024.0/1024))) 初始,
trunc(sum((data_size /1024.0/1024))) 在用,
trunc(sum((total_size /1024.0/1024))) 总的,
trunc(sum((target_size /1024.0/1024))) 水位
from v$mem_pool
group by regexp_replace(name,'[0-9]')
order by 总的 desc;
--数据库总内存使用情况
insert into dm_t3
select
sysdate as stdate,
(select sum(cast(n_pages as bigint)* page_size)/1024/1024 from v$bufferpool)||'MB' as BUFFER_SIZE,
( select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as mem_pool,
(select sum(cast(n_pages as bigint) * page_size)/1024/1024 from v$bufferpool)+(select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as TOTAL_SIZE
From dual;
--如果看到REFNUM值特别的大,一直不会变小,说明该内存存在堆积,需要具体的分析。其中fname指定了该内存池的内存来自哪个文件,lineno指定是在该文件哪一行
-- alter system set 'MEMORY_LEAK_CHECK'=1 ; --打开内存泄漏参数
insert into dm_t4 select sysdate as stdate, * from V$MEM_REGINFO ORDER BY REFNUM DESC ;
insert into dm_t5 select sysdate as stdate, * from V$sysstat ;
commit;
sleep(10);
i=i+1;
end LOOP;
end;
/
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了