达梦数据库的性能优化

达梦数据库的性能优化 “棱镜门”、“微软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日志分析小工具（）   该工具运行效果图如下： 分析完成后，会在分析程序所在目录生成一个文件夹，保存分析结果： 详细的分析结果保存到一个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查询计划中各操作符详细的说明请参考下面这个帖子：。   另外，我们还需知道DM中如何找到SQL执行计划中各操作符的实际执行时间，如果知道了计划中各操作符的实际执行时间，那么我们就可以对慢的操作符进行针对性的优化。 ? 首先我们需要修改dm.ini，修改一个参数，记录SQL执行时各操作符的实际执行时间，修改完成后需要重启数据库服务才能生效。 ENABLE_MONITOR = 3   ? 然后我们创建一个存储过程，查询一些动态性能视图，获取我们需要的信息：