DB2_数据仓库集群设计.ppt

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 01 node1 12 node2 03 node2 1,节点1分区 0 在0号逻辑端口上监听,节点2分区 3 在1号逻辑端口上监听,节点2分区 2 在0号逻辑端口上监听,节点1分区 1 在1号逻辑端口上监听,db2nodes.cfg DB2 实例配置文件 所有数据库共享该配置 位于DB2实

5、例目录 Sqllib 在其中一个节点上 NFS共享给其他节点,DPF 处理模型,每个数据分区有更多的资源 相同的响应时间处理更多的查询,更多的资源更多的数据分区 相同数据量：更快的查询响应时间 更多的数据量：相同响应时间,OLTP,DSS,扩展,扩展,I/O 层次,页面Page 读写的基本单位 扩展块Extent 分配的基本单位 页面数可定制 能且只能用于存放数据库的一个对象（表、索引） 通常为大的块I/O大小 容器Container DB2从中分配扩展块 DMS文件 (/hello.dat) DMS 裸设备 (/dev/rhello) SMS：目录 (/hello) 表空间Tablespac

6、e 一组容器,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,并行预取,agent,age

7、nt,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 /database/z

8、houxzh/tbs_demo $N 20M) extentsize 16 prefetchsize automatic bufferpool bp4k autoresize yes no file system caching;,DB2 表定义 从逻辑到物理,Physical Data Model CREATE 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 HA

9、SH(cust_id) COMPRESS YES IN data_tbs; CREATE TABLE sales ( cust_id INTEGER NOT NULL REFERENCES 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) Partitionnin

10、g key (分散数据) 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) PARTITIO

11、NING KEY(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 : 优化连接处理

12、和并行查询,Colocation 连接匹配的数据行位于同一个分区，否则将发生跨数据分区的数据迁移 (非 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 其

13、他字段,SALES cust_id 其他字段,Partition 0 Coordinator,Colocation,概念 如果一个查询需要连接两张或多张表， collocation可以保证在分区内部连接，无需将表重新做哈希分布 否则，数据重定向将会发生 实现 如果所有连接的表 在同一个数据库分区组，且 分区键值有相同数量的字段，且 分区健字段的数据类型成对兼容 那么，具有不同表具有相同分区键的数据行将会存放在同一个数据分区,Collocated 连接,CUST和SALES 表的分区键均定义CUST_ID字段上 连接将在每个数据库分区本地发生,Partition 1,Partition 2,CU

14、ST : pk (CUST_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 q1 Process Return results,q1,q1,1,1,3,2,2,广播连接,SALES 的分区键是 CUST_

15、ID 字段 CUST的分区键是另外一个字段 CUST表数据将发送到有SALES表的所有数据库分区,Partition 1,Partition 2,SALES : pk (CUST_ID),定向的外表连接,CUST 的分区键是 CUST_ID字段 SALES表的分区键是另外一个不同的字段 SALES表用CUST_ID字段重新哈希，并发送到正确的数据库分区,Partition 1,Partition 2,Partition 0,Read q1 Process Return results,q1,q1,Scan SALES Apply predicates Hash CUST_ID Insert i

16、nto q2,Scan CUST Apply predicates Read q2 Join Insert into q1,Scan SALES Apply predicates Hash CUST_ID Insert into q2,Scan CUST Apply predicates Read q2 Join 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 q1 Process Return 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

18、SELECT * FROM t1;,CREATE TABLE T1 (col1,col2) PARTITIONING KEY (col1); CREATE TABLE T2 (col1,col2) PARTITIONING KEY (col2); INSERT INTO t2 SELECT * FROM t1;,分区键选择,做: 分区键包含表连接中需要经常使用的字段 分区数据均匀分布 分区键取值更广泛 整形字段比字符型字段更高效、字符型比十进制数据类型更高效 不做： 长型字段不运行 唯一索引或主键必须是分区键的超集 分区键不允许更改 避免更新分区键字段 V8.2开始提供 分区建议向导,数据迁移

19、 - Import,Import程序将输入文件数据插入表或可更新视图 ，如果接收数据的表或视图已经包含数据，可以选择替换或追加方式导入数据,迁移数据 - Import 使用缓冲区方式,使用缓冲区模式下装载效率更高，因为数据在缓冲区满时发送到目标数据库分区,绑定选项: insert buf,迁移数据 并行插入,需要写程序将数据哈希分区到正确的分区 产品如Ascential Parallel Extender提供并行插入支持,迁移数据 - DB2 Load,协调程序Coordinator 创建并监控其他代理 预分区代理Pre-partitioning agent 每个输入源一个代理 运行在协调分区

20、上 分区代理Partitioning agent 代理数量和运行的分区可配置 介质读程序Media reader 每个目标分区一个,file,file,DB2 SQL : 并行方式思考,DB2 SQL非常强大，几乎在所有情况下，在数据库中执行完整的SQL比应用程序从游标中选择获取更快、扩展性更强 获取数据行是串行的，在ETL处理中应尽量避免（除非使用并行实用程序，如Ascential Parallel Extender 或Ab Initio) 在任何情况下，运行EXPLAIN 观察在协调节点上运行的子任务: 触发器 SQL/PL ,并行SQL举例,BEGIN ATOMIC FOR row AS

21、 SELECT new.cust_id,new.gender,old.cust_id old_cust FROM dss.customer_staging new LEFT OUTER JOIN dss.customer old ON new.cust_id = old.cust_id DO IF row.old_cust is NULL THEN INSERT INTO dss.customer VALUES(row.cust_id, row.gender); ELSE UPDATE dss.customer SET gender = row.gender WHERE cust_id = r

22、ow.cust_id; END IF; END FOR; END,MERGE INTO dss.customer AS old USING 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个分区可以将表的最大尺寸增加为单个分区表最大尺寸的N倍 内存也可能是个限制，特别是在32为操作系统环境 因为每个数据库分区管理并拥有自己的资源，因此通过数据库分区可以克服这个限制,数据库装载,可并行装载数据到所有数据库分区，极大减少单表的装载时间 数据装载的时间特别重要，特别是我们期望一个实时商业智