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 這是表空間限制造