达梦数据库的性能优化应对数据膨胀-技术开发专区

达梦数据库的性能优化应对数据膨胀

作者：IT168 技术开发频道编辑：徐志远 2014-04-01 13:50 IT168网站原创

　　【IT168 资讯】“棱镜门”、“微软XP系统停摆”的接踵而至给我国信息安全敲响了警钟，也加速了国内 “去IOE”运动的进程。达梦数据库作为连续5年国产数据库市场占有率第一的高性能、高可靠性、高安全性、高兼容性大型关系型数据库管理系统，已成功替代了Oracle，在电力、金融、电子政务、教育等行业领域得到了广泛的应用，逐渐成为国家信息化建设的重要基础平台。为了更好地支撑业务应用，有效管理和利用信息时代不断产生并急剧膨胀的数据，对达梦数据库的优化显得尤为重要。

　　一、数据库参数优化

　　1. 优化内存

　　公共内存池

　　公共内存池提供了一组内存申请/释放接口，为系统中需要动态分配内存的模块提供服务。

　　SQL>select * from v$dm_ini where para_name like '%MEM%POOL%';
　　行号 PARA_NAME PARA_VALUE
　　---------- --------------- ----------
　　1 MEMORY_POOL 40
　　2 MEMORY_BAK_POOL 4
　　3 N_MEM_POOLS 4

　　MEMORY_POOL决定了以M为单位的公共内存池的大小，上例中40M; N_MEM_POOLS决定把内存池划分为几个独立的单元，以减少并发访问的冲突，提升并发效率;MEMORY_BAK_POOL表示系统保留的备用内存量，当常规的内存申请都失败时，从这个备用内存里分配，然后在上层模块中进行必要的容错处理。

　　可以在v$sysstat中查看当前公用内存池的使用情况：

　　SQL>select * from v$sysstat where name like '%MEM%';

　　行号     ID       CLASSID     NAME              STAT_VAL
　　-------- -------- ----------- ---------------   --------------------
　　1        26       3           MEMORY USED BYTES 25831296

　　这里的STAT_VAL给出的是已经使用的字节数。正常情况下，应该小于配置的池大小，否则系统不得不从池外向操作系统申请/释放内存，造成效率低下，并可能把操作系统的内存搞得很零碎。

　　系统缓冲区BUFFER

　　为了加速数据访问，系统开辟了一个缓冲区，使用LRU算法存放经常访问的数据页，逐步淘汰不用的数据页。

　　使用下列参数，可配置基本的系统缓冲区的大小：

　　SQL>select * from v$dm_ini where para_name like '%BUFFER%';
　　行号       PARA_NAME     PARA_VALUE
　　---------- ------------  ----------
　　1          HUGE_BUFFER   8
　　2          BUFFER        4000
　　3          MAX_BUFFER    8000
　　4          BUFFER_POOLS  1

　　其中HUGE_BUFFER 是专门用于列存表的缓存区，BUFFER是用户行存表的系统缓冲区。BUFFER表示初始的系统缓冲区大小，单位为M。通常情况下，如果物理数据量大于物理内存，则应该把BUFFER调到物理内存的三分之二比较合适。

　　当BUFFER_POOLS = 1时，系统支持缓冲区的自动扩展。MAX_BUFFER表示最多能扩到多大。在自动扩展后，如果系统的压力在一段时间内比较低，系统又会自动收缩缓冲区。

　　系统缓冲区是一个共享资源，受一个mutex保护，在一个时间点，只允许一个线程可以持有这个资源。在高并发情况下，这个限制将极大降低并发效率，因此，可以配置BUFFER_POOLS把一个大的系统缓冲区分割为多个小的部分，每一个小的部分作为临界资源，这样只要所访问的数据页不在同一个子池里，就不会发生冲突，从而提升并发性能。注意，如果配置了BUFFER_POOLS > 1, 则MAX_BUFFER参数就失效了，最大可用的缓冲区由BUFFER参数决定。

　　系统缓冲区RECYCLE

　　这是DM新引入的缓冲区，专门用于缓冲临时表空间。RECYCLE的淘汰算法与BUFFER完全一样，但是它有独立的HASH表， LRU和更新链。引入 RECYCLE的目的是防止某些复杂查询的中间结果挤占大量的BUFFER空间,降低BUFFER的命中率，从而增加额外的 IO操作。

　　使用RECYCLE的场景主要有：

　　●大表的散列连接，在内存达到HJ_BUF_SIZE时使用

　　●排序，大数据量的排序操作，

　　●蓄水池操作符，如:NTTS, SPL, HTAB等，这些操作符需要把数据收集在一起

　　●临时表数据

　　●MAL系统中，堆积的邮件

　　●并行查询中，堆积的消息

　　●大字段的临时数据

　　RECYCLE的配置可以从V$DM_INI查到，单位M

　　SQL>select * from v$dm_ini where para_name = 'RECYCLE';
　　行号       PARA_NAME PARA_VALUE
　　---------- --------- ----------
　　1          RECYCLE   64

　　2. 利用缓存

　　DM的缓存机制，可以避免系统重复的SQL解析工作，比如对于非常耗时的SQL语句解析，极大提升系统性能。SQL缓存池的大小用CACHE_POOL_SIZE来设置，缺省为10M。

　　如果应用程序对SQL语句都是先准备，再绑定参数，然后反复执行，那么就不需要计划缓存了。在这样理想的模式下，每一种SQL语句都使用不同的语句句柄，并在应用程序启动之后不久就进行了准备，执行时使用相应的语句句柄，并给定不同的参数。但是这个理想模式要求有良好的应用设计，有限或很少的SQL语句形式，限制太多。因此DM数据库系统提供了计划缓存机制。

　　计划缓存由USE_PLN_POOL参数控制，当USE_PLN_POOL = 0，禁止计划缓存;当USE_PLN_POOL = 1,SQL语句需要完全匹配，才能使用计划。比如:

　　Selct * from t1 where id = 1;
　　Select * from t1 where id = 2;

　　虽然这两个语句很相似，计划也基本上一样，但是因为常量不同，不能重用计划。因此使用精确匹配，会造成大量类似重复的计划。精确匹配一般应该使用在语句非常复杂，查询很耗时的分析型场景。这类场景语句中，常量取值的不同对计划的影响很大。

　　当USE_PLN_POOL = 2时，使用模糊匹配模式。系统首先试图做精确匹配，如果没有找到合适的计划，则需要做语法分析，把常量提取出来，把语句转换为参数的形式，再从计划缓存中查找合适的计划。如果找到，则提取该计划运行，否则就需要做关系变换和代价分析，并把新生成的计划放入缓存中。

　　模糊匹配适用于大部分OLTP应用，但是系统还是需要做一遍语法分析,需要把常量分解出来,并重新把语法树反拼成一个字符串,这个过程还把多余空格,注释去掉,因此,下列两个语句都匹配成同一个计划:

　　Select * from t1 where id = 1;
　　Select /* this is a test */ * from t1 where id = 2;

　　二、 SQL优化

　　1. 定位慢的SQL

　　要分析性能瓶颈，首先得把执行的又慢、又多的SQL语句找出来，DM提供了SQL日志的功能，可以将系统中运行的SQL语句、语句绑定的参数、SQL执行时间记录到SQL日志文件中，并提供参数来进行过滤，比如只更新select语句、DELETE语句、update语句、报错的语句等等。

　　1) 修改dm.ini文件

　　SVR_LOG = 1
　　SVR_LOG_FILE_NUM =2
　　SQL_TRACE_MASK = 1
　　SVR_LOG_SWITCH_COUNT = 1000000

　　#SVR_LOG：1表示开启SQL日志功能，0表示关闭

　　#SVR_LOG_FILE_NUM：1表示不切换，2表示记录两个日志文件，互相切换

　　#SQL_LOG_MASK：要记录的语句类型掩码，1表示全部记录

　　4:7表示记录update和select语句

　　#SVR_LOG_SWITCH_COUN:每个SQL日志文件中记录的消息条数

　　#修改完成后需要重启数据库服务

　　2) 执行系统存储过程开启

　　--开启SQL日志，记录所有的SQL语句，两个文件互相切换

　　--每个文件记录100万行

　　--动态修改INI参数，无需重启服务

　　call sp_set_para_value(1,'SVR_LOG',1);
　　call sp_set_para_value(1,'SVR_LOG_FILE_NUM',2);
　　call sp_set_para_value(1,'SQL_LOG_MASK',1);
　　call sp_set_para_value(1,'SVR_LOG_SWITCH_COUNT',1000000);

　　通过上面两种方法，我们成功开启了SQL日志功能，那么如何从这些日志文件中找到性能瓶颈呢?给大家提供一个SQL日志分析小工具(http://bbs.dameng.com/forum.php?mod=viewthread&tid=44187&extra=page=1)

　　该工具运行效果图如下：

　　分析完成后，会在分析程序所在目录生成一个文件夹，保存分析结果：

　　详细的分析结果保存到一个excel文件中

　　2. 优化慢的SQL

　　既然我们已经把执行的又慢又多的SQL语句找到了,就马上开始SQL优化之旅吧。

　　SQL优化首先我们得会看SQL的执行计划，DM中查看SQL执行计划非常简单，在SQL的前面加EXPLAIN关键字即可，如下：

　　explain select * from sysobjects where subtype$='UTAB';
　　1 #NSET2: [8, 1465, 3405]
　　2 #PRJT2: [8, 1465, 3405]; exp_num(17), is_atom(FALSE)
　　3 #SLCT2: [8, 1465, 3405]; SYSOBJECTS.SUBTYPE$ = UTAB
　　4 #CSCN2: [8, 7328, 3405]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

　　--从计划可以看出，这是一个对SYSOBJECTS表的全表扫描，过滤条件是SYSOBJECTS.SUBTYPE$ = UTAB

　　--DM查询计划中各操作符详细的说明请参考下面这个帖子：http://www.itpub.net/forum.php?mod=viewthread&tid=173512。

　　另外，我们还需知道DM中如何找到SQL执行计划中各操作符的实际执行时间，如果知道了计划中各操作符的实际执行时间，那么我们就可以对慢的操作符进行针对性的优化。

　　首先我们需要修改dm.ini，修改一个参数，记录SQL执行时各操作符的实际执行时间，修改完成后需要重启数据库服务才能生效。

　　ENABLE_MONITOR = 3

　　然后我们创建一个存储过程，查询一些动态性能视图，获取我们需要的信息：

　　create or replace procedure sql_et(eid int)
　　is
　　begin
　　select
　　name as "OP",
　　time_used/1000
　　|| 'ms' as "TIME",
　　cast(time_used * 100.0/sum(time_used) over() as dec(10, 2))
　　|| '%' as "PERCENT",
　　rank() over (order by time_used desc) as "RANK" ,
　　seq_no as "SEQ"
　　from
　　v$sql_node_history a,
　　v$sql_node_name b
　　where
　　a.type$ = b.type$
　　and exec_id = eid
　　order by
　　time_used desc;
　　End;

　　准备工作做完了，下面我们就构造一个简单的例子来实际操作一下。

　　--创建表

　　Create table tx(id int, name varchar(100));
　　Create table ty(id int, name varchar(100));
　　--Create index txl01 on tx(id);
　　--Create index tyl01 on ty(id);

　　--创建存储过程

　　Create or replace procedure ins_p(cnt int) as
　　Declare
　　X int;
　　Begin
　　For x in 1 .. cnt loop
　　Insert into tx values(x, 'hello');
　　Insert into ty values (x, 'world');
　　End loop;
　　Commit;
　　End;

　　--每个表插入100万数据

　　call ins_p(1000000);

　　--分析一下下面这个相关子查询

　　select * from tx a
　　where
　　exists
　　(select * from tx b where a.id = b.id and a.name = b.name and b.ID <= 10)

　　执行一下这个SQL语句，记录下他的执行号3304：

数据库应用优化

　　查看其执行计划：

数据库应用优化

　　使用SQL_ET存储过程查看该SQL各操作符的执行时间：

　　--从操作符的执行时间可以看出：时间主要消耗在对TX表和TY表的全表扫描上了，我们有针对性的创建两个索引，将全表扫描转化为索引扫描即可。

　　Create index txl01 on tx(id);
　　Create index tyl01 on ty(id);

　　--创建索引后，我们再来看看执行计划：

数据库应用优化

　　--再来看这个SQL各操作符的执行时间

数据库应用优化

　　三、应用优化

　　1. 会话管理

　　在SQL SERVER数据库中鼓励开发人员每执行一个SQL就创建一个会话，这样能够提高性能，但在DM和ORACLE中这种应用的会话管理在性能上就是一个灾难，DM中一个会话足够能干，不需要为每一个SQL创建一个会话来执行，移植类似的SQL SERVER的应用需要注意。

　　2. 索引优化

　　索引对数据库I/O的影响十分巨大。如果通过索引来访问数据库，可以大大减少对大型表的全表扫描，从而减少I/O的开销。索引的使用通常能够提高SELECT，UPDATE以及DELETE语句的性能，但会降低INSERT语句的性能，因此索引并非是越多越好，使用索引应该遵循以下原则:

　　仅当要通过索引访问表中很少的一部分行(1%~20%)

　　如果要处理表中的多行，而且可以使用索引而不用表

　　不能利用索引的场合：

　　索引列上有函数(确定性的函数可以创建函数索引)

　　索引列存在隐式类型转换

　　3. 索引列选择率差，不如全表扫描

　　仅查询需要的列

　　很多没有经验的程序员为了贪图一时的方便喜欢使用SELECT * 来查询，这样实际上会带来很多额外的网络、IO开销，严重的时候会引发灾难。给大家分享一个案例:

　　某应用有条SQL计划非常好，索引都利用上了，但是运行非常慢：

　　SELECT * FROM MANALOG_DATA_SCADA WHERE ……

　　原因是：该表有1441列，这个业务只需要其中的3个列，却写了SELECT * 带来不必要的网络和IO的开销，导致性能低下。

　　优化方法：修改SQL语句，只查询需要的列：

　　Select c1,c2,c3 from MANALOG_DATA_SCADA where ………

　　更多精彩尽在2014年4月10日-12日在北京五洲皇冠国际酒店举办的第五届中国数据库技术大会，敬请期待！

Facebook专家：Hadoop不足以处理大数据
▲进入官网了解更多详情

关注我们

达梦数据库的性能优化 应对数据膨胀

达梦数据库的性能优化应对数据膨胀