DB2-數(shù)據(jù)倉庫集群設計課件

1、DB2數(shù)據(jù)倉庫集群設計1第1頁，共40頁。日程不同的并行類型 硬件軟件DB2 DPF 并行架構數(shù)據(jù)庫分區(qū)Database partitioning 服務器分區(qū)Server partitioningIO 并行物理數(shù)據(jù)庫設計分區(qū)鍵Partitioning keys對性能和擴展性的影響應用并行何時分區(qū)？第2頁，共40頁。目標通過本文介紹，您將能夠:理解不同種類的并行架構理解DB2 MPP并行架構的原理理解DB2是如何處理并行SQL的理解并行機制是怎樣影響B(tài)I架構設計的知道何時需要進行數(shù)據(jù)庫分區(qū)了解IBM BCU第3頁，共40頁。從單處理器到大規(guī)模并行處理支持海量數(shù)據(jù)庫擴展性所有都并行 (MPP支持)

2、集成的數(shù)據(jù)倉庫/商業(yè)只能函數(shù)開放的企業(yè)級解決方案64位全面支持單處理器SMP群集MPP相同功能所有架構 所有平臺平臺 :AIXHP/UXSolarisLinuxWindowsCPUSQLCPUSQLCPUSQLCPUSQL并行交易CPUCPUCPUCPUSQL并行SQL第4頁，共40頁。概念和術語非共享體系架構Shared-nothing軟件概念：應用（數(shù)據(jù)庫/ETL等）并行處理單一任務的能力，每個子任務處理一部分數(shù)據(jù)大規(guī)模并行處理 (MPP)硬件概念： 一組服務器/節(jié)點，通過高速網(wǎng)絡通訊，運行非共享應用（數(shù)據(jù)庫、ETL等）群集大規(guī)模并行計算、各節(jié)點共享磁盤子系統(tǒng)；當一個節(jié)點宕掉，另一個能訪問

3、相同物理磁盤的節(jié)點將會接管失效節(jié)點的工作負荷。擴展性架構擴展能力： 垂直擴展 通過增加單節(jié)點組件（CPU內存等）來擴展水平擴展 通過增加更多的節(jié)點來擴展線性線性擴展 效率指標，增加資源將線性提升性能第5頁，共40頁。分區(qū)數(shù)據(jù)庫模型數(shù)據(jù)庫被分為多個分區(qū)數(shù)據(jù)庫分區(qū)可運行在不同的節(jié)點上每個數(shù)據(jù)庫分區(qū)有獨立的資源（引擎、日志管理、鎖管理、緩存管理等）所有分區(qū)并行處理，由數(shù)據(jù)庫系統(tǒng)進行統(tǒng)一協(xié)調和管理對用戶和應用來講是單一系統(tǒng)映象高速通訊管理（FCM）數(shù)據(jù)日志數(shù)據(jù)庫分區(qū)DB2 非共享體系架構datalogdatalogdatalogdatalog第6頁，共40頁。DB2 數(shù)據(jù)庫拓樸高速網(wǎng)絡tableCPU

4、MEMCPUMEMCPUMEMCPUMEMMPP 小服務器LinuxWindowsRS/SPSMP 大服務器RegattaSun StarFireHPCPUMEMCPUCPUCPUtableCPUMEMCPUCPUCPUtableCPUMEMCPUCPUCPU高速網(wǎng)絡群集SMP群集第7頁，共40頁。數(shù)據(jù)庫分區(qū)與節(jié)點關系Node 1part0part1Node 2part2part3# sqllib/db2nodes.cfg#0 node1 01 node1 12 node2 03 node2 1FCMFCMFCMFCM節(jié)點1分區(qū) 0 在0號邏輯端口上監(jiān)聽 節(jié)點2分區(qū) 3 在1號邏輯端口上監(jiān)聽節(jié)

5、點2分區(qū) 2 在0號邏輯端口上監(jiān)聽節(jié)點1分區(qū) 1 在1號邏輯端口上監(jiān)聽 db2nodes.cfgDB2 實例配置文件所有數(shù)據(jù)庫共享該配置位于DB2實例目錄Sqllib 在其中一個節(jié)點上NFS共享給其他節(jié)點第8頁，共40頁。DPF 處理模型part0part1part2part3TXNTXNTXNTXNTXNTXNTXNTXNTXNTXNTXNTXNpart0part1part2part3TXNTXNTXNpart0part1part2part3TXNTXNTXNTXNTXNpart0part1part2part3part4part5TXNTXNTXN每個數(shù)據(jù)分區(qū)有更多的資源 相同的響應時間處理

6、更多的查詢更多的資源更多的數(shù)據(jù)分區(qū)相同數(shù)據(jù)量：更快的查詢響應時間更多的數(shù)據(jù)量：相同響應時間OLTPDSS垂直擴展水平擴展擴展擴展第9頁，共40頁。I/O 層次頁面Page讀寫的基本單位擴展塊Extent分配的基本單位頁面數(shù)可定制能且只能用于存放數(shù)據(jù)庫的一個對象（表、索引）通常為大的塊I/O大小容器Container DB2從中分配擴展塊DMS文件 (/hello.dat)DMS 裸設備 (/dev/rhello)SMS：目錄 (/hello)表空間Tablespace一組容器Page 0Page 1Page 2Page 3Page 4Page 5Page 6Page 7Extent:一組頁面r

7、ow 1row 2row 3Page:4k, 8k, 16k or 32kExtent 0Extent 3Extent 1Extent 4Extent 2Extent 5Container:包含擴展塊的文件containercontainercontainertablespaceTablespace:表和索引的存放空間Cont 1Cont 2Cont 3第10頁，共40頁?？偨Y: 并行 I/ObufferPartition 0bufferPartition 2bufferIOIOIOIOIOIOIOIOIOagentagentagentPartition 1coordextents i/o s

8、erversContainersContainersContainersdb agents并行預取agentagentagent第11頁，共40頁。創(chuàng)建單節(jié)點Partition Groupcreate database partition group sdpg on dbpartitionnums(0); create database partition group pdpg on dbpartitionnums(1 TO 8);創(chuàng)建多節(jié)點Partition Group第12頁，共40頁。創(chuàng)建跨節(jié)點表空間create tablespace ts_demo in database partit

9、ion group pdpg pagesize 4kmanaged by database using ( file /database/zhouxzh/tbs_demo $N 20M)extentsize 16 prefetchsize automatic bufferpool bp4k autoresize yes no file system caching; 第13頁，共40頁。DB2 表定義 從邏輯到物理Physical Data ModelCREATE TABLE customer ( cust_id INTEGER NOT NULL, tel CHAR(20), zip CHAR

10、(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 REFERENCES customer(cust_id), Foreign key (確保存在) sales_date DATE NOT NULL, region CHAR(5) NOT NULL, prod_id INTEGER NOT NULL PRIMARY KEY(cust_i

11、d,sales_date) Primary key (確保唯一)DISTRIBUTE BY HASH(cust_id) Partitionning key (分散數(shù)據(jù)) IN data_tbs; CREATE INDEX sales_cust ON sales (cust_id); Non unique index (加速查詢)邏輯模型第14頁，共40頁。使用哈希（Hashing）和分區(qū)位圖（ Partition Map）自動分布數(shù)據(jù)Partition 0Partition 1Partition 2customersalesSmithFSmith1Smith2Smith3WoeMWoe1Woe

12、2BillMBill1Bill2MaryFMary1Mary2ZoolFZool2Zool1Zool3CREATE TABLE customer ( cust_id VARCHAR(80),gender CHAR(5) PARTITIONING KEY(cust_id);CREATE TABLE sales ( cust_id VARCHAR(80),qty INTEGER) PARTITIONING KEY(cust_id);02102102104096876543210210210210409687654321Hash(cust_id)第15頁，共40頁。數(shù)據(jù)分區(qū) 目標 1: 保證數(shù)據(jù)跨所

13、有分區(qū)平均分布選擇分區(qū)鍵 值多的字段數(shù)據(jù)分布均勻值少的字段導致數(shù)據(jù)不均勻某些分區(qū)比別的分區(qū)有更多的數(shù)據(jù)系統(tǒng)的性能取決于裝載數(shù)據(jù)最多的分區(qū)的性能CREATE TABLE customer ( cust_id VARCHAR(80),gender CHAR(5) PARTITIONING KEY(cust_id);part0part1part2part3datadatadatadataCREATE TABLE customer ( cust_id VARCHAR(80),gender CHAR(5) PARTITIONING KEY(gender);part0datapart1datapart2p

14、art3好！ - 所有分區(qū)相同的數(shù)據(jù)量差！ - 某些分區(qū)裝滿了數(shù)據(jù)而其他的分區(qū)沒有數(shù)據(jù)不均勻第16頁，共40頁。數(shù)據(jù)分布 目標 2 : 優(yōu)化連接處理和并行查詢Colocation連接匹配的數(shù)據(jù)行位于同一個分區(qū)，否則將發(fā)生跨數(shù)據(jù)分區(qū)的數(shù)據(jù)遷移 (非 colocated 連接)連接方法 訪問路徑選擇嵌套循環(huán)Nested-loop排序歸并Sort/merge哈希Hash笛卡爾連接Product join星型連接Star join分區(qū)和表隊列廣播Broadcast定向Directed (inner/outer)合并Merge例如：Select * from cust,sales where cust.

15、cust_id=sales.cust_idPartition 1 Partition 2 CUST cust_id其他字段 SALEScust_id其他字段Partition 0Coordinator 第17頁，共40頁。Colocation概念如果一個查詢需要連接兩張或多張表， collocation可以保證在分區(qū)內部連接，無需將表重新做哈希分布否則，數(shù)據(jù)重定向將會發(fā)生實現(xiàn)如果所有連接的表在同一個數(shù)據(jù)庫分區(qū)組，且分區(qū)鍵值有相同數(shù)量的字段，且分區(qū)健字段的數(shù)據(jù)類型成對兼容那么，具有不同表具有相同分區(qū)鍵的數(shù)據(jù)行將會存放在同一個數(shù)據(jù)分區(qū)第18頁，共40頁。Collocated 連接CUST和SALE

16、S 表的分區(qū)鍵均定義CUST_ID字段上連接將在每個數(shù)據(jù)庫分區(qū)本地發(fā)生Partition 1 Partition 2 CUST : 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 r

17、esultsq1q1113222211113322select * from cust c,sales s where c.cust_id=s.cust_id1111222233第19頁，共40頁。廣播連接SALES 的分區(qū)鍵是 CUST_ID 字段CUST的分區(qū)鍵是另外一個字段CUST表數(shù)據(jù)將發(fā)送到有SALES表的所有數(shù)據(jù)庫分區(qū)Partition 1 Partition 2 CUST : pk (其他字段)132SALES : pk (CUST_ID)Partition 1 Partition 2 Partition 0 Read q1 Process Return resultsq1q1S

18、can CUSTApply predicatesBroadcast into q2Scan SALESApply 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第20頁，共40頁。定向的外

19、表連接CUST 的分區(qū)鍵是 CUST_ID字段SALES表的分區(qū)鍵是另外一個不同的字段SALES表用CUST_ID字段重新哈希，并發(fā)送到正確的數(shù)據(jù)庫分區(qū)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 q

20、2Scan CUSTApply predicatesRead q2 JoinInsert into q1q2q2q2q2Partition 1 Partition 2 CUST : pk (CUST_ID)SALES : pk (其他字段)1322211113322113221321222113select * from cust c,sales s where c.cust_id=s.cust_id1111222233第21頁，共40頁。定向的內表和外表連接沒有一張表的分區(qū)鍵是CUST_ID字段兩張表均重新哈希，發(fā)送到新的數(shù)據(jù)庫分區(qū)后再連接表隊列q2 和q3都定向Partition 1 Pa

21、rtition 2 Partition 0 Read q1 Process 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 predica

22、tesHash CUST_IDInsert into q3q2Scan CUSTApply predicatesHash CUST_IDInsert into q2q3q2q3q2q312322111133221132213212select * from cust c,sales s where c.cust_id=s.cust_id1111222233第22頁，共40頁。Insert / SelectCREATE TABLE T1 (col1,col2) PARTITIONING KEY (col1);CREATE TABLE T2 (col1,col2) PARTITIONING KEY

23、 (col1);INSERT INTO t2 SELECT * FROM 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;第23頁，共40頁。分區(qū)鍵選擇做:分區(qū)鍵包含表連接中需要經(jīng)常使用的字段分區(qū)數(shù)

24、據(jù)均勻分布分區(qū)鍵取值更廣泛整形字段比字符型字段更高效、字符型比十進制數(shù)據(jù)類型更高效不做：長型字段不運行唯一索引或主鍵必須是分區(qū)鍵的超集分區(qū)鍵不允許更改避免更新分區(qū)鍵字段V8.2開始提供分區(qū)建議向導第24頁，共40頁。數(shù)據(jù)遷移 - ImportImport程序將輸入文件數(shù)據(jù)插入表或可更新視圖 ，如果接收數(shù)據(jù)的表或視圖已經(jīng)包含數(shù)據(jù)，可以選擇替換或追加方式導入數(shù)據(jù)part3part2part1part0Coord.WRITEsqlcodeHASHInsertsqlcodeImport from file of delinsert into tbl;第25頁，共40頁。遷移數(shù)據(jù) - Import 使用

25、緩沖區(qū)方式使用緩沖區(qū)模式下裝載效率更高，因為數(shù)據(jù)在緩沖區(qū)滿時發(fā)送到目標數(shù)據(jù)庫分區(qū)Coord.InsertsqlcodeImport from file of delinsert into tbl;part3part2part1part0WRITEWRITEWRITE0123hash1 : 數(shù)據(jù)插入緩沖區(qū)2 : 緩沖區(qū)數(shù)據(jù)發(fā)送到數(shù)據(jù)分區(qū)然后批量寫入綁定選項: insert buf第26頁，共40頁。遷移數(shù)據(jù) 并行插入需要寫程序將數(shù)據(jù)哈希分區(qū)到正確的分區(qū)產(chǎn)品如Ascential Parallel Extender提供并行插入支持insertInsertsqlcodepart3part2part1p

26、art0WRITEWRITEWRITEWRITEinsertInsertsqlcodeinsertInsertsqlcodeinsertInsertsqlcodeHASH第27頁，共40頁。遷移數(shù)據(jù) - DB2 Load協(xié)調程序Coordinator創(chuàng)建并監(jiān)控其他代理預分區(qū)代理Pre-partitioning agent 每個輸入源一個代理運行在協(xié)調分區(qū)上分區(qū)代理Partitioning agent代理數(shù)量和運行的分區(qū)可配置介質讀程序Media reader 每個目標分區(qū)一個part0part1part2part3db2lpartdb2lpartdb2lpartdb2lmrdb2lmrdb2l

27、mrdb2lmrdb2lpprtdb2lpprtdb2agentfilefile第28頁，共40頁。DB2 SQL : 并行方式思考DB2 SQL非常強大，幾乎在所有情況下，在數(shù)據(jù)庫中執(zhí)行完整的SQL比應用程序從游標中選擇獲取更快、擴展性更強獲取數(shù)據(jù)行是串行的，在ETL處理中應盡量避免（除非使用并行實用程序，如Ascential Parallel Extender 或Ab Initio)在任何情況下，運行EXPLAIN 觀察在協(xié)調節(jié)點上運行的子任務: 觸發(fā)器SQL/PL第29頁，共40頁。并行SQL舉例BEGIN ATOMICFOR row AS SELECT new.cust_id,new.

28、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 = row.cust_id; END IF;END F

29、OR;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);游標處理將會慢 (在協(xié)調節(jié)點上每次處理一行) - 有限的擴展性功能上和上述等價，但是運行更快 (所有數(shù)據(jù)分區(qū)并行處理) - 高擴展性第30頁，共40頁。為什么要分區(qū)？查詢擴展性架構限制數(shù)據(jù)裝載維護操作并行插入/刪除備份和恢復恢復日志 第31頁，共40頁。查詢擴展性最顯著的原因之一將一個大的數(shù)據(jù)庫分成多個小的數(shù)據(jù)庫可以提高查詢的性能，因為每個數(shù)據(jù)庫分區(qū)擁有自己的一小部分數(shù)據(jù)假設您想掃描1億條記錄對一個單一分區(qū)的數(shù)據(jù)庫來講，該掃描操作需要數(shù)據(jù)庫管理器獨立掃描一億條記錄如果您將數(shù)據(jù)庫系統(tǒng)做成50個分區(qū)，并將這1億條記錄平均分配到這50個分區(qū)上，那么每個數(shù)據(jù)庫分區(qū)的數(shù)據(jù)庫管理器將只掃描200萬記錄第32頁，共40頁。架構限制在DB2 V8和以前版本，非分區(qū)的最大的表取決于頁面大小64 GB 到 512 GB這是表空間限制造成的表和表空間大小限制是每個分區(qū)上的限制 因此