达梦数据库使用内存监控指导
达梦数据库使用内存监控指导
2.1 如何判断内存池空闲还是紧张
v$mem_pool 可以查看所有内存池的使用信息。当前系统的内存池总大小可以通过以下语句查询,单位是 M:
select name, --内存池名称
is_shared, --是否是共享的
is_overflow, --是否用到了备份池
org_size/1024.0/1024.0, --内存池初始大小
TOTAL_size/1024.0/1024.0, --内存池总大小(包括扩展的)
RESERVED_SIZE/1024.0/1024.0, --当前已分配大小(包括扩展的)
DATA_SIZE/1024.0/1024.0, --实际有效字节
EXTEND_SIZE, --每次扩展多少
TARGET_SIZE, --目标大小
N_EXTEND_NORMAL , --TARGET 范围内累计扩展次数
N_EXTEND_EXCLUSIVE --超过 TARGET 累计扩展次数
from v$mem_pool order by TOTAL_size desc;
注意：
1、N_EXTEND_EXCLUSIVE 如果长期大于 0，说明长期从池外扩展，可能存在内存泄露。需要重点关注。
2、用到备份池的话,需要保持高度关注，此时系统非常危险。
3、内存池创建的线程号 creator 可以与 session 的 thrd_id 关联，查看对应的某个会话的内存使用情况。
4、如果 RESERVED_SIZE 比 org_size 小，说明内存池非常空闲，需要把对于的初始内存放小，否则浪费。
5、如果 TOTAL_size 比 TARGET_SIZE 大，说明内存池不够，经常向池外申请，需要把对于的参数调大。尽量保持每个池自持。
2.2 如何判断 BUFFER 空闲还是紧张
select name, --缓冲区名称
n_pages, --页数
free, --空闲页数目
N_DISCARD64 --淘汰的页数
from v$bufferpool
注意重点：
1、如果 free 很多说明该缓冲区很空闲，可以适当的调整降低 buffer 缓冲区参数值。
2、如果 free 项为 0，或者 N_DISCARD64 非零，表示该缓冲区经常淘汰。这种情况，就说明对应的缓冲区
参数太小，导致频繁淘汰，需要调整对应的缓冲区的参数。
2.3 达梦内存总量
达梦数据库使用的内存大致等于 BUFFER + MPOOL，对应的 SQL 语句为：
select
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool)||'MB' as BUFFER_SIZE, ( select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as mem_pool, (select sum(n_pages * page_size)/1024/1024 from v$bufferpool)+(select sum(total_size)/1024/1024 from
v$mem_pool)||'MB' as TOTAL_SIZE
From dual;
2.4 使用内存最多的 sql
V$SQL_STAT 需 要 ENABLE_MONITOR=1 才 开 始 监 控 。 其 中 5~58 列 中 的 监 控 项 ， 可 以 通 过
SP_SET_SQL_STAT_THRESHOLD()设置监控阀值，超过阀值才开始监控。
例如：监控 sql 执行在 1 秒以上的 SQL 语句使用的内存
SELECT "SESSID", MAX_MEM_USED||'KB',SQL_TXT FROM V$SQL_STAT order by MAX_MEM_USED DESC;
通过此 SQL 语句确定使用内存较大的 SQL，进行针对性的优化，如消除 HASH,SORT,DISTINCT 等操作。该查
询只能查询当前活动 STMT 上的语句消耗情况，历史情况需要查询 V$SQL_STAT_HISTORY ，该视图上保留 1W 行 SQLSTAT 历史信息.
2.5 单个会话内存使用总量
SELECT
A.CREATOR , B.SQL_TEXT ,
11
SUM(A.TOTAL_SIZE)/1024.0/1024.0 TOTAL_M, --当前总量(包括扩展)
SUM(A.DATA_SIZE) /1024.0/1024.0 DATA_SIZE_M --实际使用量
FROM
V$MEM_POOL A, V$SESSIONS B
WHERE
A.CREATOR = B.THRD_ID
GROUP BY
A.CREATOR, B.SQL_TEXT
ORDER BY
TOTAL_M DESC;
2.6 内存增长监控
--打开内存泄露检查参数 MEMORY_LEAK_CHECK
alter system set 'MEMORY_LEAK_CHECK'=1 ; --执行查询
select * from V$MEM_REGINFO ORDER BY REFNUM DESC
如果看到 REFNUM 值特别的大，一直不会变小，说明该内存存在堆积，需要具体的分析。其中 fname 指定
了该内存池的内存来自哪个文件，lineno 指定是在该文件哪一行。
2.7 通过 v$sysstat 视图监控内存的使用情况
select name ,stat_val/1024.0/1024.0 from v$sysstat where CLASSID=11 ;
其中：
memory pool size in bytes： 内存池总的大小
memory used bytes： 内存池使用的内存大小
memory used bytes from os： 内存池从操作系统分配的大小
######sample 1 某台主机上跑了3个达梦数据，内存设置不合理，导致swap 爆出告警提示。
for i in `cd /proc;ls |grep "^[0-9]"|awk ' $0 >100'` ;do awk '/Swap:/{a=a+$2}END{print '"$i"',a/1024"M"}' /proc/$i/smaps ;done 2>&1 |sort -k2nr |head
################# 检查swap 消耗的进程，发现是达梦进程占用最多的资源
[root@daa-dmdb02 ~]# for i in `cd /proc;ls |grep "^[0-9]"|awk ' $0 >100'` ;do awk '/Swap:/{a=a+$2}END{print '"$i"',a/1024"M"}' /proc/$i/smaps ;done 2>&1 |sort -k2nr |head
2138134 29280.6M
2614407 1681.82M
3017051 958.082M
3544 185.191M
1788853 143.074M
17351 62.6523M
4147620 13.625M
3258 12.5742M
1703 9.26562M
2707145 8.57031M
[root@daa-dmdb02 ~]# ps -ef|grep 2138134
root 199000 195831 0 14:55 pts/2 00:00:00 grep 2138134
opelgs 2138134 1 0 Sep20 ? 00:57:37 /ddddk/elgs/app/dmdbms/bin/dmserver path=/ddddk/elgs/data/elgs/dm.ini -noconsole
[root@daa-dmdb02 ~]#
当前内存总大小124G.
[root@daa-dmdb02 ~]# free -g
total used free shared buff/cache available
Mem: 124 79 3 0 41 38
Swap: 31 31 0
[root@daa-dmdb02 ~]#
分别检查每个数据库消耗的内存大小。
sql1:------buffer和内存池的大小
select
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool)|| 'MB' as BUFFER_SIZE,
( select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as mem_pool,
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool)+(select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as TOTAL_SIZE
From dual;
LINEID BUFFER_SIZE MEM_POOL TOTAL_SIZE
---------- ----------- -------- ----------
1 44007MB 15320MB 59327MB
BUFFER_SIZE:系统缓存区，用户行存表的系统缓冲区
MEM_POOL:共享内存池，当系统在运行过后过程中需要申请小片内存时，可以在共享内存中进行申请，用完会释放
----
每个库BUFFER占用40000M，服务器总内存为128G
opaaa 占用BUFFER_SIZE 44007MB, MEM_POOL 7401MB,TOTAL_SIZE 51408MB。
opbbbb占用BUFFER_SIZE 44007MB，MEM_POOL 15306MB，TOTAL_SIZE 59313MB。
opccccc占用BUFFER_SIZE 44007MB，MEM_POOL 13619MB，TOTAL_SIZE 57626MB。
running : the scirpts:AutoParaAdj3.4_dm8_20231010
declare
exec_mode int:= 1; --0表示直接执行脚本修改参数，1表示不直接修改参数，打印设置参数的语句，设置为1后，必须调整v_mem_mb和v_cpus
is_dsc int := 0; --是否是dsc集群,如果是dsc集群请设置为1，将自动调整dsc相关参数
mem_per int:= 30; --默认所有的内存归达梦数据库使用，如实际不能100%可用，可以调整此参数
##v_mem_mb int:= 124000; --exec_mode为1时请自行根据机器实际内存调整此参数
v_mem_mb int:= 256000; --exec_mode为1时请自行根据机器实际内存调整此参数
v_cpus int:= 96; --exec_mode为1时请自行根据机器实际CPU核数调整此参数
sort_mode int:=0; --并发量较高的OLTP类型系统此参数设置为0，并发量不高的一般业务系统和OLAP类的系统此参数设置为1
##条件1 ： 128G ,96 cpu,3个实例都可以如此运行： 58.2.10.17
SP_SET_PARA_VALUE(2,'WORKER_THREADS',64);
SP_SET_PARA_VALUE(2,'TASK_THREADS',16);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',8);
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY', 45);
SP_SET_PARA_VALUE(2,'MEMORY_POOL', 2000);
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS', 11);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET', 4000); <- 4G
SP_SET_PARA_VALUE(2,'BUFFER', 15000); <- 15G
SP_SET_PARA_VALUE(2,'MAX_BUFFER', 15000);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS', 47);
SP_SET_PARA_VALUE(2,'RECYCLE', 1000);
SP_SET_PARA_VALUE(2,'RECYCLE_POOLS', 17);
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES', 9999);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES', 1000);
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS',1);
SP_SET_PARA_VALUE(2,'MULTI_PAGE_GET_NUM',1);
SP_SET_PARA_VALUE(2,'PRELOAD_SCAN_NUM',0);
SP_SET_PARA_VALUE(2,'PRELOAD_EXTENT_NUM',0);
SP_SET_PARA_VALUE(2,'MEMORY_MAGIC_CHECK', 1);
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE', 2000);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE', 250);
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',2000);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE', 250);
SP_SET_PARA_VALUE(2,'SORT_FLAG',0);
SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',1);
SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE', 2);
SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE', 2000);
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE', 1024);
SP_SET_PARA_VALUE(2,'CACHE_POOL_SIZE', 1024);
SP_SET_PARA_VALUE(2,'DICT_BUF_SIZE', 50);
SP_SET_PARA_VALUE(2,'VM_POOL_TARGET', 16384);
SP_SET_PARA_VALUE(2,'SESS_POOL_TARGET', 16384);
SP_SET_PARA_VALUE(2,'USE_PLN_POOL', 1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR', 1);
SP_SET_PARA_VALUE(2,'SVR_LOG', 0);
SP_SET_PARA_VALUE(2,'TEMP_SIZE', 1024);
SP_SET_PARA_VALUE(2,'TEMP_SPACE_LIMIT', 102400);
SP_SET_PARA_VALUE(2,'MAX_SESSIONS', 1500);
SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT', 20000);
SP_SET_PARA_VALUE(2,'PK_WITH_CLUSTER', 0);
SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);
SP_SET_PARA_VALUE(2,'OLAP_FLAG',2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG',1);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
SP_SET_PARA_VALUE(2,'ADAPTIVE_NPLN_FLAG',0);
SP_SET_PARA_VALUE(2,'PARALLEL_PURGE_FLAG',1);
SP_SET_PARA_VALUE(2,'PARALLEL_POLICY',2);
SP_SET_PARA_VALUE(2,'UNDO_EXTENT_NUM',16);
SP_SET_PARA_VALUE(2,'ENABLE_INJECT_HINT',1);
SP_SET_PARA_VALUE(2,'BTR_SPLIT_MODE',1);
SP_SET_PARA_VALUE(2,'FAST_LOGIN',1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR_BP',0);
##条件2 ： 256G ,96 cpu,3个实例都可以如此运行： 11.0.1.1
SP_SET_PARA_VALUE(2,'WORKER_THREADS',64);
SP_SET_PARA_VALUE(2,'TASK_THREADS',16);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',8);
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY', 45);
SP_SET_PARA_VALUE(2,'MEMORY_POOL', 2000);
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS', 59);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET', 9000); <-9g
SP_SET_PARA_VALUE(2,'BUFFER', 28000); <-29g
SP_SET_PARA_VALUE(2,'MAX_BUFFER', 28000);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS', 101);
SP_SET_PARA_VALUE(2,'RECYCLE', 3000);
SP_SET_PARA_VALUE(2,'RECYCLE_POOLS', 41);
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES', 99999);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES', 9999);
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS',1);
SP_SET_PARA_VALUE(2,'MULTI_PAGE_GET_NUM',1);
SP_SET_PARA_VALUE(2,'PRELOAD_SCAN_NUM',0);
SP_SET_PARA_VALUE(2,'PRELOAD_EXTENT_NUM',0);
SP_SET_PARA_VALUE(2,'MEMORY_MAGIC_CHECK', 1);
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE', 12000);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE', 512);
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',3000);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE', 512);
SP_SET_PARA_VALUE(2,'SORT_FLAG',0);
SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',1);
SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE', 2);
SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE', 2000);
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE', 2048);
SP_SET_PARA_VALUE(2,'CACHE_POOL_SIZE', 2048);
SP_SET_PARA_VALUE(2,'DICT_BUF_SIZE', 50);
SP_SET_PARA_VALUE(2,'VM_POOL_TARGET', 16384);
SP_SET_PARA_VALUE(2,'SESS_POOL_TARGET', 16384);
SP_SET_PARA_VALUE(2,'USE_PLN_POOL', 1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR', 1);
SP_SET_PARA_VALUE(2,'SVR_LOG', 0);
SP_SET_PARA_VALUE(2,'TEMP_SIZE', 1024);
SP_SET_PARA_VALUE(2,'TEMP_SPACE_LIMIT', 102400);
SP_SET_PARA_VALUE(2,'MAX_SESSIONS', 1500);
SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT', 20000);
SP_SET_PARA_VALUE(2,'PK_WITH_CLUSTER', 0);
SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);
SP_SET_PARA_VALUE(2,'OLAP_FLAG',2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG',1);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
SP_SET_PARA_VALUE(2,'ADAPTIVE_NPLN_FLAG',0);
SP_SET_PARA_VALUE(2,'PARALLEL_PURGE_FLAG',1);
SP_SET_PARA_VALUE(2,'PARALLEL_POLICY',2);
SP_SET_PARA_VALUE(2,'UNDO_EXTENT_NUM',16);
SP_SET_PARA_VALUE(2,'ENABLE_INJECT_HINT',1);
SP_SET_PARA_VALUE(2,'BTR_SPLIT_MODE',1);
SP_SET_PARA_VALUE(2,'FAST_LOGIN',1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR_BP',0);
