达梦内存等资源持续监控

有些情况下需要监控数据库资源使用情况来定位分析问题

操作系统层监控

--### 监控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;

/