MaxComputeSql效能調優

標籤：不同類   project   分發   調優   hold   寫入   合并   分區   多次   

轉載自xiaorui
   
      部分使用者(尤其對外輸出)使用MaxCompute(原Odps)時，由於對產品的使用層面和執行層面瞭解程度不同，導致提交的任務執行時間過長、佔用了較多叢集資源；嚴重的會導致失敗、不僅需要投入支援同學精力協助解決、也影響了使用者正常業務。 合并整理部分效能提升方法方便支援使用者查詢和最佳化Sql，提高效率；部分需要原來手動調優的如mapjoin、ppd謂詞下推注意分區位置等原有的調優設定在不斷衍進的產品中都已實現了自動化調優、 不同階段的產品調優參數和細節會有不一致、但是熟悉了調優思路和方法後可以做到舉一反三、逐步深入。

一. 整體思路

      整體上，調優從底層到sql可以有多個層面的調優，隨著產品的不斷完善，部分調優已經實現了自動化。如果使用者能熟悉常見的調優機制和執行原理，在開發執行sql、商務邏輯和相關參數設定調整來提高效能、可以做到事半功倍。
      1. 硬體及作業系統層面調優：包括磁碟I/O調優（多工等）、網路調優（緩衝區大小、串連數放大等）、記憶體調優（虛擬記憶體設定、記憶體控制等）；
      2. 分散式運算平台及儲存層面調優：儲存格式設定、壓縮格式設定、RPC調用設定、串連數控制設定、調度機制設定、block及分區設定、執行資源設定等；
      3. 商務邏輯層面及參數調整，除整體執行的調優外，對不同類型的操作進行參數層級的調優、針對彙總、串連、一讀多寫等修改為不同的sql或者設定不同的參數可以極大的提高效能；      
      4. Sql層面及應用程式層面的調優，重構sql寫法、合并sql，大小表串連修改為mapjoin等，在odps2.0中已處理了自動mapjoin等、目前未升級使用者及對外輸出的使用者仍需要修改sql來支援；
      不同層面調優及優缺點見下：
 

二. 情境及調優列舉

1. 大小表關聯修改為mapjoin，增加Mapjoin hint
方法及注意事項：

• 所有指定的小表佔用的記憶體總和不超過 512M
• 多表Join時，最左邊的兩個表，不能同時是 Mapjoin 的表
• 不同的關聯方式（left/right/inner)，對錶的順序有要求
  
  • left outer join 左表必須為大表
  • right outer join 右表必須為大表
  • inner join 左右表皆可為大表
• full outer join 不能直接使用 mapjoin，修改為mapjoin + union all
• 最新的發布版本上已經支援了automapjoin，可以根據join表的大小自動把小錶轉為mapjoin，對部分沒有顯示增加hint的sql和中間結果為小表的sql進行自動最佳化

2. 資料扭曲
資料扭曲表現:
任務進度長時間維持在99%，查看監控，只有少量 reduce 子任務未完成
單一 reduce 記錄數與平均記錄數差異多大，最長時間長度遠大於平均時間長度
最佳化方法及注意事項：

• 彙總傾斜，設定參數：set odps.sql.groupby.skewindata=true
• 關聯傾斜，設定參數:set odps.sql.skewinfo=tab1:(col1,col2)[(v1,v2),(v3,v4),...|(v1,v2),(v3,v4),...];set odps.sql.optimize.skewjoin=true
• 關聯傾斜，同時大小表，考慮修改為mapjoin
• 具體問題具體分析：因為彙總key值null引起的資料扭曲，可以修改null為隨機值，打撒資料分發到不同的instance執行

3.小檔案過多-動態分區產生,減少小檔案的產生
方法及注意事項：

• 設定參數：set odps.sql.reshuffle.dynamicpt=true
• 能使用靜態分區時推薦使用靜態分區
• 開啟該參數選項會增加一級reduce，消耗額外資源
• 要注意參數使用過程中可能出現的資料扭曲
• 動態分區數較少時建議關閉該參數（False）

4. 小檔案過多合并
方法及注意事項：

• 合并小檔案方式:
  • ALTER 合并模式： alter table … [partition] merge smallfiles;
  • SQL 合并模式： odps.task.merge.enable=true
• 調整參數控制合并的效果
  • 是否允許跨路徑合并（false）： odps.merge.cross.paths
  • 小檔案閥值（32M）： odps.merge.smallfile.filesize.threshold
  • 合并輸出檔案大小（500M） ：odps.merge.maxmerged.filesize.threshold
  • 單instance允許合并檔案數（200）：odps.merge.max.filenumber.per.instance
  • 合并最大的小檔案個數（50000） ：odps.merge.max.filenumber.per.instance
  • 小檔案合并的逾時時間（600S）：odps.task.merge.wait.for.fuxi.timeout
  • 一次提交SQL最多可以合并分區的個數（20）：odps.merge.max.partition.count
  • 如果 sessiion 和 project 均設定了上述參數，則以 session 設定為準

5. Map 端一讀多寫
情境及最佳化：
多次讀取同一張物理表，執行不同操作，寫入多張表；考慮與multiinsert 的聯絡和區別，是否合適做修改
建立暫存資料表，實現暫存資料表的並行化
注意事項：

• 優點：大大節省了叢集的計算資源和磁碟I/O資源
• 缺點：寫的次數非常多時，可能導致效能問題，影響任務整體執行效率

6. 分區裁剪
情境：事實表很多分區，資料量大
最佳化：避免全表掃描，減少資源浪費；關注分區裁剪有無生效，見下注意事項：從表設計、使用上注意，盡量讓分區裁剪生效
注意事項：

• 過濾條件中的分區列上有UDF 則分區裁剪生效
• 表關聯時關聯條件中包含分區列：
  • 出現在 on 條件中，分區裁剪生效
  • 出現在 where 條件中，主表分區裁剪生效，其餘可能失敗

7. SQL 合并
情境：
1. 多次讀取相同的資料且來源資料資料量大、效能差、費用高
2. 統一商務程序前後關聯sql或統計多種指標、篩選不同資料的sql
最佳化方法及注意事項：通過修改sql，合并為1個sql執行，盡量減少對相同資料來源的讀取次數，達到一次掃描計算多個基礎統計量，一次掃描，處理多個篩選條件；以下調整列舉：

• CASE … WHEN…：合并相同資料來源的不同子查詢的關聯
• 動態分區&多路插入等：將滿足不同條件的會員統計資訊插入到不同的表或表分區中去
• 前後流程sql合并為1個sql執行

8. 使用視窗函數最佳化SQL
視窗函數：
1. 可以進行靈活的分析處理工作
2. 使用 partition by 開窗，order by 排序
3. 可以用 rows 指定開窗範圍
4. 豐富的開窗函數

最佳化及注意事項：合理使用視窗函數，可以減少Join次數，提高運行效能;不用視窗函數處理需要寫複雜sql的功能，用開窗函數可以高效執行得到預期結果。

三. 相關參數含義及預設值

 

 

歡迎加入MaxComputeDingTalk群討論

 

 閱讀原文請點擊

MaxComputeSql效能調優