https://community.cloudera.com/t5/Support-Questions/queries-taking-forever-upgrading-to-hive-on-tez-from-MR/m-p/343660#M233987 <P>Hive from MR was upgraded to tez with the latest upgrade to cdp 7 and we are seeing significant performance drop. Tried running the same query with static single hour partition just to observe outcome and it took 5hrs to finish whereas it used to complete within 4-5 hrs across the same ORC data set for all 24hrs partition.</P><P>&nbsp;</P><P>INSERT OVERWRITE TABLE `user_tables`.`dummy_table`&nbsp;PARTITION(date_partition, hour_partition)<BR />SELECT `(date_partition|hour_partition)?+.+`, to_date(srt.date_time) as date_partition, SUBSTR(srt.date_time, 12, 2) AS hour_partition<BR />FROM `user_tables`.`source_dummy_table` srt<BR />WHERE srt.date_partition BETWEEN "2022-04-05" AND date_add("2022-04-05", 4)<BR />AND upper(srt.prop1) = "XYZ"<BR />AND to_date(srt.date_time) BETWEEN "2022-04-05" AND "2022-04-05";<BR /><BR /><BR />DAG shows for above:&nbsp;<BR />VERTICES MODE&nbsp; &nbsp; &nbsp;STATUS&nbsp; &nbsp; &nbsp;TOTAL&nbsp; &nbsp;COMPLETED&nbsp; RUNNING&nbsp; &nbsp; PENDING&nbsp; &nbsp;FAILED&nbsp; &nbsp; &nbsp;KILLED<BR />----------------------------------------------------------------------------------------------<BR />1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; container&nbsp; &nbsp; KILLED&nbsp; &nbsp; 136366&nbsp; &nbsp; &nbsp;9112&nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 127254&nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1258<BR />----------------------------------------------------------------------------------------------<BR />VERTICES: 00/01 [=&gt;&gt;-------------------------] 6% ELAPSED TIME: 39377.91 s<BR /><BR /><BR />We are using dynamic partitioning because this used to work fine on mr. What memory parameters can be tweaked for tez to make it work because this time line is unrealistic and its a relatively powerful cluster with 34 nodes with enough cores/memory (3TB).&nbsp;<BR />Below settings already added as per suggestion:<BR />hive.exec.compress.intermediate=true<BR />hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec</P><P>hive.intermediate.compression.type=BLOCK</P><P>hive.exec.parallel=true</P><P>hive.enforce.sorting=true</P><P>hive.exec.orc.split.strategy=BI</P><P>tez.grouping.max-size=67108864</P><P>tez.grouping.min-size=67108864</P><P>hive.merge.tezfiles=true</P><P>hive.merge.smallfiles.avgsize=67108864</P><P>hive.merge.size.per.task=134217728</P><P>tez.am.resource.memory.mb=16384</P><P><SPAN>hive.tez.container.size=16384<BR /><BR />Any help or suggestion is appreaciated.&nbsp;</SPAN></P> Thu, 12 May 2022 13:53:58 GMT Djentlguy 2022-05-12T13:53:58Z