(15)达梦数据库中的性能优化技术分享

本篇博客向各位同学分享下达梦数据库中的相关性能方面的话题。首先,我们对性能问题应该有个完整的认识。数据库的运行是在主机系统、存储环境、计算单元、网络等一系列单元的共同配合下完成的,因此,在分析和处理问题的时候,应该有一个全局观。这里面,从性能角度来讲,应该关注的就包括有:数据库运行参数相关配置的正确性和结合硬件运行环境做调整,如CPU和数据库并行线程等信息的调整;操作系统层面的,如文件打开数,IO调度、虚拟内存以及最大线程数等;以及数据库监控层面的如慢sql日志记录与入库分析、数据库等待事件结合操作系统的相关监控记录进行综合分析判断等。以上情况,对我们的日常维护工作和性能分析工作有着巨大的帮助,可以从全局角度去定位和分析性能问题。在解决问题这个层面上,无论是改结构还是调SQL,需要结合实际客户业务场景来做分析决定。通常情况下,高频SQL是我们解决问题过程中需要重点关注解决的对象。

在解决问题的过程中,达梦数据库也为我们提供了很多优秀的解决方案和工具,如ET和达梦awr 报告等等,都有助于我们去分析和解决问题,也是我们日常工作中需要去掌握的。除此以外,在OS层面的相关性能工具和监控工具,也可以有效帮助我们去处理问题。

在达梦数据库中,DBA还可以通过查看系统日志来辅助分析问题。数据库运行过程中,会将一些关键信息记录到安装目录下一级log目录下的名称为dm_实例名_YYYYMM.log的系统日志文件中,其中YYYY表示年份,MM表示月份。例如,dm_DMSERVER_202206.log。该文件会记录下数据库服务启动/关闭的时间、系统关键错误如打开文件失败等。

一、启用SQL日志来辅助分析性能问题

在达梦数据库中,DBA可以通过查看系统SQL日志来辅助分析SQL性能问题。

将DM数据库配置文件中的参数SVR_LOG设置为打开,系统会在安装的log目录下生成名为dmsql_实例名_日期_时间.log的SQL日志文件。在该文件中记录了启用SVR_LOG之后数据库接收到的所有SQL语句等信息,DBA可以通过分析该文件来帮助解决问题。

1.1开启SQL日志:

SP_SET_PARA_VALUE(1, 'SVR_LOG', 1);

1.2根据实际情况需要,修改sqllog.ini来满足需要。

重点关注信息:记录执行时间超过200ms的语句;生产环境必须设置为异步日志;设置SQL过滤规则,只记录必要的SQL等。

(15)达梦数据库中的性能优化技术分享_第1张图片

以下是 sqllog.ini 参数文件的配置参考。

(15)达梦数据库中的性能优化技术分享_第2张图片

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

通过SQL日志,可以对关注的性能问题SQL进行汇总分析,找出可能的问题所在。

二、分析当前等待事件

当一个进程连接到数据库后, 进程所经历的种种等待就开始被记录, 并且通过一系列的性能视图进行展示, 通过等待事件用户可以很快发现数据库的性能瓶颈, 从而进行针对性能的优化和分析。结合操作系统的监控信息记录以及数据库层面的等待事件来发现运行瓶颈,是我们需要关注的重点之一。

达梦数据库中的动态性能视图能自动收集数据库中的一些活动信息,日常工作上根据这些信息可以了解数据库运行的基本情况,为数据库的维护和优化提供依据。动态视图信息是随着数据库的运行随时更改,具有一定的即时性。与 数据库事件相关的 视图主要有以下几个:

    V$WAIT_HISTORY  通过该视图可以查询等待事件的具体信息,如等待的线程id,会话id等。可以查看具体等待事件的信息,如果某个事务等待时间过长,则可以查询到具体事务信息以及所在的线程和所牵涉的对象,分析原因进行优化等操作。

    V$EVENT_NAME  显示当前系统所支持的等待事件的类型汇总信息。

    V$SYSTEM_EVENT  显示自系统启动以来所有等待事件的详细信息。

    V$SESSION_EVENT  显示当前会话等待事件的所有信息。

    V$SESSION_WAIT_HISTORY  显示会话等待事件的历史信息。

    V$DANGER_EVENT  数据库重要事件和行为信息视图。

    V$TASK_QUEUE   任务队列信息。

    V$TRACE_QUEUE   事件跟踪任务队列信息。

整体上,归纳起来有如下几个大类:

2.1 与IO相关的有10类事件

(15)达梦数据库中的性能优化技术分享_第3张图片

2.2 与并发相关的是26类

(15)达梦数据库中的性能优化技术分享_第4张图片

2.3 其他方面的事件有11类

(15)达梦数据库中的性能优化技术分享_第5张图片

下面是对等待事件的类型做一个补充说明:

dbfile read:读用户数据文件;

dbfile multi read:批量读用户数据文件;

dbfile read wait:写用户数据文件;

dbfile remote read:DSC引起的读磁盘;

instance recovery read:recorvery引起的读磁盘;

dbfile remote write:DSC引起的写磁盘;

dbfile discard write:buf不够引起的写磁盘;

dbfile ckpt write:ckpt引起的写磁盘;

dbfile extend:扩库文件引起的写磁盘;

shared memory pool busy:共享内存并发使用冲突;

buffer busy wait:buffer并发使用冲突;

dict cache busy:字典cache并发使用冲突;

plan cache busy:计划cache并发使用冲突;

redo log system busy:redo log系统并发冲突;

redo log buffer busy:redo log缓冲区并发冲突;

kernel busy:kernel并发冲突;

table lock busy:表锁系统并发冲突;

tid lock busy:行锁系统并发冲突;

parallel bdta pool busy:并行bdta pool并发冲突;

iid system busy:iid系统并发冲突;

session system busy:session系统并发冲突;

public vpool busy:公共vpool并发冲突;

pseg queue busy:pseg队列并发冲突;

pseg stack busy:pseg堆栈并发冲突;

page busy wait:数据页并发冲突;

table lock wait:事务间表锁等待发生;

trxid lock wait:事务间行锁等待发生;

dead lock:事务间产生死锁;

transaction system busy:事务系统并发冲突;

transaction view busy:事务可见性视图并发冲突;

purge system busy:purge系统并发冲突;

file system busy:文件系统并发冲突;

asm system busy:ASM系统并发冲突;

out of share mem pool:共享内存池不足;

out of share coldata pool:共享coldata池不足:

network send wait:网络发送等待;

network recv wait:网络接收等待。

 

2.4 常用查询事务会话阻塞等待相关语句

1 查询会话中等待事务

语法:SELECT * FROM V$TRXWAIT;

ID:事务ID

WAIT_FOR_ID:所等待的事务ID

WAIT_TIME:当前等待时间

2会话等待信息

语法:SELECT * FROM V$WAIT_HISTORY;

通过该视图可以查询等待事件的具体信息。可以查看具体等待事件的信息,如果某个事务等待的时间过长,就可以查询到具体事务信息以及所在的线程和所牵涉的对象,分析原因进行优化等操作。

THREAD_ID:线程ID

TRX_ID:事务ID

WAIT_CLASS:等待类型号

WAIT_OBJECT:等待对象

WAIT_START:等待事件的开始时间

WAIT_TIME:等待时间

SPACE_ID:表空间号

FILE_ID:文件号

PAGE_NO:页号

3.事务

select

ID as 当前活动事务的id号,STATUS as 当前事务的状态,ISOLATION as 隔离级,READ_ONLY as 是否是一个只读事务,SESS_ID as 会话id,INS_CNT as 插入数目,DEL_CNT as 删除数目,UPD_CNT as 更新数目,UPD_INS_CNT 更新插入回滚记录个数,UREC_SEQNO 当前UNDO记录的递增序列号,WAITING as 事务等待的锁

FROM SYS.V$TRX;

4.活动事务数量

SELECT COUNT(1) FROM V$TRX WHERE STATUS=‘ACTIVE’;

5. 等待事件

SELECT THREAD_ID,TRX_ID,WAIT_CLASS,WAIT_OBJECT,WAIT_START,WAIT_TIME, SPACE_ID,FILE_ID,PAGE_NO FROM V$WAIT_HISTORY;

THREAD_ID:进程ID

TRX_ID:事务ID

WAIT_CLASS:等待类型号

WAIT_OBJECT:等待对象

WAIT_START:等待事件的开始时间

WAIT_TIME:等待时间

SPACE_ID:表空间号

FILE_ID:文件号

PAGE_NO:页号

6 事务死锁数量

SELECT * FROM V$DEADLOCK_HISTORY;

锁信息  SELECT * FROM V$LOCK;

上述SQL语句仅供工作参考,实际工作上,大家可以参考我前面给出的 数据库事件相关的 视图,结合数据库事务相关的隔离级别和锁相关理论,加工出自己想要的等待信息。

三、性能方面的操作实践小案例分享

3.1 事务等待案例

会话一:在 g1(c1 为主键)中插入一行记录(不提交):

insert into g1(c1,c2) values (20001,'lock1');

(15)达梦数据库中的性能优化技术分享_第6张图片

打开disql会话二:在 g1 中插入相同记录:

insert into g1(c1,c2) values (20001,'lock2');

(15)达梦数据库中的性能优化技术分享_第7张图片

查询事务等待:

select a.sess_id, a.sql_text, a.state, b.* from v$sessions a, v$trxwait b where a.trx_id = b.id;

(15)达梦数据库中的性能优化技术分享_第8张图片

查询等待的锁:

(15)达梦数据库中的性能优化技术分享_第9张图片

查询未提交事务

select b.object_name, c.sess_id, c.thrd_id, c.state, c.sql_text, a.*

from v$lock a, dba_objects b, v$sessions c

where a.table_id = b.object_id

and a.ltype = 'OBJECT'

and a.trx_id = c.trx_id

and a.ign_flag=0;

(15)达梦数据库中的性能优化技术分享_第10张图片

sp_close_session(sess_id);结束上一个插入会话。

(15)达梦数据库中的性能优化技术分享_第11张图片

3.2 SQL优化案例分享

3.2.1 案例1 : G1和g2 两表关联,走嵌套循环连接

测试数据准备:

CREATE TABLE g1(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );

CREATE TABLE g2(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );

(15)达梦数据库中的性能优化技术分享_第12张图片

INSERT INTO g1

SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL FROM DUAL

CONNECT BY LEVEL<=200000;

(15)达梦数据库中的性能优化技术分享_第13张图片

INSERT INTO g2

SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL  FROM DUAL

CONNECT BY LEVEL<=200000;

(15)达梦数据库中的性能优化技术分享_第14张图片

过滤列和连接列都没有索引,也可以走nest loop,但是该计划很差。右边的计划代价很大。

(15)达梦数据库中的性能优化技术分享_第15张图片

优化:创建索引并搜集统计信息

create index idx_g1_c2 on g1(c2);

create index idx_g2_c1 on g2(c1);

dbms_stats.gather_index_stats('SYSDBA','IDX_G1_C2');

dbms_stats.gather_index_stats('SYSDBA','IDX_G2_C1');

(15)达梦数据库中的性能优化技术分享_第16张图片

优化后的执行计划:

(15)达梦数据库中的性能优化技术分享_第17张图片

3.2.2 案例2:优化索引SQL操作

索引合理创建,可以帮助业务快速提高查询效率。我们就以常用的组合索引为例,介绍下索引列的顺序。原则是:

最优先把等值匹配的列放最前面,范围匹配的放后面;

其次把过滤性好的列放前面,过滤性差的放后面;

查询时组合索引只能利用一个非等值字段。

下面通过具体操作,来做实践验证:

1.创建测试表

create table abc(c1 int,c2 char(1),c3 char(1),c4 int);

(15)达梦数据库中的性能优化技术分享_第18张图片

2.构造测试数据

insert into abc

select level c1,chr(mod(level,27)+65) c2,chr(mod(level,27)+65) c3,level c4

from dual

connect by level<=200000;

(15)达梦数据库中的性能优化技术分享_第19张图片

待优化语句的执行计划如下:

SELECT * FROM ABC WHERE C1 BETWEEN 10 AND 20 AND C2 ='A' AND C3='B';

(15)达梦数据库中的性能优化技术分享_第20张图片

创建组合索引:

CREATE INDEX IDX_C1_C2_C3_ABC ON ABC(C1,C2,C3);

CREATE INDEX IDX_C2_C3_C1_ABC ON ABC(C2,C3,C1);

(15)达梦数据库中的性能优化技术分享_第21张图片

采用组合索引,优化后的查询执行计划如下:

(15)达梦数据库中的性能优化技术分享_第22张图片

3.2.3案例3:统计信息缺失对SQL操作带来的性能影响

创建测试表CREATE TABLE GUOY(ID INT, NAME VARCHAR(100));

(15)达梦数据库中的性能优化技术分享_第23张图片

插入数据

BEGIN

FOR i IN 1 .. 200000 LOOP

  INSERT INTO GUOY VALUES(i, 'GUO');

END LOOP;

COMMIT;

END;

(15)达梦数据库中的性能优化技术分享_第24张图片

创建索引 CREATE INDEX GY01 ON GUOY(ID);

(15)达梦数据库中的性能优化技术分享_第25张图片

未更新统计信息:

EXPLAIN SELECT * FROM GUOY WHERE ID <500;

(15)达梦数据库中的性能优化技术分享_第26张图片

更新统计信息  SP_INDEX_STAT_INIT('SYSDBA','GY01');

执行计划有了显著变化:

(15)达梦数据库中的性能优化技术分享_第27张图片

 

四、优化数据库布局

数据库的布局直接影响整个系统的I/O性能。通常情况下,以下原则是应该遵循的:

    1.日志文件放在独立的物理磁盘上,保持与数据文件分开存储;

    2.预先估算并分配好磁盘空间,避免运行过程中频繁扩充数据文件;

    3.系统中不同表空间尽量分布在不同的磁盘上,这样当数据分布在多个表空间时,可以充分利用不同磁盘的并行I/O能力;

    4.对于分区表,尽可能将不同的分区放到不同的表空间;

    5.对于分析型应用,数据库的页大小和簇大小都可以考虑取最大值,并且在采用列存储的情况下,应该尽可能让每列存放在独立的表。

五、小结

    在数据库的设计方面,如果项目前期就能够从表和索引等的开发设计,或者是数据库运行架构设计等开始就介入进来,远远要比项目后期上线运行后再去做数据库性能相关工作带来的成效好的多。同时,合规有效的SQL语句开发,会给项目的上线稳定运行带来不小的收益,这也是我们应该去关心的重点。

以上是这次技术分享的相关内容,欢迎小伙伴给我留言进行交流指导。

达梦技术社区:https://eco.dameng.com

你可能感兴趣的