数据仓库集群设计PPT课件

1、日程日程不同的并行类型 硬件硬件软件软件DB2 DPF 并行架构数据库分区数据库分区Database partitioning 服务器分区服务器分区Server partitioningIO 并行并行物理数据库设计分区键分区键Partitioning keys对性能和扩展性的影响对性能和扩展性的影响应用并行何时分区？第1页/共40页目标目标 通过本文介绍，您将能够: 理解不同种类的并行架构 理解DB2 MPP并行架构的原理 理解DB2是如何处理并行SQL的 理解并行机制是怎样影响BI架构设计的 知道何时需要进行数据库分区 了解IBM BCU第2页/共40页从单处理器到大规模并行处理从单处理器到

2、大规模并行处理支持海量数据库扩展性所有都并行 (MPP支持)集成的数据仓库/商业只能函数开放的企业级解决方案64位全面支持单处理器单处理器SMP群集群集MPP相同功能所有架构 所有平台平台平台 :AIXHP/UXSolarisLinuxWindowsCPUSQLCPUSQLCPUSQLCPUSQL并行交易并行交易CPUCPUCPUCPUSQL并行并行SQL第3页/共40页概念和术语概念和术语 非共享体系架构Shared-nothing 软件概念：应用（数据库/ETL等）并行处理单一任务的能力，每个子任务处理一部分数据 大规模并行处理 (MPP) 硬件概念： 一组服务器/节点，通过高速网络通讯，

3、运行非共享应用（数据库、ETL等） 群集 大规模并行计算、各节点共享磁盘子系统；当一个节点宕掉，另一个能访问相同物理磁盘的节点将会接管失效节点的工作负荷。 扩展性 架构扩展能力： 垂直扩展 通过增加单节点组件（CPU内存等）来扩展水平扩展 通过增加更多的节点来扩展 线性 线性扩展 效率指标，增加资源将线性提升性能第4页/共40页分区数据库模型分区数据库模型数据库被分为多个分区数据库分区可运行在不同的节点上每个数据库分区有独立的资源（引擎、日志管理、锁管理、缓存管理等）所有分区并行处理，由数据库系统进行统一协调和管理对用户和应用来讲是单一系统映象高速通讯管理（高速通讯管理（FCM）数据数据日志日

4、志数据库分数据库分区区DB2 非共享体系架构非共享体系架构datalogdatalogdatalogdatalog第5页/共40页DB2 数据库拓朴数据库拓朴高速网络tableCPUMEMCPUMEMCPUMEMCPUMEMMPP 小服务器LinuxWindowsRS/SPSMP 大服务器RegattaSun StarFireHPCPUMEMCPUCPUCPUtableCPUMEMCPUCPUCPUtableCPUMEMCPUCPUCPU高速网络群集SMP群集第6页/共40页数据库分区与节点关系数据库分区与节点关系Node 1part0part1Node 2part2part3# sqllib

5、/db2nodes.cfg#0 node1 01 node1 12 node2 03 node2 1FCMFCMFCMFCM节点1分区 0 在0号逻辑端口上监听 节点2分区 3 在1号逻辑端口上监听节点2分区 2 在0号逻辑端口上监听节点1分区 1 在1号逻辑端口上监听 db2nodes.cfgDB2 实例配置文件所有数据库共享该配置位于DB2实例目录Sqllib 在其中一个节点上NFS共享给其他节点第7页/共40页DPF 处理模型处理模型part0part1part2part3TXNTXNTXNTXNTXNTXNTXNTXNTXNTXNTXNTXNpart0part1part2part3TX

6、NTXNTXNpart0part1part2part3TXNTXNTXNTXNTXNpart0part1part2part3part4part5TXNTXNTXN每个数据分区有更多的资源 相同的响应时间处理更多的查询更多的资源更多的数据分区相同数据量：更快的查询响应时间更多的数据量：相同响应时间OLTPDSS垂直扩展垂直扩展水平扩展水平扩展扩展扩展扩展扩展第8页/共40页I/O 层次层次页面Page读写的基本单位扩展块Extent分配的基本单位页面数可定制能且只能用于存放数据库的一个对象（表、索引）通常为大的块I/O大小容器Container DB2从中分配扩展块DMS文件 (/hello.d

7、at)DMS 裸设备 (/dev/rhello)SMS：目录 (/hello)表空间Tablespace一组容器Page 0Page 1Page 2Page 3Page 4Page 5Page 6Page 7Extent:一组页面row 1row 2row 3Page:4k, 8k, 16k or 32kExtent 0Extent 3Extent 1Extent 4Extent 2Extent 5Container:包含扩展块的文件containercontainercontainertablespaceTablespace:表和索引的存放空间Cont 1Cont 2Cont 3第9页/共4

8、0页总结总结: 并行并行 I/ObufferPartition 0bufferPartition 2bufferIOIOIOIOIOIOIOIOIOagentagentagentPartition 1coordextents i/o serversContainersContainersContainersdb agents并行预取agentagentagent第10页/共40页创建单节点创建单节点Partition Groupcreate database partition group sdpg on dbpartitionnums(0); create database partitio

9、n group pdpg on dbpartitionnums(1 TO 8);创建多节点Partition Group第11页/共40页创建跨节点表空间创建跨节点表空间create tablespace ts_demo in database partition group pdpg pagesize 4kmanaged by database using ( file /database/zhouxzh/tbs_demo $N 20M)extentsize 16 prefetchsize automatic bufferpool bp4k autoresize yes no file sy

10、stem caching; 第12页/共40页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 HASH(cust_id)COMPRESS YESIN data_tbs; CREATE TABLE sales ( cust_id INTEGER NOT NULL REFERE

11、NCES 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 key (分散数据) IN data_tbs; CREATE INDEX sales_cust ON sales (cust_id); Non unique index (加速查

12、询)Salescust_idsales_dateregionprod_id FK1M3675200John Smith01/01/2004Northp1John Smith01/02/2004Southp2Bill Woe02/01/2004N/Ap1PK逻辑模型逻辑模型Customercust_idTelZipBirth_datePK 1M800K50K5000John Smith xxx-xxx-xxxx920001965Bill Woeyyy-yyy-yyyy750001972第13页/共40页使用哈希（使用哈希（Hashing）和分区位图（）和分区位图（ Partition Map）自

13、动分布数据自动分布数据Partition 0Partition 1Partition 2customersalesSmithFSmith1Smith2Smith3WoeMWoe1Woe2BillMBill1Bill2MaryFMary1Mary2ZoolFZool2Zool1Zool3CREATE TABLE customer ( cust_id VARCHAR(80),gender CHAR(5) PARTITIONING KEY(cust_id);CREATE TABLE sales ( cust_id VARCHAR(80),qty INTEGER) PARTITIONING KEY(c

14、ust_id);Customercust_idGenderSmithFBillMWoeMZoolMMaryFSalescust_idQtySmith1Smith2Smith3Zool1Zool202102102104096876543210210210210409687654321Hash(cust_id)第14页/共40页数据分区数据分区 目标目标 1: 保证数据跨所有分区平均分布保证数据跨所有分区平均分布选择分区键 值多的字段数据分布均匀值少的字段导致数据不均匀某些分区比别的分区有更多的数据系统的性能取决于装载数据最多的分区的性能CREATE TABLE customer ( cust_i

15、d VARCHAR(80),gender CHAR(5) PARTITIONING KEY(cust_id);part0part1part2part3datadatadatadataCREATE TABLE customer ( cust_id VARCHAR(80),gender CHAR(5) PARTITIONING KEY(gender);part0datapart1datapart2part3好！好！ - 所有分区相同的数据量差！差！ - 某些分区装满了数据而其他的分区没有数据不均匀第15页/共40页数据分布数据分布 目标目标 2 : 优化连接处理和并行查询优化连接处理和并行查询 C

16、olocation 连接匹配的数据行位于同一个分区，否则将发生跨数据分区的数据迁移 (非 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_idPartition 1 Partition 2 CUST cust_id其他字段 SALEScust_

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

18、ST : pk (CUST_ID)SALES : pk (CUST_ID)132Partition 1 Scan CUSTApply predicatesScan SALESApply predicatesJoinInsert into q1Partition 2 Scan CUST Apply predicatesScan SALESApply predicatesJoinInsert into q1Partition 0 Read q1 Process Return resultsq1q1113222211113322select * from cust c,sales s where c

19、.cust_id=s.cust_id1111222233第18页/共40页广播连接广播连接 SALES 的分区键是 CUST_ID 字段 CUST的分区键是另外一个字段 CUST表数据将发送到有SALES表的所有数据库分区Partition 1 Partition 2 CUST : pk (其他字段)132SALES : pk (CUST_ID)Partition 1 Partition 2 Partition 0 Read q1 Process Return resultsq1q1Scan CUSTApply predicatesBroadcast into q2Scan SALESAppl

20、y predicatesRead q2 JoinInsert into q1Scan CUSTApply predicatesBroadcast into q2Scan SALESApply predicatesRead q2 JoinInsert into q1121233132132q2q2q2q2221111332211322select * from cust c,sales s where c.cust_id=s.cust_id1111222233第19页/共40页定向的外表连接定向的外表连接 CUST 的分区键是 CUST_ID字段 SALES表的分区键是另外一个不同的字段 SAL

21、ES表用CUST_ID字段重新哈希，并发送到正确的数据库分区Partition 1 Partition 2 Partition 0 Read q1 Process Return resultsq1q1Scan SALESApply predicatesHash CUST_IDInsert into q2Scan CUSTApply predicatesRead q2 JoinInsert into q1Scan SALESApply predicatesHash CUST_IDInsert into q2Scan CUSTApply predicatesRead q2 JoinInsert i

22、nto q1q2q2q2q2Partition 1 Partition 2 CUST : pk (CUST_ID)SALES : pk (其他字段)1322211113322113221321222113select * from cust c,sales s where c.cust_id=s.cust_id1111222233第20页/共40页定向的内表和外表连接定向的内表和外表连接 没有一张表的分区键是CUST_ID字段 两张表均重新哈希，发送到新的数据库分区后再连接 表队列q2 和q3都定向Partition 1 Partition 2 Partition 0 Read q1 Proc

23、ess Return resultsq1q1Partition 1 Partition 2 CUST : pk (其他字段)SALES : pk (其他字段)132Read q2Read q3JoinInsert q1Scan SALESApply predicatesHash CUST_IDInsert into q3q2Scan CUSTApply predicatesHash CUST_IDInsert into q2q3Read q2Read q3JoinInsert q1Scan SALESApply predicatesHash CUST_IDInsert into q3q2Sca

24、n CUSTApply predicatesHash CUST_IDInsert into q2q3q2q3q2q312322111133221132213212select * from cust c,sales s where c.cust_id=s.cust_id1111222233第21页/共40页Insert / SelectCREATE TABLE T1 (col1,col2) PARTITIONING KEY (col1);CREATE TABLE T2 (col1,col2) PARTITIONING KEY (col1);INSERT INTO t2 SELECT * FRO

25、M t1;part 0 part 1 part 2 part 3t1t2Co-locatedpart 0 part 1 part 2 part 3t1t2Non Co-locatedCREATE TABLE T1 (col1,col2) PARTITIONING KEY (col1);CREATE TABLE T2 (col1,col2) PARTITIONING KEY (col2);INSERT INTO t2 SELECT * FROM t1;第22页/共40页分区键选择分区键选择 做: 分区键包含表连接中需要经常使用的字段 分区数据均匀分布 分区键取值更广泛 整形字段比字符型字段更高效

26、、字符型比十进制数据类型更高效 不做： 长型字段不运行 唯一索引或主键必须是分区键的超集 分区键不允许更改 避免更新分区键字段 V8.2开始提供 分区建议向导第23页/共40页数据迁移数据迁移 - Import Import程序将输入文件数据插入表或可更新视图 ，如果接收数据的表或视图已经包含数据，可以选择替换或追加方式导入数据part3part2part1part0Coord.WRITEsqlcodeHASHInsertsqlcodeImport from file of delinsert into tbl;第24页/共40页迁移数据迁移数据 - Import 使用缓冲区方式使用缓冲区方式

27、 使用缓冲区模式下装载效率更高，因为数据在缓冲区满时发送到目标数据库分区Coord.InsertsqlcodeImport from file of delinsert into tbl;part3part2part1part0WRITEWRITEWRITE0123hash1 : 数据插入缓冲区2 : 缓冲区数据发送到数据分区然后批量写入绑定选项绑定选项: insert buf第25页/共40页迁移数据迁移数据 并行插入并行插入 需要写程序将数据哈希分区到正确的分区 产品如Ascential Parallel Extender提供并行插入支持insertInsertsqlcodepart3pa

28、rt2part1part0WRITEWRITEWRITEWRITEinsertInsertsqlcodeinsertInsertsqlcodeinsertInsertsqlcodeHASH第26页/共40页迁移数据迁移数据 - DB2 Load协调程序Coordinator 创建并监控其他代理预分区代理Pre-partitioning agent 每个输入源一个代理 运行在协调分区上分区代理Partitioning agent 代理数量和运行的分区可配置介质读程序Media reader 每个目标分区一个part0part1part2part3db2lpartdb2lpartdb2lpartd

29、b2lmrdb2lmrdb2lmrdb2lmrdb2lpprtdb2lpprtdb2agentfilefile第27页/共40页DB2 SQL : 并行方式思考并行方式思考 DB2 SQL非常强大，几乎在所有情况下，在数据库中执行完整的SQL比应用程序从游标中选择获取更快、扩展性更强 获取数据行是串行的，在ETL处理中应尽量避免（除非使用并行实用程序，如Ascential Parallel Extender 或Ab Initio) 在任何情况下，运行EXPLAIN 观察在协调节点上运行的子任务: 触发器 SQL/PL 第28页/共40页并行并行SQL举例举例BEGIN ATOMICFOR ro

30、w AS 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

31、 = row.cust_id; END IF;END FOR;ENDMERGE INTO dss.customer AS old USING dss.customer_staging AS newON old.cust_id = new.cust_idWHEN MATCHTHEN UPDATE SET old.gender=new.genderWHEN NOT MATCHEDTHEN INSERT VALUES (new.cust_id,new.gender);游标处理将会慢 (在协调节点上每次处理一行) - 有限的扩展性功能上和上述等价，但是运行更快 (所有数据分区并行处理) - 高扩展性第

32、29页/共40页为什么要分区？为什么要分区？ 查询扩展性 架构限制 数据装载 维护操作 并行插入/删除 备份和恢复 恢复 日志 第30页/共40页查询扩展性查询扩展性 最显著的原因之一 将一个大的数据库分成多个小的数据库可以提高查询的性能，因为每个数据库分区拥有自己的一小部分数据 假设您想扫描1亿条记录 对一个单一分区的数据库来讲，该扫描操作需要数据库管理器独立扫描一亿条记录 如果您将数据库系统做成50个分区，并将这1亿条记录平均分配到这50个分区上，那么每个数据库分区的数据库管理器将只扫描200万记录第31页/共40页架构限制架构限制 在DB2 V8和以前版本，非分区的最大的表取决于页面大小 64 GB 到 512 GB 这是表空间限制造