数据分析系统性能调整

　　银行数据大集中之后，业务部门越来越迫切地希望能从现有的数据中找到对开展业务有价值的信息，提供更多的辅助功能。在此背景下，出现了各种各样的分析系统，有的银行正在规划数据仓库（ＤＷ）的开发，有些银行已开发了客户关系管理（ＣＲＭ）系统。

　　ＤＷ与ＣＲＭ都以数据分析为基础，有的称之为决策支持系统（ＤＳＳ），有的称之为商务智能（ＢＩ）。但无论是ＣＲＭ还是ＤＷ，都不容易实现，即便在欧美发达国家已经有了成功的实践经验，国有四大商业银行的账务数据量之大和客户数之多可能是欧美任何一家大银行都无法相比的。在欧美，如果银行的客户数或账户数超过一千万就是大型银行了，与我国四大国有商业银行的一些省级分行的数据量相当。

　　要在四大国有商业银行实施数据分析系统，即使使用最先进的计算机设备，最先进的应用系统，最科学的算法，都不得不面临一个共同的问题——性能调整。

　　一、数据分析系统与联机交易系统性能的度量区别

　　数据分析系统对响应速度的要求虽然没有联机交易系统高，但也有一定的业务要求，每天增量的数据转换、装载、抽取、更新等必须在规定许可的时间内处理完。

　　响应时间和吞吐量仍是数据分析系统最为关注的两个问题，与传统的ＯＬＴＰ系统不同的是：
　　１．ＯＬＴＰ系统重在联机事务处理的响应速度、吞吐量；数据分析系统重在查询，只有少量的联机事务处理，其吞吐量主要表现为单位时间内允许的查询数。
　　２．ＯＬＴＰ系统的查询交易一般仅返回一条或很少的记录；数据分析系统的查询有两个响应  http://www.csai.cn  ①从提交查询的时刻到第一条记录返回给最终用户时刻之间的时间，②从查询提交到返回所有记录之间的时间。

　　二、数据分析系统性能调整的过程

　　１．首先建立并定义性能指标。在系统正式投入生产之前就有明确的性能需求，数据量与要求实现的功能决定了要采用的系统平台。
　　２．制定并实施性能监控方案。如什么时候监控，监控的周期，并及时记录监控的结果（包括各应用程序运行时各类资源的占用情况、响应速度、各数据表的数据量及访问频率等）。
　　３．根据监控记录分析各度量元素（响应速度、吞吐量、ＣＰＵ、内存、Ｉ/Ｏ等）是否满足预期定义的指标。
　　４．确定主要约束是ＣＰＵ、内存、网络，还是Ｉ/Ｏ，如ＣＰＵ已达到或超过８５％的利用率等。
　　５．确定可以调整的资源。了解哪些方面可负担得起性能改进，哪些资源可承受附加的负荷。
　　６．调整数据库配置。每次更改一个，最好不要同时更改多个参数。
　　７．如果调整数据库配置不起作用，则应调整操作系统或硬件配置。
　　以上是一个重复的过程。

　　三、应用设计

　　性能调整不仅是系统管理员的事，更需要应用开发人员的配合。不好的应用设计会吞噬掉机器的性能。

　　１．使用存储过程

　　为了提高处理性能，应尽可能采用存储过程而不采用嵌入ＳＱＬ。存储过程主要有以下优点：
　　（１）同发出单一的ＳＱＬ语句或发送整块的ＰＬ/ＳＱＬ文本到服务器相比，信息只需发送一次，减少了交互，通过网络传送的信息量较少。
　　（２）存储过程经创建、分析、认证、编译等处理后，存放在数据库管理系统（ＤＢＭＳ）服务器，ＳＱＬ语句是静态的，可以即时调用，执行时不需要再编译。如果存储过程已经在共享内存中，就不需要从磁盘中读取，可以立即执行。
　　（３）减少对内存的需求。多个用户执行时，只需将过程的单个拷贝即可，多用户共享同样的代码。
　　（４）连续的执行以较小的开销实现，从而改善性能。动态ＳＱＬ每次运行都需要较大的预处理开销。与表和索引一样，系统可以收集存储过程的统计信息，优化器可以创建最合适的访问计划。

　　有数据表明，实现同一个功能，采用存储过程只需采用嵌入ＳＱＬ的２０％的时间。
使用存储过程还比较安全，存储过程的执行需要有该存储过程定义者或所有者的权力，这样就限制了用户能够执行的数据库操作。同时，使用存储过程还能提高软件开发的生产率，可以把存储过程看成面向对象设计中的经过封装的类，其他存储过程或应用程序为完成某个功能，可以继承该存储过程而不需要重写ＳＱＬ语句。如果需要修改该存储过程功能，也不必对所有继承该存储过程的应用进行修改，可使修改的工作量降到最小，保证了应用程序的完整性与一致性。另外，存储过程也是可重用的单元，对提高可靠性也有所帮助。

　　２．使用Ｃ程序设计语言作为补充

　　不要使用Ｃ＋＋程序设计语言处理数据，更不要使用Ｊａｖａ程序设计语言。在所有的高级程序设计语言中，Ｃ程序设计语言的运行效率是公认的，可以使用Ｃ程序设计语言作为补充。存储过程有自身的缺陷，如编程语言ＳＱＬ功能较差，与编程环境集成不够，移植性差，能提供的内部功能函数有限，执行过程中能提供的信息有限，不便调试等，对于一些特定的业务需求目标和数据的具体情况，可能用嵌Ｃ更好。

　　３．建立索引

　　建索引是提高数据访问效率的重要途径，但建索引有时并不一定能起到作用，索引不能满足未知需求，有时会适得其反，降低系统性能。关于索引，可归纳出以下几点：
　　（１）索引数尽量少。当要在一个合理的时间内结束查询时，应避免添加索引。过多的索引会降低更新操作的速度并消耗额外的空间，使磁盘有效使用率降低，增加系统的复杂性和管理成本。低速查询不一定表示数据库或索引有问题，很可能是程序有问题。
　　（２）基数较大的列很适合用来做索引。如果索引的惟一值有限（如＜１００），则不宜使用索引。
　　（３）最好不选择大的列作索引。
　　（４）考虑到管理上的开销，应避免在索引中使用多于 ５ 个的列，避免覆盖多个查询的大型索引。
　　（５）对于多列索引，将查询中引用最多的列放在定义的前面。
　　（６）添加与已有索引相似的索引会给优化器带来更多的工作，降低更新操作的速度。如果需要，可以修改已有的索引，使其包含附加的列。
　　（７）在经常进行连接，但没有指定为外键的列上建立索引。
　　（８）在频繁进行排序或分组的列上建立索引。
　　（９）在需要时建群集索引。群集索引允许对数据页采用更线性的访问模式，允许更有效的预取，并且有助于避免排序。这样查询操作会更快，但插入操作会慢。适合插入更新频率不高的表。
　　（１０）确保最优的索引使用。

　　４．优化查询

　　优化查询的出发点是如何减少查询的资源利用：减少Ｉ/Ｏ，减少运算量。可以用Ｓｅｔ Ｅｘｐｌａｉｎ命令分析各种查询语句的效率。
　　（１）把频繁使用的小表放在内存或高速缓存中，可以明显减少Ｉ/Ｏ。
　　（２）减少所读的行数和所读或更新的列。如，仅读取满足条件的第一条记录，仅读取或更新必须的列。
　　（３）用临时表缩小查询范围。
　　（４）尽量避免子查询和明确关联的子查询。
　　（５）避免复杂规则表达式。
　　（６）尽量使用数据库引擎提供的函数，如：ｓｕｍ，ａｖｇ，ｍｉｎ，ｍａｘ。
　　（７）尽量使用连接而不用嵌套循环，避免连接长字符串。
　　（８）尽量用Ｗｈｅｒｅ缩小连接范围。
　　（９）避免不必要的大表的全表搜索。

　　５．数据结构与算法设计

　　（１）尽量采用整数类型

　　整数是最常用的数据类型，在运算字符串变量时计算机先把字符串中的每一个字符逐一转换为整数，然后再对每一个整数分别进行运算。计算机软件处理整数的效率要比处理字符串快２ｎ倍，其中ｎ是字符串中字符的个数。

　　所以，在设计表结构时，一些计算时频繁用到的字段，如分支机构代码、系统用户代码、客户代码等，应尽量采用整数类型。

　　（２）少用可变长字符串

　　 对少于或等于３０个字节的字段，应避免使用可变长字符串（ＶＡＲＣＨＡＲ）数据类型。在这种情况下，ＶＡＲＣＨＡＲ类型通常会浪费空间，所以建议使用ＣＨＡＲ 类型。如果数据量很大，空间的浪费往往会对查询时间造成影响。

　　（３）数据模型

　　为了减少数据冗余，数据建模时一般都需要规格化达到第三范式，如果需要冗余也是因为外键关系和数据引用完整性的需要。适当的非规格化虽然需要占用更多的空间，但通过减少频繁的连接、聚集和推导可以提高数据查询的性能。例如，客户经理号与客户经理名，客户经理名依赖客户经理号，如果在客户经理业绩表中仅有客户经理号，在查询客户经理业绩时就需要做表的关联，从而降低查询响应速度。非规格化以数据更新和数据空间的损失为代价换取数据访问的优化。一般的非规格化形式有：列复制，创建数据阵列，预连接表，预聚集数据。

　　（４）算法设计

　　良好的算法，对提高性能有很大的帮助。对于一个业务功能，可以用多种算法去实现，因此应该评估每种算法的执行效率，从而选择最优算法。

　　这里说一下排序。是排序，就要耗费资源，所以应尽量减少排序，避免不必要的排序，简化排序。可以将数据放在临时表中以避免排序，减少所需排序的行数，用简单关键字排序，排序较少或较小的列，以简化排序。

　　如果要排序应尽量使用内排序，避免使用外排序。外排序和内排序相比较要慢很多，而且磁盘排序会消耗临时表空间中的资源。

　　四、数据库系统参数调整

　　ＯＬＴＰ系统为了达到更新交易吞吐量的最大化，一般使用缓冲日志，增加物理日志长度，最大化写入缓冲百分比。为防止任何ＤＳＳ类型的查询占用系统资源，可通过将相关并行查询参数配置设为较小值以限制并行查询的资源（如Ｉｎｆｏｒｍｉｘ的ＰＤＱＰＲＩＯＲＩＴＹ、Ｏｒａｃｌｅ的ＰＱＯ并行查询选项、ＤＢ２的ＩＮＴＲＡ＿ＰＡＲＡＬＬＥＬ、ＣＵＲＲＥＮＴ ＤＥＧＲＥＥ等）。

　　要使查询引擎处理的查询数最大化（吞吐量），以Ｉｎｆｏｒｍｉｘ为例，可以将相关的并行查询参数设为ＰＤＱＰＲＩＯＲＩＴＹ≤２５％。

　　要使单个查询的处理时间最小化（响应速度），可设置ＰＤＱＰＲＩＯＲＩＴＹ＝５０％，最好采用快速ＣＰＵ多处理器。

　　以上看起来似乎有些矛盾，但应根据实际情况平衡优先级。在不同的运行时间或针对不同的执行任务，使用不同的设置。例如，日常工作时间用户数较多时，将ＰＤＱＰＲＩＯＲＩＴＹ设置为较低，同时限制一些大数据量的查询，大数据量的查询可以放在非工作时间做，可设置较高的ＰＤＱＰＲＩＯＲＩＴＹ。

　　一些数据库管理系统允许用户设置页面大小，如ＤＢ２，对于ＯＬＴＰ应用程序采用较小的页面更为可取，这样消耗的缓冲池中的空间更少。对ＯＬＡＰ应用程序，通常使用较大页面，可以减少在读取特定数量的行时发出的Ｉ/Ｏ请求的数量。较大的页面还可以减少索引中的层数。但是，如果行长度小于页面大小的１/２５５，则每页中都将存在浪费的空间，因为每页最多只能有２５５行（对于索引数据页不适用）。在这种情况下，采用较小的页面大小或许更合适一些。

　　ＤＢ２、Ｉｎｆｏｒｍｉｘ、Ｏｒａｃｌｅ都有许多参数可以调整，这里不一一叙述。

　　五、硬件系统方面的考虑

　　对于数据分析系统而言，由于系统用户较少，网络一般不会成为瓶颈。所以系统方面主要考虑处理器、内存和磁盘，原则是并行性相较于速度而言对提高性能更有效。

　　对于大型分块表，使用多ＣＰＵ可以并行搜索每个分块。增加ＣＰＵ个数可以支持更多的用户数并增加并行性。ＣＰＵ越快，越能支持复杂查询和大型数据库。处理器的缓冲区越多就越快，多层缓冲可以帮助平衡处理器负荷。

　　一般来说，处理器、内存越多越好，但在现实环境中，数据处理的瓶颈常在输入/输出上，存储器与计算机间的数据传输速度比计算机运算速度一般慢２～３个数量级，磁盘速度的提高远远落后于ＣＰＵ。我们在多个数据分析系统中发现存在Ｉ/Ｏ瓶颈。

　　系统中磁盘利用率一般不应超过４５％。磁盘除了考虑磁盘机速度、控制器与通道速度外，还要考虑当前和最大驱动器数（磁盘多多益善，多些小驱动器比少些大驱动器好，新驱动器较快但较大）。

　　如果为了提高可靠性使用了ＲＡＩＤ磁盘设备，为提高Ｉ/Ｏ性能，最好采用ＲＡＩＤ１０而不是ＲＡＩＤ５。在ＤＢ２中可以用ＤＢ２＿ＰＡＲＡＬＬＥＬ＿ＩＯ注册表变量，强制对由多个物理磁盘组成的单个ＲＡＩＤ容器表空间执行并行Ｉ/Ｏ。

　　使用裸设备、对存储空间分块可以显著提高系统性能。

　　１．使用裸设备

　　Ｉ/Ｏ操作有两种方式：文件系统或ｃｏｏｋｉｅｄ ｆｉｌｅ（熟文件）和裸设备Ｒａｗ Ｉ/Ｏ（或原始设备、生设备）。

　　文件系统又分为缓冲（Ｂｕｆｆｅｒｅｄ）Ｉ/Ｏ、内存映射（Ｍｅｍｏｒｙ Ｍａｐｐｅｄ）Ｉ/Ｏ、ＤＩＯ、ＣＩＯ。以往最常用的文件系统为Ｍｅｍｏｒｙ Ｍａｐｐｅｄ Ｉ/Ｏ、Ｂｕｆｆｅｒｅｄ Ｉ/Ｏ。ＤＩＯ可以在文件系统级越过ｃａｃｈｉｎｇ，减少ＣＰＵ负载。２００３年ＩＢＭ在ＡＩＸ ５Ｌ ｖｅｒｓｉｏｎ ５．２．１０文件系统ＪＦＳ２推出了ＣＩＯ，包含了ＤＩＯ的优点，多线程可以同时读写共享的文件，但性能仍比裸设备Ｒａｗ Ｉ/Ｏ低。

　　与文件系统相比，裸设备有以下优点：
　　（１）空间是物理连接的，由一大块相邻磁盘空间构成，数据库管理引擎不需要跳过磁盘寻找数据时，能大大提高性能。熟文件的磁盘空间可能由多个Ｕｎｉｘ文件共享。连续的磁盘空间是设计表格与索引的重要因素。
　　（２）数据直接在共享内存与磁盘之间传输，不需要操作系统缓冲过程，从而加快了响应速度。数据库管理引擎对数据库操作优化Ｉ/Ｏ，避免其他涉及操作系统的步骤，而文件系统的实现要通过ｃａｃｈｉｎｇ和锁机制。
　　（３）利用裸设备能更好地保证系统出现故障时恢复数据。
当然，文件系统使用比较方便，Ｒａｗ Ｉ/Ｏ对存储管理要求比较高。Ｉ/Ｏ按性能速度从高到低为Ｒａｗ Ｉ/Ｏ、ＣＩＯ、ＤＩＯ、Ｍｅｍｏｒｙ Ｍａｐｐｅｄ Ｉ/Ｏ、Ｂｕｆｆｅｒｅｄ Ｉ/Ｏ。有许多测试表明，裸设备比熟文件快１５％～２５％。

　　２．磁盘分区

　　ＤＢ２、Ｉｎｆｏｒｍｉｘ、Ｏｒａｃｌｅ建表时都可以确定Ｅｘｔｅｎｔ初始值。Ｉｎｆｏｒｍｉｘ建议Ｅｘｔｅｎｔ不超过３３个。ＤＢ２对Ｅｘｔｅｎｔ Ｓｉｚｅ的建议为估计空间大小/４ｋ×１６Ｍ。Ｏｒａｃｌｅ Ｅｘｔｅｎｔ的管理很灵活，除了操作系统自身的限制外，可以指定Ｅｘｔｅｎｔ数量的最大和最小值。

　　数据分析系统包含了当前和历史的数据，一般数据量都很大，需要分布存放。一些数据库管理系统有限制：如在一个分区上的总页数不能超过１６Ｍ页，如果超过这个限制，为了提高性能则需采用分布存储方式，把数据存放在不同的物理设备上，通过并行读写可以有效利用多磁盘的Ｉ/Ｏ带宽，实现真正的数据分布并发处理。如果存在磁盘瓶颈，应确保表空间分布在所有可用磁盘上。如果磁盘利用率仍然很高，可能需要更多的磁盘。

　　数据分块的方法有范围分割法、哈希分割法、循环分割法、表达式分割法等，哈希分割法普遍使用于数据仓库。如：对于交易数据可以按交易日期存放，对账户数据可以按所属分支机构存放。各数据库管理系统可以采用的分割法可能不同。

　　给数据对象分配空间时应注意：
　　（１）把频繁访问的表尽量放置到不同的表空间中。
　　（２）把索引和表数据分开放置到不同的表空间中。
　　（３）高增长表不要放到同一个表空间中。
　　（４）把经常使用的表存放在磁盘中间。

　　数据分布存放的另一个好处是，可以提高数据的安全性，某个表空间损坏不至于影响到其他表空间。

　　六、运行规划

　　数据分析系统必须每天装载、更新生产交易系统下传的数据。系统从初始化开始正常运行后，数据会随着业务的增长而增长，分析功能及用户也会随之增长。每天在生产交易系统下传数据到达后，需在规定的有限时间里完成这些任务。所以做好运行设计，不但要考虑现在每天的任务安排，还要考虑未来的增长情况。

　　运行规划包括以下几个部分：

　　（１）首先确定批处理系统可用时间窗口和终端用户可用时间窗口。例如，每天早晨三点收到总行数据中心的数据，终端用户在上午九点就要查看到最新数据，这之间有六个小时可以用来做批处理。
　　（２）确定任务完成窗口。估算数据抽取、清理/转换、装载需要的时间，估算汇总统计、更新等正常业务功能处理需要的时间，各种查询的响应时间，各种任务的执行周期等。确定哪些必须在终端用户工作时间之前完成，哪些可以在终端用户工作时完成，哪些可以在终端用户工作时间之后完成。
　　（３）计划性维护。确定何时维护及维护的内容。随着时间的推移、用户需求的改变，有的数据会过时，应将其定期归档，把长时间不用的数据转移到其他设备中予以存储，删除休眠数据，清理脏数据。还要考虑备份，包括备份的数据量，备份所需时间，备份的级别，何时备份，备份的周期等。
　　定期更新数据表的统计信息是计划性维护中一个很重要的任务，如运行ＤＢ２的ｒｕｎｓｔａｔｓ ｏｎ命令、Ｉｎｆｏｒｍｉｘ的ｕｐｄａｔｅ ｓｔａｔｉｓｔｉｃｓ ｆｏｒ命令等，这些统计信息直接影响查询的执行计划。ＤＢ２有一个实用程序ＲＥＯＲＧＣＨＫ，如果运行ＲＵＮＳＴＡＴＳ后仍不能改进性能，ＲＥＯＲＧ实用程序可以选择根据指定的索引将数据重新排列成一个物理序列。对有大量更新的表，如果统计信息后性能提高不明显，可以删除和重建索引。
　　（４）纠正性维护。包括允许维护的时间，允许多少故障时间和恢复时间。
　　一般情况下，银行购买产品后很少做参数调整、程序优化等方面的工作。针对机器出现的性能方面的问题，生产厂商也往往是推出系统升级方案。而银行数据分析系统同交易系统不同，由于其需求不断增加，没有确定的边界，因此，性能调整也是一个没有结束的过程，若要最终达到资源极限，则需要升级硬件。