创建数据库后需要根据主机资源配置情况修改初始化参数,避免数据库在运行中出现性能瓶颈。
在达梦的manager中执行AutoParaAdj_Print2.4_zzj.sql,得到优化参数建议。
/*
**************************************************************************
* *
* Auto parameter adjustment 2.2 *
* [December 30, 2020 ]
* Written by Mr. Huanghaiming *
* Take effect after restart dmserver
* *
**************************************************************************
*/
declare
mem_per int:= 80; --默认所有的内存归达梦数据库使用,如实际不能100%可用,可以调整此参数
v_mem_mb int:= 256000; --根据数据库服务器实际内存修改此参数的默认值
v_cpus int:= 144; --根据数据库服务器实际CPU核数修改此参数的默认值
MEMORY_POOL int;
MEMORY_N_POOLS int;
MEMORY_TARGET 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;
SORT_FLAG INT;
SORT_BLK_SIZE INT;
RLOG_POOL_SIZE INT;
TASK_THREADS INT;
IO_THR_GROUPS INT;
FAST_POOL_PAGES INT;
FAST_ROLL_PAGES INT;
begin
v_mem_mb := v_mem_mb * (mem_per/100.0);
v_mem_mb=round(v_mem_mb,-3);
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;
MEMORY_TARGET=round(cast(v_mem_mb * 0.12 as int),-3);
TASK_THREADS :=4;
IO_THR_GROUPS :=4;
IF v_cpus < 8 THEN
TASK_THREADS :=4;
IO_THR_GROUPS :=2;
END IF;
IF v_cpus >= 64 THEN
v_cpus := 64;
TASK_THREADS :=16;
IO_THR_GROUPS :=8;
END IF;
BUFFER := round(cast(v_mem_mb * 0.4 as int),-3);
MAX_BUFFER := BUFFER;
RECYCLE :=cast(v_mem_mb * 0.04 as int);
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;
--设置根据内存情况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;
ELSE
BUFFER_POOLS := 101;
RECYCLE_POOLS := 41;
END IF;
--修改内存池
IF v_mem_mb >= 16000 THEN
IF v_mem_mb= 16000 THEN
MEMORY_POOL := 1500;
SORT_BUF_GLOBAL_SIZE := 1000;
ELSE
MEMORY_POOL := 2000;
SORT_BUF_GLOBAL_SIZE := 2000;
END IF;
FAST_POOL_PAGES:=99999; --32K≈3G
FAST_ROLL_PAGES:=9999;
CACHE_POOL_SIZE := 1024;
SORT_FLAG = 0;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE := 10;
SORT_BUF_GLOBAL_SIZE := 2000;
SESS_POOL_SIZE := 2048;
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 :=250;
HAGR_BUF_SIZE :=250;
RECYCLE :=round(RECYCLE,-3);
IF v_mem_mb >= 32000 THEN --32K≈3G
FAST_POOL_PAGES:=99999;
FAST_ROLL_PAGES:=9999;
END IF;
IF v_mem_mb >= 64000 THEN
CACHE_POOL_SIZE := 2048;
RLOG_POOL_SIZE := 2048;
SORT_FLAG = 1;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE=50;
SORT_BUF_GLOBAL_SIZE= cast(v_mem_mb * 0.02 as int);
SESS_POOL_SIZE := 2048;
HJ_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.15625 as int);
HAGR_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.04 as int);
HJ_BUF_SIZE :=512;
HAGR_BUF_SIZE :=512;
FAST_POOL_PAGES:=499999; --32K≈15G
FAST_ROLL_PAGES:=49999;
END IF;
IF v_mem_mb >= 128000 THEN --32K≈30G
FAST_POOL_PAGES:=999999;
FAST_ROLL_PAGES:=99999;
END IF;
IF v_mem_mb >= 256000 THEN
FAST_POOL_PAGES:=99999999;
FAST_ROLL_PAGES:=9999999;
END IF;
DICT_BUF_SIZE := 50;
HJ_BUF_GLOBAL_SIZE :=round(HJ_BUF_GLOBAL_SIZE,-3);
HAGR_BUF_GLOBAL_SIZE :=round(HAGR_BUF_GLOBAL_SIZE,-3);
SORT_BUF_GLOBAL_SIZE :=round(SORT_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 =2048;
SORT_FLAG = 0;
SORT_BLK_SIZE=1;
FAST_POOL_PAGES:=29999;
FAST_ROLL_PAGES:=2999;
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;
MEMORY_N_POOLS :=MEMORY_POOL/200;
PRINT '--机器内存:' || v_mem_mb ||'MB CPU核数:' || v_cpus ;
PRINT '--请执行以下脚本调整数据库参数,执行完成后请重启数据库服务生效' ;
--修改cpu相关参数
PRINT 'SP_SET_PARA_VALUE(2,''WORKER_THREADS'','||v_cpus||');';
PRINT 'SP_SET_PARA_VALUE(2,''TASK_THREADS'','||TASK_THREADS||');';
PRINT 'SP_SET_PARA_VALUE(2,''IO_THR_GROUPS'','||IO_THR_GROUPS||');';
--修改内存池相关参数
PRINT 'SP_SET_PARA_VALUE(2,''MAX_OS_MEMORY'', '||mem_per||');';
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_POOL'', '||MEMORY_POOL||');';
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_TARGET'', '||MEMORY_TARGET||');';
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_FREQROOTS'', 1);------在dsc集群中要改成0';
PRINT 'SP_SET_PARA_VALUE(2,''FAST_POOL_PAGES'', '||FAST_POOL_PAGES||');';
PRINT 'SP_SET_PARA_VALUE(2,''FAST_ROLL_PAGES'', '||FAST_ROLL_PAGES||');';
if exists(select 1 from v$dm_ini where para_name='MEMORY_N_POOLS') then
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_N_POOLS'', '||MEMORY_N_POOLS||');';
end if;
--修改缓冲区相关参数
PRINT 'SP_SET_PARA_VALUE(2,''BUFFER'', '||BUFFER||');';
--PRINT 'SP_SET_PARA_VALUE(2,''MAX_BUFFER'', '||MAX_BUFFER||');'; --新版没有此参数
PRINT 'SP_SET_PARA_VALUE(2,''BUFFER_POOLS'', '||BUFFER_POOLS||');';
PRINT 'SP_SET_PARA_VALUE(2,''RECYCLE'', '||RECYCLE||');';
PRINT 'SP_SET_PARA_VALUE(2,''RECYCLE_POOLS'', '||RECYCLE_POOLS||');';
--修改HASH相关参数
PRINT 'SP_SET_PARA_VALUE(1,''HJ_BUF_GLOBAL_SIZE'', '||HJ_BUF_GLOBAL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(1,''HJ_BUF_SIZE'', '||HJ_BUF_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(1,''HAGR_BUF_GLOBAL_SIZE'','||HAGR_BUF_GLOBAL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(1,''HAGR_BUF_SIZE'', '||HAGR_BUF_SIZE||');';
--修改排序相关参数
PRINT 'SP_SET_PARA_VALUE(2,''SORT_FLAG'','||SORT_FLAG||');';
PRINT 'SP_SET_PARA_VALUE(2,''SORT_BLK_SIZE'','||SORT_BLK_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''SORT_BUF_SIZE'', '||SORT_BUF_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''SORT_BUF_GLOBAL_SIZE'', '||SORT_BUF_GLOBAL_SIZE||');';
--修改其他内存参数
PRINT 'SP_SET_PARA_VALUE(2,''RLOG_POOL_SIZE'', '||RLOG_POOL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''SESS_POOL_SIZE'', 2048);';
PRINT 'SP_SET_PARA_VALUE(2,''VM_POOL_SIZE'', 2048);';
PRINT 'SP_SET_PARA_VALUE(2,''CACHE_POOL_SIZE'', '||CACHE_POOL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''DICT_BUF_SIZE'', '||DICT_BUF_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''VM_POOL_TARGET'', 16384);';
PRINT 'SP_SET_PARA_VALUE(2,''SESS_POOL_TARGET'', 16384);';
PRINT 'SP_SET_PARA_VALUE(2,''FAST_POOL_PAGES'', '||FAST_POOL_PAGES||');';
PRINT 'SP_SET_PARA_VALUE(2,''FAST_ROLL_PAGES'', '||FAST_ROLL_PAGES||');';
--修改实例相关参数
PRINT 'SP_SET_PARA_VALUE(2,''USE_PLN_POOL'', 1);';
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_MAGIC_CHECK'', 1);';
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_MONITOR'', 1);';
PRINT 'SP_SET_PARA_VALUE(2,''SVR_LOG'', 1);';
PRINT 'SP_SET_PARA_VALUE(2,''TEMP_SIZE'', 1024);';
PRINT 'SP_SET_PARA_VALUE(2,''TEMP_SPACE_LIMIT'', 102400);';
PRINT 'SP_SET_PARA_VALUE(2,''MAX_SESSIONS'', 1500);';
PRINT 'SP_SET_PARA_VALUE(2,''MAX_SESSION_STATEMENT'', 20000);';
PRINT 'SP_SET_PARA_VALUE(2,''PK_WITH_CLUSTER'', 0);';
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_ENCRYPT'',0);';
PRINT 'SP_SET_PARA_VALUE(2,''PARALLEL_PURGE_FLAG'',1);';
--修改优化器相关参数
PRINT 'SP_SET_PARA_VALUE(2,''OLAP_FLAG'',2);';
PRINT 'SP_SET_PARA_VALUE(2,''VIEW_PULLUP_FLAG'',3);';
PRINT 'SP_SET_PARA_VALUE(2,''OPTIMIZER_MODE'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''ADAPTIVE_NPLN_FLAG'',0);';
PRINT 'SP_SET_PARA_VALUE(2,''TOP_ORDER_OPT_FLAG'',1);';
exception
when others then
raise_application_error (-20001,substr( ' 执行失败, '||SQLCODE||' '||SQLERRM||' '||dbms_utility.format_error_backtrace , 1, 400));
end;
将执行结果应用到数据库,以下仅为示例:
--机器内存:410000MB CPU核数:64
--请执行以下脚本调整数据库参数,执行完成后请重启数据库服务生效
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', 80);
SP_SET_PARA_VALUE(2,'MEMORY_POOL', 2000);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET', 49000);
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS', 1);------在dsc集群中要改成0
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES', 99999999);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES', 9999999);
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS', 10);
SP_SET_PARA_VALUE(2,'BUFFER', 164000);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS', 101);
SP_SET_PARA_VALUE(2,'RECYCLE', 16000);
SP_SET_PARA_VALUE(2,'RECYCLE_POOLS', 41);
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE', 64000);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE', 512);
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',16000);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE', 512);
SP_SET_PARA_VALUE(2,'SORT_FLAG',1);
SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',1);
SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE', 50);
SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE', 8000);
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE', 2048);
SP_SET_PARA_VALUE(2,'SESS_POOL_SIZE', 2048);
SP_SET_PARA_VALUE(2,'VM_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,'FAST_POOL_PAGES', 99999999);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES', 9999999);
SP_SET_PARA_VALUE(2,'USE_PLN_POOL', 1);
SP_SET_PARA_VALUE(2,'MEMORY_MAGIC_CHECK', 1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR', 1);
SP_SET_PARA_VALUE(2,'SVR_LOG', 1);
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,'PARALLEL_PURGE_FLAG',1);
SP_SET_PARA_VALUE(2,'OLAP_FLAG',2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG',3);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
SP_SET_PARA_VALUE(2,'ADAPTIVE_NPLN_FLAG',0);
SP_SET_PARA_VALUE(2,'TOP_ORDER_OPT_FLAG',1);