达梦数据库内存使用率高分析思路
达梦数据库赶上了XC的红利，早几年前在一些ZF行业已上线， 最近有客户反馈DM8数据库内存总时自动增长很快， 每隔不到一周时间就需要重启操作系统，希望协助分析一下原因，达梦DM我的研究经历并没有多久， 但关系型数据库的内存结构功能都大同小异， 所以尝试以Oracle DBA思路去排查一下， 这里必须吐槽一下国产数据库的资料实在稀缺，连最基本的内存结构网上都没有找到透彻的文档，也许是连google都没收录到吧，如果这类文档只存在于高昂的认证培训中，寄希望于应试教育的群体推广恐怕动力不足。
下面是DM的内存架构，是的感觉连内存池者和Oracle SGA 、PGA很接近。
# DM 内存架构
#ORACLE 内存架构
谈谈对DM内存区的简单理解：
注：下面数据来自我的虚机默认环境，值无参考意义。
1， buffer区
DM 的buffer区相当于Oracle的buffer cache,数据库缓冲区是DMDB用于缓存数据块的内存区域。当数据从磁盘读取到内存时，它们通常会被存储在数据库缓冲区中，以提高数据访问的性能。缓冲区的大小可以通过配置参数进行调整。同样buffer又分为几个子池，如normal , keep, recycle,, fast, roll用于数据内存中周期管理。
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ------------------- ------- ----- --------- ---------- -------------------------------------------------
1 HUGE_BUFFER IN FILE 80 80 80 Initial Huge Buffer Size In Megabytes
2 HUGE_BUFFER_POOLS IN FILE 4 4 4 Huge Buffer Pools
3 BUFFER IN FILE 1000 1000 1000 Initial System Buffer Size In Megabytes
4 BUFFER_POOLS IN FILE 19 19 19 Buffer Pools
5 BUFFER_MODE IN FILE 0 0 0 Buffer pool elimination mode, 0 = LRU, 1 = clock
6 BUFFER_FAST_RELEASE SYS 1 1 1 Whether discard freed page in recycle pool
7 MAX_BUFFER IN FILE 1000 1000 1000 maximum system buffer size in Megabytes
7 rows got
2, sql buffer区`
sql buffer区相当于oracle的shared pool中的一部分或是library cache中的shared sql area， 用于记录sql text, sql execution plan ,plsql obj, 共享池的目的是减少重复SQL语句的解析和优化成本，提高数据库的性能。还有包含类似oracle 的result cache
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- --------------------------- --------- ------ --------- ---------- ------------------------------------------------------------------------------------------------------------
1 HFS_CACHE_SIZE SYS 160 160 160 hfs cache size
2 DYN_SQL_CAN_CACHE SYS 1 1 1 Dynamic SQL cache mode. 0: Forbidden; 1: Allowed if the USE_PLN_POOL is non-zero
3 VPD_CAN_CACHE IN FILE 0 0 0 VPD SQL cache mode. 0: Forbidden; 1: Allowed if the USE_PLN_POOL is non-zero
4 RS_CAN_CACHE IN FILE 0 0 0 Resultset cache mode. 0: Forbidden; 1: Allowed only if the USE_PLN_POOL is non-zero; 2: Allowed only be set
5 RS_CACHE_TABLES READ ONLY Tables allowed to enable result set cache
6 RS_CACHE_MIN_TIME SYS 0 0 0 Least time for resultset to be cached
7 ENABLE_NEST_LOOP_JOIN_CACHE SESSION 0 0 0 whether enable cache temporary result of nest loop join child
8 CACHE_POOL_SIZE IN FILE 100 100 100 SQL buffer size in megabytes
9 DETERMIN_CACHE_SIZE SYS 5 5 5 Deterministic function results cache size(M)
10 CLT_CACHE_TABLES READ ONLY Tables that can be cached in client
11 RLS_CACHE_SIZE SYS 100000 100000 100000 Max number of objects for RLS cache.
11 rows got
3, dict buffer
dict buffer相当于oracle的dictionary cache或叫row cache， 记录的是数据字典对象的buffer，如table,columns 等等
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ----------------------- --------- ----- --------- ---------- --------------------------------------------------------------
1 DICT_BUF_SIZE IN FILE 50 50 50 dict buffer size
2 PLN_DICT_HASH_THRESHOLD IN FILE 20 20 20 Threshold in megabytes for plan dictionary hash table creating
3 LOCK_DICT_OPT READ ONLY 2 2 2 lock dict optimize
4 TRX_DICT_LOCK_NUM SYS 64 64 64 Maximum ignorable dict lock number
4， rlog buffer
rlog buffer相同于oracle 的redo buffer，当事务提交时，相关的重做日志信息会被写入重做日志缓冲区，然后异步刷写到磁盘上的重做日志文件中，以确保数据的持久性
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ------------------------ --------- ----- --------- ---------- -----------------------------------------------------------------------------------------------
1 CKPT_RLOG_SIZE SYS 128 128 128 Checkpoint Rlog Size, 0: Ignore; else: Generate With Redo Log Size
2 RLOG_BUF_SIZE IN FILE 1024 1024 1024 The Number Of Log Pages In One Log Buffer
3 RLOG_POOL_SIZE IN FILE 256 256 256 Redo Log Pool Size In Megabyte
4 RLOG_PARALLEL_ENABLE IN FILE 0 0 0 Whether to enable database to write redo logs in parallel mode
5 RLOG_GEN_FOR_HUGE READ ONLY 0 0 0 Whether to generate rlog for huge table, 0: disable, 1: enable
6 RLOG_IGNORE_TABLE_SET SYS 1 1 1 Whether ignore table set
7 RLOG_APPEND_LOGIC SYS 0 0 0 Type of logic records in redo logs
8 RLOG_APPEND_SYSTAB_LOGIC SYS 0 0 0 Whether to write logic records of system tables in redo logs when RLOG_APPEND_LOGIC is set as 1
9 RLOG_CHECK_SPACE SYS 1 1 1 Whether to check that the available space in redo logs is enough
10 RLOG_SAFE_SPACE IN FILE 128 128 128 Free redo log size in megabytes that can be considered as a save value
11 RLOG_RESERVE_THRESHOLD SYS 0 0 0 Redo subsystem try to keep the used space of online redo less than this target
12 RLOG_RESERVE_SIZE IN FILE 4096 4096 4096 Number of reserved redo log pages for each operation
13 RLOG_SEND_APPLY_MON IN FILE 64 64 64 Monitor recently send or apply rlog_buf info
14 RLOG_COMPRESS_LEVEL IN FILE 0 0 0 The redo log compress level,value in [0,10],0:do not compressed
15 RLOG_ENC_CMPR_THREAD IN FILE 4 4 4 The redo log thread number of encrypt and compress task,value in [1,64],default 4
16 RLOG_PKG_SEND_ECPR_ONLY IN FILE 0 0 0 Only send encrypted or compressed data to standby instance without original data
17 DFS_RLOG_SEND_POLICY IN FILE 1 1 1 DFS rlog send policy, 0: sync; 1:async
18 RLOG_HASH_NAME READ ONLY The name of the hash algorithm used for Redo log
19 TRX_RLOG_WAIT_MODE READ ONLY 0 0 0 Trx rlog wait mode
20 RLOG_ENC_KEY_LEN READ ONLY 0 0 0 rlog_enc_key_len
21 RLOG_PKG_SEND_NUM SYS 1 1 1 Need wait standby database's response message after the number of rlog packages were sent
21 rows got
5， 排序区和HASH区
排序区和HASH区在DM中属于运行时内存区，在ORACLE中同样算是PGA中的两个子池， 运行时内存顾名思义就是仅在进程运行期间存在，进程退出内存释放。
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- -------------------- ------- ----- --------- ---------- -----------------------------------------------------
1 SORT_BUF_SIZE SESSION 20 20 20 maximum sort buffer size in Megabytes
2 SORT_BLK_SIZE SESSION 1 1 1 maximum sort blk size in Megabytes
3 SORT_BUF_GLOBAL_SIZE SYS 1000 1000 1000 maximum global sort buffer size in Megabytes
4 SORT_OPT_SIZE IN FILE 0 0 0 once max memory size of radix sort assist count array
已用时间: 9.726(毫秒). 执行号:1111.
SQL> `p hj
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ------------------ ------- ----- --------- ---------- -----------------------------------------------
1 HJ_BUF_GLOBAL_SIZE SYS 5000 5000 5000 hash buf global size for hash join in megabytes
2 HJ_BUF_SIZE SESSION 500 500 500 hash buf size for hash join in megabytes
3 HJ_BLK_SIZE SESSION 2 2 2 hash blk size for hash join in megabytes
已用时间: 8.828(毫秒). 执行号:1110.
6， SESSION 和VIRTUAL MACHINE
在DM库中每个活动会话有自己的运行时内存池vm_pool和会话内存 sess_pool，当 VM_POOL 和SESS_POOL 大小不足时，会向主内存池MEM_POOL申请内存，到私有池上的 VM_POOL 和 SESS_POOL 进行扩展,每个连接会分配一个SESSION区，应用是用于存储会话级别的数据和变量，在SQL运行期间会分配该内存区1个或多个vm pool
SQL> `p session
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ------------------------ ------- ----- --------- ---------- ------------------------------------------------------------------------
1 SESSION_RESULT_SET_LIMIT SYS 10000 10000 10000 Maximum number of cached result sets for each session, 0 means unlimited
2 MAX_SESSIONS IN FILE 10000 10000 10000 Maximum number of concurrent sessions
3 MAX_SESSION_STATEMENT SYS 10000 10000 10000 Maximum number of statement handles for each session
4 MAX_SESSION_MEMORY SYS 0 0 0 Maximum memory(In Megabytes) a single session can use
已用时间: 15.305(毫秒). 执行号:1325.
SQL> `p vm
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ----------------- ------- ----- --------- ---------- ------------------------------------------
1 VM_STACK_SIZE IN FILE 256 256 256 vm stack size
2 VM_POOL_SIZE IN FILE 64 64 64 vm pool size
3 VM_POOL_TARGET IN FILE 16384 16384 16384 vm pool target size
4 VM_MEM_HEAP SYS 0 0 0 Whether to allocate memory to VM from HEAP
5 VM_STACK_VALIDATE SYS 2 2 2 Whether to validate VM value stack
已用时间: 7.430(毫秒). 执行号:1326.
-- sesssion 1 connect db
-- session 2 检查 session1新连接的内存区
select creator,name, sum(TOTAL_SIZE) t_TOTAL_SIZE from V$MEM_POOL group by rollup (creator,name)
order by 1, 2 nulls last;
行号 CREATOR NAME T_TOTAL_SIZE
---------- ----------- ---------------------- --------------------
52 12346 SESSION 1114112
53 12346 TRX 16384
54 12346 NULL 1130496
-- session 1执行sql
SQL> select sessid;
行号 SESSID
---------- --------------------
1 140421110382416
-- session 2 检查 session1的内存区
行号 CREATOR NAME T_TOTAL_SIZE
---------- ----------- ---------------------- --------------------
52 12346 SESSION 3211264
53 12346 TRX 16384
54 12346 VIRTUAL MACHINE 65536
55 12346 NULL 3293184
7， SSD buffer
猜测于用于使用SSD存储的个性化增强， 不确认是不是像ORACLE的flash cache一样，做为一些文件二级缓存，但当前的版本中已没找到SSD相关的参数。
8， 关于综合参数
SQL> `sp MEMORY
SQL> select para_name,para_value,MAX_VALUE,DEFAULT_VALUE,DESCRIPTION,PARA_TYPE from v$dm_ini where para_name like '%&1%';
行号 PARA_NAME PARA_VALUE MAX_VALUE DEFAULT_VALUE DESCRIPTION PARA_TYPE
---------- ---------------------- ---------- --------- ------------- ---------------------------------------------------------------------------------- ---------
1 MAX_OS_MEMORY 100 100 100 Maximum Percent of OS Memory Can Be Used IN FILE
2 MEMORY_POOL 500 67108864 500 Memory Pool Size In Megabyte IN FILE
3 MEMORY_N_POOLS 1 2048 1 Number of Memory Share Pool IN FILE
4 MEMORY_TARGET 15000 67108864 15000 Memory Share Pool Target Size In Megabyte SYS
5 MEMORY_EXTENT_SIZE 32 10240 32 Memory Pool Extent Size In Megabyte IN FILE
6 MEMORY_LEAK_CHECK 0 1 0 Memory Leak Checking Flag SYS
7 MEMORY_MAGIC_CHECK 1 4 1 Memory Magic Checking Flag IN FILE
8 MEMORY_BAK_POOL 4 2048 4 Memory Backup Pool Size In Megabyte IN FILE
9 HUGE_MEMORY_PERCENTAGE 50 100 50 Maximum percent of HUGE buffer that can be allocated to work as common memory pool IN FILE
10 MAX_SESSION_MEMORY 0 262144 0 Maximum memory(In Megabytes) a single session can use SYS
11 XBOX_MEMORY_TARGET 1024 262144 1024 Memory target size in Megabyte of XBOX SYS SYS
11 rows got
已用时间: 10.160(毫秒). 执行号:1702.
SQL> `p max
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- --------------------------- --------- ------- --------- ---------- -------------------------------------------------------------------------------
1 MAX_OS_MEMORY IN FILE 100 100 100 Maximum Percent of OS Memory Can Be Used
2 MAX_BUFFER IN FILE 1000 1000 1000 maximum system buffer size in Megabytes
..
19 MAX_SESSION_MEMORY SYS 0 0 0 Maximum memory(In Megabytes) a single session can use
20 MAX_CONCURRENT_OLAP_QUERY IN FILE 0 0 0 Maximum number of concurrent OLAP queries
...
38 MAX_HEAP_SIZE IN FILE 0 0 0 Maximum heap size in megabyte allowed to use during analysis phase
Note:
MEMORY_TARGET和MEMORY_POOL 还并不是ORACLE中的MEMORY TARGET也不是SGA target, 感觉像是除了buffer cache外的其它综合，MEMORY_TARGET和MEMORY_POOL不是内存使用硬上限，实际使用内存可以超过该限制， 希望空闲后回收到target size.
MAX_OS_MEMORY是操作系统的比例，不确认降低它，防止OS内存耗尽，是否会导致DB crash.
DM中并没有v$sgastat和v$pgastat这类明确的划分，数据库的主要内存使用查询从V$MEM_POOl和v$bufferpool， 另外对于一些子池也有一些独立的view, 可以从查询V$DYNAMIC_TABLES检索VIEW NAME（类似oracle V$FIXED_TABLE ）
DM主要的内存区为:
MEMORY_POOL_TARGET+ v$bufferpool(buffer,recycle,fast_pool_pages) +RLOG_POOL_SIZE +sqlcache( CACHE_POOL_SIZE )
# DM 总内存 select a.mem_all mem_size, b.mem_all buffer_size,a.MEM_ALL+b.MEM_ALL MEM_M,a.MEM_USED+b.MEM_USED USED_M from (select sum(TOTAL_SIZE)/1024/1024 MEM_ALL, sum(RESERVED_SIZE)/1024/1024 MEM_USED from v$mem_pool)a, (select sum(PAGE_SIZE*N_PAGES)/1024/1024 MEM_ALL, sum(PAGE_SIZE*(N_PAGES-FREE))/1024/1024 MEM_USED from v$bufferpool)b 行号 MEM_SIZE BUFFER_SIZE MEM_M USED_M ---------- -------------------- -------------------- -------------------- -------------------- 1 931 1308 2239 559 # mem pool SQL> select name, is_shared,CHK_LEAK,IS_OVERFLOW,sum(ORG_SIZE) t_ORG_SIZE,sum(TOTAL_SIZE) t_TOTAL_SIZE,sum(TARGET_SIZE) t_TARGET_SIZE,count(*) cnt from V$MEM_POOl group by name, is_shared,CHK_LEAK,IS_OVERFLOW order by 6 desc; 行号 NAME IS_SHARED CHK_LEAK IS_OVERFLOW T_ORG_SIZE T_TOTAL_SIZE T_TARGET_SIZE CNT ---------- ---------------------- --------- -------- ----------- -------------------- -------------------- -------------------- -------------------- 1 SHARE POOL 000 Y Y N 524288000 524288000 15728640000 1 ---- MEMORY_POOL 2 MON ITEM ARR Y Y N 9437184 124780544 136314880 1 3 SQL CACHE MANAGERMENT Y Y N 104857600 104857600 314572800 1 ---- CACHE_POOL_SIZE 4 DICT CACHE Y Y N 52428800 52428800 104857600 1 ---- DICT_BUF_SIZE 5 RT_MEMOBJ_VPOOL N Y N 3145728 28311552 67108864 2 6 FLASHBACK SYS Y Y N 130944 22879992 16777216 1 7 RT_MEMOBJ_VPOOL Y Y N 16777216 16777216 536870912 16 8 DSQL STAT HISTORY Y Y N 15728640 15728640 16777216 1 9 DSQL ET POOL Y Y N 3145728 11534336 16777216 1 10 DSQL BIND DATA HISTORY Y Y N 10485760 10485760 16777216 1 11 BACKUP POOL Y Y N 4194304 4194304 4194304 1 12 VIRTUAL MACHINE N Y N 65536 3211264 16777216 1 13 POLICY GRP Y Y N 65536 2162688 16777216 1 14 SESSION N Y N 65536 1114112 16777216 1 15 LARGE_MEM_SQL_MONITOR Y Y N 1048576 1048576 0 1 16 TRX Y Y N 311296 376832 0 19 17 CYT_CACHE Y Y N 65536 327680 10485760 1 18 CHECK POINT N Y N 131072 131072 10485760 1 19 DBLINK POOL Y Y N 131072 131072 16777216 1 20 HUGE AUX Y Y N 65536 65536 16777216 1 21 INJECT HINT N Y N 65536 65536 0 1 22 PURG_POOL N Y N 65536 65536 10485760 1 23 PARALLEL LOADER POOL Y Y N 65536 65536 33554432 1 24 NSEQ CACHE Y Y N 65536 65536 655360 1 25 XBOX SYS Y Y N 65536 65536 1073741824 1 26 MEM FOR PIPE Y Y N 65536 65536 655360 1 27 XMAL SYS Y Y N 65536 65536 1073741824 1 28 RT_HEAP N Y N 16384 16384 8388608 1 # buffer pool SQL> select name,PAGE_SIZE,sum(N_TOTAL_PAGES),sum(FREE) from v$bufferpool group by name,page_size; 行号 NAME PAGE_SIZE SUM(N_TOTAL_PAGES) SUM(FREE) ---------- ------- ----------- -------------------- -------------------- 1 KEEP 8192 1024 1024 --- KEEP 2 RECYCLE 8192 38400 38374 --- RECYCLE 3 FAST 8192 3000 0 --- FAST_POOL_PAGES 4 NORMAL 8192 124982 124425 --- BUFFER 5 ROLL 8192 128 128 --- FAST_ROLL_PAGES # mem pool中占用的线程 SQL> select v1.*,thrd.name,THREAD_DESC,sess.sess_id,user_name,CREATE_TIME,STATE,CLNT_HOST from ( select creator, sum(TOTAL_SIZE) t_TOTAL_SIZE from V$MEM_POOL group by creator )v1 left join v$threads thrd on v1.creator=thrd.id left join v$sessions sess on v1.creator=sess.thrd_id2 3 4 5 6 7 ; 行号 CREATOR T_TOTAL_SIZE NAME THREAD_DESC SESS_ID USER_NAME CREATE_TIME STATE CLNT_HOST ---------- ----------- -------------------- ------------- ------------------- -------------------- --------- -------------------------- ------ --------- 1 12130 3293184 dm_sql_thd User session thread 140422956543696 SYSDBA 2023-08-16 02:24:01.000000 IDLE oel7db1 2 12346 3293184 dm_sql_thd User session thread 140421110382416 SYSDBA 2023-08-16 02:27:26.000000 IDLE oel7db1 3 13300 4407296 dm_sql_thd User session thread 128281968 SYSDBA 2023-08-16 02:46:53.000000 IDLE oel7db1 4 13481 8601600 dm_sql_thd User session thread 140422954446160 SYSDBA 2023-08-16 02:50:27.000000 ACTIVE oel7db1 5 14782 1574912 NULL NULL NULL NULL NULL NULL NULL 6 13471 32840704 NULL NULL NULL NULL NULL NULL NULL 7 2712 54525952 NULL NULL NULL NULL NULL NULL NULL 8 1903 16384 dm_wrkgrp_thd User working thread NULL NULL NULL NULL NULL 9 1902 16384 dm_wrkgrp_thd User working thread NULL NULL NULL NULL NULL 10 1890 65536 dm_purge_thd Purge thread NULL NULL NULL NULL NULL 11 1905 278528 dm_wrkgrp_thd User working thread NULL NULL NULL NULL NULL 12 1904 16384 dm_wrkgrp_thd User working thread NULL NULL NULL NULL NULL 13 1471 867344120 NULL NULL NULL NULL NULL NULL NULL 13 rows got 已用时间: 13.107(毫秒). 执行号:1710. SQL> select creator,name, sum(TOTAL_SIZE) t_TOTAL_SIZE from V$MEM_POOL group by rollup (creator,name) order by 1, 2 nulls last;2 行号 CREATOR NAME T_TOTAL_SIZE ---------- ----------- ---------------------- -------------------- 1 NULL NULL 998643920 2 1471 BACKUP POOL 4194304 3 1471 CHECK POINT 131072 4 1471 CYT_CACHE 327680 5 1471 DBLINK POOL 131072 6 1471 DICT CACHE 52428800 7 1471 DSQL BIND DATA HISTORY 10485760 8 1471 FLASHBACK SYS 22879992 9 1471 HUGE AUX 65536 10 1471 INJECT HINT 65536 11 1471 LARGE_MEM_SQL_MONITOR 1048576 12 1471 MEM FOR PIPE 65536 13 1471 MON ITEM ARR 125829120 14 1471 NSEQ CACHE 65536 15 1471 PARALLEL LOADER POOL 65536 16 1471 POLICY GRP 2162688 17 1471 RT_MEMOBJ_VPOOL 17825792 18 1471 SHARE POOL 000 524288000 19 1471 SQL CACHE MANAGERMENT 104857600 20 1471 TRX 294912 21 1471 XBOX SYS 65536 22 1471 XMAL SYS 65536 23 1471 NULL 867344120 24 1890 PURG_POOL 65536 25 1890 NULL 65536 26 1902 RT_HEAP 16384 27 1902 NULL 16384 28 1903 RT_HEAP 16384 29 1903 NULL 16384 30 1904 RT_HEAP 16384 31 1904 NULL 16384 32 1905 RT_HEAP 278528 33 1905 NULL 278528 34 2712 DSQL ET POOL 11534336 35 2712 DSQL STAT HISTORY 15728640 36 2712 RT_MEMOBJ_VPOOL 27262976 37 2712 NULL 54525952 38 12130 SESSION 3211264 39 12130 TRX 16384 40 12130 VIRTUAL MACHINE 65536 41 12130 NULL 3293184 42 12346 SESSION 3211264 43 12346 TRX 16384 44 12346 VIRTUAL MACHINE 65536 45 12346 NULL 3293184 46 13300 SESSION 3211264 47 13300 TRX 81920 48 13300 VIRTUAL MACHINE 1114112 49 13300 NULL 4407296 50 13471 PURG_ITEM_POOL 1114112 51 13471 RT_MEMOBJ_VPOOL 31726592 52 13471 NULL 55210456 53 13481 SESSION 5308416 54 13481 TRX 81920 55 13481 VIRTUAL MACHINE 3211264 56 13481 NULL 8601600 57 14782 RT_MEMOBJ_VPOOL 1574912 58 14782 NULL 1574912
OS
[dmdba@oel7db1 ~]$ ps -aux|head -n1; ps -aux|grep dmserver|grep -v grep USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND dmdba 1471 1.7 13.5 3176152 510184 ? Sl Aug15 16:19 /home/dm8/dmdbms/bin/dmserver path=/home/dm8/dmdbms/data/anbob/dm.ini -noconsole [dmdba@oel7db1 ~]$ top -H -p 1471 top - 03:33:21 up 15:27, 4 users, load average: 0.00, 0.00, 0.00 Threads: 69 total, 0 running, 69 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.0 us, 0.3 sy, 0.0 ni, 99.3 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 3765384 total, 2900720 free, 607044 used, 257620 buff/cache KiB Swap: 4063228 total, 4063228 free, 0 used. 3095004 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1890 dmdba 20 0 3176152 510184 35072 S 0.3 13.5 0:26.01 dm_purge_thd 1911 dmdba 20 0 3176152 510184 35072 S 0.3 13.5 0:03.84 dm_lsnr_thd 1471 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.59 dmserver 1521 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:02.41 dm_sqllog_thd 1607 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_quit_thd 1629 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1630 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1631 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1632 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1633 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.02 dm_io_thd 1634 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1635 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1636 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1637 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.01 dm_io_thd 1638 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1639 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1640 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.01 dm_io_thd 1641 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1642 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1643 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1644 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_io_thd 1647 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:10.89 dmserver 1752 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:01.38 dm_chkpnt_thd 1753 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:27.58 dm_redolog_thd 1756 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_hio_thd 1757 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_hio_thd 1758 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_hio_thd 1759 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.00 dm_hio_thd 1873 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.50 dm_tskwrk_thd 1874 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.52 dm_tskwrk_thd 1875 dmdba 20 0 3176152 510184 35072 S 0.0 13.5 0:00.46 dm_tskwrk_thd
当内存率使用较高时
1，查看dmserver进程占用内存 ps
2, 查看线程内存 top -H -p
3, 统计buffer pool和mem_pool内存区使用
4，查找使用大内存进程
5，输出proc meminfo信息
6，pmap查看进程内存
我们看这套生产的mem_pool
name SUM(total_size) ------ --------------- ... BACKUP POOL 4194304 MON ITEM ARR 9437184 TRX 26886144 RT_MEMOBJ_VPOOL 29360128 RT_HEAP 33406976 MAL SYS 134217728 DICT CACHE 524288000 SQL CACHE MANAGERMENT 2147483648 SESSION 2414575616 SHARE POOL 000 5578424320 VIRTUAL MACHINE 6348472320 name is_shared is_overflow org_size/1024./1024. TOTAL_size/1024./1024. SHARE POOL 000 Y N 2048 9999 VIRTUAL MACHINE N N 0.0625 31.0625 VIRTUAL MACHINE N N 0.0625 31.0625 VIRTUAL MACHINE N N 0.0625 31.0625 VIRTUAL MACHINE N N 0.0625 31.0625 VIRTUAL MACHINE N N 0.0625 31.0625 VIRTUAL MACHINE N N 0.0625 31.0625 VIRTUAL MACHINE N N 0.0625 31.0625 VIRTUAL MACHINE N N 0.0625 31.0625 VIRTUAL MACHINE N N 0.0625 31.0625 SESSION N N 0.03125 31.03125 SESSION N N 0.03125 31.03125 SESSION N N 0.03125 31.03125 SESSION N N 0.03125 31.03125 SESSION N N 0.03125 31.03125
Note:
主要为SESSION和VIRTUAL MACHINE 初始化很少但每个都已长到31M. 当进程数较多，主机内存较小时，建议缩小VM_POOL_TARGET和SESS_POOL_TARGET的参数配置，同样检查VM_POOL_SIZE，SESS_POOL_SIZE 是否合理。如果配置MAX_SESSION_MEMORY有可能导致SQL执行失败， 因memory配额溢出。
找出占用内存较大SQL，优化。
SELECT * FROM V$LARGE_MEM_SQLS ORDER BY MEM_USED_BY_K DESC; SELECT SF_GET_SESSION_SQL(SESSID),MAX_MEM_USED,SQL_TXT FROM V$SQL_STAT ORDER BY MAX_MEM_USED DESC;
另外注意dm如果使用本地文件系统，当内存使用率高时，需要检查OS buffer/cache的使用如inode和slab使用空间，从meminfo确认明细。
— over —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。