【视频】DB2数据库应用系统性能经验谈(演讲者:张建华)

2007年8月28日在北京福乐旺斯举办的DB2技术专家沙龙活动，联信永益科技股份公司项目经理张建华以《DB2数据库应用系统性能经验谈》为主题的精彩演讲。

[b]张建华——联信永益科技股份公司项目经理学历 软件工程硕士[/b]

现在负责项目 北京网通决策支持系统一期维护/二期
数据仓库工作经验: 参加过经营分析的项目研发和针对湖南、西藏、内蒙等地的经营分析需求分析和调研工作。根据移动集团公司的规范来进行省市公司的两级需求调研,并根据需求完成相关模块主题的设计。对数据仓库的基础模型到应用模型分析开发设计的业务流程.从数据仓库的数据抽取到基础模型,从基础模型的数据转换到应用模型,多维OLAP的CUBE建模,能够对技术和业务进行框架设计。

熟悉IBM的Visual Warehouse（VW）工具和NCR的TERADATA+Informatica，框架下的元数据管理中数据抽取、转换、装载和调度。基于Essbase/DB2 OLAP Server建立多维数据库，(MOLAP混合的HOLAP）用于数据仓库建模和，又可用于ERWIN建立数据仓库的基础模型（物理和逻辑）应用，对于数据挖掘DB2 Intelligent Miner有一定的了解。 使用过DB2的项目主要有2003年-2004年的湖南移动、西藏移动经营分析系统，2006年-2007年北京网通决策支持项目。

[b]主题：DB2数据库应用系统性能经验谈——演讲者张建华[/b]

本次演讲会分三个方面：介绍数据仓库的总体情况，具体会讲到项目应用；DB2的实施介绍，主要采用单个实例，顺便提到多实例遇到的问题。采用多节点的建议表空间的管理方式；数据仓库展现的应用（根据数据仓库得到的钻取、旋转等）等。

[b]DB2数据仓库性能经验交流纪要[/b]

[b]张建华整理[/b]

[b]第一部分：[/b]
本次交流DB2的性能优化主要是基于OLAP电信行业的数据仓库和经营分析项目应用实例.对基于OLTP方面本次交流会涉及一些,但不是重点.电信行业OLAP处理的数据量是比较庞大的,一般月数据都有1百多G到几百G,数据仓库容量在若干TB以上.OLAP采用的优化方式会与OLAP方式不同.
下面是本次交流的情况,先介绍一下数据仓库的系统概况.分为总体技术架构 应用举例 本次案例的硬件配置. 然后重点介绍DB2数据库数据仓库性能经验谈 ,大致分数据仓库的性能要素、数据库架构设计、分区的使用及优化、数据仓库的空间管理、应用设计优化等五个方面讨论.
数据仓库总体技术架构这张图上可以看到,各种接口数据通过ETCL的方式抽取到DB2数据仓库中来,在本次案例中采用的是DataStage的工具.在数据接口加载方面还采用了DB2的LOAD方式,对大数据量格式化文本进行高效的加载,后面我们会有实例数据说明.电信仓库话单入库批价文本一般都采用这种高性能LOAD方式加载.比其他工具和数据库(SQL*LOAD)的加载方式快很多倍，一般项目中采用的文本接口比较多，LOAD方式也被大量采用.此外数据仓库还包含OLAP多维数据及展现、数据挖掘等功能,OLAP多维数据可以采用DB2 的OLAP Server、Cognos、ESSBASE等.本次讨论的重点是红框中DB2数据仓库的优化.
DB2中数据应用举例是介绍某项目一期的情况，根据应用数据仓库中可以分业务层、采集层、存储层、访问层。在存储层有可以细分操作层、临时层、集市层等等。每个数据仓库系统会根据自己业务要求进行数据分层或业务分层，在移动的经营分析系统和网通、联通的数据仓库中都有类似的业务分层。根据这些业务需求进行下一步的数据仓库设计和建模。
下面配置是本案例的一期的配置情况，存储3.2TB、p5-570 4*CPU、p550 2*CPU 上面有各自的应用软件。

[b]第二部分：[/b]
项目实施中数据仓库的主要性能要素有三个大的方面：架构与分区(单事例、模式、数据分层、多节点分区）、存储结构（数据空间、索引空间、日志空间、应用数据空间、合理的分区）、应用设计（表的分区键、表的索引、SQL优化、联邦数据使用技巧和跨库访问优化、数据类型的优化、物理分表）
下面这是某一个电信数据仓库的应用架构，最初采用3个实例，每个实例下面建立一个数据库，按实例方式来实现数据分层，每一个数据库分配60G的日志空间。在这种架构设计下出现了一些问题：数据层之间的DML操作都比较慢，在早些DB2 7.2的版本在LOAD的时候出现表锁和表空间锁，当应用在做大量INSERT操作的时候容易日志空间满。系统的理论峰值可以达到400M/S，实际系统的IO只有20M/S左右，最高100M/S，CPU的使用率一般在15%一下。可以看出资源利用率很低。
经过后来的优化，项目上采用一个实例，在一个实例上建立一个数据库，数据层采用模式来区分，事例上采用多个数据节点来提高系统的并行度。将上面的三个数据库的日志合成一个大的180G日志，由优化后的唯一一个数据库使用，避免了日志空间满的情况。采用多个数据节点以后，数据的处理能力提高了N倍（与节点数有关），数据加载能力得到提高。系统优化后，一般应用IO在100M/S，最高时能达到系统峰值390M/S，CPU利用率最高时候达99%。应用执行时间大大的缩短到原来的1/4。关于优化的情况，后续再详细介绍。
在系统采用一个实例和多个分区节点后，在数据表空间上根据业务应用情况，把帐单、详单、客户资料等数据的表空间分开，能够避免在并行LOAD的时候发生表空间锁。同时也能够提高系统的IO.根据下面给出的数量级和执行时间，可以看出在优化后的LOAD的加载速度非常快。这一方面比ORACLE的SQL LOAD的快很多倍。采用分区有很多好处，但是再某些方面可能需要注意，如图：某一个资料表，尤其CDR这种大的数据表的分区键很重要，理想情况是每个节点分配均匀，但也会出现不均匀的情况。
在表空间管理方面就上面提到的一个数据库考虑多个数据表空间，同时将索引、日志空间独立开。日志表空间为应用程序最大日志空间。在数据表空间里面考虑到的是分区键导致数据表空间利用率不高。如图所示，由于分区键中有大量默认值或重复值导致某个表空间满了，数据却加载不进去，而其他几个表空间还有很多剩余。此时考虑改变分区键，同时旧的分区键可以建索引。具体可以通过select count(分区键) , dbpartitionnum(分区键)   from 表  group by dbpartitionnum(分区键) 方式检查。
再系统进行空间分配时，考虑使用裸设备方式，此比采用文件目录方式能提高一定的系统IO. 建表时，将数据和索引表空间分开，并设置好合理的分区键。
在考虑系统优化的同时，也要考虑应用程序的优化。在应用程序中或存储过程中有大量的DML事物，在OLTP情况下，如果大量的事务做完DML以后不及时提交，会导致大量的行锁，等待时间过长，则产生雪崩效应。在OLAP情况下，一个事物处理的数据量是非常大的，在每个DML操作时候都要及时提交，避免导致升级锁。当然可以通过配置数据的参数MAXLOCKS列表的百分比。 LOCKLIST锁资资源大小 。LOCKTIMEOUT,锁等待超时时间。
应用优化也要考虑到索引优化，在系统建模的时候根据E-R关系图可以确定表之间的主外键关系，同时也能确认个表之间的关联关系，这些信息对建立索引是有帮助的。在OLAP的应用中，一般电信的数据量都在几百万与几千万的数据关联，有的CDR数据几亿，如果不考虑这些表的索引，整个事物执行时间很长，系统资源大量的浪费。在多表关联的时候如何使COST最小呢？有几个办法可以实现，首先可以考虑DB2的Design Advisor确定最佳索引，其次考虑执行计划。在考虑执行计划的时候，REORGCHK 根据统计公式，计算表是否需要重整，如果修改表的很多信息，可以先reorg，再用runstats 更新统计信息，这操作耗很多资源，建议一般晚上空闲时候执行。最终优化的目的是，表与表之间的关联是索引扫描而不是使用表扫描，如果全部是索引扫描目的达到了，速度还慢则考虑提高机器配置或者应用优化（分表或者9版本的表分区技术）。例如：项目中有一个SQL使用的数据关联在百万左右，由于索引建立不合理，一个多小时才执行完，通过工具优化索引以后，这个SQL执行时间只使用到秒级.
在OLAP应用可以通过数据类型的优化，上面几种类型的优化一般在项目中应用得比较多，但是上面的方法不建议在OLTP方面应用，比如时间类型如果非采用整型，不利于OLTP方面的DAYOFWEEK等时间函数的应用。通过数据类型的优化，可以节约很多的数据空间，比如采用VARCHAR（18），改位整型，在数据存储空间减少很多。数据如有失真情况，则根据业务情况来定。在整型做关联的时候比字符型能够提高很速度。通过合理的数据类型有两点好处：1.提高数据存储空间的利用率，2.不同数据类型应用时能提高关联速度。最后举一例，100G的文本数据LOAD到数据库中，一般会使用到120G-190G的数据空间。如果采用合理的类型，我们出现过只使用90G的数据空间，比文本数据空间还少。
在电信的大数据量方面需要考虑分表技术，在db2  9的版本可以考虑表分区技术。才用多节点的方式能够提高并发性，但对于大量的CDR数据则根据自己系统的情况，可以按时间的月份或日期、按地区来进行分表。
大数据量的情况如果采用物理分表的方式，在应用上考虑采用动态SQL来实现业务。下面举一存储过程的例子，参数可以输入分表的参数，根据这些参数，后台形成动态的SQL.如果采用分区表的方式则用采用动态SQL方式。在OLAP的方式中，一个简单的DELETE操作是特别消耗系统资源的，如果经常遇到异常情况，整个业务流程需要重做的话，可以采用分表的技术可以避免频繁的删数据使用DELETE操作，避免DEL时候产生大量回滚日志，它可以采用很多快速办法，如：load from /dev/null of ixf replace … … ;DROP table-CREATE table, alter table . activate not logged initially with empty table;

[b]第三部分：[/b]
若干业务流程经过DB2的加工处理，按主题方式可以进行多维展现。下面是一个典型的雪花模型，若干事实表和维表关联，在DB2中实现基于数据库的ROLAP分析。当然也可以通过MOLAP方式进行展现，如：DB2 OLAP SERVER、Hyperion ESSBASE、Cognos等软件。前端工具进行旋转切片钻取等功能，通过导航仪界面进行多维表格、图形展现，此部分由于时间原因就不做演示了。

专辑地址：[url]http://www.56.com/w13/play_album-aid-2664395_vid-MjA5MjU0NTI.html[/url]

[[i] 本帖最后由 艾依然 于 2007-10-19 11:30 编辑 [/i]]

第一节

[align=center][flash]http://www.56.com/n_v198_/c31_/28_/15_/ibm-club_/zhajm_119270423123_/1083520_/0_/20925452.swf[/flash][/align]

第二节

[align=center][flash]http://www.56.com/n_v198_/c31_/28_/29_/ibm-club_/zhajm_119270179574_/1303160_/0_/20923531.swf[/flash][/align]