DM針對(duì)大數(shù)據(jù)量環(huán)境下分析型應(yīng)用的支持方案

1、DTCC2011DM針對(duì)大數(shù)據(jù)量環(huán)境下分析型應(yīng)用的支持方案大綱·一個(gè)實(shí)際案例·挑戰(zhàn)和解決方案·下一步工作規(guī)劃DTCC2011DTCC2011一個(gè)實(shí)際案例案例簡(jiǎn)介DTCC2011· 海量數(shù)據(jù)· 基于已有硬件投資 單服務(wù)器節(jié)點(diǎn) 操作庫(kù)和分析庫(kù)合并· 以查詢分析為主，兼顧少量數(shù)據(jù)維護(hù)硬件與拓?fù)淝д捉粨Q機(jī)DTCC2011應(yīng)用服務(wù)器數(shù)據(jù)匯總文本數(shù)據(jù)源文本 Excel數(shù)據(jù)數(shù)據(jù)清洗與入庫(kù)數(shù)據(jù)庫(kù)服務(wù)器P550Cpu x 4Mem 32GBP550Cpu x 4Mem 32GB源源16 X 1TB SASRAID 5文本數(shù)據(jù)源數(shù)據(jù)案例簡(jiǎn)介-數(shù)據(jù)DTCC

2、2011· 以常規(guī)數(shù)據(jù)為主，主要為數(shù)值、字符串、時(shí)間類型· 日增長(zhǎng)數(shù)據(jù)量為約56G，3億條元組· 當(dāng)前數(shù)據(jù)量3TB· 最大單表為計(jì)費(fèi)表，目前約150億條記錄· 數(shù)據(jù)保存20年后歸檔為歷史數(shù)據(jù)· 在線數(shù)據(jù)規(guī)模將超過(guò)400TB典型業(yè)務(wù)流程DTCC2011 源數(shù)據(jù)清洗入庫(kù) 分析統(tǒng)計(jì)型查詢· 第一步過(guò)濾的篩選條件不確定· 試錯(cuò)式的查詢分析過(guò)程，成功后固化，一般包含20多個(gè)步驟· 大規(guī)模的連接查詢、子查詢、聯(lián)合查詢、數(shù)據(jù)分組與排序、臨時(shí)結(jié)果集與臨時(shí)表等· 復(fù)雜SQL不多，但I(xiàn)O非常大 日常數(shù)據(jù)維護(hù)

3、3; 手工修改記錄內(nèi)容· 批量刪除· 定期維護(hù)案例需求DTCC2011· 關(guān)鍵在查詢性能 第一個(gè)過(guò)濾步驟· 篩選字段由用戶隨機(jī)定義，因此無(wú)法使用索引· 一般會(huì)得到千萬(wàn)級(jí)別的結(jié)果集 大量的多表連接查詢· 數(shù)據(jù)裝載性能· 初始入庫(kù)48億條，近1T：限48小時(shí)，相當(dāng)于3萬(wàn)條/s· 后續(xù)每3天入庫(kù)一次，9億條，168G，限10小時(shí)內(nèi)完成DTCC2011挑戰(zhàn)-核心是性能原有產(chǎn)品難以支持分析型應(yīng)用DTCC2011·······只支持行式存儲(chǔ)查詢優(yōu)化器比較簡(jiǎn)陋

4、虛擬機(jī)實(shí)現(xiàn)不盡合理物理存儲(chǔ)設(shè)計(jì)有待優(yōu)化日志系統(tǒng)過(guò)于復(fù)雜不能充分利用多機(jī)資源提升性能數(shù)據(jù)分片技術(shù)不完善于2009年開(kāi)始新一代產(chǎn)品DM7的研制DTCC2011實(shí)驗(yàn)室原型技術(shù)積累階段實(shí)現(xiàn)各類標(biāo)準(zhǔn)持續(xù)的技術(shù)積累5.6引入物理操作符,虛擬機(jī)6.0引入高級(jí)特性和oracle 兼容特性5DM72011穩(wěn)定性及功能與開(kāi)源系統(tǒng)有差距3DM5.64DM62009對(duì)DM4-DM6的技術(shù)總結(jié)融合列存儲(chǔ)與行存儲(chǔ)基于向量數(shù)據(jù)的1DM1-DM32DM420042007執(zhí)行內(nèi)核原生的MVCCOLAP應(yīng)用的支持1988-2003DM系統(tǒng)研制歷程對(duì)于性能的理解DTCC2011應(yīng)用系統(tǒng)的設(shè)計(jì)表達(dá)式計(jì)算優(yōu)化器綜合性能數(shù)據(jù)/控制權(quán)傳遞

5、I/O效率并發(fā)/并行數(shù)據(jù)控制權(quán)傳遞-批量技術(shù) DTCC2011 向量數(shù)據(jù)處理 在數(shù)據(jù)泵一次傳送一批數(shù)據(jù) 減少控制轉(zhuǎn)移的CPU損耗； 有利于批量的表達(dá)式計(jì)算傳統(tǒng)的數(shù)據(jù)傳遞PROJECTFILTER一次只傳遞一條記錄每個(gè)操作符一次只處理一行記錄111控制權(quán)需要反復(fù)傳遞SCANDTCC2011向量式的數(shù)據(jù)傳遞PROJECT減少控制權(quán)限的反復(fù)傳遞提升CPU的有效利用率FILTER便于表達(dá)式批量計(jì)算SCAN12N12NDTCC2011批量技術(shù)-數(shù)據(jù)入庫(kù)DTCC2011 將系統(tǒng)的初始數(shù)據(jù)入庫(kù) 原有BCP接口達(dá)到5000條/s，仍無(wú)法滿足要求 改進(jìn)：· 在服務(wù)器端實(shí)現(xiàn)批量，減少執(zhí)行流程中的控制跳轉(zhuǎn)

6、· 效率提升倍批量技術(shù)-全表更新DTCC2011普通批量普通批量綁定針對(duì)大表更新的特定的批量綁定消息計(jì)劃生成生成特定計(jì)劃，減少執(zhí)行流程單趟掃描一個(gè)ID進(jìn)行更新，執(zhí)行20萬(wàn)次ID進(jìn)行排序，單趟掃描20萬(wàn)個(gè)ID并進(jìn)行更新性能提升100倍以上，控制在2秒以內(nèi)批量技術(shù)-LIKE謂詞· select count(*) from orders whereo_comment not like'%special%requests%DTCC2011DBMS O 11g:3.3DBMS S 2005: 10DM7:0.4orders : 1,500,000記錄cpu 2.2G,多次執(zhí)行

7、DTCC2011· 一個(gè)表達(dá)式出現(xiàn)多次 Select sum(2 * c1), sum(3 * (2 * c1) from t· 只計(jì)算一次，結(jié)果緩存 v1 = 2 * c1； Select sum(v1), sum(3 * v1) from t· 類似思路：中間結(jié)果重用 一個(gè)復(fù)雜查詢?cè)谝粭lsql語(yǔ)句中使用多次的情況 將復(fù)雜查詢提取，并將結(jié)果緩存，多次使用表達(dá)式計(jì)算-表達(dá)式結(jié)果重用批量表達(dá)式計(jì)算for (i = 0; i < n; i+)r = (int64)opr1i + opr2;if (r != (int)r)return EC_DATA_OVERFL

8、OW;resi= (int)r;···一次計(jì)算一批數(shù)據(jù)利用CPU的CACHE利用CPU的SIMD特性·避免傳統(tǒng)DBMS的函數(shù)反復(fù)調(diào)用代價(jià)··接近于C的效率比一次一行模式快10-100倍以上·虛擬機(jī)支持批量計(jì)算指令DTCC2011批量尺寸對(duì)性能的影響·SF=1, TPCH Q1· BDTA_SIZE: 可配置的批量大小參數(shù)· 增大BDTA_SIZE可以有效的提高執(zhí)行效率DTCC2011DTCC2011TPCHDM7DBMSO11PGSQL8.3DBMSS2005Q121.579.154.622.0

9、8Q131.734.475.1312.03Q140.718.972.801.16Q150.668.985.511.04Q160.870.334.525.41Q171.038.94>1001.80Q181.279.2122.012.90Q191.929.065.624.17Q200.789.23>1000.79Q212.248.8833.015.49Q220.240.34>1001.16TPCHDM7DBMSO11PGSQL8.3DBMSS2005Q11.3149.0916.0112.87Q20.160.0460.190.14Q30.8621.619.302.78Q40.989

10、.030.800.68Q51.49.054.611.58Q60.7892.720.96Q71.6111.7319.542.35Q82.30.282.972.01Q931.6118.015.45Q101.369.165.832.23Q110.1944.670.550.46TPC-H /SF=1對(duì)比測(cè)試(S)優(yōu)化器-分析器流程DTCC2011SQL腳本語(yǔ)法分析語(yǔ)法樹(shù)語(yǔ)義分析SFW結(jié)構(gòu)關(guān)系代數(shù)變換關(guān)系樹(shù)代價(jià)優(yōu)化優(yōu)化了的關(guān)系樹(shù)物理計(jì)劃生成執(zhí)行計(jì)劃智能優(yōu)化器· 基于多趟分析的代價(jià)優(yōu)化器· 語(yǔ)義分析、代價(jià)優(yōu)化過(guò)程分離· 靈活的計(jì)劃變換控制· 基于時(shí)間單位(ms)的代

11、價(jià)計(jì)算· 解決統(tǒng)計(jì)信息的使用性問(wèn)題· 增加頻率直方圖· 增加高度直方圖的桶數(shù)DTCC2011查詢優(yōu)化：關(guān)系變換DTCC2011· SFW結(jié)構(gòu)轉(zhuǎn)換為關(guān)系樹(shù)Select : ID , nameFrom : TSFW結(jié)構(gòu)投影(PROJECT)連接(JOIN)半連接(SEMI JOIN)選擇(SELECT)基本表(BASE TABLE)Where : ID = 10PROJECT(ID , name )SELECT(ID = 10)BASE _TABLE (T)關(guān)系樹(shù)查詢優(yōu)化：關(guān)系變換的關(guān)鍵DTCC2011· 消除子查詢，“平坦”的關(guān)系樹(shù)·

12、子查詢一律轉(zhuǎn)化為半連接（SEMI JOIN）例：select from T1 where t1.id in (select ID from T2)PROJECTSEMIJOINT1T2查詢優(yōu)化：待選關(guān)系樹(shù)的生成DTCC2011· 考慮三個(gè)因素· A.確定的連接次序· B.確定的卡特蘭2叉樹(shù)形狀· C.是否下放過(guò)濾條件· 采用臨時(shí)結(jié)果減少重復(fù)計(jì)算· 代價(jià)模型基本覆蓋所有情況· 對(duì)連接表的個(gè)數(shù)非常多的情況,特殊處理查詢優(yōu)化：統(tǒng)計(jì)信息DTCC2011· 記錄數(shù)據(jù)分布情況，用于精確行數(shù)估計(jì)，特別是數(shù)據(jù)分布不規(guī)則的情況，對(duì)基

13、數(shù)及代價(jià)計(jì)算有重大影響· 頻率直方圖：不同值較少500450400350300250400200238432300200150100500124167w_id = 0w_id = 1w_id = 2w_id = 3w_id = 4w_id = 5w_id = 6· 等高直方圖：不同值較多4050400040023990403239803950390038503800395039603888DTCC2011· 列存儲(chǔ): 數(shù)據(jù)按列存儲(chǔ) 結(jié)合自適應(yīng)壓縮技術(shù) 與批量計(jì)算技術(shù)緊密結(jié)合· 列存儲(chǔ)優(yōu)缺點(diǎn) 大幅提升掃描性能 適合批量裝載與刪除不適合頻繁的插入、刪除和更新

14、· 融合列存儲(chǔ)和行存儲(chǔ) 提供按列存儲(chǔ)選項(xiàng)結(jié)合分區(qū)技術(shù) 同時(shí)適應(yīng)OLAP和OLTP應(yīng)用需求I/O效率-融合列存儲(chǔ)和行存儲(chǔ)I/O效率· 行存儲(chǔ)優(yōu)化簡(jiǎn)化物理記錄格式字段物理次序與邏輯次序分離· 多buffer類型常駐內(nèi)存和常規(guī)方式淘汰用戶可以指定· 批量讀：預(yù)處理· 支持垂直分區(qū)和水平分區(qū)DTCC2011提高并發(fā)度· 支持并行插入的物理數(shù)據(jù)存儲(chǔ)· 并行備份和恢復(fù)· 分區(qū)技術(shù)及相應(yīng)的并行查詢操作符號(hào)DTCC2011典型場(chǎng)景一：大結(jié)果集DTCC2011· 場(chǎng)景描述 某表T，31個(gè)字段，48億條記錄 隨機(jī)基于某字段篩

15、選：SELECT * FROM T WHEREFLD1=753 查詢符合條件的結(jié)果集達(dá)到千萬(wàn)條記錄· 分析SQL語(yǔ)句非常簡(jiǎn)單，沒(méi)有更優(yōu)的等效語(yǔ)句結(jié)果集篩選條件不確定，無(wú)法使用索引服務(wù)器內(nèi)存為32G，在掃描的過(guò)程中必然出現(xiàn)頁(yè)面淘汰由于基礎(chǔ)數(shù)據(jù)量大，因此即使命中率不高（0.2%），也會(huì)生成960萬(wàn)條記錄的結(jié)果集典型場(chǎng)景一：大結(jié)果集DTCC2011從3個(gè)方向入手，提升全表掃描的IO效率· 批量技術(shù)· 降低結(jié)果集處理的時(shí)間消耗· 調(diào)整數(shù)據(jù)頁(yè)讀取策略典型場(chǎng)景一：大結(jié)果集DTCC2011· 返回結(jié)果集策略改進(jìn) 優(yōu)化前· 根據(jù)通信塊大小決定結(jié)果集分

16、批次返回的數(shù)量· 第一批結(jié)果集返回后，自動(dòng)完成后續(xù)結(jié)果集獲取和返回 優(yōu)化后· 由應(yīng)用設(shè)定第一批結(jié)果集的大小和返回的時(shí)機(jī)· 當(dāng)返回第一批結(jié)果集后，工作線程暫停SQL查詢請(qǐng)求，直到下一批結(jié)果集請(qǐng)求到來(lái)或開(kāi)始新事務(wù) 效果· 快速返回部分結(jié)果集，提高用戶體驗(yàn)· 避免自動(dòng)返回所有結(jié)果集，降低服務(wù)器資源消耗典型場(chǎng)景一：大結(jié)果集DTCC2011· 調(diào)整數(shù)據(jù)讀取策略 數(shù)據(jù)頁(yè)（page）是數(shù)據(jù)讀寫(xiě)的單位 優(yōu)化前的全表掃描：按頁(yè)讀取，每次IO只掃描一個(gè)頁(yè) 優(yōu)化后：一次掃描多個(gè)頁(yè)，減少IO數(shù)量 測(cè)試：經(jīng)過(guò)優(yōu)化后，磁盤的吞吐量提升1倍典型場(chǎng)景二：大表連接DT

17、CC2011· 場(chǎng)景描述 表T1，31個(gè)字段，5000W條記錄，數(shù)據(jù)類型包括int、varchar、datetime、Dec；表T2,15個(gè)字段，500W條記錄，數(shù)據(jù)類型包括varchar、datetime、Dec； SELECT T1.NAME, T2.TITLE FROM PERSON.PERSONT1, RESOURCES.EMPLOYEE T2 WHERET1.PERSONID = T2.PERSONID AND T1.SEX = 'M' 連接查詢字段由最終用戶臨時(shí)指定，表上未建索引 結(jié)果集不大，但查詢表數(shù)據(jù)量大，連接查詢響應(yīng)時(shí)間陡增典型場(chǎng)景二：大表連接DTC

18、C2011· 分析· 行存儲(chǔ)特性：連接查詢所連接的字段在數(shù)據(jù)頁(yè)中的存儲(chǔ)非連續(xù)，進(jìn)行連接查詢，需將所有數(shù)據(jù)頁(yè)讀到內(nèi)存，IO消耗巨大；· 連接匹配時(shí)，要對(duì)讀入緩存中的所有頁(yè)進(jìn)行掃描。· 行存儲(chǔ)：連接列分散在每個(gè)數(shù)據(jù)頁(yè)中Cn+1頁(yè)1Cn+1頁(yè)NC1C2CnC1CmC1C2CnC1Cm典型場(chǎng)景二：大表連接DTCC2011· 優(yōu)化方向：列存儲(chǔ)按字段存儲(chǔ)連接列被集中存儲(chǔ)Cn+1 Cn+1頁(yè)1Cn+1頁(yè)N讀入緩存中的數(shù)據(jù)頁(yè)明顯減少，系統(tǒng)IO下降C1C1C1C2C2C3Cm典型場(chǎng)景二：大表連接· 優(yōu)化方向：存儲(chǔ)壓縮 適用于列存儲(chǔ)模式的壓縮算法 初步壓

19、縮結(jié)果：DTCC2011····采用本案例數(shù)據(jù)進(jìn)行測(cè)試Float 54%（壓縮后大小/壓縮前大?。〥ouble 33%Dec 52%·字符56%典型場(chǎng)景二：大表連接· 優(yōu)化效果從17小時(shí)降至10分鐘以內(nèi)DTCC2011典型場(chǎng)景三：全表查詢建表DTCC2011· 場(chǎng)景描述 表T,15個(gè)字段，500W條記錄，數(shù)據(jù)類型包括int、varchar、datetime、Dec ； 根據(jù)T進(jìn)行查詢建表：CREATE TABLE TT as SELECT * FROMT;典型場(chǎng)景三：全表查詢建表DTCC2011· 分析 大表進(jìn)行查

20、詢建表時(shí)，需經(jīng)過(guò)以下五個(gè)步驟初始化目標(biāo)表全表掃描生成結(jié)果集插入結(jié)果事務(wù)提交 這個(gè)過(guò)程中可優(yōu)化的操作有：查詢與結(jié)果集的生成和大量數(shù)據(jù)的插入操作典型場(chǎng)景三：全表查詢建表DTCC2011· 直接B樹(shù)操作 避免結(jié)果集處理與數(shù)據(jù)插入操作 直接復(fù)制根節(jié)點(diǎn)和葉子是在內(nèi)存中進(jìn)行操作，速度更快· 優(yōu)化效果對(duì)案例中的T進(jìn)行建表查詢 優(yōu)化前耗時(shí)約35S 優(yōu)化后耗時(shí)約4S，性能提升9倍裝載表數(shù)據(jù)到內(nèi)存源表B樹(shù)掃描復(fù)制B樹(shù)典型場(chǎng)景四：重復(fù)表達(dá)式計(jì)算DTCC2011· 場(chǎng)景描述 針對(duì)500萬(wàn)條記錄的表進(jìn)行如下查詢 SELECT IDnum,sub(6,8,IDnum) as 生日,(now(

21、)-sub(6,8,IDnum) as 年齡 from · 問(wèn)題分析· 表達(dá)式sub(6,8,IDnum) 可重用典型場(chǎng)景四：重復(fù)表達(dá)式計(jì)算DTCC2011· 改進(jìn)優(yōu)化： 一個(gè)表達(dá)式出現(xiàn)多次，只計(jì)算一次 本例中性能提升70%。其他場(chǎng)景性能提升程度取決于計(jì)算表達(dá)式的復(fù)雜度與數(shù)據(jù)量典型場(chǎng)景五：并行查詢插入DTCC2011· 場(chǎng)景描述 同結(jié)構(gòu)的表T1T10，每張表500萬(wàn)條記錄，需要將10張表的所有數(shù)據(jù)合并到一個(gè)臨時(shí)表Ttmp中 INSERT INTO Ttmp SELECT * FROM T1 INSERT INTO Ttmp SELECT * FROM T

22、2。 應(yīng)用的并行化并沒(méi)有帶來(lái)較大的提升 分析 Ttmp成為瓶頸：原有的邏輯Rowid成為資源瓶頸 邏輯Rowid：不代表物理存儲(chǔ)位置，更新、插入、重組等操作代價(jià)降低，但Rowid需要通過(guò)臨界資源獲取 原有產(chǎn)品針對(duì)OLTP業(yè)務(wù)場(chǎng)景，OLTP事務(wù)以分散、短小事務(wù)為主，原有的RowID機(jī)制不會(huì)成為突出瓶頸典型場(chǎng)景五：并行查詢插入DTCC2011· 改進(jìn) 物理RowID：代表記錄的物理存儲(chǔ)位置 多個(gè)工作線程進(jìn)行插入操作，無(wú)需進(jìn)入臨界資源獲取rowid，每個(gè)工作線程自行生成RowID 實(shí)現(xiàn)真正意義上的并發(fā)插入應(yīng)用優(yōu)化DTCC2011· 好的性能需要應(yīng)用與數(shù)據(jù)庫(kù)的配合實(shí)現(xiàn) 應(yīng)用架構(gòu)設(shè)計(jì)

23、應(yīng)站在系統(tǒng)全局考慮性能問(wèn)題 應(yīng)用與數(shù)據(jù)庫(kù)應(yīng)該取長(zhǎng)補(bǔ)短· 數(shù)據(jù)存儲(chǔ) 基于分區(qū)表進(jìn)行數(shù)據(jù)劃分· 應(yīng)用的并行化 復(fù)雜事務(wù)分解為多個(gè)可并行的簡(jiǎn)單事務(wù)應(yīng)用優(yōu)化-手段DTCC2011····保存第一步過(guò)濾結(jié)果集利用視圖減少中間結(jié)果集的保存數(shù)據(jù)按月份分區(qū)TOP查詢減少不必要的全結(jié)果集應(yīng)用優(yōu)化-大表的全表掃描 DTCC2011· 典型場(chǎng)景 5000萬(wàn)無(wú)索引TOP查詢：SELECT * FROM T6WHERE NAME LIKE 張三 優(yōu)化前：數(shù)據(jù)庫(kù)服務(wù)器CPU滿載而應(yīng)用服務(wù)器沒(méi)有負(fù)載 在最壞情況下，將需要掃描整個(gè)表· 分析： 系

24、統(tǒng)設(shè)計(jì)需要站在全局角度，充分考慮應(yīng)用、中間件、數(shù)據(jù)庫(kù)之間的負(fù)載分配 充分利用已有的硬件應(yīng)用優(yōu)化-大表的全表掃描 DTCC2011· 改進(jìn)：· 數(shù)據(jù)進(jìn)行分表和分區(qū)· DM已實(shí)現(xiàn)的分區(qū)表并行查詢操作符，提供了分區(qū)表優(yōu)化的支持· 應(yīng)用依據(jù)分表更改查詢模塊，從單線程改為多線程· 在應(yīng)用服務(wù)器將各分表的查詢結(jié)果合并· 效果：· 按最壞情況測(cè)試，查詢時(shí)間由原來(lái)的不可預(yù)期，提升到2分鐘內(nèi)應(yīng)用優(yōu)化-數(shù)據(jù)清洗與入庫(kù) DTCC2011 最初方式： 基于JDBC驅(qū)動(dòng)的數(shù)據(jù)遷移工具進(jìn)行清洗和入庫(kù)操作 批量綁定 遷移工具的資源消耗隨著遷移時(shí)間的持續(xù)增

25、加，導(dǎo)致遷移速度在運(yùn)行3天后急劇下降 初始數(shù)據(jù)（1T）入庫(kù)時(shí)間達(dá)到1個(gè)月，相當(dāng)于400條/s應(yīng)用優(yōu)化-數(shù)據(jù)清洗與入庫(kù) DTCC2011 問(wèn)題分析： 超過(guò)100億條記錄，即使每5000條提交一次，也有2百萬(wàn)次的解析-計(jì)劃-代價(jià)-執(zhí)行流程 大量的數(shù)據(jù)庫(kù)redo與undo日志操作 解決方案 利用批量 利用并行化充分發(fā)揮多CPU處理能力，增加IO的吞吐量 JDBC方式轉(zhuǎn)變?yōu)镴NI+ODBC 實(shí)現(xiàn)動(dòng)態(tài)編譯型的ETL腳本引擎DTCC2011圖 DMETL 內(nèi)嵌BCP應(yīng)用優(yōu)化-DM ETL的技術(shù)改進(jìn)DTCC2011應(yīng)用優(yōu)化-數(shù)據(jù)劃分和并行化應(yīng)用優(yōu)化-BCPDTCC2011· 將清洗與入庫(kù)分離· 并行化清洗和裝載入庫(kù)· 入庫(kù)應(yīng)用BCP方式· 通過(guò)批量綁定減少了網(wǎng)絡(luò)開(kāi)銷· 服務(wù)器內(nèi)部為BCP專門實(shí)現(xiàn)了”bcp_fast_insert”方法· 繞過(guò)SQL處理流程，直接操作B樹(shù)葉子節(jié)點(diǎn)· 不進(jìn)行Redo與Undo&#