周淳針對大數(shù)據(jù)量環(huán)境下分析型應用的支持方案樣本

DTCCDM針對大數(shù)據(jù)量環(huán)境下分析型應用支持方案大綱·一種實際案例·挑戰(zhàn)和解決方案·下一步工作規(guī)劃

DTCCDTCC一種實際案例案例簡介

DTCC·海量數(shù)據(jù)·基于已有硬件投資–單服務器節(jié)點–操作庫和分析庫合并·以查詢分析為主，兼顧少量數(shù)據(jù)維護硬件與拓撲千兆互換機

DTCC應用服務器數(shù)據(jù)匯總文本數(shù)據(jù)源文本Excel數(shù)據(jù)

數(shù)據(jù)清洗與入庫

數(shù)據(jù)庫服務器P550Cpux4Mem32GB

P550Cpux4Mem32GB源

源

16X1TBSASRAID5文本數(shù)據(jù)源數(shù)據(jù)文本數(shù)據(jù)源數(shù)據(jù)案例簡介-數(shù)據(jù)

DTCC·以常規(guī)數(shù)據(jù)為主，重要為數(shù)值、字符串、時間類型·日增長數(shù)據(jù)量為約56G，3億條元組·當前數(shù)據(jù)量3TB·最大單表為計費表，當前約150億條記錄·數(shù)據(jù)保存后歸檔為歷史數(shù)據(jù)·在線數(shù)據(jù)規(guī)模將超過400TB典型業(yè)務流程

DTCC–源數(shù)據(jù)清洗入庫–分析記錄型查詢·第一步過濾篩選條件不擬定·試錯式查詢分析過程，成功后固化，普通包括20各種環(huán)節(jié)·大規(guī)模連接查詢、子查詢、聯(lián)合查詢、數(shù)據(jù)分組與排序、臨時成果集與暫時表等·復雜SQL不多，但IO非常大–尋常數(shù)據(jù)維護·手工修改記錄內容·批量刪除·定期維護案例需求

DTCC·核心在查詢性能–第一種過濾環(huán)節(jié)·篩選字段由顧客隨機定義，因而無法使用索引·普通會得到千萬級別成果集–大量多表連接查詢·數(shù)據(jù)裝載性能·初始入庫48億條，近1T：限48小時，相稱于3萬條/s·后續(xù)每3天入庫一次，9億條，168G，限10小時內完畢DTCC挑戰(zhàn)-核心是性能原有產品難以支持分析型應用DTCC·······

只支持行式存儲查詢優(yōu)化器比較簡陋虛擬機實現(xiàn)不盡合理物理存儲設計有待優(yōu)化日記系統(tǒng)過于復雜不能充分運用多機資源提高性能數(shù)據(jù)分片技術不完善于開始新一代產品DM7研制DTCC實驗室原型技術積累階段實現(xiàn)各類原則

持續(xù)技術積累5.6引入物理操作符,虛擬機6.0引入高檔特性和oracle兼容特性

5

DM7穩(wěn)定性及功能與開源系統(tǒng)有差距

3

DM5.6

4

DM6

對DM4-DM6技術總結融合列存儲與行存儲基于向量數(shù)據(jù)1DM1-DM3

2

DM4

執(zhí)行內核原生MVCCOLAP應用支持1988-DM系統(tǒng)研制歷程DM系統(tǒng)研制歷程對于性能理解

DTCC應用系統(tǒng)設計表達式計算

優(yōu)化器綜合性能數(shù)據(jù)/控制權傳遞

I/O效率并發(fā)/并行數(shù)據(jù)控制權傳遞-批量技術DTCC–向量數(shù)據(jù)解決–在數(shù)據(jù)泵一次傳送一批數(shù)據(jù)–減少控制轉移CPU損耗；–有助于批量表達式計算老式數(shù)據(jù)傳遞PROJECTFILTER

一次只傳遞一條記錄每個操作符一次只解決一行記錄1

1

1

…

控制權需要重復傳遞SCANDTCCDTCC向量式數(shù)據(jù)傳遞PROJECT減少控制權限重復傳遞提高CPU有效運用率FILTER便于表達式批量計算SCAN12…N12…N…………12…N12…N…………DTCCDTCC批量技術-數(shù)據(jù)入庫

DTCC–將系統(tǒng)初始數(shù)據(jù)入庫–原有BCP接口達到5000條/s，仍無法滿足規(guī)定–改進：·在服務器端實現(xiàn)批量，減少執(zhí)行流程中控制跳轉·效率提高８倍批量技術-全表更新

DTCC普通批量

普通批量綁定針對大表更新特定批量綁定消息

籌劃生成生成特定計劃，減少執(zhí)行流程

單趟掃描一種ID進行更新，執(zhí)行20萬次ID進行排序，單趟掃描20萬個ID并進行更新性能提高100倍以上，控制在2秒以內批量技術-LIKE謂詞·selectcount(*)fromorderswhereo_commentnotlike'%special%requests%‘

DTCCDBMS‘O’11g:

3.3DBMS‘S’：10DM7:

0.4orders：1,500,000記錄cpu2.2G,多次執(zhí)行DTCC·一種表達式浮現(xiàn)多次–Selectsum(2*c1)，sum(3*(2*c1))fromt·只計算一次，成果緩存–v1=2*c1；–Selectsum(v1)，sum(3*v1)fromt·類似思路：中間成果重用–一種復雜查詢在一條sql語句中使用多次狀況–將復雜查詢提取，并將成果緩存，多次使用表達式計算-表達式計算-表達式成果重用批量表達式計算for(i=0；i<n；i++){r=(int64)opr1[i]+opr2;if(r!=(int)r)returnEC_DATA_OVERFLOW;}

res[i]

=(int)r;

···

一次計算一批數(shù)據(jù)運用CPUCACHE運用CPUSIMD特性·

避免老式DBMS函數(shù)重復調用代價··

接近于C效率比一次一行模式快10-100倍以上·虛擬機支持批量計算指令DTCC·虛擬機支持批量計算指令DTCC批量尺寸對性能影響·

SF=1，TPCHQ1·BDTA_SIZE：可配置批量大小參數(shù)·增大BDTA_SIZE可以有效提高執(zhí)行效率DTCCDTCCDTCCTPCHDM7DBMS‘O’TPCHDM7DBMS‘O’11PGSQL8.3DBMS‘S’2005Q121.579.154.622.08Q131.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.16TPCHTPCHDM7DBMS‘O’11PGSQL8.3DBMS‘S’2005Q11.3149.0916.0112.87Q20.160.0460.190.14Q30.8621.619.302.78Q40.989.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=1TPC-H/SF=1對比測試(S)優(yōu)化器-分析器流程

DTCCSQL腳本語法分析語法樹語義分析SFW構造關系代數(shù)變換關系樹代價優(yōu)化優(yōu)化了關系樹物理籌劃生成執(zhí)行籌劃智能優(yōu)化器·基于多趟分析代價優(yōu)化器·語義分析、代價優(yōu)化過程分離·靈活籌劃變換控制·基于時間單位(ms)代價計算·解決記錄信息使用性問題·增長頻率直方圖·增長高度直方圖桶數(shù)

DTCC查詢優(yōu)化：關系變換

DTCC·SFW構造轉換為關系樹

Select：ID，nameFrom：T

SFW構造–投影(PROJECT)–連接(JOIN)–半連接(SEMIJOIN)–選取(SELECT)–基本表(BASETABLE)

Where：ID=10PROJECT(ID，name)SELECT(ID=10)BASE_TABLE(T)

關系樹查詢優(yōu)化：關系變換核心DTCC·消除子查詢，“平坦”關系樹·子查詢一律轉化為半連接（SEMIJOIN）例：selectfromT1wheret1.idin(selectIDfromT2)PROJECTSEMIJOINT1

T2查詢優(yōu)化：待選關系樹生成DTCC·考慮三個因素·A.擬定連接順序·B.擬定卡特蘭2叉樹形狀·C.與否下放過濾條件·采用暫時成果減少重復計算·代價模型基本覆蓋所有狀況·對連接表個數(shù)非常多狀況,特殊解決查詢優(yōu)化：記錄信息

DTCC·記錄數(shù)據(jù)分布狀況，用于精準行數(shù)預計，特別是數(shù)據(jù)分布不規(guī)則狀況，對基數(shù)及代價計算有重大影響·頻率直方圖：不同值較少

·等高直方圖：不同值較多

40504000

4002

3990

4032

39803950390038503800

3950

3960

3888DTCC·列存儲:–數(shù)據(jù)按列存儲–結合自適應壓縮技術–與批量計算技術緊密結合·列存儲優(yōu)缺陷–大幅提高掃描性能–適合批量裝載與刪除–不適合頻繁插入、刪除和更新·融合列存儲和行存儲–提供按列存儲選項–結合分區(qū)技術–同步適應OLAP和OLTP應用需求I/O效率I/O效率-融合列存儲和行存儲I/O效率·行存儲優(yōu)化–簡化物理記錄格式–字段物理順序與邏輯順序分離·多buffer類型–常駐內存和常規(guī)方式裁減–顧客可以指定·批量讀：預解決·支持垂直分區(qū)和水平分區(qū)

DTCC提高并發(fā)度·支持并行插入物理數(shù)據(jù)存儲·并行備份和恢復·分區(qū)技術及相應并行查詢操作符號

DTCC典型場景一：大成果集

DTCC·場景描述–某表T，31個字段，48億條記錄–隨機基于某字段篩選：SELECT*FROMTWHEREFLD1=753–查詢符合條件成果集達到千萬條記錄·分析––––

SQL語句非常簡樸，沒有更優(yōu)等效語句成果集篩選條件不擬定，無法使用索引服務器內存為32G，在掃描過程中必然浮現(xiàn)頁面裁減由于基本數(shù)據(jù)量大，因而雖然命中率不高（0.2%），也會生成960萬條記錄成果集典型場景一：大成果集

DTCC從3個方向入手，提高全表掃描IO效率·批量技術·減少成果集解決時間消耗·調節(jié)數(shù)據(jù)頁讀取方略典型場景一：大成果集

DTCC·返回成果集方略改進–優(yōu)化前·依照通信塊大小決定成果集分批次返回數(shù)量·第一批成果集返回后，自動完畢后續(xù)成果集獲取和返回–優(yōu)化后·由應用設定第一批成果集大小和返回時機·當返回第一批成果集后，工作線程暫停SQL查詢祈求，直到下一批成果集祈求到來或開始新事務–效果·迅速返回某些成果集，提高顧客體驗·避免自動返回所有成果集，減少服務器資源消耗典型場景一：大成果集

DTCC·調節(jié)數(shù)據(jù)讀取方略–數(shù)據(jù)頁（page）是數(shù)據(jù)讀寫單位–優(yōu)化前全表掃描：按頁讀取，每次IO只掃描一種頁–優(yōu)化后：一次掃描各種頁，減少IO數(shù)量–測試：通過優(yōu)化后，磁盤吞吐量提高1倍典型場景二：大表連接

DTCC·場景描述–表T1，31個字段，5000W條記錄，數(shù)據(jù)類型涉及int、varchar、datetime、Dec；表T2,15個字段，500W條記錄，數(shù)據(jù)類型涉及varchar、datetime、Dec；–SELECTT1.NAME，T2.TITLEFROMPERSON.PERSONT1，RESOURCES.EMPLOYEET2WHERET1.PERSONID=T2.PERSONIDANDT1.SEX='M';–連接查詢字段由最后顧客暫時指定，表上未建索引–成果集不大，但查詢表數(shù)據(jù)量大，連接查詢響應時間陡增典型場景二：大表連接

DTCC·分析·行存儲特性：連接查詢所連接字段在數(shù)據(jù)頁中存儲非持續(xù)，進行連接查詢，需將所有數(shù)據(jù)頁讀到內存，IO消耗巨大；·連接匹配時，要對讀入緩存中所有頁進行掃描?！ば写鎯Γ酣C連接列分散在每個數(shù)據(jù)頁中Cn+1

頁1

…

Cn+1

頁NC1C2C1C2…CnC1…Cm…………C1C1C2…CnC1…Cm…………典型場景二：大表連接

DTCC·優(yōu)化方向：列存儲–按字段存儲–連接列被集中存儲Cn+1Cn+1…頁1

Cn+1

頁N–讀入緩存中數(shù)據(jù)頁明顯減少，系統(tǒng)IO下降C1C1C1C1…C1C2C2…C3…Cm…………典型場景二：大表連接·優(yōu)化方向：存儲壓縮–合用于列存儲模式壓縮算法–初步壓縮成果：

DTCC····

采用本案例數(shù)據(jù)進行測試Float54%（壓縮后大小/壓縮前大?。〥ouble33%Dec52%·

字符

56%典型場景二：大表連接·優(yōu)化效果從17小時降至10分鐘以內

DTCC典型場景三：全表查詢建表DTCC·場景描述–表T,15個字段，500W條記錄，數(shù)據(jù)類型涉及int、varchar、datetime、Dec；–依照T進行查詢建表：CREATETABLETTasSELECT*FROMT;典型場景三：全表查詢建表DTCC·分析–大表進行查詢建表時，需通過如下五個環(huán)節(jié)初始化目標表

全表掃描

生成成果集

插入成果

事務提交–這個過程中可優(yōu)化操作有：查詢與成果集生成和大量數(shù)據(jù)插入操作典型場景三：全表查詢建表DTCC·直接B樹操作–避免成果集解決與數(shù)據(jù)插入操作–直接復制根節(jié)點和葉子是在內存中進行操作，速度更快·優(yōu)化效果對案例中T進行建表查詢–優(yōu)化前耗時約35S–優(yōu)化后耗時約4S，性能提高9倍

裝載表數(shù)據(jù)到內存源表B樹掃描復制B樹典型場景四：重復表達式計算DTCC·場景描述–針對500萬條登記表進行如下查詢–SELECTIDnum,sub(6,8,IDnum)as生日,(now()-sub(6,8,IDnum))as年齡from…·問題分析·表達式sub(6,8,IDnum)可重用典型場景四：重復表達式計算DTCC·改進優(yōu)化：–一種表達式浮現(xiàn)多次，只計算一次–本例中性能提高70%。其她場景性能提高限度取決于計算表達式復雜度與數(shù)據(jù)量典型場景五：并行查詢插入DTCC·場景描述–同構造表T1~T10，每張表500萬條記錄，需要將10張表所有數(shù)據(jù)合并到一種暫時表Ttmp中–INSERTINTOTtmpSELECT*FROMT1–INSERTINTOTtmpSELECT*FROMT2。。。–應用并行化并沒有帶來較大提高–分析–Ttmp成為瓶頸：原有邏輯Rowid成為資源瓶頸–邏輯Rowid：不代表物理存儲位置，更新、插入、重組等操作代價減少，但Rowid需要通過臨界資源獲取–原有產品針對OLTP業(yè)務場景，OLTP事務以分散、短小事務為主，原有RowID機制不會成為突出瓶頸典型場景五：并行查詢插入DTCC·改進–物理RowID：代表記錄物理存儲位置–各種工作線程進行插入操作，無需進入臨界資源獲取rowid，每個工作線程自行生成RowID–實現(xiàn)真正意義上并發(fā)插入應用優(yōu)化

DTCC·好性能需要應用與數(shù)據(jù)庫配合實現(xiàn)–應用架構設計應站在系統(tǒng)全局考慮性能問題–應用與數(shù)據(jù)庫應當取長補短·數(shù)據(jù)存儲–基于分區(qū)表進行數(shù)據(jù)劃分·應用并行化–復雜事務分解為各種可并行簡樸事務應用優(yōu)化-手段

DTCC····

保存第一步過濾成果集運用視圖減少中間成果集保存數(shù)據(jù)按月份分區(qū)TOP查詢減少不必要全成果集應用優(yōu)化-大表全表掃描DTCC·典型場景–5000萬無索引TOP查詢：SELECT*FROMT6WHERENAMELIKE‘張三’–優(yōu)化前：數(shù)據(jù)庫服務器CPU滿載而應用服務器沒有負載–在最壞狀況下，將需要掃描整個表·分析：–系統(tǒng)設計需要站在全局角度，充分考慮應用、中間件、數(shù)據(jù)庫之間負載分派–充分運用已有硬件應用優(yōu)化-大表全表掃描DTCC·改進：·數(shù)據(jù)進行分表和分區(qū)·DM已實現(xiàn)分區(qū)表并行查詢操作符，提供了分區(qū)表優(yōu)化支持·應用根據(jù)分表更改查詢模塊，從單線程改為多線程·在應用服務器將各分表查詢成果合并·效果：·按最壞狀況測試，查詢時間由本來不可預期，提高到2分鐘內應用優(yōu)化-數(shù)據(jù)清洗與入庫DTCC–最初方式：–基于JDBC驅動數(shù)據(jù)遷移工具進行清洗和入庫操作–批量綁定–遷移工具資源消耗隨著遷移時間持續(xù)增長，導致遷移速度在運營3天后急劇下降–初始數(shù)據(jù)（1T）入庫時間達到1個月，相稱于400條/s應用優(yōu)化-數(shù)據(jù)清洗與入庫DTCC–問題分析：–超過100億條記錄，雖然每5000條提交一次，也有2百萬次解析-籌劃-代價-執(zhí)行流程–大量數(shù)據(jù)庫redo與undo日記操作–解決方案–運用批量＋ＢＣＰ–運用并行化充分發(fā)揮多CPU解決能力，增長IO吞吐量–JDBC方式轉變?yōu)镴NI+ODBC–實現(xiàn)動態(tài)編譯型ETL腳本引擎DTCC圖DMETL內嵌BCP應用優(yōu)化-DMETL技術改進應用優(yōu)化-DMETL技術改進DTCC應用優(yōu)化-數(shù)據(jù)劃分和并行化應用優(yōu)化-數(shù)據(jù)劃分和并行化應用優(yōu)化-BCP

DTCC·將清洗與入庫分離·并行化清洗和裝載入庫·入庫應用BCP方式·通過批量綁定減少了網絡開銷·服務器內部為BCP專門實現(xiàn)了”bcp_fast_insert”辦法·繞過SQL解決流程，直接操作B樹葉子節(jié)點·不進行Redo與Undo·不進行約束檢查·對原有BCP也進行了服務器端批量化解決最后