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

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

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

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

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

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

6、更多的查詢更多的資源更多的數(shù)據(jù)分區(qū)相同數(shù)據(jù)量：更快的查詢響應(yīng)時(shí)間更多的數(shù)據(jù)量：相同響應(yīng)時(shí)間OLTPDSS垂直擴(kuò)展水平擴(kuò)展擴(kuò)展擴(kuò)展第9頁，共40頁。I/O 層次頁面Page讀寫的基本單位擴(kuò)展塊Extent分配的基本單位頁面數(shù)可定制能且只能用于存放數(shù)據(jù)庫的一個(gè)對(duì)象（表、索引）通常為大的塊I/O大小容器Container DB2從中分配擴(kuò)展塊DMS文件 (/hello.dat)DMS 裸設(shè)備 (/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:包含擴(kuò)展塊的文件containercontainercontainertablespaceTablespace:表和索引的存放空間Cont 1Cont 2Cont 3第10頁，共40頁。總結(jié): 并行 I/ObufferPartition 0bufferPartition 2bufferIOIOIOIOIOIOIOIOIOagentagentagentPartition 1coordextents i/o s

8、erversContainersContainersContainersdb agents并行預(yù)取agentagentagent第11頁，共40頁。創(chuàng)建單節(jié)點(diǎn)Partition Groupcreate database partition group sdpg on dbpartitionnums(0); create database partition group pdpg on dbpartitionnums(1 TO 8);創(chuàng)建多節(jié)點(diǎn)Partition Group第12頁，共40頁。創(chuàng)建跨節(jié)點(diǎn)表空間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）自動(dòng)分布數(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ū) 目標(biāo) 1: 保證數(shù)據(jù)跨所

13、有分區(qū)平均分布選擇分區(qū)鍵 值多的字段數(shù)據(jù)分布均勻值少的字段導(dǎo)致數(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ù)分布 目標(biāo) 2 : 優(yōu)化連接處理和并行查詢Colocation連接匹配的數(shù)據(jù)行位于同一個(gè)分區(qū)，否則將發(fā)生跨數(shù)據(jù)分區(qū)的數(shù)據(jù)遷移 (非 colocated 連接)連接方法 訪問路徑選擇嵌套循環(huán)Nested-loop排序歸并Sort/merge哈希Hash笛卡爾連接Product join星型連接Star join分區(qū)和表隊(duì)列廣播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概念如果一個(gè)查詢需要連接兩張或多張表， collocation可以保證在分區(qū)內(nèi)部連接，無需將表重新做哈希分布否則，數(shù)據(jù)重定向?qū)?huì)發(fā)生實(shí)現(xiàn)如果所有連接的表在同一個(gè)數(shù)據(jù)庫分區(qū)組，且分區(qū)鍵值有相同數(shù)量的字段，且分區(qū)健字段的數(shù)據(jù)類型成對(duì)兼容那么，具有不同表具有相同分區(qū)鍵的數(shù)據(jù)行將會(huì)存放在同一個(gè)數(shù)據(jù)分區(qū)第18頁，共40頁。Collocated 連接CUST和SALE

16、S 表的分區(qū)鍵均定義CUST_ID字段上連接將在每個(gè)數(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ū)鍵是另外一個(gè)字段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ū)鍵是另外一個(gè)不同的字段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頁。定向的內(nèi)表和外表連接沒有一張表的分區(qū)鍵是CUST_ID字段兩張表均重新哈希，發(fā)送到新的數(shù)據(jù)庫分區(qū)后再連接表隊(duì)列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ū)鍵取值更廣泛整形字段比字符型字段更高效、字符型比十進(jìn)制數(shù)據(jù)類型更高效不做：長型字段不運(yùn)行唯一索引或主鍵必須是分區(qū)鍵的超集分區(qū)鍵不允許更改避免更新分區(qū)鍵字段V8.2開始提供分區(qū)建議向?qū)У?4頁，共40頁。數(shù)據(jù)遷移 - ImportImport程序?qū)⑤斎胛募?shù)據(jù)插入表或可更新視圖 ，如果接收數(shù)據(jù)的表或視圖已經(jīng)包含數(shù)據(jù)，可以選擇替換或追加方式導(dǎo)入數(shù)據(jù)part3part2part1part0Coord.WRITEsqlcodeHASHInsertsqlcodeImport from file of delinsert into tbl;第25頁，共40頁。遷移數(shù)據(jù) - Import 使用

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

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

27、mrdb2lmrdb2lpprtdb2lpprtdb2agentfilefile第28頁，共40頁。DB2 SQL : 并行方式思考DB2 SQL非常強(qiáng)大，幾乎在所有情況下，在數(shù)據(jù)庫中執(zhí)行完整的SQL比應(yīng)用程序從游標(biāo)中選擇獲取更快、擴(kuò)展性更強(qiáng)獲取數(shù)據(jù)行是串行的，在ETL處理中應(yīng)盡量避免（除非使用并行實(shí)用程序，如Ascential Parallel Extender 或Ab Initio)在任何情況下，運(yùn)行EXPLAIN 觀察在協(xié)調(diào)節(jié)點(diǎn)上運(yùn)行的子任務(wù): 觸發(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);游標(biāo)處理將會(huì)慢 (在協(xié)調(diào)節(jié)點(diǎn)上每次處理一行) - 有限的擴(kuò)展性功能上和上述等價(jià)，但是運(yùn)行更快 (所有數(shù)據(jù)分區(qū)并行處理) - 高擴(kuò)展性第30頁，共40頁。為什么要分區(qū)？查詢擴(kuò)展性架構(gòu)限制數(shù)據(jù)裝載維護(hù)操作并行插入/刪除備份和恢復(fù)恢復(fù)日志 第31頁，共40頁。查詢擴(kuò)展性最顯著的原因之一將一個(gè)大的數(shù)據(jù)庫分成多個(gè)小的數(shù)據(jù)庫可以提高查詢的性能，因?yàn)槊總€(gè)數(shù)據(jù)庫分區(qū)擁有自己的一小部分?jǐn)?shù)據(jù)假設(shè)您想掃描1億條記錄對(duì)一個(gè)單一分區(qū)的數(shù)據(jù)庫來講，該掃描操作需要數(shù)據(jù)庫管理器獨(dú)立掃描一億條記錄如果您將數(shù)據(jù)庫系統(tǒng)做成50個(gè)分區(qū)，并將這1億條記錄平均分配到這50個(gè)分區(qū)上，那么每個(gè)數(shù)據(jù)庫分區(qū)的數(shù)據(jù)庫管理器將只掃描200萬記錄第32頁，共40頁。架構(gòu)限制在DB2 V8和以前版本，非分區(qū)的最大的表取決于頁面大小64 GB 到 512 GB這是表空間限制造成的表和表空間大小限制是每個(gè)分區(qū)上的限制 因此