1、1,DB2数据仓库集群设计,日程,不同的并行类型 硬件 软件 DB2 DPF 并行架构 数据库分区Database partitioning 服务器分区Server partitioning IO 并行 物理数据库设计 分区键Partitioning keys 对性能和扩展性的影响 应用并行 何时分区？,目标,通过本文介绍，您将能够: 理解不同种类的并行架构 理解DB2 MPP并行架构的原理 理解DB2是如何处理并行SQL的 理解并行机制是怎样影响BI架构设计的 知道何时需要进行数据库分区 了解IBM BCU,从单处理器到大规模并行处理,支持海量数据库 扩展性 所有都并行 (MPP支持) 集成
2、的数据仓库/商业只能函数 开放的企业级解决方案 64位全面支持,单处理器,SMP,群集,MPP,相同功能 所有架构 所有平台,平台 : AIX HP/UX Solaris Linux Windows,概念和术语,非共享体系架构Shared-nothing 软件概念：应用（数据库/ETL等）并行处理单一任务的能力，每个子任务处理一部分数据 大规模并行处理 (MPP) 硬件概念： 一组服务器/节点，通过高速网络通讯，运行非共享应用（数据库、ETL等） 群集 大规模并行计算、各节点共享磁盘子系统；当一个节点宕掉，另一个能访问相同物理磁盘的节点将会接管失效节点的工作负荷。 扩展性 架构扩展能力： 垂直
3、扩展 通过增加单节点组件（CPU内存等）来扩展 水平扩展 通过增加更多的节点来扩展 线性 线性扩展 效率指标，增加资源将线性提升性能,分区数据库模型 数据库被分为多个分区 数据库分区可运行在不同的节点上 每个数据库分区有独立的资源（引擎、日志管理、锁管理、缓存管理等） 所有分区并行处理，由数据库系统进行统一协调和管理 对用户和应用来讲是单一系统映象,高速通讯管理（FCM）,数据,日志,数据库分区,DB2 非共享体系架构,DB2 数据库拓朴,高速网络,table,CPU,MEM,CPU,MEM,CPU,MEM,CPU,MEM,MPP 小服务器 Linux Windows RS/SP,SMP 大服
4、务器 Regatta Sun StarFire HP,CPU,MEM,CPU,CPU,CPU,table,CPU,MEM,CPU,CPU,CPU,table,CPU,MEM,CPU,CPU,CPU,高速网络,群集SMP群集,数据库分区与节点关系,# # sqllib/db2nodes.cfg # 0 node1 0 1 node1 1 2 node2 0 3 node2 1,节点1分区 0 在0号逻辑端口上监听,节点2分区 3 在1号逻辑端口上监听,节点2分区 2 在0号逻辑端口上监听,节点1分区 1 在1号逻辑端口上监听,db2nodes.cfg DB2 实例配置文件 所有数据库共享该配置
5、位于DB2实例目录 Sqllib 在其中一个节点上 NFS共享给其他节点,DPF 处理模型,每个数据分区有更多的资源相同的响应时间处理更多的查询,更多的资源更多的数据分区 相同数据量：更快的查询响应时间 更多的数据量：相同响应时间,OLTP,DSS,扩展,扩展,I/O 层次,页面Page 读写的基本单位扩展块Extent 分配的基本单位 页面数可定制 能且只能用于存放数据库的一个对象（表、索引） 通常为大的块I/O大小容器Container DB2从中分配扩展块 DMS文件 (/hello.dat) DMS 裸设备 (/dev/rhello) SMS：目录 (/hello)表空间Tablesp
6、ace 一组容器,Extent: 一组页面,Page: 4k, 8k, 16k or 32k,Container: 包含扩展块的文件,Tablespace: 表和索引的存放空间,Cont 1,Cont 2,Cont 3,总结: 并行 I/O,buffer,Partition 0,buffer,Partition 2,buffer,IO,IO,IO,IO,IO,IO,IO,IO,IO,agent,agent,agent,Partition 1,coord,extents,i/o servers,Containers,Containers,Containers,db agents,并行预取,age
7、nt,agent,agent,创建单节点Partition Group,create database partition group sdpg on dbpartitionnums(0); create database partition group pdpg on dbpartitionnums(1 TO 8);,创建多节点Partition Group,创建跨节点表空间,create tablespace ts_demo in database partition group pdpg pagesize 4k managed by database using ( file /data
8、base/zhouxzh/tbs_demo $N 20M) extentsize 16 prefetchsize automatic bufferpool bp4k autoresize yes no file system caching;,DB2 表定义 从逻辑到物理,Physical Data ModelCREATE TABLE customer (cust_id INTEGER NOT NULL,tel CHAR(20),zip CHAR(5) NOT NULL,birth_date DATE NOT NULL, PRIMARY KEY(cust_id) DISTRIBUTE BY H
9、ASH(cust_id) COMPRESS YES IN data_tbs; CREATE TABLE sales (cust_id INTEGER NOT NULLREFERENCES customer(cust_id), Foreign key (确保存在)sales_date DATE NOT NULL,region CHAR(5) NOT NULL,prod_id INTEGER NOT NULL PRIMARY KEY(cust_id,sales_date) Primary key (确保唯一) DISTRIBUTE BY HASH(cust_id) Partitionning ke
10、y (分散数据) IN data_tbs; CREATE INDEX sales_cust ON sales (cust_id); Non unique index (加速查询),使用哈希（Hashing）和分区位图（ Partition Map）自动分布数据,customer,sales,CREATE TABLE customer (cust_id VARCHAR(80) ,gender CHAR(5) PARTITIONING KEY(cust_id);CREATE TABLE sales (cust_id VARCHAR(80) ,qty INTEGER) PARTITIONING KE
11、Y(cust_id);,Hash(cust_id),数据分区 目标 1: 保证数据跨所有分区平均分布,选择分区键 值多的字段 数据分布均匀值少的字段导致数据不均匀 某些分区比别的分区有更多的数据 系统的性能取决于装载数据最多的分区的性能,CREATE TABLE customer (cust_id VARCHAR(80) ,gender CHAR(5) PARTITIONING KEY(cust_id);,part0,part1,part2,part3,好！ - 所有分区相同的数据量,差！ - 某些分区装满了数据而其他的分区没有数据不均匀,数据分布 目标 2 : 优化连接处理和并行查询,Col
12、ocation 连接匹配的数据行位于同一个分区，否则将发生跨数据分区的数据迁移 (非 colocated 连接) 连接方法 访问路径选择 嵌套循环Nested-loop 排序归并Sort/merge 哈希Hash 笛卡尔连接Product join 星型连接Star join 分区和表队列 广播Broadcast 定向Directed (inner/outer) 合并Merge 例如： Select * from cust,sales where cust.cust_id=sales.cust_id,Partition 1,Partition 2,CUST cust_id 其他字段,SALES
13、 cust_id 其他字段,Partition 0 Coordinator,Colocation,概念 如果一个查询需要连接两张或多张表， collocation可以保证在分区内部连接，无需将表重新做哈希分布 否则，数据重定向将会发生实现 如果所有连接的表 在同一个数据库分区组，且 分区键值有相同数量的字段，且 分区健字段的数据类型成对兼容 那么，具有不同表具有相同分区键的数据行将会存放在同一个数据分区,Collocated 连接,CUST和SALES 表的分区键均定义CUST_ID字段上 连接将在每个数据库分区本地发生,Partition 1,Partition 2,CUST : pk (C
14、UST_ID),SALES : pk (CUST_ID),1,3,2,Partition 1,Scan CUST Apply predicates Scan SALES Apply predicates Join Insert into q1,Partition 2,Scan CUST Apply predicates Scan SALES Apply predicates Join Insert into q1,Partition 0,Read q1ProcessReturn results,q1,q1,1,1,3,2,2,广播连接,SALES 的分区键是 CUST_ID 字段 CUST的分
15、区键是另外一个字段 CUST表数据将发送到有SALES表的所有数据库分区,Partition 1,Partition 2,SALES : pk (CUST_ID),定向的外表连接,CUST 的分区键是 CUST_ID字段 SALES表的分区键是另外一个不同的字段 SALES表用CUST_ID字段重新哈希，并发送到正确的数据库分区,Partition 1,Partition 2,Partition 0,Read q1ProcessReturn results,q1,q1,Scan SALES Apply predicates Hash CUST_ID Insert into q2,Scan CU
16、ST Apply predicates Read q2Join Insert into q1,Scan SALES Apply predicates Hash CUST_ID Insert into q2,Scan CUST Apply predicates Read q2Join Insert into q1,Partition 1,Partition 2,CUST : pk (CUST_ID),SALES : pk (其他字段),1,1,3,2,2,1,3,2,1,2,2,2,定向的内表和外表连接,没有一张表的分区键是CUST_ID字段 两张表均重新哈希，发送到新的数据库分区后再连接 表队
17、列q2 和q3都定向,Partition 0,Read q1ProcessReturn results,q1,q1,Partition 1,Partition 2,CUST : pk (其他字段),SALES : pk (其他字段),1,3,2,1,2,3,1,1,3,2,2,2,1,2,Insert / Select,CREATE TABLE T1 (col1,col2) PARTITIONING KEY (col1); CREATE TABLE T2 (col1,col2) PARTITIONING KEY (col1);INSERT INTO t2 SELECT * FROM t1;,C
18、REATE TABLE T1 (col1,col2) PARTITIONING KEY (col1); CREATE TABLE T2 (col1,col2) PARTITIONING KEY (col2);INSERT INTO t2 SELECT * FROM t1;,分区键选择,做: 分区键包含表连接中需要经常使用的字段 分区数据均匀分布 分区键取值更广泛 整形字段比字符型字段更高效、字符型比十进制数据类型更高效不做： 长型字段不运行 唯一索引或主键必须是分区键的超集 分区键不允许更改 避免更新分区键字段V8.2开始提供 分区建议向导,数据迁移 - Import,Import程序将输入文
19、件数据插入表或可更新视图 ，如果接收数据的表或视图已经包含数据，可以选择替换或追加方式导入数据,迁移数据 - Import 使用缓冲区方式,使用缓冲区模式下装载效率更高，因为数据在缓冲区满时发送到目标数据库分区,绑定选项: insert buf,迁移数据 并行插入,需要写程序将数据哈希分区到正确的分区 产品如Ascential Parallel Extender提供并行插入支持,迁移数据 - DB2 Load,协调程序Coordinator 创建并监控其他代理预分区代理Pre-partitioning agent 每个输入源一个代理 运行在协调分区上分区代理Partitioning agent
20、 代理数量和运行的分区可配置介质读程序Media reader 每个目标分区一个,file,file,DB2 SQL : 并行方式思考,DB2 SQL非常强大，几乎在所有情况下，在数据库中执行完整的SQL比应用程序从游标中选择获取更快、扩展性更强获取数据行是串行的，在ETL处理中应尽量避免（除非使用并行实用程序，如Ascential Parallel Extender 或Ab Initio)在任何情况下，运行EXPLAIN 观察在协调节点上运行的子任务: 触发器 SQL/PL ,并行SQL举例,BEGIN ATOMIC FOR row ASSELECT new.cust_id,new.gend
21、er,old.cust_id old_custFROM dss.customer_staging new LEFT OUTER JOIN dss.customer oldON new.cust_id = old.cust_id DOIF row.old_cust is NULL THENINSERT INTO dss.customer VALUES(row.cust_id, row.gender);ELSEUPDATE dss.customerSET gender = row.gender WHERE cust_id = row.cust_id;END IF; END FOR; END,MER
22、GE INTO dss.customer AS oldUSING dss.customer_staging AS new ON old.cust_id = new.cust_id WHEN MATCH THEN UPDATE SET old.gender=new.gender WHEN NOT MATCHED THEN INSERT VALUES (new.cust_id,new.gender);,游标处理将会慢 (在协调节点上每次处理一行) - 有限的扩展性,功能上和上述等价，但是运行更快 (所有数据分区并行处理) - 高扩展性,为什么要分区？,查询扩展性 架构限制 数据装载 维护操作 并行
23、插入/删除 备份和恢复 恢复 日志,查询扩展性,最显著的原因之一 将一个大的数据库分成多个小的数据库可以提高查询的性能，因为每个数据库分区拥有自己的一小部分数据假设您想扫描1亿条记录 对一个单一分区的数据库来讲，该扫描操作需要数据库管理器独立扫描一亿条记录 如果您将数据库系统做成50个分区，并将这1亿条记录平均分配到这50个分区上，那么每个数据库分区的数据库管理器将只扫描200万记录,架构限制,在DB2 V8和以前版本，非分区的最大的表取决于页面大小 64 GB 到 512 GB 这是表空间限制造成的 表和表空间大小限制是每个分区上的限制 因此将数据库分成N个分区可以将表的最大尺寸增加为单个分
24、区表最大尺寸的N倍 内存也可能是个限制，特别是在32为操作系统环境 因为每个数据库分区管理并拥有自己的资源，因此通过数据库分区可以克服这个限制,数据库装载,可并行装载数据到所有数据库分区，极大减少单表的装载时间数据装载的时间特别重要，特别是我们期望一个实时商业智能系统时,数据库维护,将数据库分散到多个数据库分区服务器可以加快系统维护，因为每个操作都运行在分区所管理的一个数据子集上面 尽管DB2中创建索引是并行的，您可以通过数据库分区进一步减少创建索引的时间 分区数据库环境可以减少搜集统计信息的时间，因为runstats仅运行在一个数据库分区上面 在DB2 V8中, Runstats取样选项可以
25、进一步减少运行时间（有无数据库分区均可） 将数据库分区可以减少表重整(reorg)的时间,并行INSERT/DELETE,一个数据库分区内部并行SQL操作 在一个使用SQL大量并发插入和删除的数据库环境中，多个数据库分区可以提高交易的吞吐量，因为所有数据库分区都在并行处理插入和删除，这个好处也体现在insert/select中,备份/恢复,将数据库分区到不同的数据库服务器上可以大大减少数据库备份的时间，这往往是觉定是否使用数据库分区很重要的一点DB2通过为每个表空间分配独立的进程或线程来实现备份和恢复操作的并行处理的。在分区数据库环境的备份中，每个分区的备份是独立的，通过并行备份数据库分区可以
26、大大减少备份整个数据库的时间,恢复,分区数据库环境可以加速前滚和重启恢复 在分区数据库环境中，必须前滚的日志对每个分区是特有的，对每个数据库分区“分而治之”的策略可以加速整个处理过程，如果每个数据库分区不需要前滚，在重启恢复中将跳过,日志,在高度活动的系统中，数据库日志的性能可能会限制系统的整体吞吐量。在分区数据库环境中，每个分区有自己一套日志。当大量插入、更新、删除操作时，多个数据库分区可以提高性能，因为日志是在每个数据库分区上是并行写的，且每个单一的分区需要记录的日志更少。,扩展性,DB2随数据量或处理器和分区的增加，提供近线性的扩展能力 可是，数据库分区是否提供最多的益处依赖于处理的工作负荷、最大表的大小及其他因素,
