转载

DB2 索引创建时间过长的性能优化案例

概述

索引对于改善数据库查询操作性能至关重要，但在大表或者超大表上创建索引可能会非常的耗时！本文将通过具体案例说明优化思路以及具体步骤。

回页首

问题描述

客户 A 数据仓库系统采用 IBM DB2 数据库，每日夜间从 OLTP 系统抽取数据，由于随着时间的增长及数据的积累，该表变得越来越大，以至该 ETL 批处理作业无法在业务要求的时间窗口内完成，影响下游数据业务，经分析该 ETL 作业主要消耗在创建索引这一过程上。

清单 1. 创建索引命令

db2fox@bivm:~/test> db2batch -d fox -f sample.sql /*db2batch 可以观察 SQL 执行时间等信息*/ * Timestamp: Sat Aug 01 2015 23:09:11 EDT --------------------------------------------- * SQL Statement Number 1: create index cust.i_samp_name on cust.samp_1(CUST_NAME,CUST_ADD,CUST_NUM); * Elapsed Time is: 210.495788 seconds

回页首

问题分析与解决

步骤一：分析影响索引创建速度的参数设置

DATABASE MANAGER：

创建索引的过程可近似的认为对全表扫描后将所得键值进行排序后生成索引的过程，因此可能影响索引创建性能的参数可能有：

SHEAPTHRES
INTRA_PARALLEL

SHEAPTHRES 参数影响创建索引时键值 SORT 过程，但由于 DB2 引入了自动内存管理机制，即：

INSTANCE_MEMORY，且该参数已经设置为 AUTOMATIC，DB2 将会根据需要自动决定内存的分配，因此暂不考虑修改该参数。

INTRA_PARALLEL 参数影响索引创建的全表扫描过程，该参数可以使得 DB2 服务器采取“并行”的方式处理事物，对于大表全表扫描尤为有效，因此可尝试将该参数开启。

DATABASE：

影响索引创建性能的 DATABASE 参数可能包含：

BUFFER POOL
SHEAPTHRES_SHR
SORTHEAP
NUM_IOSERVERS

BUFFER_POOL 如果过小将影响命中率，增加 I/O 负担。检查该系统后确认 BUFFER_POOL 设置基本合理，因此暂不考虑对其进行调整。

SHEAPTHRES_SHR 和 SORTHEAP 参数将影响索引创建过程中 SORT 操作，经检查这两个参数均采用了 AUTOMATIC 设置，因此暂不考虑进行调整。

NUM_IOSERVERS 影响全表扫描 I/O 读写性能，经检查该参数已经设置为 AUTOMATIC，因此暂时不考虑进行调整。

步骤二：修改相关参数并验证其效果

该系统有 4 颗 CPU，10G 物理内存，为了使得 DB2 可以“并行”工作，因此决定修改 INTRA_PARALLEL 参数。

清单 2. 修改相关参数

db2fox@bivm:~> db2 get dbm cfg |grep -i INTRA_PARALLEL  Enable intra-partition parallelism (INTRA_PARALLEL) = NO db2fox@bivm:~> db2 update dbm cfg using INTRA_PARALLEL Yes DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed  successfully. db2fox@bivm:~> db2 get dbm cfg |grep -i INTRA_PARALLEL  Enable intra-partition parallelism (INTRA_PARALLEL) = YES /*启用该参数*/

清单 3. 重新执行创建索引的命令，验证优化效果

db2fox@bivm:~/test> db2batch -d fox -f sample.sql * Timestamp: Sat Aug 01 2015 23:19:58 EDT --------------------------------------------- create index cust.i_samp_name on cust.samp_1(CUST_NAME,CUST_ADD,CUST_NUM); * Elapsed Time is: 100.489176 seconds /*执行时间已经有明显的改善*/

重新执行 db2batch -d fox -f sample.sql 验证其优化效果，可以看出创建索引时间已经有明显改善，但依然没有满足业务期望。

步骤三：分析操作系统相关性能

由于创建索引的过程可能会引起大量的 I/O 操作，在索引创建之初要执行大量的 I/O“读”操作，而在索引创建结束前要执行大量的 I/O“写”操作，通过操作系统命令 vmstat 可以观察到在索引创建过程中存在大量的 I/O 等待，且主要集中在索引创建结束前。

清单 4. VMSTAT 输出结果

0 0 0 3223424 24508 1563336 0 0 0 0 55 171 0 0 100 0 0  0 0 0 3223424 24508 1563336 0 0 0 0 68 221 0 0 100 0 0  1 0 0 3223440 24508 1563336 0 0 0 0 91 290 1 0 99 0 0  0 0 0 3223440 24508 1563336 0 0 0 0 98 304 1 1 99 0 0  0 0 0 3223432 24508 1563336 0 0 0 0 87 271 0 0 100 0 0  1 0 0 3223440 24508 1563336 0 0 0 0 103 328 0 0 100 0 0  1 0 0 3213456 24516 1563328 0 0 0 48 158 447 1 2 96 2 0  0 0 0 3222944 24516 1563332 0 0 0 0 179 443 3 2 95 0 0  1 0 0 3208180 24532 1573700 0 0 12077 180 611 1060 20 7 46 27 0  1 0 0 3207924 24568 1573980 0 0 35748 0 884 1631 31 7 37 26 0  1 0 0 3207932 24592 1574020 0 0 23704 8 548 1146 20 5 36 40 0  1 0 0 3207932 24612 1574016 0 0 23828 0 687 1191 27 6 39 28 0  1 1 0 3207932 24628 1574024 0 0 12984 0 525 731 27 6 31 37 0  0 2 0 3207932 24644 1574032 0 0 19176 0 640 1054 18 8 23 51 0  0 2 0 3207956 24664 1574028 0 0 19992 0 670 1071 29 4 34 34 0  1 0 0 3207964 24676 1574040 0 0 19212 0 712 1117 27 4 30 39 0 procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------  r b swpd free buff cache si so bi bo in cs us sy id wa st  1 3 0 3207592 24680 1573456 0 0 5848 14320 933 1700 36 10 18 36 0  1 3 0 3207660 24688 1573124 0 0 0 12896 750 1557 42 13 16 28 0  0 1 0 3207700 24688 1573180 0 0 0 11192 696 1563 16 7 31 45 0  0 2 0 3207692 24688 1573180 0 0 0 6804 1148 2188 0 9 37 54 0  1 2 0 3207700 24688 1573180 0 0 0 5220 1195 2200 3 11 33 54 0  2 2 0 3207700 24688 1573180 0 0 0 8796 1285 2576 1 10 40 49 0  1 1 0 3207700 24688 1573180 0 0 0 7816 1298 2421 1 9 37 53 0  2 1 0 3207700 24688 1573180 0 0 0 5948 1578 3503 1 9 47 43 0  2 2 0 3207692 24688 1573180 0 0 0 7032 1639 3377 1 12 37 50 0  1 0 0 3207700 24688 1573180 0 0 0 5328 1440 2667 0 10 44 46 0  0 2 0 3191076 24716 1580404 0 0 5844 4072 870 1906 11 12 5 72 0  1 1 0 3187772 24748 1581992 0 0 1546 2812 1520 4143 10 17 19 54 0  0 1 0 3187796 24748 1581952 0 0 0 5736 1518 3092 1 13 47 39 0  0 0 0 3192508 24748 1581952 0 0 10 3282 1514 3179 0 6 64 31 0  0 0 0 3192508 24748 1581952 0 0 0 4 92 214 0 0 100 0 0  0 0 0 3192508 24748 1581952 0 0 0 0 88 197 0 0 100 0 0  0 0 0 3192508 24748 1581952 0 0 0 0 99 219 1 0 99 0 0  0 0 0 3192508 24748 1581952 0 0 0 0 99 221 0 1 99 0 0  0 0 0 3192516 24748 1581952 0 0 0 0 277 699 2 1 98 0 0

步骤四：重新分析可能影响系统 I/O 的参数

索引创建生成的日志是当索引所有键值生成完成后统一写入 LOG 中的，而不是每生成一个键值就写入一次 LOG，如果大量 LOG 一次性写入将引起 PAGE 被同时大量写出，而增加 I/O 压力。检查该系统注意到：LOGFILSIZ 为 2G 每个，而 CHNGPGS_THRESH 设置为 80，该参数设置表明：当 LOG 文件 80%“满”的时候将会把 PAGE“刷”到磁盘上，这将引起大量的 I/O 写操作，从而引起 I/O 等待。

步骤五：修改相关参数并验证其效果

经以上分析，可将 CHNGPGS_THRESH 改小，以使得 PAGE 被分步的“刷”到“磁盘”以及减少一次性写入“磁盘”而带来的 I/O 压力。

清单 5. 修改 CHNGPGS_THRESH 参数配置

db2fox@bivm:~> db2 get db cfg for fox |grep -i LOGFILSIZ  Log file size (4KB) (LOGFILSIZ) = 524288 db2fox@bivm:~>  db2fox@bivm:~> db2 get db cfg for fox |grep -i CHNGPGS_THRESH  Changed pages threshold (CHNGPGS_THRESH) = 80  db2fox@bivm:~> db2 update db cfg for fox using CHNGPGS_THRESH 20 db2fox@bivm:~> db2 get db cfg for fox |grep -i CHNGPGS_THRESH  Changed pages threshold (CHNGPGS_THRESH) = 20 /*修改后参数为 20*/

重新执行 db2batch -d fox -f sample.sql 验证其优化效果，效果非常明显，已经满足业务要求。

清单 6. 验证优化效果

db2fox@bivm:~/test> db2batch -d fox -f sample.sql * Timestamp: Sun Aug 02 2015 00:16:42 EDT --------------------------------------------- * SQL Statement Number 1: create index cust.i_samp_name on cust.samp_1(CUST_NAME,CUST_ADD,CUST_NUM); * Elapsed Time is: 11.232249 seconds /*执行时间已经大幅缩小*/

回页首

后续问题

索引创建性能问题解决后又出现了新的问题，“下游”查询该表作业相比以前执行时间大幅增加！由于并未对相关程序进行过修改，因此怀疑是该表的“统计信息”不准确而导致 DB2 优化器选择了错误的执行计划，从而导致了性能问题，因此首先检查该表的统计信息。

清单 7. 检查表的统计信息

db2fox@bivm:~/test> db2 "select stats_time from syscat.tables where tabname='SAMP_1'" STATS_TIME  -------------------------- 2015-01-05-21.44.13.824913 /*该日期显示该表已经很久没有进行了统计信息收集了*/  1 record(s) selected. db2fox@bivm:~/test>

更新该表上的“统计信息”以使得 DB2 优化器可以选择正确的执行计划。

清单 8. 收集相关表的统计信息

db2fox@bivm:~/test> db2 runstats on table cust.samp_1 with distribution on key columns  and detailed indexes all shrlevel change DB20000I The RUNSTATS command completed successfully. db2fox@bivm:~/test>  db2fox@bivm:~/test> db2 "select stats_time from syscat.tables where tabname='SAMP_1'" STATS_TIME  -------------------------- 2015-08-02-17.18.40.270036 /*该日期显示该表已经有了最新的统计信息*/  1 record(s) selected. db2fox@bivm:~/test>

重新执行该作业，问题解决。

统计信息对 DB2 优化器计算正确的执行计划至关重要，DB2 根据统计信息计算出访问数据的最佳途径，如果统计信息“缺失”或者过于陈旧，将导致 DB2 优化器无法计算出最佳的访问计划，由此可能引起严重的性能问题。在一些经常变化的表上要经常检查统计信息的状态，在一些诸如：REORG TABLE、CREATE INDEX、TRUNCATE TABLE 等操作后也要考虑对统计信息更新，以便 DB2 优化器能够根据最新统计信息计算出最佳的执行计划。

回页首