达梦数据库学习笔记转载：达梦数据库自动根据机器配置调整性能相关参数（转载）

brave_zhao

已于 2023-03-23 16:36:15 修改

阅读量406

点赞数

文章标签：数据库 sql mysql

于 2023-03-23 16:13:52 首次发布

原文链接：https://eco.dameng.com/community/article/f5bc4b0827b7ba6f8b05f0e6aea24d47

版权

原文链接：达梦数据库自动根据机器配置调整性能相关参数 | 达梦技术社区

达梦数据库自动根据机器配置调整性能相关参数

安装完达梦数据库后，可以执行以下脚本自动调整性能相关的参数：

declare
v_mem_mb int;
v_cpus int;
mem_per int:=100;
MEMORY_POOL int;
BUFFER INT;
MAX_BUFFER INT;
RECYCLE int;
CACHE_POOL_SIZE int;
BUFFER_POOLS int;
RECYCLE_POOLS int;
SORT_BUF_SIZE int;
SORT_BUF_GLOBAL_SIZE INT;
DICT_BUF_SIZE INT;
SESS_POOL_SIZE INT;
HJ_BUF_SIZE INT;
HAGR_BUF_SIZE INT;
HJ_BUF_GLOBAL_SIZE INT;
HAGR_BUF_GLOBAL_SIZE INT;
TASK_THREADS INT;
SORT_FLAG INT;
SORT_BLK_SIZE INT;
RLOG_POOL_SIZE INT;

begin
   SELECT TOP 1 N_CPU,TOTAL_PHY_SIZE/1024/1024 INTO v_cpus,v_mem_mb FROM V$SYSTEMINFO;

   print v_cpus;
   print v_mem_mb;

   v_mem_mb=round(v_mem_mb,-3);
   print v_mem_mb;

   IF v_mem_mb <= 2000 THEN
   return;
   END IF;


   IF v_mem_mb > 512000 THEN
   v_mem_mb :=v_mem_mb*0.8;
   END IF;

   TASK_THREADS :=8;
   IF v_cpus < 8 THEN
   TASK_THREADS :=4;
   END IF;

   IF v_cpus >= 64 THEN
   v_cpus := 64;
   TASK_THREADS :=16;
   END IF;

   SP_SET_PARA_VALUE(2,'WORKER_THREADS',v_cpus);
   SP_SET_PARA_VALUE(2,'TASK_THREADS',TASK_THREADS);

   BUFFER := round(cast(v_mem_mb * 0.5 as int),-3);
   MAX_BUFFER := BUFFER;
   RECYCLE :=LEAST(cast(v_mem_mb * 0.125 as int),50000);


IF v_mem_mb < 70000 THEN

with t as
(
select rownum rn from dual connect by level <= 100
) ,
t1 as
(
select * from t where rn > 1 minus
select
ta.rn * tb.rn
from
t ta,
t tb
where
ta.rn <= tb.rn
and ta.rn > 1
and tb.rn > 1
)
select top 1 rn into BUFFER_POOLS from t1 where rn > v_mem_mb/800 order by 1;

PRINT 'BUFFER_POOLS ' || BUFFER_POOLS;


   --设置根据内存情况RECYCLE_POOLS参数
       with t as
(
select rownum rn from dual connect by level <= 100
) ,
t1 as
(
select * from t where rn > 1 minus
select
ta.rn * tb.rn
from
t ta,
t tb
where
ta.rn <= tb.rn
and ta.rn > 1
and tb.rn > 1
)
select top 1 rn into RECYCLE_POOLS from t1 where rn > v_mem_mb/800/3 order by 1;

PRINT 'RECYCLE_POOLS ' || RECYCLE_POOLS;


ELSE
BUFFER_POOLS := 101;
RECYCLE_POOLS := 41;
END IF;

   --修改内存池
   IF v_mem_mb >= 16000 THEN
   MEMORY_POOL := 2048;
   CACHE_POOL_SIZE := 1024;

   SORT_FLAG = 0;
   SORT_BLK_SIZE=1;
   SORT_BUF_SIZE := 10;
   SORT_BUF_GLOBAL_SIZE := 2000;
   SESS_POOL_SIZE := 16;
   RLOG_POOL_SIZE := 1024;

   HJ_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),10000);
   HAGR_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),10000);
   HJ_BUF_SIZE :=500;
HAGR_BUF_SIZE :=500;

   IF v_mem_mb >= 64000 THEN
   CACHE_POOL_SIZE := 2048;
       RLOG_POOL_SIZE := 2048;
   SORT_FLAG = 1;
   SORT_BLK_SIZE=1;
   SORT_BUF_SIZE=512;
   SORT_BUF_GLOBAL_SIZE=5120 ;
   SESS_POOL_SIZE := 32;

   HJ_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),15000);
   HAGR_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),15000);
   HJ_BUF_SIZE :=1000;
HAGR_BUF_SIZE :=1000;
   END IF;

   DICT_BUF_SIZE := 500;
HJ_BUF_GLOBAL_SIZE :=round(HJ_BUF_GLOBAL_SIZE,-3);
HAGR_BUF_GLOBAL_SIZE :=round(HAGR_BUF_GLOBAL_SIZE,-3);
RECYCLE :=round(RECYCLE,-3);
   ELSE
   MEMORY_POOL :=GREAT(cast(v_mem_mb * 0.0625 as int),100);
   CACHE_POOL_SIZE := 200;
   RLOG_POOL_SIZE := 256;
   SORT_BUF_SIZE := 10;
   SORT_BUF_GLOBAL_SIZE := 500;
   DICT_BUF_SIZE := 50;
   SESS_POOL_SIZE =16;
   SORT_FLAG = 0;
   SORT_BLK_SIZE=1;

   HJ_BUF_GLOBAL_SIZE := GREAT(cast(v_mem_mb * 0.0625 as int),500);
   HAGR_BUF_GLOBAL_SIZE := GREAT(cast(v_mem_mb * 0.0625 as int),500);
HJ_BUF_SIZE := GREAT(cast(v_mem_mb * 0.00625 as int),50);
HAGR_BUF_SIZE :=GREAT(cast(v_mem_mb * 0.00625 as int),50);
   END IF;

   v_mem_mb := v_mem_mb * (mem_per/100.0);
   --修改内存
   SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY', mem_per);
   SP_SET_PARA_VALUE(2,'MEMORY_POOL', MEMORY_POOL);
   SP_SET_PARA_VALUE(2,'BUFFER', BUFFER);
   SP_SET_PARA_VALUE(2,'MAX_BUFFER', MAX_BUFFER);
   SP_SET_PARA_VALUE(2,'BUFFER_POOLS', BUFFER_POOLS);
   SP_SET_PARA_VALUE(2,'RECYCLE',     RECYCLE);
   SP_SET_PARA_VALUE(2,'RECYCLE_POOLS', RECYCLE_POOLS);

   SP_SET_PARA_VALUE(2,'HJ_BUF_GLOBAL_SIZE', HJ_BUF_GLOBAL_SIZE);
   SP_SET_PARA_VALUE(2,'HJ_BUF_SIZE', HJ_BUF_SIZE );
   SP_SET_PARA_VALUE(2,'HAGR_BUF_GLOBAL_SIZE',HAGR_BUF_GLOBAL_SIZE);
   SP_SET_PARA_VALUE(2,'HAGR_BUF_SIZE', HAGR_BUF_SIZE );

   SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE', RLOG_POOL_SIZE);
   SP_SET_PARA_VALUE(2,'SESS_POOL_SIZE', SESS_POOL_SIZE);
   SP_SET_PARA_VALUE(2,'CACHE_POOL_SIZE', CACHE_POOL_SIZE);
   SP_SET_PARA_VALUE(2,'DICT_BUF_SIZE', DICT_BUF_SIZE);

   SP_SET_PARA_VALUE(2,'SORT_FLAG',SORT_FLAG);
   SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',SORT_BLK_SIZE);
   SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE', SORT_BUF_SIZE);
   SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE', SORT_BUF_GLOBAL_SIZE);

   SP_SET_PARA_VALUE(2,'USE_PLN_POOL', 1);
   SP_SET_PARA_VALUE(2,'OLAP_FLAG', 2);
   SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG', 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', 20480);
   SP_SET_PARA_VALUE(2,'MAX_SESSIONS', 1500);
   SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT', 20000);
   SP_SET_PARA_VALUE(1,'PK_WITH_CLUSTER',       0);
   SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);
   SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
end;
/