当达梦数据库安装部署用于生产环境时
1.需要对相关参数进行优化
2.需要开启部分作业策略
3.需要执行优化脚本
操作系统参数优化
防火墙临时/永久关闭
防火墙的关闭是为了外部能够访问数据库端口,以及集群间通信,更好的处理方式应该是对防火墙、安全组进行配置,开放相对应的端口
systemctl stop firewalld
systemctl disable firewalld
关闭numa、透明大页、SELinux
vi /etc/default/grub
在GRUB_CMDLINE_LINUX这一行添加 numa=off
find / -name grub.cfg
# 将上述文件路径在下面命令进行替换
grub2-mkconfig -o /boot/efi/EFI/kylin/grub.cfg
# 验证
dmesg | grep -i numa
# 自动关闭透明大页
vi /etc/rc.local
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
chmod u+x /etc/rc.local
# 验证
cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag
vi /etc/selinux/config
找到SELINUX,取消注释,修改为 SELINUX=disabled
# 验证
getenforce
资源配置
检查内存和磁盘资源
free -g
df -h
<!-- 如果临时目录剩余可用小于1g,需要改变临时目录 -->
mkdir -p xxx
DM_INSTALL_TMPDIR=xxx
export DM_INSTALL_TMPDIR
cat /etc/fstab
lsblk
# 检查磁盘挂载,依实际情况确认是否需要关闭swap分区
# 磁盘io测试
dd if=/dev/zero of=test bs=64k count=4k oflag=dsync
操作系统配置
vi /etc/security/limits.conf
dmdba soft core unlimited
dmdba hard core unlimited
# data seg size
dmdba soft data unlimited
dmdba hard data unlimited
# file size
dmdba soft fsize unlimited
dmdba hard fsize unlimited
# open files
dmdba soft nofile 65536
dmdba hard nofile 65536
# virtual memory
dmdba soft as unlimited
dmdba hard as unlimited
$ vi /etc/systemd/system.conf
DefaultLimitNPROC=10240
DefaultLimitNICE=0
$ cat /etc/systemd/system.conf|grep -v '#'
$ vim /etc/sysctl.conf
# 添加内容,内存小就设置为1,内存够就设置为10
vm.swappiness = 10
vm.overcommit_memory = 0
# core 目录,可自定义
kernel.core_pattern =/dm8/core/core-%e-%p-%s
# 使配置生效
$ sysctl -p
针对麒麟操作系统
# 修改麒麟RemoveIPC参数
vim /etc/systemd/logind.conf -- 针对RemoveIPC,去掉注释,修改yes为no
systemctl daemon-reload;
systemctl restart systemd-logind;
# 修改完成后,检查确认
systemctl show systemd-logind | grep -i removeipc
作业调度
初始化作业环境
SP_INIT_JOB_SYS(1);
归档配置
进入sql终端/管理工具执行,配置数据库归档,数据库备份+归档可以使数据定时恢复到任意时间点
ALTER DATABASE MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ADD ARCHIVELOG 'DEST=/iflytek/web/dm8/arch, TYPE=LOCAL, FILE_SIZE=1024, SPACE_LIMIT=307200';
ALTER DATABASE OPEN;
统计信息收集
sql执行计划的生成依赖于统计信息,错误的统计信息会导致优化器选择不合适的执行计划从而导致sql性能下降,建议在有大规模数据迁移、导入时手动执行,其余时间可在业务低峰定时执行
call SP_CREATE_JOB('statistics',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('statistics');
call SP_JOB_SET_EP_SEQNO('statistics', 0);
call SP_ADD_JOB_STEP('statistics', 'statistics1', 0, 'begin
for rs in (select ''sf_set_session_para_value(''''HAGR_HASH_SIZE'''',(select cast(
case when max(table_rowcount(owner,table_name))<=(select max_value from v$dm_ini
where para_name=''''HAGR_HASH_SIZE'''') and max(table_rowcount(owner,table_name))>=(
select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') then
max(table_rowcount(owner,table_name)) when max(table_rowcount(owner,table_name))<(
select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') then
(select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') else
(select max_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') end as bigint)
from dba_tables where owner=''''''||NAME||''''''));'' sql1,
''DBMS_STATS.GATHER_SCHEMA_STATS(''''''||NAME||'''''',10,TRUE,''''FOR ALL COLUMNS SIZE AUTO'''');'' sql2
from SYS.SYSOBJECTS where TYPE$=''SCH'' ) loop
execute immediate rs.sql1;
execute immediate rs.sql2;
end loop;
end;', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('statistics', 'stat_cycle', 1, 2, 1, 64, 0, '00:00:00', NULL, '2024-04-20 00:00:00', NULL, '');
call SP_ADD_JOB_SCHEDULE('statistics', 'stat_once', 1, 0, 0, 0, 0, NULL, NULL, sysdate+1/1440, NULL, '');
call SP_JOB_CONFIG_COMMIT('statistics');
备份
建议备份单独挂盘,与数据库数据盘区分开,防止备份影响数据库正常使用
- 全量备份
call SP_CREATE_JOB('bakfull',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('bakfull');
call SP_ADD_JOB_STEP('bakfull', 'bakfull', 6, '00000000/dm8/bak/', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('bakfull', 'bakfull_cycle', 1, 2, 1, 64, 0, '23:00:00', NULL, '2024-04-20 00:00:00', NULL, '');
call SP_ADD_JOB_SCHEDULE('bakfull', 'bakfull_once', 1, 0, 0, 0, 0, NULL, NULL, sysdate+1/1440, NULL, '');
call SP_JOB_CONFIG_COMMIT('bakfull');
- 增量备份
当数据库数据太大时,每次全备都耗费太多资源,可以采取一周一次全备六次增备的策略
call SP_CREATE_JOB('bakincr',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('bakincr');
call SP_ADD_JOB_STEP('bakincr', 'bakincr', 6, '40000000/dm8/bak/|/dm8/bak/', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('bakincr', 'bakincr_cycle', 1, 2, 1, 63, 0, '23:00:00', NULL, '2024-04-20 00:00:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('bakincr');
- 删除策略
设置完备份策略后也要补充删除备份策略的操作,不然长时间会造成磁盘爆满
call SP_CREATE_JOB('delbak',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('delbak');
call SP_ADD_JOB_STEP('delbak', 'delbak', 0, 'sf_bakset_backup_dir_add("DISK","/dm8/bak/");call sp_db_bakset_remove_batch("DISK",now()-90);', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('delbak', 'delbak', 1, 1, 1, 0, 0, '23:30:00', NULL, '2024-04-20 00:00:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('delbak');
执行优化脚本
可以将实际cpu、内存传入数据库优化脚本中,执行优化脚本,使数据库在运行时合理利用cpu、内存资源
ps:建议生产环境数据库独立部署,与业务系统区分开,如果和其他应用夹杂在一起,则传入优化脚本中的cpu、内存数也要适当做调整(预估可供数据库使用的资源量)