[[186643]]

背景

同事寫(xiě)了這樣一段HQL(涉及公司數(shù)據(jù)，表名由假名替換，語(yǔ)句與真實(shí)場(chǎng)景略有不同，但不影響分析)：

CREATE TABLE tmp AS 
SELECT 
       t1.exk, 
       t1.exv, 
       M.makename AS m_makename, 
       S.makename AS s_makename, 
FROM 
  (SELECT 
          exk, 
          exv 
   FROM xx.xxx_log 
   WHERE etl_dt = '2017-01-12' 
     AND exk IN ('xxID', 'yyID') ) t1 
LEFT JOIN xx.xxx_model_info M ON (M.modelid=t1.exv AND t1.exk='xxID') 
LEFT JOIN xx.xxx_style_info S ON (S.styleid=t1.exv AND t1.exk='yyID') 

任務(wù)運(yùn)行過(guò)程中非常緩慢，同事反映說(shuō)這個(gè)任務(wù)要跑一個(gè)多小時(shí)。初步問(wèn)了下，xx.xxx_log表數(shù)據(jù)量在分區(qū)etl_dt = '2017-01-12'上大概1億3000萬(wàn)，xx.xxx_model_info大概3000多，xx.xxx_style_info大概4萬(wàn)多。

分析

***步，分析HQL語(yǔ)句著手

從同事提供的數(shù)據(jù)量上看，兩個(gè)left join顯然應(yīng)該是mapjoin，因?yàn)閿?shù)據(jù)量差距懸殊。當(dāng)前只有HQL語(yǔ)句，所以?xún)?yōu)化***步當(dāng)然要從HQL語(yǔ)句本身出發(fā)，看HQL語(yǔ)句是否有不恰當(dāng)?shù)牡胤健?/p>

從語(yǔ)句上看，就是取三張表的數(shù)據(jù)，按條件進(jìn)行join，***創(chuàng)建并插入一張hive表。語(yǔ)句上看沒(méi)什么問(wèn)題。

那就來(lái)看執(zhí)行計(jì)劃吧~ 我們只看建表后面的SELECT語(yǔ)句，如下

STAGE DEPENDENCIES: 
  Stage-5 is a root stage 
  Stage-4 depends on stages: Stage-5 
  Stage-0 depends on stages: Stage-4 
 
STAGE PLANS: 
  Stage: Stage-5 
    Map Reduce Local Work 
      Alias -> Map Local Tables: 
        m  
          Fetch Operator 
            limit: -1 
        s  
          Fetch Operator 
            limit: -1 
      Alias -> Map Local Operator Tree: 
        m  
          TableScan 
            alias: m 
            Statistics: Num rows: 3118 Data size: 71714 Basic stats: COMPLETE Column stats: NONE 
            HashTable Sink Operator 
              filter predicates: 
                0 {(_col0 = 'xxID')} {(_col0 = 'yyID')} 
                1  
                2  
              keys: 
                0 UDFToDouble(_col1) (type: double) 
                1 UDFToDouble(modelid) (type: double) 
                2 UDFToDouble(styleid) (type: double) 
        s  
          TableScan 
            alias: s 
            Statistics: Num rows: 44482 Data size: 1023086 Basic stats: COMPLETE Column stats: NONE 
            HashTable Sink Operator 
              filter predicates: 
                0 {(_col0 = 'xxID')} {(_col0 = 'yyID')} 
                1  
                2  
              keys: 
                0 UDFToDouble(_col1) (type: double) 
                1 UDFToDouble(modelid) (type: double) 
                2 UDFToDouble(styleid) (type: double) 
 
  Stage: Stage-4 
    Map Reduce 
      Map Operator Tree: 
          TableScan 
            alias: xx.xxx_log 
            Statistics: Num rows: 136199308 Data size: 3268783392 Basic stats: COMPLETE Column stats: NONE 
            Filter Operator 
              predicate: ((exk) IN ('xxID', 'yyID')) (type: boolean) 
              Statistics: Num rows: 22699884 Data size: 544797216 Basic stats: COMPLETE Column stats: NONE 
              Select Operator 
                expressions: exk (type: string), exv (type: string) 
                outputColumnNames: _col0, _col1 
                Statistics: Num rows: 22699884 Data size: 544797216 Basic stats: COMPLETE Column stats: NONE 
                Map Join Operator 
                  condition map: 
                       Left Outer Join0 to 1 
                       Left Outer Join0 to 2 
                  filter predicates: 
                    0 {(_col0 = 'SerialID')} {(_col0 = 'CarID')} 
                    1  
                    2  
                  keys: 
                    0 UDFToDouble(_col1) (type: double) 
                    1 UDFToDouble(modelid) (type: double) 
                    2 UDFToDouble(styleid) (type: double) 
                  outputColumnNames: _col0, _col1, _col13, _col32 
                  Statistics: Num rows: 49939745 Data size: 1198553901 Basic stats: COMPLETE Column stats: NONE 
                  Select Operator 
                    expressions: _col0 (type: string), _col1 (type: string), _col13 (type: string), _col32 (type: string) 
                    outputColumnNames: _col0, _col1, _col2, _col3 
                    Statistics: Num rows: 49939745 Data size: 1198553901 Basic stats: COMPLETE Column stats: NONE 
                    File Output Operator 
                      compressed: false 
                      Statistics: Num rows: 49939745 Data size: 1198553901 Basic stats: COMPLETE Column stats: NONE 
                      table: 
                          input format: org.apache.hadoop.mapred.TextInputFormat 
                          output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat 
                          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 
      Local Work: 
        Map Reduce Local Work 
 
  Stage: Stage-0 
    Fetch Operator 
      limit: -1 
      Processor Tree: 
        ListSink 

執(zhí)行計(jì)劃上分為三個(gè)stage，***個(gè)處理兩張小表的，把小表內(nèi)容處理成HashTable來(lái)做mapjoin，這個(gè)跟我們上面的分析一致。第二個(gè)用于處理大表和小表的mapjoin，***一個(gè)則是關(guān)聯(lián)后的數(shù)據(jù)輸出。

從執(zhí)行計(jì)劃上看，似乎也沒(méi)什么問(wèn)題，一切很正常~~

既然從SQL本身找不到問(wèn)題，那說(shuō)明有可能出現(xiàn)在數(shù)據(jù)上或機(jī)器上，就只能具體問(wèn)題具體看了。

第二步，查建表語(yǔ)句，看表的壓縮格式是不是不支持分割導(dǎo)致部分map任務(wù)處理時(shí)間過(guò)長(zhǎng)

Hive支持多種壓縮格式，有的壓縮格式支持split，而有的并不支持，比如LZO。當(dāng)不支持split的時(shí)候，數(shù)據(jù)塊有多大，Hive的map任務(wù)就得處理多大，而Hive表的分區(qū)數(shù)據(jù)有可能存在不均衡的現(xiàn)象，就會(huì)導(dǎo)致有的map快，有的map慢。當(dāng)遇到LZO格式的時(shí)候，***的方式是建立索引，可以加快處理速度。

用show create table從建表語(yǔ)句里看，并沒(méi)有使用LZO的表。而在hdfs上直接查看三張表的文件大小，***的那張表加上條件后還有24個(gè)分區(qū)，每個(gè)分區(qū)的大小不一樣。但是由于任務(wù)有40個(gè)map，由此可知有些分區(qū)的數(shù)據(jù)是拆成了幾個(gè)map任務(wù)的，所以再一次證明是可切分的，排除不可切分導(dǎo)致的map任務(wù)問(wèn)題。

第三步，分析任務(wù)運(yùn)行狀況

找到運(yùn)行完的任務(wù)，查看運(yùn)行界面圖可以看到，map任務(wù)的時(shí)間長(zhǎng)短不一，最短的1分鐘之內(nèi)，最長(zhǎng)的達(dá)半個(gè)多小時(shí)。

乍一看，好像是數(shù)據(jù)傾斜導(dǎo)致的，要確定是否數(shù)據(jù)傾斜，我們需要隨機(jī)挑幾個(gè)時(shí)間長(zhǎng)的map任務(wù)，和時(shí)間短的map任務(wù)，看看各自的數(shù)據(jù)量和數(shù)據(jù)大小。

對(duì)比發(fā)現(xiàn)，數(shù)據(jù)量基本都在100萬(wàn)到130之間，而數(shù)據(jù)大小也在100多MB左右(不能只看數(shù)據(jù)量，數(shù)據(jù)大小也很重要，防止空列這種數(shù)據(jù)傾斜情況)。

這樣一來(lái)，map端的數(shù)據(jù)傾斜其實(shí)是不存在的，所以map任務(wù)應(yīng)該基本上是均衡的。那為什么時(shí)間上會(huì)相差這么大呢?

進(jìn)而猜測(cè)，是不是因?yàn)槟承┞娜蝿?wù)剛好被擠在某臺(tái)或某幾臺(tái)機(jī)器上，而剛好這幾臺(tái)機(jī)器負(fù)載重，所以比較慢?

到這里，我們統(tǒng)計(jì)下慢的幾個(gè)map任務(wù)都在什么機(jī)器上，統(tǒng)計(jì)完發(fā)現(xiàn)，果然最慢的十幾個(gè)任務(wù)集中分布在兩臺(tái)機(jī)器上，一臺(tái)機(jī)器大概六七個(gè)的樣子。按7個(gè)任務(wù)算，每個(gè)任務(wù)讀100多MB的文件，怎么說(shuō)都在幾分鐘之內(nèi)可以搞定吧，所以好像真的跟機(jī)器負(fù)載有關(guān)系~

所以機(jī)器這里我們也必須去看一看，看看是不是負(fù)載導(dǎo)致的。重新跑一下上面的任務(wù)，找到map慢的幾臺(tái)機(jī)器，做如下查看：

分別從CPU、內(nèi)存、磁盤(pán)IO和網(wǎng)絡(luò)IO來(lái)看，這是服務(wù)器狀況查看的基本入口：

• top 命令可以輔助我們查看CPU的狀況，結(jié)果發(fā)現(xiàn)CPU平均負(fù)載不過(guò)50%
• iostat -x 5 命令可以輔助我們查看磁盤(pán)IO情況，我們發(fā)現(xiàn)請(qǐng)求數(shù)比較高但是平均等待隊(duì)列并不高，磁盤(pán)讀寫(xiě)都跟得上，所以磁盤(pán)也不是問(wèn)題
• sar -n DEV 5 命令可以輔助我們查看網(wǎng)絡(luò)IO的情況，服務(wù)器至少是千兆網(wǎng)卡，支持至少1Gb/s的速度，而從輸出來(lái)看，網(wǎng)絡(luò)遠(yuǎn)遠(yuǎn)不是問(wèn)題

由此，我們排除了機(jī)器負(fù)載過(guò)高導(dǎo)致無(wú)法服務(wù)的問(wèn)題，同時(shí)問(wèn)了下同事，說(shuō)是這個(gè)任務(wù)跑了好多次都這樣，好多次都這樣說(shuō)明機(jī)器應(yīng)該不是問(wèn)題，因?yàn)闄C(jī)器隨機(jī)分，不可能每次都分到慢的機(jī)器上。所以說(shuō)每次都map慢跟機(jī)器無(wú)關(guān)，而是我們SQL的問(wèn)題。

第四步，再觸SQL，分段分析

上面的分析已經(jīng)確認(rèn)跟機(jī)器無(wú)關(guān)，與數(shù)據(jù)不可分割也無(wú)關(guān)，而執(zhí)行計(jì)劃上看也沒(méi)什么問(wèn)題。那么只好一段一段的來(lái)看SQL了。

1、兩張小表是要分發(fā)到各節(jié)點(diǎn)的，所以不考慮，我們按條件讀一次大表的數(shù)據(jù)，統(tǒng)計(jì)下行數(shù)

SELECT COUNT(1) 
FROM xx.xxx_log 
WHERE etl_dt = '2017-01-12' 
  AND exk IN ('xxID', 'yyID') 

結(jié)果發(fā)現(xiàn)時(shí)間只花了2分鐘左右，說(shuō)明SQL不慢在這里。只能慢在join兩張小表上了，而小表join是mapjoin，理論上應(yīng)該不慢才對(duì)。

2、考慮只join一張表來(lái)看 先選表xx.xxx_model_info

SELECT COUNT(1) 
from ( 
SELECT 
       t1.exk, 
       t1.exv, 
       M.makename AS m_makename 
FROM 
  (SELECT 
          exk, 
          exv 
   FROM xx.xxx_log 
   WHERE etl_dt='2017-01-12' 
     AND exk IN ('xxID', 'yyID') ) t1 
LEFT JOIN xx.xxx_model_info M ON (M.modelid=t1.exv AND t1.exk='xxID')) tmp 

上面是跟3118的一張小表join，可以看到執(zhí)行計(jì)劃是mapjoin，而執(zhí)行時(shí)間則出乎意料，用了大概2分鐘，與單獨(dú)計(jì)算大表行數(shù)差不多。

由此可以想到，mapjoin很慢應(yīng)該與另一張表有關(guān)系，我們下面再執(zhí)行跟另一張表join的情況，如果還是這么快，那說(shuō)明兩個(gè)同時(shí)mapjoin在Hive上可能存在缺陷，而如果很慢，則說(shuō)明mapjoin只跟那張小表有關(guān)系。

再選表xx.xxx_style_info

SELECT COUNT(1) 
from ( 
SELECT 
       t1.exk, 
       t1.exv, 
       S.makename AS s_makename 
FROM 
  (SELECT 
          exk, 
          exv 
   FROM xx.xxx_log 
   WHERE etl_dt='2017-01-12' 
     AND exk IN ('xxID', 'yyID') ) t1 
LEFT JOIN xx.xxx_style_info S ON (S.styleid=t1.exv AND t1.exk='yyID') ) tmp 

這下執(zhí)行結(jié)果奇慢無(wú)比，map階段進(jìn)展很緩慢。由此說(shuō)明大表與這張小表的mapjoin存在問(wèn)題，可是mapjoin為啥還存在問(wèn)題呢? 問(wèn)題又在哪呢?

第五步，仍不放棄執(zhí)行計(jì)劃

當(dāng)看到上面問(wèn)題的時(shí)候，一頭霧水，所以著重再看執(zhí)行計(jì)劃是一個(gè)不錯(cuò)的方案。很容易想到，同是兩個(gè)數(shù)據(jù)量相差不大的小表，mapjoin的運(yùn)行速度為什么會(huì)不一樣?是字段類(lèi)型導(dǎo)致join連接出問(wèn)題?

當(dāng)我們仔細(xì)再去看最上面的執(zhí)行計(jì)劃的時(shí)候，我們會(huì)發(fā)現(xiàn)我們之前忽視的一個(gè)細(xì)節(jié)，那就是執(zhí)行計(jì)劃里有UDFToDouble這個(gè)轉(zhuǎn)換，很奇怪我們并沒(méi)有調(diào)用這樣的UDF啊，怎么會(huì)有這樣的轉(zhuǎn)換呢? 唯一的解釋只能是join字段匹配。

我們查一下join字段發(fā)現(xiàn)，大表的exv字段是string類(lèi)型，兩個(gè)小表的關(guān)聯(lián)字段都是int型，它們?cè)趈oin的時(shí)候，居然都先轉(zhuǎn)成了double型??? 這是什么鬼? 難道不應(yīng)該都往string類(lèi)型轉(zhuǎn)換，然后再join嗎?

查下Hive官網(wǎng)才發(fā)現(xiàn)，類(lèi)型關(guān)系是醬紫的...

double類(lèi)型是string類(lèi)型和int類(lèi)型的公共類(lèi)型，所以它們都會(huì)往公共類(lèi)型上轉(zhuǎn)!

實(shí)際寫(xiě)SQL中，也強(qiáng)烈建議自己做類(lèi)型匹配的處理，不要拜托給解析器，不然問(wèn)題很?chē)?yán)重。

我們把小表的int類(lèi)型轉(zhuǎn)換為string類(lèi)型再做上面第二張表的join，如下：

SELECT COUNT(1) 
from ( 
SELECT 
       t1.exk, 
       t1.exv, 
       S.makename AS s_makename 
FROM 
  (SELECT 
          exk, 
          exv 
   FROM xx.xxx_log 
   WHERE etl_dt='2017-01-12' 
     AND exk IN ('xxID', 'yyID') ) t1 
LEFT JOIN xx.xx_style_info S ON (cast(S.styleid as string)=t1.exv AND t1.exk='yyID') ) tmp 

結(jié)果符合預(yù)料，2分鐘左右的時(shí)間可以完成這個(gè)SQL任務(wù)。而當(dāng)整個(gè)任務(wù)也這么改之后，任務(wù)跑完也只要幾分鐘!

由此可見(jiàn)，事情都因細(xì)節(jié)而起!做join操作的時(shí)候，寫(xiě)SQL的人其實(shí)是最清楚字段類(lèi)型的，做上字段類(lèi)型匹配小菜一碟，可以避免很多問(wèn)題!!!

第六步，解開(kāi)謎團(tuán)

到這里，我們這個(gè)Hive任務(wù)的問(wèn)題已經(jīng)找到，那就是join兩邊key的數(shù)據(jù)類(lèi)型不對(duì)，導(dǎo)致兩邊的數(shù)據(jù)類(lèi)型都要向上做提升才能關(guān)聯(lián)。

但其實(shí)還是有問(wèn)題的，上面第四步的實(shí)驗(yàn)提到，當(dāng)用大表與3118條數(shù)據(jù)的小表xx.xxx_model_info進(jìn)行關(guān)聯(lián)的時(shí)候，很快可以出結(jié)果。但是當(dāng)用大表與另一張小表xx.xxx_style_info進(jìn)行關(guān)聯(lián)時(shí)，卻發(fā)現(xiàn)奇慢無(wú)比，也即問(wèn)題跟它有很大關(guān)系。大表無(wú)論與哪張小表關(guān)聯(lián)，都要做類(lèi)型提升，兩張小表的數(shù)據(jù)量相比大表而言，其實(shí)相差不大，但為啥數(shù)據(jù)量稍大的小表關(guān)聯(lián)就出問(wèn)題呢?

我們單獨(dú)對(duì)三張表做類(lèi)型轉(zhuǎn)換，轉(zhuǎn)為double類(lèi)型，結(jié)果發(fā)現(xiàn)三張表的類(lèi)型轉(zhuǎn)換都很快，并不存在因?yàn)閿?shù)據(jù)不同導(dǎo)致轉(zhuǎn)換速度不一樣的情況，由此排除是類(lèi)型提升時(shí)出的問(wèn)題。因而問(wèn)題最有可能出現(xiàn)在MapJoin身上!

在MapJoin階段，會(huì)把小表的內(nèi)容加載到內(nèi)存中，使用容器HashMap做存儲(chǔ)，然后對(duì)大表的關(guān)聯(lián)列進(jìn)行掃描，每掃描一行都會(huì)去查看HashMap中有沒(méi)有對(duì)應(yīng)的關(guān)聯(lián)列。這樣做起來(lái)其實(shí)是很快的，同時(shí)在Map端也減少了大量數(shù)據(jù)輸出到Reduce端。

HashMap不允許key有重復(fù)，在Hive里，如果key有重復(fù)怎么辦呢?顯然是不能把重復(fù)數(shù)據(jù)直接覆蓋的，因?yàn)閗ey重復(fù)不代表value里的其他列也是重復(fù)的。這時(shí)Hive會(huì)把小表的存儲(chǔ)由HashMap降級(jí)為L(zhǎng)inkedList，而HashMap里key是否重復(fù)由key對(duì)應(yīng)類(lèi)型的hashcode和equals方法決定。

在MapJoin階段，double類(lèi)型使用的是DoubleWriteable，它的hashcode實(shí)現(xiàn)是一個(gè)錯(cuò)誤的實(shí)現(xiàn)，如下：

return (int)Double.doubleToLongBits(value); 

long轉(zhuǎn)為int會(huì)產(chǎn)生溢出，因此不同的value很可能得到相同的hashcode，hashcode碰撞非常明顯。

這個(gè)問(wèn)題早在 Hadoop-12217 里被提到，因?yàn)樗麄冊(cè)谑褂肏ive的時(shí)候碰到了和我相同的問(wèn)題，就是類(lèi)型提升為double時(shí)出現(xiàn)MapJoin異常緩慢的情況。其描述如下：

其patch里提到正確的更改方式如下：

long dblBits = Double.doubleToLongBits(value); 
return (int) (dblBits ^ (dblBits >>> 32)); 

不過(guò)這個(gè)bug目前并未修復(fù)(當(dāng)前版本：Hadoop 2.6.0-cdh5.5.1, Hive 1.1.0-cdh5.5.1)，由于它導(dǎo)致我們數(shù)據(jù)量稍大的那張小表在MapJoin階段由HashMap轉(zhuǎn)為了LinkedList，因此數(shù)據(jù)掃描及其緩慢。而另一張3118條數(shù)據(jù)的小表，則剛好不存在hash code碰撞的問(wèn)題，所以Map Join很快。

所以，最終的問(wèn)題就在于此，所有的表象皆由它引起。***的解決辦法是在Join之前先做轉(zhuǎn)換，讓join時(shí)的鍵關(guān)聯(lián)保持類(lèi)型一致并不為double類(lèi)型即可。這個(gè)需要在寫(xiě)HQL時(shí)時(shí)常注意，問(wèn)題雖小，但是要找到它確實(shí)不容易，很是花時(shí)間。