达梦数据库性能检测与提升总结

前言

本篇为学习整理资料，汇集了网络平台上关于达梦数据库性能相关的文章链接，以及自己的一些知识、经验总结。目的是能够在学习、工作中进行参照。
数据库的性能主要关注下面几个方面：

• 数据库运行参数相关配置的正确性：需要结合硬件运行环境做调整，如CPU和数据库并行线程等信息的调整；操作系统层面的，如文件打开数，IO调度、虚拟内存以及最大线程数等；
• sql执行是否高效：结构设计、sql设计、统计信息的及时更新；
• 等待事件：锁表、io等待等

1 性能检测

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

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

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

1.1.1 SQL日志的开启和关闭

• 开启：SP_SET_PARA_VALUE(1, ‘SVR_LOG’, 1);
• 关闭：SP_SET_PARA_VALUE(1, ‘SVR_LOG’, 0);

1.1.2 根据实际情况需要，修改sqllog.ini来满足需要

sqllog.ini 用于 sql 日志的配置，当且仅当 INI （dm.ini）参数 SVR_LOG=1 时使用。
如果在服务器启动过程中，修改了 sqllog.ini 文件。修改之后的文件，只要调用过程 SP_REFRESH_SVR_LOG_CONFIG() 就会生效。

• sqllog.ini位置：

/DCE/dmdata/DAMENG
dmdata为达梦数据库安装路径，DAMENG为数据库具体路径。

• sqllog.ini结构说明：
  
  sqllog.ini中每个配置的含义，参见：官方技术文档
  其中跟性能相关比较重要的是【ASYNC_FLUSH】，如果要开启SQL日志，一定要设置为1（打开异步日志功能），否则开启日志本身就会对性能产生较大影响。

1.2 实时监测SQL语句执行时间

通过DM性能监视工具（monitor）的“SQL监视”功能实时查看sql语句执行的时间。

• 打开自动刷新，并设置刷新间隔，可以定时更新执行过的sql语句集（也可以点击手动刷新，得到最新的sql执行语句集）；
• 点击【SQL统计】可以设置显示执行最长时间的N条SQL，也可以设置显示执行频率最高的N条SQL；
• 点击具体的记录可以查看SQL详细，和该SQL语句的执行计划；
• 选择其中一条记录后可以点击【会话统计】，进行会话监视：
  
• 选择其中一条记录后可以点击【查看事务】，进行事务监视：
  
• 可以将当前时间点的SQL语句统计信息保存到txt或xml，供日后查看。

1.3 分析当前等待事件

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

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

• 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 事件跟踪任务队列信息。

详细参见这篇文章中的第二章

上面的等待事件中包含一种死锁等待，也可以直接从DM性能监视工具中观测：

1.4 数据库重演(REPLAY)

数据库重演（Database Replay）是DM中用来重现、定位和分析问题的一个重要手段，其基本原理是在数据库系统上捕获所有负载（记录外部客户端对服务器的请求），保存到二进制捕获文件，然后通过DM提供的数据库重演工具将捕获文件中的请求发送给捕获前由原始数据库备份恢复而来的重演测试系统上，从而帮助重现当时的场景。

详细参见：官方文档

或：第三方文档

1.5 通过动态视图确定高负载的SQL

1.5.1 显示最近1000条执行时间较长的SQL语句

SELECT * FROM V$LONG_EXEC_SQLS;

1.5.2 显示服务器启动以来执行时间最长的20条SQL语句

SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS;

2 性能优化

2.1 结构设计

2.1.1 表的填充因子

原则上，在只读表上应该设置填充因子高，而有大量更新的表上应该设置较低的值。默认情况下，DM新建的表和索引的填充因子是100，可根据实际情况设置合适的填充因子大小。

详细参见官方技术文档

2.1.2 指定表的聚集索引

聚集索引明显提升B树查找的速度，同时可能因导致页面的分裂而影响插入性能。
是不是可以得出一个结论：空表插入100条数据，和100条数据的表更新100条记录对比，更新更快。

详细参见官方技术文档

2.1.3 创建临时表

通过AS SELECT子句建立的临时表是将复杂查询的结果通过临时B树记录了下来，下次访问不用重新执行查询就可以获得数据，并且会话或事务结束后数据将自动删除，是复杂查询的一个优秀的解决方案，且提高了性能。

详细参见官方技术文档

2.1.4 指定表的存储位置

创建表时，在STORAGE子句中，可对表指定存储的表空间。
因为不同的数据库表可能对应不同的数据文件，可减少对相同文件的竞争，从而提高数据库系统的性能。

2.1.5 为性能而安排索引列

如果查询中有多个字段组合定位，则不应为每个字段单独创建索引，而应该创建一个组合索引。当两个或多个字段都是等值查询时，组合索引中各个列的前后关系是无关紧要的。但是如果是非等值查询时，要想有效利用组合索引，则应该按等值字段在前，非等值字段在后的原则创建组合索引，查询时只能利用一个非等值的字段。

2.1.6 为每个索引指定表空间

将表及其索引放在不同的表空间（在不同磁盘上）产生的性能比放在相同的表空间更好，因为这样做减少了磁盘竞争。

2.1.7 重建索引

当一个表经过大量的增删改操作后，表的数据在物理文件中可能存在大量碎片，从而影响访问速度。

CALL SP_REBUILD_INDEX(SCHEMA_NAME varchar(256), INDEX_ID int);

2.1.8 禁止完整性约束

在下面的情况下，从性能的角度考虑，可以暂时将完整性约束禁用。

1. 导入大量的数据到一张表中；
2. 做批处理操作并对一张表做大规模修改时；
3. 导入导出一张表。

2.1.9 创建分区表

分区是指将表、索引等数据库对象划分为较小的可管理片段的技术，每一个片段称为分区子表或分区索引。一个表被分区后，对表的查询操作可以局限于某个分区进行，而不是整个表，这样可以大大提高查询速度。

由于每一个分区都以一个子表作为实体，那么不同分区可以存储于相同表空间，也可以位于不同的表空间中。这样就可以将同一个表中的数据分布在不同的磁盘上，从而均衡磁盘上的I/O操作。

分区的方法有：

1. 范围(range)水平分区；
2. 哈希（hash）水平分区；
3. 列表（list）水平分区；
4. 多级分区表。

关于分区表详细可以参照：官方文档

2.1.10 创建堆表

堆表采用了物理ROWID形式的堆表，DM服务器内部对聚集索引进行了调整，没有采用传统B树结构，取而代之的是“扁平B树”，数据页都是通过链表形式存储。为支持并发插入，扁平B树可以支持最多128个数据页链表（最多64个并发分支和最多64个非并发分支），在B树的控制页中记录了所有链表的首、尾页地址。对于非并发分支，如果分支数有多个，即存在多个链表，则不同的用户登录系统之后，会依据其事务ID号，随机选择一条链表来对堆表进行插入操作。对于并发分支，则不同用户会选择不同的分支来进行插入，如果存在多个用户选择了同一条分支的情况，才需要等待其他用户插入结束并释放锁之后才能进行插入。在并发情况下，不同用户可以在不同的链表上进行插入，效率得到较大提升。

创建方式：

1. INI参数方式
   - 如果LIST_TABLE = 1，则在未显式指定表是否为堆表或非堆表时，默认情况下创建的表为堆表；
   - 如果LIST_TABLE = 0，则在未显式指定表是否为堆表或非堆表时，默认情况下创建的表为普通表形式。

2. SQL语句显示指定

CREATE TABLE LIST_TABLE(C1 INT) STORAGE(BRANCH (2,4));

	- NOBRANCH：如果指定为NOBRANCH，则创建的表为堆表，并发分支个数为0，非并发分支个数为1；
	- BRANCH(n，m)：如果为该形式，则创建的表为堆表，并发分支个数为n，非并发个数为m；
	- BRANCH n：指定创建的表为堆表，并发分支个数为n，非并发分支个数为0；
	- CLUSTERBTR：创建的表为非堆表，即普通B树表。

2.2 统计信息更新

2.2.1 统计信息的作用

1. 统计信息的收集可以大概率的修正对过滤行数的估算；

2. 统计信息对CBO选择正确的执行计划非常重要，对执行效率的影响非常大，在应用系统运行过程中，有时候用户可能会发现系统的响应速度越来越慢。这其中可能的原因之一就是随着数据规模的增长，SQL 语句的执行计划已经不是处于最优状态，需要对 SQL语句进行调整或者对统计信息进行更新；

3. 统计信息收集的比列根据实际情况来设置，如果可行的话，就选择采用率为100 的方式收集。业务繁忙又必须更新统计信息的情况下，可先更新一部分来满足应用需求；

4. 使用统计信息可以提升数据查询的效率，而定期地更新统计信息则有助于提高统计信息的有效性。象为了回收空间做清理一样，经常更新统计信息也是对更新频繁的表更有用。不过，即使是更新非常频繁的表，如果它的数据的统计分布并不经常改变，那么也不需要更新统计信 息。更新统计信息需要在业务低谷的时候去操作，在高峰操作可能导致业务瘫痪；

2.2.2 何时需要更新统计信息

1. 数据库升级完需要更新统计信息；
2. dts迁移工具迁移数据后需要更新统计信息；
3. 新建表或者新建表索引后需要更新统计信息；
4. 批量的DML操作后明显影响该表的执行效率；

详细参见文章：达梦8统计信息更新和使用

2.3 参数配置

2.3.1 dm.ini

2.3.1.1 共享内存池

共享内存池是DM Server在启动时从操作系统申请的一大片内存。在DM Server的运行期间，经常会申请与释放小片内存，而向操作系统申请和释放内存时需要发出系统调用，此时可能会引起线程切换，降低系统运行效率。采用共享内存池则可一次向操作系统申请一片较大内存，即为内存池，当系统在运行过程中需要申请内存时，可在共享内存池内进行申请，当用完该内存时，再释放掉，即归还给共享内存池。

合适大小的共享内存池、合适的每次扩展大小和合适的MEMORY_TARGET可以最大限度的减少数据库实例向操作系统申请的次数，以提高整体性能。

配置项	配置项说明
MEMORY_POOL	如果在运行时所需内存大于配置值，共享内存池也可进行自动扩展
MEMORY_EXTENT_SIZE	指定了共享内存池每次扩展的大小
MEMORY_TARGET	指定了共享内存池扩展到超过该值后，空闲时会收缩到的大小

2.3.1.2 数据缓冲区

数据缓冲区是DM Server在将数据页写入磁盘之前以及从磁盘上读取数据页之后，数据页所存储的地方。这是DM Server至关重要的内存区域之一，将其设定得太小，会导致缓冲页命中率低，磁盘IO频繁；将其设定得太大，又会导致操作系统内存本身不够用。

配置项	配置项说明
NORMAL	主要是提供给系统处理的一些数据页，没有特定指定缓冲区的情况下，默认缓冲区为NORMAL 。用户可以在创建表空间或修改表空间时，指定表空间属于NORMAL
KEEP	特性是对缓冲区中的数据页很少或几乎不怎么淘汰出去，主要针对用户的应用是否需要经常处在内存当中。用户可以在创建表空间或修改表空间时，指定表空间属于KEEP
RECYCLE	供临时表空间使用
FAST_POOL_PAGES	FAST缓冲区根据用户指定的FAST_POOL_PAGES大小由系统自动进行管理

读多页
在需要进行大量I/O的应用当中，DM之前版本的策略是每次只读取一页。如果知道用户需要读取表的大量数据，当读取到第一页时，可以猜测用户可能需要读取这页的下一页，在这种情况下，一次性读取多页就可以减少I/O次数，从而提高了数据的查询、修改效率。

但是这个值也不是越大效率就越高，如果命中率不高很可能导致过多消耗I/O，从而导致查询、修改效率下降。因此需要看表数据的存储结构，可能需要水平分区表的配合？

配置项	配置项说明
MULTI_PAGE_GET_NUM	控制每次读取的页数

2.3.1.3 日志缓冲区

配置项	配置项说明
RLOG_BUF_SIZE	对日志缓冲区大小进行控制，日志缓冲区所占用的内存是从共享内存池中申请的，单位为页数量，且大小必须为2的N次方，否则采用系统默认大小512页

注意：由于日志缓冲区所占用的内存是从共享内存池中申请的，因此不能单独设置日志缓冲区的页数，还要同步调整共享内存池的大小

2.3.1.4 字典缓冲区

如果在实际应用中涉及对分区数较多的水平分区表访问，例如上千个分区，那么就需要适当调大DICT_BUF_SIZE参数值。

配置项	配置项说明
DICT_BUF_SIZE	默认的配置大小为5M

2.3.1.5 SQL缓冲区

很多应用当中都存在反复执行相同SQL语句的情况，此时可以使用缓冲区保存这些语句和它们的执行计划，这就是计划重用。这样带来的好处是加快了SQL语句执行效率，但同时给内存也增加了压力。

配置项	配置项说明
USE_PLN_POOL	当指定为非0时，则启动计划重用；为0时禁止计划重用
CACHE_POOL_SIZE	改变SQL缓冲区大小，系统管理员可以设置该值以满足应用需求，默认值为20M

2.3.1.6 排序区

配置项	配置项说明
SORT_BUF_SIZE	由于该值是由系统内部排序算法和排序数据结构决定，建议使用默认值2M

2.3.1.7 哈希区

DM8提供了为哈希连接而设定的缓冲区，不过该缓冲区是个虚拟缓冲区。之所以说是虚拟缓冲，是因为系统没有真正创建特定属于哈希缓冲区的内存，而是在进行哈希连接时，对排序的数据量进行了计算。如果计算出的数据量大小超过了哈希缓冲区的大小，则使用DM8创新的外存哈希方式；如果没有超过哈希缓冲区的大小，实际上还是使用内存池来进行哈希操作。

配置项	配置项说明
HJ_BUF_SIZE	该值的大小可能会限制哈希连接的效率，所以建议保持默认值，或设置为更大的值

2.3.1.8 查询计划重用

如果同一条语句执行频率较高，或者每次执行的语句仅仅是常量值不同，则可以考虑使用计划重用机制。避免每次执行都需要优化器进行分析处理，可以直接从计划缓存中获取已有的执行计划，减少了分析优化过程，提高执行率。

配置项	配置项说明
USE_PLN_POOL	当置为非0时，会启用计划重用

2.3.1.9 结果集重用

执行计划的生成与优化是一个非常依赖CPU的操作，而执行一个查询获得结果集也是一个非常消耗资源的操作。当系统连续执行两个完全相同的SQL语句，其执行计划和结果集很有可能是相同的，如果重新生成和执行计划，会大大浪费系统资源。这时如果使用计划重用和结果集重用，系统的响应速度可以大大提升。

基于查询计划重用。

配置项	配置项说明
RS_CAN_CACHE	控制结果集重用，当置为0时表示手动模式（MANUAL），在此模式下默认不缓存查询结果集，但是DBA可以通过语句提示等方法指示系统对必要的查询结果集进行缓存；置为1时表示强制模式（FORCE），在此模式下默认缓存所有可缓存结果集
RS_CACHE_TABLES	指定可以缓存结果集的基表清单，只有查询涉及的所有基表全部在参数指定范围内，此查询才会缓存结果集
RS_CACHE_MIN_TIME	指定了缓存结果集的查询语句执行时间的下限，只有实际执行时间不少于指定值的查询结果集才会缓存

详细约束参见：官方文档

2.4 优化数据库布局

数据库的布局直接影响整个系统的I/O性能。通常情况下，DBA应该遵循下述原则：

1. 日志文件放在独立的物理磁盘上，保持与数据文件分开存储；
2. 预先估算并分配好磁盘空间，避免运行过程中频繁扩充数据文件；
3. 系统中不同表空间尽量分布在不同的磁盘上，这样当数据分布在多个表空间时，可以充分利用不同磁盘的并行I/O能力；
4. 对于分区表，尽可能将不同的分区放到不同的表空间；
5. 对于分析型应用，数据库的页大小和簇大小都可以考虑取最大值，并且在采用列存储的情况下，应该尽可能让每列存放在独立的表。

2.5 SQL调优

官方网站有一个关于SQL调优的说明，很详尽，参见：SQL调优

2.5.1 获取即时的SQL计划

很多时候我们在研究某条SQL的执行计划时都是直接使用EXPLAIN去看，由于执行计划的生成跟统计信息等多方面相关，因此我们看到的执行计划有可能不是SQL发生问题那次的计划。

通过下面的方法大概率可以看到问题发生时的SQL执行计划：

1. 找到SQL执行计划缓存的id：

SELECT * FROM v$cachepln;

也可以在后面加上WHERE sqlstr LIKE xxxx，以便过滤出你要找的SQL的计划缓存id。

2. 根据计划缓存id获取SQL的dump_file，查看dump_file了解问题发生时的执行计划：

ALTER SESSION SET EVENTS ‘immediate trace name plndump level 281457642194992, dump_file ‘’/data/dmdb/xxx/xxxx.log’‘’;

其中的281457642194992是在第一步中获得的SQL的执行计划缓存id，log文件是我们指定的要生成的文件名，注意在这个路径下达梦数据库启动用户必须要有可写入权限。

下面是dump_file的内容例子：

2.5.2 获取SQL的执行trace

• 开启SQL trace功能

ALTER SESSION 0 SET EVENTS '10053 trace name context forever';

• 关闭SQL trace功能

ALTER SESSION 0 SET EVENTS '10053 trace name context off';

• trace文件路径：

dmdb/库/trace

trace文件例：

附录1_执行计划详解

参见文章：达梦sql优化之执行计划