达梦数据库学习笔记之 — 内存结构
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 达梦数据库学习笔记之 — 内存结构
近几年国产数据库越来越火，作为老牌国产数据库达梦，近期也是炙手可热，不得不抽时间研究一下。大家都说达梦数据库跟Oracle的兼容性是比较高的，从个人实际测试来看，确实还不错，提供了很多类似Oracle数据库的视图，作为Oracle dba的我，基本上可以无缝切换，直接上手。
近期打算写一个系列的达梦数据库文章，首先从内存结构开始吧。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SQL> select name,TOTAL_SIZE,TARGET_SIZE,IS_OVERFLOW,FILE_NAME from v$mem_pool; LINEID NAME TOTAL_SIZE TARGET_SIZE IS_OVERFLOW FILE_NAME ---------- --------------------- -------------------- -------------------- ----------- ----------------------------------------------- 1 SHARE POOL 524288000 0 N /data/sdb/wxy/trunk8_rel_2008_f/knl/mem2.c 2 BACKUP POOL 4194304 4194304 N /data/sdb/wxy/trunk8_rel_2008_f/knl/mem2.c 3 MON ITEM ARR 134217728 136314880 N /data/sdb/wxy/trunk8_rel_2008_f/mon/dthrd.c 4 LARGE_MEM_SQL_MONITOR 1048576 0 N /data/sdb/wxy/trunk8_rel_2008_f/mon/dsql.c 5 CYT_CACHE 327680 10485760 N /data/sdb/wxy/trunk8_rel_2008_f/crypto/cyt.c 6 XMAL SYS 65536 0 N /data/sdb/wxy/trunk8_rel_2008_f/xmal/xmal.c 7 XBOX SYS 327680 0 N /data/sdb/wxy/trunk8_rel_2008_f/xmal/xbox.c 8 DICT CACHE 52428800 104857600 N /data/sdb/wxy/trunk8_rel_2008_f/dict/ndct.c 9 INJECT HINT 65536 0 N /data/sdb/wxy/trunk8_rel_2008_f/dict/ndct.c 10 CHECK POINT 131072 10485760 N /data/sdb/wxy/trunk8_rel_2008_f/log/ckpt2.c 11 HUGE AUX 65536 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/hfs/haux.c 12 SQL CACHE MANAGERMENT 209715200 629145600 N /data/sdb/wxy/trunk8_rel_2008_f/mgr/scp.c 13 MEM FOR PIPE 65536 655360 N /data/sdb/wxy/trunk8_rel_2008_f/pub/ifun_pipe.c 14 FLASHBACK SYS 393088 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/trx/fback.c 15 RT_MEMOBJ_VPOOL 1048576 33554432 N /data/sdb/wxy/trunk8_rel_2008_f/job/job.c 16 DBLINK POOL 131072 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/dblnk/dblnk.c 17 NSEQ CACHE 65536 655360 N /data/sdb/wxy/trunk8_rel_2008_f/npar/nseq.c 18 PARALLEL LOADER POOL 65536 33554432 N /data/sdb/wxy/trunk8_rel_2008_f/bldr_dll/bldr.c 19 POLICY GRP 65536 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/dict/ndctpgrp.c 20 PURG_POOL 65536 10485760 N /data/sdb/wxy/trunk8_rel_2008_f/trx/purg2.c 21 DSQL STAT HISTORY 15728640 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/mon/dsql.c 22 SESSION 6307840 33554432 N /data/sdb/wxy/trunk8_rel_2008_f/mgr/sess4.c 23 RT_HEAP 1064960 8388608 N /data/sdb/wxy/trunk8_rel_2008_f/mgr/sess4.c 24 VIRTUAL MACHINE 2162688 33554432 N /data/sdb/wxy/trunk8_rel_2008_f/op/vm.c 25 DSQL ET POOL 3145728 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/mon/dsql.c 25 rows got |
从达梦的内存结构来看；DM8中包含了25个内存结构；相比Oracle来讲似乎简单的太多了。这里简单描述一下相关核心内存结构的作用：
1、共享内存
即共享内存，从字面意思来看，类似Oracle的share pool；实际上并不是；该参数是整个共享内存的大小；通过参数memory_pool来进行控制。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SQL> select name,VALUE,DESCRIPTION from v$parameter where name='MEMORY_POOL'; LINEID NAME VALUE DESCRIPTION ---------- ----------- ----- ---------------------------- 1 MEMORY_POOL 500 Memory Pool Size In Megabyte used time: 6.684(ms). Execute id is 234. SQL> select PARA_NAME,PARA_VALUE,DESCRIPTION ,PARA_TYPE from v$dm_ini where PARA_NAME like '%MEMORY_%'; LINEID PARA_NAME PARA_VALUE DESCRIPTION PARA_TYPE ---------- ---------------------- ---------- ---------------------------------------------------------------------------------- --------- 1 MEMORY_POOL 500 Memory Pool Size In Megabyte IN FILE 2 MEMORY_TARGET 0 Memory Share Pool Target Size In Megabyte SYS 3 MEMORY_EXTENT_SIZE 1 Memory Pool Extent Size In Megabyte IN FILE 4 MEMORY_LEAK_CHECK 0 Memory Leak Checking Flag SYS 5 MEMORY_MAGIC_CHECK 2 Memory Magic Checking Flag IN FILE 6 MEMORY_BAK_POOL 4 Memory Backup Pool Size In Megabyte IN FILE 7 HUGE_MEMORY_PERCENTAGE 50 Maximum percent of HUGE buffer that can be allocated to work as common memory pool IN FILE 7 rows got SQL> select PARA_TYPE,count(1) from v$dm_ini group by PARA_TYPE; LINEID PARA_TYPE COUNT(1) ---------- --------- -------------------- 1 READ ONLY 78 2 SYS 161 --静态参数 3 IN FILE 178 --手动参数 4 SESSION 226 --动态参数 |
从参数来看，DM也有类似Oracle的内存自动管理机制，如memory_target.
参数属性分为三种:静态、动态和手动。
静态，可以被动态修改，修改后重启服务器才 能生效。
动态，可以被动态修改，修改后即时生效。动态参数又分为会话级和系统级两种。会话级参数被修改后，新参数值只会影响新创建的会话，之前创建的会话不受影响;系统级参数 的修改则会影响所有的会话。
手动，不能被动态修改，必须手动修改 dm.ini 参数文件，然后重启才能生效。
2、buffer 缓冲区
在DM数据库中，buffer 缓冲分为4种，分别为：
normal缓冲区，对应的ini参数是buffer,
keep缓冲区，常驻的数据都放在keep数据页中，对应的ini参数就是keep,
recycle缓冲区，高并发系统、使用with as语句较多，临时数据较多需要将这个recycle数据页所占用的内存值调大，对应的ini参数是recycle。
记住所有的页大小在初始化实例时已经设定好了，现在使用ini参数修改的是缓冲区内存大小。
fast缓冲区，fast包含数据页和回滚页, 常驻缓冲区，这两者都是由系统自动管理，无需干涉对应的ini参数是fast_pool_pages和fast_roll_pages，代表fast缓冲区的大小为多少数据页。
|
1 2 3 4 5 6 7 8 9 |
SQL> select name,PAGE_SIZE,sum(N_TOTAL_PAGES),sum(FREE) from v$bufferpool group by name,page_size; LINEID NAME PAGE_SIZE SUM(N_TOTAL_PAGES) SUM(FREE) ---------- ------- ----------- -------------------- -------------------- 1 KEEP 8192 1024 1024 2 RECYCLE 8192 128000 127998 3 FAST 8192 3000 0 4 NORMAL 8192 508992 508123 5 ROLL 8192 128 128 |
从上述内存结构的作用来看，keep池类似Oracle buffer cache的keep池；正常情况下的cache 使用是normal类型的缓存。 从DM8的查询结果来这里还多了一类ROLL类型。不知道这里其中的FAST和ROLL 两类缓存的作用是什么。猜测应该是永久性内存，针对数据页和回滚页。
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> select name,type,value,SYS_VALUE,FILE_VALUE from v$parameter where name like '%BUFFER%'; LINEID NAME TYPE VALUE SYS_VALUE FILE_VALUE ---------- ------------------- ------- ----- --------- ---------- 1 HUGE_BUFFER IN FILE 80 80 80 2 HUGE_BUFFER_POOLS IN FILE 4 4 4 3 BUFFER IN FILE 4000 4000 4000 4 BUFFER_POOLS IN FILE 11 11 11 5 BUFFER_MODE IN FILE 0 0 0 6 BUFFER_FAST_RELEASE SYS 1 1 1 7 MAX_BUFFER IN FILE 4000 4000 100 |
修改参数通过sp_set_para_value进行操作；2表示静态参数，1表示动态。如果参数属性不对，那么修改是不会成功的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> sp_set_para_value(1,'BUFFER',4100); sp_set_para_value(1,'BUFFER',4100); [-839]:Try to alter static ini parameter. used time: 5.332(ms). Execute id is 0. SQL> sp_set_para_value(2,'BUFFER',4100); DMSQL executed successfully used time: 3.119(ms). Execute id is 290. SQL> root@mogdb ~]# cat /opt/dm/dmdbms/data/enmotech/dm.ini| grep BUFFER HUGE_BUFFER = 80 #Initial Huge Buffer Size In Megabytes HUGE_BUFFER_POOLS = 4 #number of Huge buffer pools BUFFER = 4100 #Initial System Buffer Size In Megabytes BUFFER_POOLS = 11 #number of buffer pools |
可以看到，我们的参数修改生效了，并写入了参数文件。
3、SQL缓冲区
在DM数据库中，SQL缓冲区类似Oracle中的shared pool，不过功能不太一样。通过cache_pool_Size参数来进行大小定义。
可以缓存执行过的SQL，SQL执行计划等，应该可以极大避免硬解析，同时还能缓存SQL执行的结果集；相当于又具备了Oracle Result cache的功能。
|
1 2 3 4 5 |
SQL> select * from v$dm_ini where para_NAME = 'CACHE_POOL_SIZE'; LINEID PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE ---------- --------------- ---------- --------- --------- ------- ---------- ---------- ---------------------------- --------- 1 CACHE_POOL_SIZE 200 1 67108864 N 200 200 SQL buffer size in megabytes IN FILE |
按照DM官方的文档管理手册描述，如果要使用结果集缓存，还需要同时设置 RS_CAN_CACHE=1和USE_PLN_POOL 参数。另外还可以通过CLT_CACHE_TABLES参数来控制，指定具体哪些表的查询结果可以被缓存。
|
1 2 3 4 5 6 |
SQL> select TYPE$,sum(ITEM_SIZE) from v$cacheitem group by type$ order by 2; LINEID TYPE$ SUM(ITEM_SIZE) ---------- ----- -------------------- 1 SQL 348841 2 PLN 5138952 |
另外还有相关试图可以查询sql和结果集的缓存情况，如：v$cachepln、v$cachers、v$cachesql。
4、字典缓存
|
1 2 3 4 5 |
SQL> select PARA_NAME,PARA_VALUE,DESCRIPTION from v$dm_ini where PARA_NAME='DICT_BUF_SIZE'; LINEID PARA_NAME PARA_VALUE DESCRIPTION ---------- ------------- ---------- ---------------- 1 DICT_BUF_SIZE 50 dict buffer size |
DM数据库中通过dict_buf_size参数来进行控制。数据库对像比如表，索引，视图，序列，同义词，触发器，存储过程的信息都缓存在数据字典缓冲区中。可以通过v$dict_cache 内存视图来查看该内存结构的使用情况：
|
1 2 3 4 5 |
SQL> select * from v$dict_cache; LINEID ADDR POOL_ID TOTAL_SIZE USED_SIZE DICT_NUM ---------- ---------------- ----------- ----------- ----------- ----------- 1 0x0x7f75f0cdcdc0 0 52428800 176962 81 |
我这里默认值是50MB，如果数据库对象比较多，这里应该需要调大一些。
5、日志缓冲区（即log buffer）
在DM数据库中，也有类似Oracle一样的Log buffer内存结构；不过在DM数据库中看上去似乎更复杂一点点。通过查看参数
发现了有6个如下相关的参数：
|
1 2 3 4 5 6 |
RLOG_BUF_SIZE 1024 The Number Of Log Pages In One Log Buffer RLOG_POOL_SIZE 256 Redo Log Pool Size In Megabyte LOG_BUF_SIZE 1024 The Number Of Log Pages In One Log Buffer LOG_POOL_SIZE 256 Redo Log Pool Size In Megabyte REDO_BUF_SIZE 64 The max buffer size of rlog redo In Megabyte REDOS_BUF_SIZE 1024 The max buffer size of rlog redo for standby In Megabyte |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> select FILE_LSN,FLUSH_LSN,CUR_LSN,NEXT_SEQ,FLUSH_PAGES,FLUSHING_PAGES,TOTAL_SPACE,FREE_SPACE from v$rlog; LINEID FILE_LSN FLUSH_LSN CUR_LSN NEXT_SEQ FLUSH_PAGES FLUSHING_PAGES TOTAL_SPACE FREE_SPACE ---------- -------------------- -------------------- -------------------- -------------------- ----------- -------------- -------------------- -------------------- 1 45107844 45107844 45107844 713825 0 0 9437171712 9437171712 used time: 1.064(ms). Execute id is 272. SQL> select file_id,PATH,RLOG_SIZE,CREATE_TIME from v$rlogfile; LINEID FILE_ID PATH RLOG_SIZE CREATE_TIME ---------- ----------- ------------------------------------------- ------------------------------------------------------------ 1 0 /opt/dm/dmdbms/data/enmotech/enmotech01.log 3145728000 2021-05-26 02:06:28.000000 2 1 /opt/dm/dmdbms/data/enmotech/enmotech02.log 3145728000 2021-05-26 02:06:28.000000 3 2 /opt/dm/dmdbms/data/enmotech/enmotech03.log 3145728000 2021-05-26 02:21:22.000000 used time: 0.970(ms). Execute id is 273. |
从上面的信息来看，通过rlog_pool_size 来定义日志缓冲区的内存大小. 另外DM也有类似Oracle的排序内存区，hash等。这里不做多余介绍。
从DM的内存结构来看，比Oralce确实要简单的太多。不知道达梦是否提供类似Oracle一样的各种dump内存结构的手段，方便进行深入研究。
DM8中共计643个数据库参数，其中跟内存相关的参数有几十个，这里我贴进来，供参考！
最后期待国产数据库越来越好！
附录：DM8中内存相关参数的解释
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
#memory pool and buffer MAX_OS_MEMORY = 100 #Maximum Percent Of OS Memory MEMORY_POOL = 500 #Memory Pool Size In Megabyte MEMORY_TARGET = 0 #Memory Share Pool Target Size In Megabyte MEMORY_EXTENT_SIZE = 1 #Memory Extent Size In Megabyte MEMORY_LEAK_CHECK = 0 #Memory Pool Leak Checking Flag MEMORY_MAGIC_CHECK = 2 #Memory Pool Magic Checking Flag MEMORY_BAK_POOL = 4 #Memory Backup Pool Size In Megabyte HUGE_MEMORY_PERCENTAGE = 50 #Maximum percent of HUGE buffer that can be allocated to work as common memory pool HUGE_BUFFER = 80 #Initial Huge Buffer Size In Megabytes HUGE_BUFFER_POOLS = 4 #number of Huge buffer pools BUFFER = 4000 #Initial System Buffer Size In Megabytes BUFFER_POOLS = 11 #number of buffer pools FAST_POOL_PAGES = 3000 #number of pages for fast pool FAST_ROLL_PAGES = 1000 #number of pages for fast roll pages KEEP = 8 #system KEEP buffer size in Megabytes RECYCLE = 1000 #system RECYCLE buffer size in Megabytes RECYCLE_POOLS = 5 #Number of recycle buffer pools ROLLSEG = 1 #system ROLLSEG buffer size in Megabytes ROLLSEG_POOLS = 19 #Number of rollseg buffer pools MULTI_PAGE_GET_NUM = 1 #Maximum number of pages for each read of buffer PRELOAD_SCAN_NUM = 4 #The number of pages scanned continuously to start preload task PRELOAD_EXTENT_NUM = 5 #The number of clusters preloaded for the first time SORT_BUF_SIZE = 10 #maximum sort buffer size in Megabytes SORT_BLK_SIZE = 1 #maximum sort blk size in Megabytes SORT_BUF_GLOBAL_SIZE = 500 #maximum global sort buffer size in Megabytes SORT_FLAG = 0 #choose method of sort HAGR_HASH_SIZE = 100000 #hash table size for hagr HJ_BUF_GLOBAL_SIZE = 500 #maximum hash buffer size for all hash join in Megabytes HJ_BUF_SIZE = 50 #maximum hash buffer size for single hash join in Megabytes HJ_BLK_SIZE = 1 #hash buffer size allocated each time for hash join in Megabytes HAGR_BUF_GLOBAL_SIZE = 500 #maximum buffer size for all hagr in Megabytes HAGR_BUF_SIZE = 50 #maximum buffer size for single hagr in Megabytes HAGR_BLK_SIZE = 1 #buffer size allocated each time for hagr in Megabytes MTAB_MEM_SIZE = 8 #memory table size in Kilobytes FTAB_MEM_SIZE = 0 #file table package size in Kilobytes MMT_GLOBAL_SIZE = 4000 #memory map table global size in megabytes MMT_SIZE = 0 #memory map table size in megabytes MMT_FLAG = 1 #ways of storing bdta data in memory map table DICT_BUF_SIZE = 50 #dictionary buffer size in Megabytes HFS_CACHE_SIZE = 160 #hfs cache size in Megabytes, used in huge horizon table for insert, update,delete VM_STACK_SIZE = 256 #VM stack size in Kilobytes VM_POOL_SIZE = 64 #VM pool size in Kilobytes VM_POOL_TARGET = 32768 #VM pool target size in Kilobytes SESS_POOL_SIZE = 16 #session pool size in Kilobytes SESS_POOL_TARGET = 32768 #session pool target size in Kilobytes RT_HEAP_TARGET = 8192 #runtime heap target size in Kilobytes VM_MEM_HEAP = 0 #Whether to allocate memory to VM from HEAP RFIL_RECV_BUF_SIZE = 16 #redo file recover buffer size in Megabytes N_MEM_POOLS = 1 #number of memory pools COLDATA_POOL_SIZE = 0 #coldata pool size for each worker group HAGR_DISTINCT_HASH_TABLE_SIZE = 10000 #Size of hagr distinct hash table CNNTB_HASH_TABLE_SIZE = 100 #Size of hash table in connect-by operation GLOBAL_RTREE_BUF_SIZE = 100 #The total size of buffer for rtree SINGLE_RTREE_BUF_SIZE = 10 #The size of buffer for single rtree SORT_OPT_SIZE = 0 #once max memory size of radix sort assist count array DFS_BUF_FLUSH_OPT = 0 #Whether to flush buffer page in opt mode for DFS storage |
Leave a Reply
You must be logged in to post a comment.