1.優(yōu)化背景

用戶提交一個(gè)?SQL 作業(yè)后，一方面是希望作業(yè)能夠成功運(yùn)行，另一方面，對(duì)于成功完成的作業(yè)，需要進(jìn)一步分析作業(yè)瓶頸，進(jìn)行性能調(diào)優(yōu)。針對(duì)這兩個(gè)方面的需求，本文將介紹如何解決作業(yè)運(yùn)行時(shí)的常見問題、如何進(jìn)一步優(yōu)化 SQL 作業(yè)性能。本文以阿里Maxcompute開發(fā)工具來舉例說明，對(duì)于其他使用開源組件搭建的數(shù)據(jù)平臺(tái)SQL優(yōu)化一樣具有通用性。

2.診斷篇

作業(yè)提交上去之后，包括預(yù)處理，編譯，執(zhí)行，結(jié)果返回四個(gè)步驟。對(duì)于比較復(fù)雜的編譯和執(zhí)行階段，又存在若干子階段。預(yù)處理階段一般不會(huì)卡住，下面會(huì)分別說明編譯，執(zhí)行，結(jié)束階段的特征，停滯在這些階段的可能原因，以及可以考慮的解決思路。

2.1 編譯階段

作業(yè)處于編譯階段的特征是有?logview，但還沒有執(zhí)行計(jì)劃。根據(jù) logview 的子狀態(tài)（SubStatusHistory）可以進(jìn)一步細(xì)分為調(diào)度、優(yōu)化、生成物理執(zhí)行計(jì)劃、數(shù)據(jù)跨集群復(fù)制等子階段。

編譯階段的問題主要表現(xiàn)為在某個(gè)子階段卡住，即作業(yè)長時(shí)間停留在某一個(gè)子階段。下面將介紹作業(yè)停留在每個(gè)子階段的可能原因和解決方法。

2.1.1 調(diào)度階段

【特征】子狀態(tài)為“Waiting for cluster resource”，作業(yè)排隊(duì)等待被編譯。

【該階段作業(yè)卡住的可能原因 1 】計(jì)算集群資源緊缺。

【解決方法】查看計(jì)算集群的狀態(tài)，需要等待計(jì)算集群的資源。

【該階段作業(yè)卡住的可能原因 2 】編譯資源池資源不夠：可能有人不小心用腳本一次提交太多作業(yè)，把編譯資源池占滿了。

【解決方法】請(qǐng)聯(lián)系集群運(yùn)維值班同學(xué)，把這些無效作業(yè)kill掉；

2.1.2 優(yōu)化階段

【特征】子狀態(tài)為“SQLTask is optimizing query”，優(yōu)化器正在優(yōu)化執(zhí)行計(jì)劃。

【該階段作業(yè)卡住的可能原因】執(zhí)行計(jì)劃復(fù)雜，需要等待較長時(shí)間做優(yōu)化。

【解決方法】一般可接受的時(shí)間是10分鐘以內(nèi)，如果真的太長時(shí)間不退出，基本上可以認(rèn)為是集群的bug，請(qǐng)進(jìn)找集群運(yùn)維值班同學(xué)咨詢。

2.1.3?生成物理計(jì)劃執(zhí)行階段

【特征】子狀態(tài)為“SQLTask is generating execution plan”。

【該階段作業(yè)卡住的可能原因?1 】讀取的分區(qū)太多。每個(gè)分區(qū)需要去根據(jù)分區(qū)信息來決定處理方式，決定 split，并且會(huì)寫到生成的執(zhí)行計(jì)劃中。

【解決方法】需要好好設(shè)計(jì)?SQL，減少分區(qū)的數(shù)量，包括：分區(qū)裁剪、篩除不需要讀的分區(qū)、把大作業(yè)拆成小作業(yè)。如何判斷 SQL 中分區(qū)剪裁是否生效，以及分區(qū)裁剪失效的常見場(chǎng)景，更多內(nèi)容請(qǐng)參考后續(xù)文章，敬請(qǐng)關(guān)注公眾號(hào)"滌生大數(shù)據(jù)" ：分區(qū)裁剪合理性評(píng)估介紹；

【該階段作業(yè)卡住的可能原因 2 】小文件太多。ODPS 會(huì)根據(jù)文件大小決定 split，小文件多了會(huì)導(dǎo)致計(jì)算 split 的過程耗時(shí)增加。產(chǎn)生小文件的原因主要有兩個(gè)：

1.對(duì)分區(qū)表進(jìn)行 insert into 操作的時(shí)候，會(huì)在 partition 目錄下面生成一個(gè)新文件；

2.采用客戶端工具上傳文件，參數(shù)設(shè)置不合理，導(dǎo)致生成了很多小文件；

【解決方法】

1. 使用集群提供的接口，可以更簡(jiǎn)單的進(jìn)行上傳功能，同時(shí)避免小文件。

2.執(zhí)行一次 alter table merge smallfiles; 讓 odps 把小文件 merge 起來。

更多內(nèi)容請(qǐng)參考后續(xù)文章，敬請(qǐng)關(guān)注公眾號(hào)"滌生大數(shù)據(jù)" ：合并小文件優(yōu)化介紹。

【注意】上面提到的“太多”不是指幾十、幾百個(gè)。基本都是要上萬，上十萬才會(huì)對(duì)生成物理執(zhí)行計(jì)劃的時(shí)間產(chǎn)生較大影響。

2.1.4?數(shù)據(jù)跨集群復(fù)制階段

【特征】子狀態(tài)列表里面出現(xiàn)多次“Task rerun”，result 里有錯(cuò)誤信息“FAILED: ODPS-0110141:Data version exception”。作業(yè)看似失敗了，實(shí)際還在執(zhí)行，說明作業(yè)正在做數(shù)據(jù)的跨集群復(fù)制。

【該階段作業(yè)卡住的可能原因?1 】project 剛做集群遷移，往往前一兩天有大量需要跨集群復(fù)制的作業(yè)。

【解決方法】這種情況是預(yù)期中的跨集群復(fù)制，需要用戶等待。

【該階段作業(yè)卡住的可能原因?2 】可能是作業(yè)提交錯(cuò)集群，或者是中間 project 做過遷移，分區(qū)過濾沒做好，讀取了一些比較老的分區(qū)。

【解決方法】

1. 檢查作業(yè)提交的集群是否正確：用戶可以通過 Logview1.0 點(diǎn)擊 Status->System Info，或 Logview2.0 任務(wù)詳情頁左側(cè)的 BasicInfo 查看作業(yè)提交的集群。

2.過濾掉不必要讀取的老分區(qū)（從這里看出，不限制日期讀取全部增量分區(qū)表是有風(fēng)險(xiǎn)的）。

2.2 執(zhí)行階段

【特征】logview 的 detail 界面有執(zhí)行計(jì)劃（執(zhí)行計(jì)劃沒有全都綠掉），且作業(yè)狀態(tài)還是 Running。

執(zhí)行階段卡住或執(zhí)行時(shí)間比預(yù)期長的主要原因有等待資源，數(shù)據(jù)傾斜，UDF 執(zhí)行低效，數(shù)據(jù)膨脹等等，下面將具體介紹每種情況的特征和解決思路。

2.2.1?等待資源

【特征】instance 處于 Ready 狀態(tài)，或部分 instance 是 Running，部分是 Ready 狀態(tài)。需要注意的是，如果 instance 狀態(tài)是 Ready 但有 debug 歷史信息，那么可能是 instance fail 觸發(fā)重試，而不是在等待資源。

【解決思路】

1.確定排隊(duì)狀態(tài)是否正常：可以通過?logview 的排隊(duì)信息“Queue”看作業(yè)在隊(duì)列的位置。

或著通過查看作業(yè) project 當(dāng)前的 quota 組作業(yè)信息、系統(tǒng)資源使用情況和歷史曲線等等，查看當(dāng)前作業(yè)所在 quota 組的資源使用情況：

如果某項(xiàng)資源的使用率已經(jīng)接近甚至超過配額了，那么證明?quota 組資源緊張，作業(yè)有排隊(duì)是正常的。作業(yè)的調(diào)度順序不僅與作業(yè)提交時(shí)間、優(yōu)先級(jí)有關(guān)，還和作業(yè)所需內(nèi)存或CPU資源大小能否被滿足有關(guān)，因此合理設(shè)置作業(yè)的參數(shù)很重要。更多內(nèi)容請(qǐng)看合理設(shè)置資源參數(shù)介紹。

2.查看 quota 組中運(yùn)行的作業(yè)。

可能會(huì)有人不小心提交了低優(yōu)先級(jí)的大作業(yè)（或批量提交了很多小作業(yè)），占用了太多的資源，可以和作業(yè)的?owner 協(xié)商，讓他先把作業(yè) kill 掉，把資源讓出來。

3.考慮去其他 quota 組的 project 跑。

只要有權(quán)限，是可以在別的 project 下訪問另一個(gè) project 的表的。當(dāng)然，有可能會(huì)存在跨集群復(fù)制；

2.2.2?數(shù)據(jù)傾斜

【特征】task 中大多數(shù) instance 都已經(jīng)結(jié)束了，但是有某幾個(gè) instance 卻遲遲不結(jié)束（長尾）。如下圖中大多數(shù)（358個(gè)）instance 都結(jié)束了，但是還有 18 個(gè)的狀態(tài)是 Running，這些 instance 運(yùn)行的慢，可能是因?yàn)樘幚淼臄?shù)據(jù)多。

【解決方法】需要找到造成數(shù)據(jù)傾斜的具體位置，對(duì)癥下藥。下面列出了定位長尾實(shí)例的常用方法：

1.利用作業(yè)執(zhí)行圖及作業(yè)詳情功能來分析作業(yè)運(yùn)行情況，定位到長尾實(shí)例，找到導(dǎo)致長尾的數(shù)據(jù)來源。

2.利用 Logveiw查看任務(wù)執(zhí)行圖和 instance 運(yùn)行情況來定位長尾實(shí)例。? ? ?

3.在確定造成數(shù)據(jù)傾斜的實(shí)例、數(shù)據(jù)來源等信息后，用戶需要針對(duì)性的對(duì)代碼甚至算法做一定的修改。后文優(yōu)化篇：數(shù)據(jù)傾斜中介紹了一些導(dǎo)致數(shù)據(jù)傾斜的常見原因及對(duì)應(yīng)的優(yōu)化思路。

2.2.3 UDF執(zhí)行低效

這里的?UDF 泛指各種用戶自定義的擴(kuò)展，包括UDF，UDAF，UDTF等。

【特征】某個(gè) task 執(zhí)行效率低，且該 task 中有用戶自定義的擴(kuò)展。甚至是 UDF 的執(zhí)行超時(shí)報(bào)錯(cuò)：“Fuxi job failed - WorkerRestart?errCode:252,errMsg:kInstanceMonitorTimeout, usually caused by bad udf performance”。

【排查方法】任務(wù)報(bào)錯(cuò)時(shí)，可以在快速通過 DAG 圖判斷報(bào)錯(cuò)的 task 中是否包含 UDF。如下左圖，可以看到報(bào)錯(cuò)的task R4_3 包含用戶使用 Java 語言編寫的 UDF。雙擊 R4_3，展開 operator 視圖如下右圖，可以看到該 task 包含的所有 UDF 名稱。

此外，在 task 的 stdout 日志里，UDF 框架會(huì)打印 UDF 輸入的記錄數(shù)、輸出記錄數(shù)、以及處理時(shí)間，如下圖。通過這些數(shù)據(jù)可以看出 UDF 是否有性能問題。一般來講，正常情況 Speed(records/s) 在百萬或者十萬級(jí)別，如果降到萬級(jí)別，那么基本上就有性能問題了。

【解決思路】當(dāng)有性能問題時(shí)，可以按照下面這些方法進(jìn)行排查和優(yōu)化：

1.檢查 UDF 是否有 bug：有時(shí)候 bug 是由于某些特定的數(shù)據(jù)值引起的，比如出現(xiàn)某個(gè)值的時(shí)候會(huì)引起死循環(huán)。

2.檢查 UDF 函數(shù)是否與內(nèi)置函數(shù)同名：內(nèi)置函數(shù)是有可能被同名 UDF 覆蓋的，當(dāng)看到一個(gè)函數(shù)像是內(nèi)置函數(shù)時(shí)，需要確定是否有同名 UDF 覆蓋了內(nèi)置函數(shù)。

3.使用內(nèi)置函數(shù)代替 UDF：有相似功能的內(nèi)置函數(shù)的情況下，盡可能不要使用 UDF。內(nèi)置函數(shù)一般經(jīng)過驗(yàn)證，實(shí)現(xiàn)比較高效，并且內(nèi)置函數(shù)對(duì)優(yōu)化器而言是白盒，能夠做更多的優(yōu)化。更多內(nèi)置函數(shù)的用法請(qǐng)參考官方文檔。

4.將 UDF 函數(shù)進(jìn)行功能拆分，部分用內(nèi)置函數(shù)替換，內(nèi)置函數(shù)無法實(shí)現(xiàn)的再用 UDF。

5.優(yōu)化 UDF 的 evaluate 方法：evaluate 中只做與參數(shù)相關(guān)的必要操作。因?yàn)?evaluate 方法會(huì)被反復(fù)執(zhí)行，所以盡可能將一些初始化的操作，或者一些重復(fù)的計(jì)算事先計(jì)算好。

6.預(yù)估 UDF 的執(zhí)行時(shí)間：先在本地模擬 1 個(gè) instance 處理的數(shù)據(jù)量測(cè)試 UDF 的運(yùn)行時(shí)間，優(yōu)化 UDF 的實(shí)現(xiàn)。默認(rèn) UDF 運(yùn)行時(shí)限是 10 分鐘，也就是 10 分鐘內(nèi) UDF 必須要返回?cái)?shù)據(jù)，或者用 context.progress() 來報(bào)告心跳。如果 UDF 預(yù)計(jì)運(yùn)行時(shí)間本身就大于 10 分鐘，可以通過參數(shù)設(shè)置 UDF 超時(shí)時(shí)間：

7.調(diào)整內(nèi)存參數(shù)：UDF效率低的原因并不一定是計(jì)算復(fù)雜度，有可能是受存儲(chǔ)復(fù)雜度影響。比如：

這時(shí)可以嘗試調(diào)整內(nèi)存參數(shù)，不過這個(gè)方法只能暫時(shí)緩解，還是需要從業(yè)務(wù)上去優(yōu)化。

需要注意的是，目前如果使用了?UDF 可能會(huì)導(dǎo)致分區(qū)剪裁失效。查看官網(wǎng)是否支持?UdfProperty 注解。在定義 UDF 時(shí)，可以指定這個(gè)注解，讓編譯器知道這個(gè)函數(shù)是確定性的。

2.2.4?數(shù)據(jù)膨脹

【特征】task 的輸出數(shù)據(jù)量比輸入數(shù)據(jù)量大很多。

比如 1G 的數(shù)據(jù)經(jīng)過處理，變成了 1T，在一個(gè) instance 下處理 1T 的數(shù)據(jù)，運(yùn)行效率肯定會(huì)大大降低。

作業(yè)運(yùn)行完成后輸入輸出數(shù)據(jù)量體現(xiàn)在 Task 的 I/O Record 和 I/O Bytes 這兩項(xiàng)：

如果作業(yè)在?Join 階段長時(shí)間不結(jié)束，可以選擇幾個(gè) Running 狀態(tài)的 Fuxi Instance 查看 StdOut 里的日志：

StdOut 里一直在打印 Merge join 的日志，說明對(duì)應(yīng)的單個(gè) worker 一直執(zhí)行 merge join，紅框中 Merge join 輸出的數(shù)據(jù)條數(shù)已經(jīng)超過 1433 億條，有嚴(yán)重的數(shù)據(jù)膨脹，需要檢查 JOIN 條件和 JOIN key 是否合理。

【解決思路】

1.檢查代碼是否有?bug

2.檢查?Aggregation 引起的數(shù)據(jù)膨脹。

因?yàn)榇蠖鄶?shù) aggregator 是 recursive 的，中間結(jié)果先做了一層 merge，中間結(jié)果不大，而且大多數(shù) aggregator 的計(jì)算復(fù)雜度比較低，即使數(shù)據(jù)量不小，也能較快完成。所以通常情況下這些操作問題不大，如：

1.select 中使用 aggregation 按照不同維度做 distinct，每一次 distinct 都會(huì)使數(shù)據(jù)做一次膨脹；

2.使用 Grouping Sets (CUBE and ROLLUP) ，中間數(shù)據(jù)可能會(huì)擴(kuò)展很多倍。? ?

但是，有些操作如 collect_list、median 操作需要把全量中間數(shù)據(jù)都保留下來，可能會(huì)產(chǎn)生問題。

3.避免join引起的數(shù)據(jù)膨脹。

比如：兩個(gè)表?join，左表是事件上報(bào)數(shù)據(jù)，數(shù)據(jù)量很大，但是由于并行度足夠，效率可觀。右表是個(gè)維表，記錄每個(gè)國家對(duì)應(yīng)的一些券信息，雖然只有幾個(gè)國家，但是每個(gè)國家都包含數(shù)百行。那么如果直接按照國家來 join，可能會(huì)讓左表膨脹數(shù)百倍。要解決這個(gè)問題，可以考慮先將右表的行轉(zhuǎn)列，變成幾行數(shù)據(jù)，這樣 join 的結(jié)果就不會(huì)膨脹了。

4.由于grouping set 導(dǎo)致的數(shù)據(jù)膨脹。

grouping set 操作會(huì)有一個(gè)expand 過程，輸出數(shù)據(jù)會(huì)按照group 數(shù)倍增。目前的plan沒有能力適配grouping set并做下游task 實(shí)例數(shù)的調(diào)整，用戶可以手動(dòng)設(shè)置下游task的實(shí)例調(diào)整。

2.3?結(jié)束階段

2.3.1?過多小文件

小文件主要帶來存儲(chǔ)和計(jì)算兩方面問題。存儲(chǔ)方面，小文件過多會(huì)給 Pangu 文件系統(tǒng)帶來一定的壓力，且影響空間的有效利用；計(jì)算方面，ODPS 處理單個(gè)大文件比處理多個(gè)小文件更有效率，小文件過多會(huì)影響整體的計(jì)算執(zhí)行性能。因此，為了避免系統(tǒng)產(chǎn)生過多小文件，SQL 作業(yè)在結(jié)束時(shí)會(huì)針對(duì)一定條件自動(dòng)觸發(fā)合并小文件的操作。小文件的判定，是通過參數(shù)

來設(shè)置的，該配置默認(rèn)為 32MB，小于這個(gè)閾值的文件都會(huì)被判定為小文件。

自動(dòng)合并小文件多出來的 merge task，雖然會(huì)增加當(dāng)前作業(yè)整體執(zhí)行時(shí)間，但是會(huì)讓結(jié)果表在合并后產(chǎn)生的文件數(shù)和文件大小更合理，從而避免對(duì)文件系統(tǒng)產(chǎn)生過大壓力，也使得表被后續(xù)的作業(yè)使用時(shí)，擁有更好的讀取性能。

2.3.2?動(dòng)態(tài)分區(qū)元數(shù)據(jù)更新

作業(yè)執(zhí)行完后，有可能還有一些元數(shù)據(jù)操作。比如要把結(jié)果數(shù)據(jù)挪到特定目錄去，然后把表的元數(shù)據(jù)更新。有可能動(dòng)態(tài)分區(qū)輸出了太多分區(qū)，那么還是可能會(huì)消耗一定的時(shí)間的。

例如，對(duì)分區(qū)表 sales 使用 insert overwrite ... values 命令新增 2000 個(gè)分區(qū)。

2.3.3?輸出文件size變大

有時(shí)候在輸入輸出條數(shù)相差不大的情況，結(jié)果膨脹幾倍：

這種問題一般是數(shù)據(jù)分布變化導(dǎo)致的，我們?cè)趯懕淼倪^程中，會(huì)對(duì)數(shù)據(jù)進(jìn)行壓縮，而壓縮算法對(duì)于重復(fù)數(shù)據(jù)的壓縮率是最高的，所以如果寫表的過程中，如果相同的數(shù)據(jù)都排布在一起，就可以獲得很高的壓縮率。寫表的數(shù)據(jù)分布情況主要取決于寫表的階段（對(duì)應(yīng)上圖的R12）是如何shuffle和排序的，上圖給出的SQL最后的操作是join，join key為on t1.query = t2.query and t1.item_id=t2.item_id

研究一下數(shù)據(jù)的特征，大部分列都是item的屬性，也就是說相同的item_id其余的列都完全相同，按照query排序會(huì)把item完全打亂，導(dǎo)致壓縮率降低非常多。這里調(diào)整一下join順序，on t1.item_id=t2.item_id and t1.query = t2.query，調(diào)整后數(shù)據(jù)減小到原來的三分之一。

繼續(xù)關(guān)注哈，莫哥隨后帶來SQL之優(yōu)化篇哈