GaussDB(DWS)的explain performance詳解

在SQL調(diào)優(yōu)過程中，經(jīng)常使用explain performance，查看某個執(zhí)行比較慢的SQL語句的實際執(zhí)行信息和估算信息，通過對比實際執(zhí)行與優(yōu)化器的估算之間的差別，找到執(zhí)行中的瓶頸點，來為優(yōu)化提供依據(jù)。

Explain performance的結(jié)果包括七部分，分別為：

以表格形式顯示的計劃

Predicate Information (identified by plan id)

Memory Information (identified by plan id)

Targetlist Information (identified by plan id)

DataNode Information (identified by plan id)

User Define Profiling

====== Query Summary =====

下面，以8個DN，數(shù)據(jù)量為1TB的GaussDB中執(zhí)行tpcds的Q67為例，對這7部分分別進(jìn)行詳細(xì)介紹。具體的SQL語句及查詢計劃參考附件Q67.txt。

以表格形式顯示的計劃

表格字段解讀：

id：執(zhí)行算子節(jié)點編號。該編號只是為了唯一標(biāo)識每個算子，不代表算子的執(zhí)行順序。

operation：具體的執(zhí)行節(jié)點算子名稱。常用的算子參見下面的常用算子介紹章節(jié)。

A-time：當(dāng)前算子執(zhí)行完成時間，一般DN上執(zhí)行的算子的A-time是由[]括起來的兩個值，分別表示此算子在所有DN上完成的最短時間和最長時間。

一般，算子的A-time中包含其下子節(jié)點的執(zhí)行時間。比如：

Id為20號的Vector Sort算子的A-time中的第二個值，其A-time =?該算子本身的執(zhí)行時間?+ 21號算子的A-time，由此可以計算出來，20號算子的執(zhí)行時間?= 28201.498 – 4097.214?≈?24000，單位為ms。

通過這種方式，就可以幫我們找到執(zhí)行時間較長的算子，即瓶頸點，分析該算子是否可以優(yōu)化。比如下圖示例計劃中從id為16到21之間部分，如下圖所示。rollup函數(shù)使用sort耗時長，總耗時達(dá)到50s（即17號-20號算子的總耗時），（并行度32的情況下），20號算子Vector sort達(dá)24s，19號算子Vector Sore Aggregate達(dá)9s，18號算子做重分布達(dá)10s，17號算子hashagg達(dá)到8s。因此需要考慮對rollup類的分析函數(shù)考慮使用其它方式進(jìn)行優(yōu)化，比如用hashagg替代sortagg，避免大數(shù)據(jù)量排序性能降低。

另外，A-time中有兩個值，第一個是所有DN中執(zhí)行該算子用時最短的時間，第二個是所有DN中執(zhí)行該算子用時最長的時間。當(dāng)這兩個值相差較大時，說明存在計算傾斜。這兩個值偏差越大，表明此算子的計算傾斜（在不同DN上執(zhí)行時間差異）越大，人工干預(yù)調(diào)優(yōu)的必要性越大。

導(dǎo)致計算傾斜的原因，可能是數(shù)據(jù)在各DN上分布存在傾斜（可通過select * from table_distribution(‘schema_name’, ‘table_name’);查看表在各DN上的數(shù)據(jù)分布情況），也可能是DN之間的資源配置有差異，也可能是分布列設(shè)置不合理等。

對于stream算子來說，它的A-time比較特殊，stream算子主要是接收子節(jié)點的數(shù)據(jù)。它的子節(jié)點是在計劃開始執(zhí)行時，就啟動多線程并發(fā)執(zhí)行，而不是等著父節(jié)點向其請求數(shù)據(jù)時才觸發(fā)執(zhí)行。所以在stream的父節(jié)點向stream節(jié)點請求數(shù)據(jù)時，它的子節(jié)點如果已經(jīng)準(zhǔn)好了數(shù)據(jù)，那么stream節(jié)點的實際執(zhí)行時間就是接收數(shù)據(jù)的時間，因此有時可以看到stream算子的A-time比其子節(jié)點的A-time小，如上圖中的12號算子。

A-rows：表示當(dāng)前算子實際輸出的全局元組數(shù)，即所有DN輸出的元組數(shù)之和。

如果是復(fù)制表，則此列的值=表中總行數(shù)*DN數(shù)。

當(dāng)要多次讀取某個算子的執(zhí)行結(jié)果，比如，nestloop中，外表中的每條數(shù)據(jù)都要掃描一遍內(nèi)表的數(shù)據(jù)，即內(nèi)表數(shù)據(jù)要被多次rescan，此時，A-rows顯示的是多次loop之后的行數(shù)。

E-rows：每個算子估算的輸出行數(shù)。

A-row和E-row的差異體現(xiàn)了優(yōu)化器估算和實際執(zhí)行的偏差度。一般來說，偏差越大，越可以認(rèn)為優(yōu)化器生成的計劃越不可信，人工干預(yù)調(diào)優(yōu)的必要性越大。如果估算不準(zhǔn)，可能會導(dǎo)致如下的幾種問題：

采取不正確的連接方式。比如把內(nèi)表數(shù)據(jù)估計太小，可能會導(dǎo)致使用性能不好的nestloop方式進(jìn)行連接

搞反內(nèi)外表。如果估計的內(nèi)表數(shù)量較小，外表數(shù)量較大，但實際內(nèi)表數(shù)量大，外表數(shù)量小，就會導(dǎo)致數(shù)據(jù)量大的表作為了內(nèi)表，數(shù)量小的表作為了外表。導(dǎo)致連接性能不好。

采用不正確的重分布方式。如果要被分布的數(shù)據(jù)被估計的太小，會使得計劃采用boardcast方式對數(shù)據(jù)進(jìn)行重分布，當(dāng)實際數(shù)據(jù)很大時，會導(dǎo)致數(shù)據(jù)在DN之間傳輸耗時較多。

連接中選取數(shù)量大的表做重分布。下圖就是一個錯誤選擇大表做重分布的例子。

E-distinct：表示單DN上hashjoin算子的distinct估計值。這一列有兩個值，第一個值代表外表的distinct值，第二個值代表內(nèi)表的distinct值。將來會將這兩個值分別顯示在內(nèi)表和外表對應(yīng)的行數(shù)，便于理解。

Peak Memory：此算子在每個DN上執(zhí)行時使用的內(nèi)存峰值。當(dāng)在SMP場景中，該列是單線程的數(shù)據(jù)。這點需要改進(jìn)一下，應(yīng)該顯示單DN上所有線程peak memory的和比較合理，便于調(diào)優(yōu)時合理設(shè)置內(nèi)存。SMP的介紹參考SMP特性章節(jié)。

E-memory：DN上每個算子估算的內(nèi)存使用量，只有DN上執(zhí)行的算子會顯示。某些場景會在估算的內(nèi)存使用量后使用括號顯示該算子在內(nèi)存資源充足下可以自動擴(kuò)展的內(nèi)存上限。

A-width：表示當(dāng)前算子每行元組的實際寬度，僅對于重內(nèi)存使用算子會顯示，包括：(Vec)HashJoin、(Vec)HashAgg、(Vec) HashSetOp、(Vec)Sort、(Vec)Materialize算子等，其中(Vec)HashJoin計算的寬度是其右子樹算子的寬度，會顯示在其右子樹上。使用括號顯示該算子輸出元組中的最小和最大寬度。

E-width：每個算子輸出元組的估算寬度。

E-costs：每個算子估算的執(zhí)行代價。

Predicate Information (identified by plan id)

這部分主要顯示的是謂詞信息，即在整個計劃執(zhí)行過程中不會變的信息，主要是一些join條件和一些filter信息。

下圖中，對這部分進(jìn)行了說明。

Memory Information (identified by plan id)

這一部分顯示的是整個計劃中會將內(nèi)存的使用情況打印出來的算子的內(nèi)存使用信息，主要是Hash、Sort算子，包括：

算子峰值內(nèi)存（peak memory）

DataNode Query Peak Memory

顯示每個DN節(jié)點在整個查詢過程中使用的內(nèi)存峰值。它們中的最大值經(jīng)常用來估算SQL語句耗費的內(nèi)存，也被用來作為SQL語句調(diào)優(yōu)時運行態(tài)內(nèi)存參數(shù)設(shè)置的重要依據(jù)。

算子內(nèi)的peak memory

顯示算子內(nèi)每個DN節(jié)點使用的內(nèi)存峰值。

控制內(nèi)存（control memory）

一般出現(xiàn)在hash join、hash agg算子中，用于創(chuàng)建hash表和hash探測所使用的內(nèi)存

估算的內(nèi)存使用（estimate memory）

執(zhí)行時實際寬度（width）

內(nèi)存使用自動擴(kuò)展次數(shù)（auto spread times）

是否提前下盤（early spilled），及下盤信息，包括重復(fù)下盤次數(shù)（spill Time(s)），內(nèi)外表下盤分區(qū)數(shù)（inner/outer partition spill num），下盤文件數(shù)（temp file num），下盤數(shù)據(jù)量及最小和最大分區(qū)的下盤數(shù)據(jù)量（written disk IO [min, max] ）。提前下盤是指還有可用內(nèi)存，但不足以支撐接下來的操作。主要在以下情況會發(fā)生：

當(dāng)hash表中剩余的內(nèi)存小于要插入數(shù)據(jù)申請的內(nèi)存

根據(jù)當(dāng)前操作的內(nèi)存使用判斷該操作是一個非常耗內(nèi)存操作時。比如大數(shù)據(jù)量的sort操作。

此時就要看一下當(dāng)前配置的work_mem是否太小。

Targetlist Information (identified by plan id)

這部分顯示的是每一個算子輸出的目標(biāo)列。格式如下：

輸出的目標(biāo)列一般是：

查詢中顯示指定的輸出列

分組、排序、過濾條件、連接條件中出現(xiàn)的列。這些非顯示指定的輸出列，在查詢的最后不會輸出，但是在執(zhí)行的中間過程中被輸出給其它算子用于以上操作。比如下圖中的store_sales.ss_sold_date_sk列就是只用于連接條件。

對于輸出列信息中還有一點需要說明的是，某些需要經(jīng)過計算的表達(dá)式列，比如下圖19號算子中的sum表達(dá)式列，在19號算子中每個DN計算了這個表達(dá)式的結(jié)果，在上層18號streaming算子中只是引用19號算子的結(jié)果進(jìn)行重分布數(shù)據(jù)，不對其進(jìn)行計算，所以在18號算子中在該sum表達(dá)式兩邊加了一對圓括號，表示是對下層節(jié)點中該表達(dá)式結(jié)果的引用，不再重復(fù)計算。

DataNode Information (identified by plan id)

這部分會將各個算子的執(zhí)行時間、CPU、buffer的使用情況全部打印出來。

如果算子采用了SMP執(zhí)行，則會詳細(xì)列出DN上每個線程的情況，如下圖：

Buffer有三種類型，分別為：

Shared buffer：用于普通表數(shù)據(jù)

Temp buffer：用于臨時文件中的數(shù)據(jù)

Local buffer：用于臨時表數(shù)據(jù)

Buffer的使用情況包括read（從外存讀入buffer數(shù)）、written（從buffer寫出到外存數(shù)）、hit（數(shù)據(jù)被讀入buffer后，再次被訪問次數(shù)）、dirtied（buffer中內(nèi)容被修改的buffer數(shù)）。其中temp buffer只會統(tǒng)計read和written情況。如下是一個對buffer使用情況的說明。

User Define Profiling

這部分顯示的是CN和DN、DN和DN建連的時間，以及存儲層的一些執(zhí)行信息。這里涉及到很多名詞，此處做個簡單介紹：

建連（build connection）是指，不同節(jié)點間（CN和DN、DN和DN）需要傳輸數(shù)據(jù)，而建立的連接。這一般只有stream算子才會有。

CU（Compression Unit），壓縮單元。列存表的最小存儲單位。

Vector batch，向量批。存放參與向量計算的一批數(shù)據(jù)。

下面分別是Q67的查詢計劃樹和user define profiling，可以通過對照計劃樹，便于了解user define profiling中各個plan node id對應(yīng)的算子操作。

====== Query Summary =====

這部分主要打印一些查詢總結(jié)信息。包括：

DataNode executor start time：DN上執(zhí)行器的開始時間

第一個括號中是兩個DN的名字，第一個是執(zhí)行器開始時間最短的DN，第二個是執(zhí)行器開始時間最長的DN

第二個括號中是對應(yīng)第一個括號中兩個DN的執(zhí)行器啟動時間

Datanode executor end time: DN上執(zhí)行器的結(jié)束時間

后面兩個括號中的含義類似DN上執(zhí)行器的開始時間，分別代表執(zhí)行器結(jié)束時間最短和最長的DN名字，及其對應(yīng)的結(jié)束用時

Remote query poll time: stream gather算子用于監(jiān)聽是否有各DN數(shù)據(jù)到達(dá)CN的網(wǎng)絡(luò)poll時間

System available mem: 當(dāng)前預(yù)計執(zhí)行時系統(tǒng)可用內(nèi)存

Query Max mem: 查詢執(zhí)行所需最大內(nèi)存

Query estimated mem: 查詢執(zhí)行所需使用內(nèi)存估計

Avail/Max core: 可用/最大CPU核數(shù)

Cpu util: 最大CPU數(shù)

Active statement: 當(dāng)前語句生成計劃時，GaussDB(DWS)上正在運行的其它語句數(shù)

Query estimated cpu: 計劃的CPU使用估計，它是首先找出所有stream算子中cpu使用最多的cpu使用值（此處簡寫為max_cpu_usage），然后用所有stream算子的cpu使用之和/max_cpu_usage計算得出

Mem allowed dop: 內(nèi)存允許的dop限制。是根據(jù)DN所在主機(jī)的空閑內(nèi)存情況、主機(jī)上DN個數(shù)、計劃中stream個數(shù)、當(dāng)前可用的CPU數(shù)及估計要使用的CPU數(shù)計算出來最大dop數(shù)。如果設(shè)置了query_dop不為0的值，則dop不能超過query_dop的限制，具體參考如何開啟SMP。如果query_dop設(shè)置為自適應(yīng)，則dop不能超過GaussDB(DWS)支持的最大dop（即Min non-spill dop）。

Min non-spill dop: dop不能超過此限制。限制為64

Initial dop: 初始dop

Final dop: 最終dop

Coordinator executor start time: CN上執(zhí)行器的啟動用時

Coordinator executor run time: CN上執(zhí)行器的運行用時

Coordinator executor end time: CN上執(zhí)行器的結(jié)束用時

Total network : 網(wǎng)絡(luò)通信總共傳輸?shù)臄?shù)據(jù)量

Planner runtime: 生成計劃的時間

Query Id: 該查詢的query id，用來唯一標(biāo)識該查詢，CN和DN上同一個查詢的query id相同，以便于查詢其他視圖中標(biāo)識屬于同一查詢的不同節(jié)點上的信息

Total runtime: 整個查詢的總用時

相關(guān)知識介紹

常用算子介紹

算子主要分為以下五類:

控制算子?？刂扑阕邮且活愑糜谔幚硖厥馇闆r的節(jié)點，用于實現(xiàn)特殊的執(zhí)行流程。

掃描算子。掃描類算子一般是執(zhí)行計劃樹的葉子節(jié)點，不僅可以掃描表，還可以掃描函數(shù)的結(jié)果集、Values鏈表結(jié)構(gòu)、子查詢結(jié)果集等，每次獲取一條元組作為上層節(jié)點的輸入。

物化算子。物化算子是一類可以緩存元組的算子。在執(zhí)行過程中，很多擴(kuò)展的物理操作符需要首先獲取所有的元組才能進(jìn)行操作（例如聚集函數(shù)操作、沒有索 引輔助的排序等），這是要用物化算子將元組緩存起來。

連接算子。連接算子對應(yīng)于關(guān)系代數(shù)中的連接操作。連接算子按實現(xiàn)方式可以分為：Nestloop、HashJoin、 MergeJoin

Streaming是一個特殊的算子，它實現(xiàn)了分布式架構(gòu)的核心數(shù)據(jù)shuffle功能，Streaming共有三種形態(tài)，分別對應(yīng)了分布式結(jié)構(gòu)下不同的數(shù)據(jù)shuffle功能：

Streaming (type: GATHER)：作用是CN從DN收集數(shù)據(jù)。

Streaming(type: REDISTRIBUTE)：作用是DN根據(jù)選定的分布列把數(shù)據(jù)重分布到所有的DN。

Streaming(type: BROADCAST)：作用是把當(dāng)前DN的數(shù)據(jù)廣播給其他所有的DN

針對以上算子，如果出現(xiàn)了Vector前綴的算子是指向量化執(zhí)行引擎算子，一般出現(xiàn)在含有列存表的Query中。

SMP特性

SMP特性通過算子并行來提升性能，同時會占用更多的系統(tǒng)資源，包括CPU、內(nèi)存、網(wǎng)絡(luò)、I/O等等。本質(zhì)上SMP是一種以資源換取時間的方式，計劃并行之后必定會引起資源消耗的增加，當(dāng)上述資源成為瓶頸的情況下，SMP無法提升性能，反而可能導(dǎo)致性能的劣化。在出現(xiàn)資源瓶頸的情況下，建議關(guān)閉SMP。

在使用了SMP特性的查詢計劃中，可能會看到類似Streaming(type: SPLIT REDISTRIBUTE dop: 10/10)的算子，這其中的dop是degree of parallel的縮寫，后面的兩個數(shù)字分別表示該算子在每個DN上執(zhí)行時的線程數(shù)，和其子節(jié)點執(zhí)行時的線程數(shù)。

SMP適用的場景

支持并行的算子

計劃中存在以下算子支持并行：

Scan：支持行存普通表和行存分區(qū)表順序掃描、列存普通表和列存分區(qū)表順序掃描、HDFS內(nèi)外表順序掃描；支持GDS數(shù)據(jù)導(dǎo)入的外表掃描并行。以上均不支持復(fù)制表。

Join：HashJoin、NestLoop

Agg：HashAgg、SortAgg、PlainAgg、WindowAgg(只支持partition by，不支持order by)。

Stream：Redistribute、Broadcast

其他：Result、Subqueryscan、Unique、Material、Setop、Append、VectoRow、RowToVec

SMP特有算子

為了實現(xiàn)并行，新增了并行線程間的數(shù)據(jù)交換Stream算子供SMP特性使用。這些新增的算子可以看做Stream算子的子類。

Local Gather：實現(xiàn)DN內(nèi)部并行線程的數(shù)據(jù)匯總

Local Redistribute：在DN內(nèi)部各線程之間，按照分布鍵進(jìn)行數(shù)據(jù)重分布

Local Broadcast：將數(shù)據(jù)廣播到DN內(nèi)部的每個線程

Local RoundRobin：在DN內(nèi)部各線程之間實現(xiàn)數(shù)據(jù)輪詢分發(fā)

Split Redistribute：在集群跨DN的并行線程之間實現(xiàn)數(shù)據(jù)重分布

Split Broadcast：將數(shù)據(jù)廣播到集群所有DN的并行線程

上述新增算子可以分為Local與非Local兩類，Local類算子實現(xiàn)了DN內(nèi)部并行線程間的數(shù)據(jù)交換，而非Local類算子（即stream算子）實現(xiàn)了跨DN的并行線程間的數(shù)據(jù)交換。

如何開啟SMP

使用SMP特性通過設(shè)置query_dop參數(shù)的值來控制。

query_dop=1（默認(rèn)值），表示不啟用SMP。

query_dop=0（自適應(yīng)），系統(tǒng)會根據(jù)資源情況和計劃特征，動態(tài)為每個查詢選取[1,8]之間的最優(yōu)的并行度，最大化提升查詢性能。

query_dop=-value，在考慮資源情況和計劃特征基礎(chǔ)上，限制dop選取的范圍為[1,value]。

query_dop=value，不考慮資源情況和計劃特征，強(qiáng)制選取dop為1或value。

數(shù)據(jù)倉庫服務(wù) GaussDB(DWS)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。