DM数据库优化

最新推荐文章于 2023-03-06 10:20:35 发布

、理想三旬

最新推荐文章于 2023-03-06 10:20:35 发布

阅读量587

点赞数 1

本文链接：https://blog.csdn.net/weixin_50168895/article/details/115204396

版权

1.参数优化

vim dm.ini
内存参数：

MAX_OS_MEMORY         95     100          
DM 数据库占用的内存占操作系统物理内存和虚拟内存之和百分比，需要设置为 100

BUFFER                100      ***     
用于缓存数据页，一般配置为操作系统物理内存的 60%~80%

MAX_BUFFER            100      ****     
用于控制系统缓冲区的上限，一般配置为和 BUFFER 参数相等

BUFFER_POOLS          1      61   
BUFFER 的分区数，一般配置为质数，取值范围为 1~512，并较大的系统需要配置这个参数；建议BUFFER/BUFFER_POOLS>=500MB ；MAX_BUFFER>BUFFER 时，动态扩展的缓冲区不参与分区

RECYCLE               64    4000       
高并发或大量使用 with、临时表、排序等应该适当调整

RECYCLE_POOLS         1      7         
RECYCLE 的分区数，一般配置为质数，建议                                              RECYCLE/RECYCLE_POOLS>=500MB  

HJ_BUF_GLOBAL_SIZE   500   4000  
HASH 连接操作符的数据总缓存大小（>=HJ_BUF_SIZE），系统级参数，以兆为单位。有效值范围(10~500000)

HJ_BUF_SIZE          50    300     
单个 HASH 连接操作符的数据总缓存大小，以兆为单位。有效值范围。有效值范围（2~100000） 

DICT_BUF_SIZE         5    50     
字典缓冲区大小，以兆为单位当数据库对象较多时建议适当放大

TEMP_SIZE             10   1000  
默认创建的临时表空间大小，以兆为单位。有效值范围（10~1048576 ），不断的扩充临时表空间也会影响性能，生产系统建议改为 1000

VM_POOL_SIZE          64   256     
系统执行时虚拟机内存池大小，在执行过程中用到的内存大部分是从这里申请的。当系统中存储过程、存储函数、包等对象较多时，此参数可以适当放大

SESS_POOL_SIZE        16    256     
会话缓冲区大小，以 KB 为单位，有效值范围（16~1024*1024）。若所申请的内存超过实际能申请的大小，则系统将按 16KB 大小重新申请


CACHE_POOL_SIZE      10     2000    
SQL 缓冲池大小，以兆为单位。有效 值 范 围 ： 32 位 平 台 下 为（1~2048）；64 位平台下为（1~67108864）。单位：MB

MEMORY_MAGIC_CHECK    2       2        
建议为 2 开启，性能有明显下降的话，可以设置为 1

PK_WITH_CLUSTER       1       0      
在建表语句中指定主关键字时，是否缺省指定 为 CLUSTER，0：不指定；1：指定 注：该参数对列存储表和堆表

CPU参数

WORKER_THREADS      4        16        
工作线程个数，有效值范围 1~64，一般配置为 CPU 核数相等或其 2倍
 
TASK_THREADS            4        16      
任务线程个数，一般配置为与WORKER_THREADS 相等

SESSION相关参数

MAX_SESSION               100    500  
系统允许同时连接的最大数，同时还受到 LICENSE 的限制，取二者中较小的值，有效值范围（1~65000 ）

MAX_SESSION_STATEMENT     100    2000              
单个会话上允许同时打开的语句句柄最大数，有的应用忘记关闭语句句柄，导致报错，这个参数可以适当放大

查询相关参数

USE_PLN_POOL         1       1      
是否重用执行计划。0：禁止执行计划的重用；1：启用执行计划的重用功能 ；2：对不包含显式参数的语句进行常量参数化优化；3：即使包含显式参数的语句，也进行常量参数化优化

OLAP_FLAG            0       2        
OLTP 类型的应用建议设置 2，OLAP 类型应用建议设置为 1

OPTIMIZER_MODE       0       1          
OPTIMIZWE_MODE=0，表示使用 老 优 化 器 模 式 ；
OPTIMIZWE_MODE=1，表示使用新优化器模式。新老优化器模式对一些优化的处理方式不同，最主要的不同在于：老优化器采用卡特兰树方式探测最优计划；新优化器模式采用左深二叉树方式探测最优计划，对于CROSS JOIN 尝试采用把左表连接列转为变量 VAR，并下放到右表的处理方式。2016 年以后的 DM7 版本建议使用新优化器，老版本基于老优化器调优好的可以采用老优化器

VIEW_PULLUP_FLAG    0        1     
是否对视图进行上拉优化，把视图转换为其原始定义，消除视图。可取值 0、1、2。0：不进行视图上拉优化；1：对不包含别名和同名列的视图进行上拉优化；2：对包含别名和同名列的视图也进行上拉优化

兼容性参数

COMPATIBLE_MODE        0             2（ ORACLE 环境是 2，建 议 根据 源 库类 型 进行设置）
是否兼容其他数据库模式
0：不兼容
1：兼容 SQL92 标准
2：兼容 ORACLE
3：兼容 MS SQL SERVER
4：兼容 MYSQL，
5：兼容 DM6
6：兼容 TERADATA

监控参数

ENABLE_MONITOR      2             1       
启用动态监控功能标记
0：不启用；
1：低级别监控；
2：高级别监控；
3：在高级别监控的基础上增加搜集各操作符的执行时间对性能影响较大，生产环境建议改为 0

日志相关

SVR_LOG        0       0
是否打开 SQL 日志功能，0：表示关闭；1：表 示日志文件非切换模式，但输出的日志格式是 详细模
式；2：表示日志文件为切换模式，输出 的日志也是详细模式；3：表示日志为非切换模 式，但输出日志为简单模式生产环境对性能影响较大，建议改为 0

IO相关

direct_io     0     0或1                      使用 SSD 时推荐为 1

Purge相关

purge_del_opt        0            0             不能修改为 1 或 2

ulimit -a
)1.data seg size
data seg size (kbytes, -d)
建议用户设置为 1048576(即 1GB)以上或 unlimited(无限制)，此参数过小
将导致数据库启动失败。

) 2. file size
file size(blocks, -f)
建议用户设置为 unlimited(无限制)，此参数过小将导致数据库安装或初始化
失败。

)3. open files
open files(-n)
建议用户设置为 65536 以上或 unlimited(无限制)。
vi /etc/security/limits.conf

 dmdba soft nofile 65536
 dmdba hard nofile 65536

)4.virtual memory
virtual memory (kbytes, -v)
建议用户设置为 1048576(即 1GB)以上或 unlimited(无限制)，此参数过小
将导致数据库启动失败。
如果用户需要为当前安装用户更改 ulimit 的资源限制，请修改文件
/etc/security/limits.conf。

)5. max user processes 最大线程数这个参数建议修改为 10240。用 vim
打开配置文件 vi /etc/security/limits.conf 在下面加两行。

dmdba soft nproc 10240
dmdba hard nproc 10240

)6.ls /etc/security/limits.d/ #如果有文件，执行以下操作
vi /etc/security/limits.d/20-nproc.conf 在下面加两行。
（RH6 系统该文件为 90-nproc.conf，RH7 系统该文件为 90-nproc.conf）

dmdba soft nproc 10240
dmdba hard nproc 10240

2.REDO 日志一般应用将两个 REDO 都扩展到 2GB 即可

alter database resize logfile 'DAMENG01.log' to 2048;
alter database resize logfile 'DAMENG02.log' to 2048;
alter database add logfile 'DAMENG03.log' size 2048;
alter database add logfile 'DAMENG04.log' size 2048;

3.LINUX常用性能监控命令
使用top命令查看cpu使用率
使用iostat命令查看磁盘I/O使用情况
使用dstat工具查看磁盘I/O使用情况
使用free命令查看内存使用情况
使用nmon工具监控系统一段时间的整体情况
使用perf top命令查看系统热点情况

4.SQL优化策略

--查询活动会话数
select count(*) from v$sessions where state='ACTIVE';
--已执行超过2秒的活动SQL
select * from (
SELECT sess_id,sql_text,datediff(ss,last_send_time,sysdate) Y_EXETIME,
       SF_GET_SESSION_SQL(SESS_ID) fullsql,clnt_ip
 FROM V$SESSIONS WHERE STATE='ACTIVE')
 where Y_EXETIME>=2;

--锁查询
select o.name,l.* from v$lock l,sysobjects o where l.table_id=o.id and blocked=1

--阻塞查询
with locks as(
  select o.name,l.*,s.sess_id,s.sql_text,s.clnt_ip,s.last_send_time  from v$lock l,sysobjects o,v$sessions s
  where l.table_id=o.id and l.trx_id=s.trx_id ),
 lock_tr as (   select trx_id wt_trxid,row_idx blk_trxid from locks where blocked=1),
 res as(    select sysdate stattime,t1.name,t1.sess_id wt_sessid,s.wt_trxid,
     t2.sess_id blk_sessid,s.blk_trxid,t2.clnt_ip,SF_GET_SESSION_SQL(t1.sess_id) fulsql,
     datediff(ss,t1.last_send_time,sysdate) ss,t1.sql_text wt_sql  from lock_tr s,locks t1,locks t2
    where t1.ltype='OBJECT'  and t1.table_id<>0   and t2.ltype='OBJECT'  and t2.table_id<>0
     and s.wt_trxid=t1.trx_id  and s.blk_trxid=t2.trx_id)
select distinct wt_sql,clnt_ip,ss,wt_trxid,blk_trxid  from res;

生成SQL日志
–设置SQL过滤规则，只记录必要的SQL，生产环境不要设成1
– 2 只记录DML语句 3 只记录DDL语句 22 记录绑定参数的语句
– 25 记录SQL语句和它的执行时间 28 记录SQL语句绑定的参数信息
SELECT SF_SET_SYSTEM_PARA_VALUE(‘SQL_TRACE_MASK’,‘2:3:22:25:28’,0,1);

–同步日志会严重影响系统效率，生产环境必须设置为异步日志
SELECT SF_SET_SYSTEM_PARA_VALUE(‘SVR_LOG_ASYNC_FLUSH’,1,0,1);

–下面这个语句设置只记录执行时间超过200ms的语句
SELECT SF_SET_SYSTEM_PARA_VALUE(‘SVR_LOG_MIN_EXEC_TIME’,200,0,1);

–下面的语句查看设置是否生效
SELECT * FROM V $DM_INI where para_name='SVR_LOG_ASYNC_FLUSH';SELECT * FROM V$ DM_INI where para_name=‘SQL_TRACE_MASK’;SELECT * FROM V$DM_INI where para_name=‘SVR_LOG_MIN_EXEC_TIME’;

–开启SQL日志：

SP_SET_PARA_VALUE(1, 'SVR_LOG', 1);
--关闭SQL日志：
SP_SET_PARA_VALUE(1, 'SVR_LOG', 0);

SQL日志分析流程
用ETL工程将sql日志入库
在这里插入图片描述
用JAR包分析sql
PS：对于比较大SQL日志（大于10G），建议采用ETL入库，入库较快
SQL日志分析流程
200ms以上的sql汇总
1次以上的sql汇总

优化目标
并发非常高
SQL特征：数量很少(5%)，但是执行频率非常高，甚至达到每秒上百次，只要一慢，系统很可能瘫痪。
优化级别：最优先处理。

并发一般
SQL特征：占大多数(80%)，如果有慢的，对系统整体稳定性影响不大，但是会造成局部的某些操作慢。
优化级别：次优先处理。

并发很少特别慢
SQL特征：数量少(15%)，往往是很复杂的查询，可能一天就执行几次，对系统整体影响不大，但是优化难度很大。
优化级别：最后处理。

确定目标SQL
如XX项目，入库生产SQL日志，汇总分析，最终找出待优化的3类目标SQL，如下图。接着就去做具体的SQL优化了。
在这里插入图片描述
SQL优化思路
高并发SQL
单个SQL调到最快

普通索引
聚集索引
覆盖索引

优化应用，减少执行次数

应用做结果集缓存
优化应用逻辑，减少无用的执行
将SQL分散其他数据库节点

一般并发SQL
使用索引

单列索引
组合索引

改写SQL

left join等价改为inner join
避免隐式转换不走索引
将过滤条件上拉，走索引
用分析函数，减少表扫描

阻塞分析过程
)1、监控阻塞会话，同时保证sql日志已经打开

with locks as(
  select o.name,l.*,s.sess_id,s.sql_text,s.clnt_ip,s.last_send_time  from v$lock l,sysobjects o,v$sessions s
  where l.table_id=o.id and l.trx_id=s.trx_id ),
 lock_tr as (   select trx_id wt_trxid,row_idx blk_trxid from locks where blocked=1),
 res as(    select sysdate stattime,t1.name,t1.sess_id wt_sessid,s.wt_trxid,
     t2.sess_id blk_sessid,s.blk_trxid,t2.clnt_ip,SF_GET_SESSION_SQL(t1.sess_id) fulsql,
     datediff(ss,t1.last_send_time,sysdate) ss,t1.sql_text wt_sql  from lock_tr s,locks t1,locks t2
    where t1.ltype='OBJECT'  and t1.table_id<>0   and t2.ltype='OBJECT'  and t2.table_id<>0
     and s.wt_trxid=t1.trx_id  and s.blk_trxid=t2.trx_id)
select distinct wt_sql,clnt_ip,ss,wt_trxid,blk_trxid  from res;

)2、监控到阻塞信息，并保留数据，供后面分析使用。

在这里插入图片描述
可以看出事务[316624]被事务[316646]所阻塞，后面可以从sql日志中找到完整事务给应用分析。
)3、将SQL日志入库，表名为log_commit，表结构如下：
)4、找出阻塞的两个事务，然后优化慢sql，同时反馈给应用去优化业务

--等待的事务
select trx_id,starttime,sqlstr,exetime from log_commit where trx_id='316624';

--阻塞的源头事务

select trx_id,starttime,sqlstr,exetime 
from log_commit where trx_id='316646';

、理想三旬

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
DM数据库优化

1.参数优化内存参数：MAX_OS_MEMORY 95 100 DM 数据库占用的内存占操作系统物理内存和虚拟内存之和百分比，需要设置为 100BUFFER 100 *** 用于缓存数据页，一般配置为操作系统物理内存的 60%~80%MAX_BUFFER 100 **** 用于控制系统缓冲区的上限，一般配置为和 BUFFER 参数相等BUFFER_POOLS
复制链接

扫一扫