[jira] [Commented] (HIVE-17783) Hybrid Grace Hash Join has performance degradation for N-way join using Hive on Tez
[ https://issues.apache.org/jira/browse/HIVE-17783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16226472#comment-16226472 ] Ferdinand Xu commented on HIVE-17783: - Thanks [~wei.zheng] for your reply. > In your case for the n-way joins, the optimizer stats estimation may not be > accurate which makes the situation worse. AFAIK, the row size estimated should be the same with non-hybrid grace hash join case. It's strange why the spill happens in hybrid grace hash join case. Another observation is o rows of data for one partition is occupying about 65636 bytes memory. > Anyway, the ultimate way to solve this problem is to have a reliable memory > manager which can provide memory usage/quota at any moment. Right now we're > following a conservative approach, which is to use a soft (possibly > inaccurate) memory limit. That way we can avoid unnecessary spilling if there > is enough memory for loading the hashtable. Interest. Any ticket addressing this part of work? > Hybrid Grace Hash Join has performance degradation for N-way join using Hive > on Tez > --- > > Key: HIVE-17783 > URL: https://issues.apache.org/jira/browse/HIVE-17783 > Project: Hive > Issue Type: Bug >Affects Versions: 2.2.0 > Environment: 8*Intel(R) Xeon(R) CPU E5-2699 v4 @ 2.20GHz > 1 master + 7 workers > TPC-DS at 3TB data scales > Hive version : 2.2.0 >Reporter: Ferdinand Xu > Attachments: Hybrid_Grace_Hash_Join.xlsx, screenshot-1.png > > > Most configurations are using default value. And the benchmark is to test > enabling against disabling hybrid grace hash join using TPC-DS queries at 3TB > data scales. Many queries related to N-way join has performance degradation > over three times test. Detailed result is attached. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (HIVE-17783) Hybrid Grace Hash Join has performance degradation for N-way join using Hive on Tez
[ https://issues.apache.org/jira/browse/HIVE-17783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16225678#comment-16225678 ] Wei Zheng commented on HIVE-17783: -- [~Ferd] Sorry for the late reply. Yes the spilling part is the bottleneck and there's no easy way to get around it. In your case for the n-way joins, the optimizer stats estimation may not be accurate which makes the situation worse. Anyway, the ultimate way to solve this problem is to have a reliable memory manager which can provide memory usage/quota at any moment. Right now we're following a conservative approach, which is to use a soft (possibly inaccurate) memory limit. That way we can avoid unnecessary spilling if there is enough memory for loading the hashtable. > Hybrid Grace Hash Join has performance degradation for N-way join using Hive > on Tez > --- > > Key: HIVE-17783 > URL: https://issues.apache.org/jira/browse/HIVE-17783 > Project: Hive > Issue Type: Bug >Affects Versions: 2.2.0 > Environment: 8*Intel(R) Xeon(R) CPU E5-2699 v4 @ 2.20GHz > 1 master + 7 workers > TPC-DS at 3TB data scales > Hive version : 2.2.0 >Reporter: Ferdinand Xu > Attachments: Hybrid_Grace_Hash_Join.xlsx, screenshot-1.png > > > Most configurations are using default value. And the benchmark is to test > enabling against disabling hybrid grace hash join using TPC-DS queries at 3TB > data scales. Many queries related to N-way join has performance degradation > over three times test. Detailed result is attached. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (HIVE-17783) Hybrid Grace Hash Join has performance degradation for N-way join using Hive on Tez
[ https://issues.apache.org/jira/browse/HIVE-17783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16202985#comment-16202985 ] Ferdinand Xu commented on HIVE-17783: - Logically it should have some performance benefits over the non hybrid grace hash join since it didn't need to rescan the big table again during the reprocessing phase when hash table can not fit into the memory. > Hybrid Grace Hash Join has performance degradation for N-way join using Hive > on Tez > --- > > Key: HIVE-17783 > URL: https://issues.apache.org/jira/browse/HIVE-17783 > Project: Hive > Issue Type: Bug >Affects Versions: 2.2.0 > Environment: 8*Intel(R) Xeon(R) CPU E5-2699 v4 @ 2.20GHz > 1 master + 7 workers > TPC-DS at 3TB data scales > Hive version : 2.2.0 >Reporter: Ferdinand Xu > Attachments: Hybrid_Grace_Hash_Join.xlsx, screenshot-1.png > > > Most configurations are using default value. And the benchmark is to test > enabling against disabling hybrid grace hash join using TPC-DS queries at 3TB > data scales. Many queries related to N-way join has performance degradation > over three times test. Detailed result is attached. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (HIVE-17783) Hybrid Grace Hash Join has performance degradation for N-way join using Hive on Tez
[
https://issues.apache.org/jira/browse/HIVE-17783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16202938#comment-16202938
]
Ferdinand Xu commented on HIVE-17783:
-
Thanks [~sershe] for your input. I think the degradation in my test should
cause by the unnecessary spilling. I take a look at the longest tasks for both
enable/disable which processing the same number of records and have a roughly
estimation for each phase with log.
!screenshot-1.png!
Extra reprocessing takes longer time and in disable case those data actually is
not spilled into the disk. And from the following logs we can see that the
spilled row numbers are actually very small (e.g. partition 0: 0 row, partition
3: 1 row) while the estimated memory is relative high (e.g. partition 0: 65636,
partition 3: 589924). This is because the estimated memory is obtained from WBS
from BytesBytesMultiHashMap. But in disabled case, there is limited overhead of
creating BytesBytesMultiHashMap which maintains only one such hash map. I think
we need to figure out a way to have better estimation of memory to avoid
unnecessary spill caused by memory. Any thoughts or suggestions about this
point?
{noformat}
2017-10-13 09:14:43,666 [INFO] [pool-29-thread-1]
|persistence.HybridHashTableContainer|: Spilling hash partition 0 (Rows: 0, Mem
size: 65636):
/ssd/ssd-pcie/hadoop/data/local-dirs/usercache/root/appcache/application_1506652027239_1242/container_1506652027239_1242_01_42/tmp/partition-0-1870407313239959464.tmp
2017-10-13 09:14:43,666 [INFO] [pool-29-thread-1]
|persistence.HybridHashTableContainer|: Memory usage before spilling: 1050880
2017-10-13 09:14:43,666 [INFO] [pool-29-thread-1]
|persistence.HybridHashTableContainer|: Memory usage after spilling: 985244
2017-10-13 09:14:43,667 [INFO] [pool-29-thread-1] |common.FileUtils|: Local
directories not specified; created a tmp file:
/ssd/ssd-pcie/hadoop/data/local-dirs/usercache/root/appcache/application_1506652027239_1242/container_1506652027239_1242_01_42/tmp/partition-3-8788724383233259683.tmp
2017-10-13 09:14:43,667 [INFO] [pool-29-thread-1]
|persistence.HybridHashTableContainer|: Trying to spill hash partition 3 ...
2017-10-13 09:14:43,669 [INFO] [pool-29-thread-1]
|persistence.HybridHashTableContainer|: Spilling hash partition 3 (Rows: 1, Mem
size: 589924):
/ssd/ssd-pcie/hadoop/data/local-dirs/usercache/root/appcache/application_1506652027239_1242/container_1506652027239_1242_01_42/tmp/partition-3-8788724383233259683.tmp
2017-10-13 09:14:43,669 [INFO] [pool-29-thread-1]
|persistence.HybridHashTableContainer|: Memory usage before spilling: 1509532
2017-10-13 09:14:43,669 [INFO] [pool-29-thread-1]
|persistence.HybridHashTableContainer|: Memory usage after spilling: 919608
2017-10-13 09:14:43,669 [INFO] [pool-29-thread-1] |common.FileUtils|: Local
directories not specified; created a tmp file:
/ssd/ssd-pcie/hadoop/data/local-dirs/usercache/root/appcache/application_1506652027239_1242/container_1506652027239_1242_01_42/tmp/partition-15-1832304146451074287.tmp
2017-10-13 09:14:43,669 [INFO] [pool-29-thread-1]
|persistence.HybridHashTableContainer|: Trying to spill hash partition 15 ...
2017-10-13 09:14:43,676 [INFO] [pool-29-thread-1]
|persistence.HybridHashTableContainer|: Spilling hash partition 15 (Rows: 2,
Mem size: 589924):
/ssd/ssd-pcie/hadoop/data/local-dirs/usercache/root/appcache/application_1506652027239_1242/container_1506652027239_1242_01_42/tmp/partition-15-1832304146451074287.tmp
{noformat}
> Hybrid Grace Hash Join has performance degradation for N-way join using Hive
> on Tez
> ---
>
> Key: HIVE-17783
> URL: https://issues.apache.org/jira/browse/HIVE-17783
> Project: Hive
> Issue Type: Bug
>Affects Versions: 2.2.0
> Environment: 8*Intel(R) Xeon(R) CPU E5-2699 v4 @ 2.20GHz
> 1 master + 7 workers
> TPC-DS at 3TB data scales
> Hive version : 2.2.0
>Reporter: Ferdinand Xu
> Attachments: Hybrid_Grace_Hash_Join.xlsx, screenshot-1.png
>
>
> Most configurations are using default value. And the benchmark is to test
> enabling against disabling hybrid grace hash join using TPC-DS queries at 3TB
> data scales. Many queries related to N-way join has performance degradation
> over three times test. Detailed result is attached.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (HIVE-17783) Hybrid Grace Hash Join has performance degradation for N-way join using Hive on Tez
[ https://issues.apache.org/jira/browse/HIVE-17783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16202933#comment-16202933 ] Sergey Shelukhin commented on HIVE-17783: - I think we can disable it by default The main motivation was actually avoiding OOMs as far as I understand. I don't thin anyone is working on perf improvements right now. cc [~gopalv] > Hybrid Grace Hash Join has performance degradation for N-way join using Hive > on Tez > --- > > Key: HIVE-17783 > URL: https://issues.apache.org/jira/browse/HIVE-17783 > Project: Hive > Issue Type: Bug >Affects Versions: 2.2.0 > Environment: 8*Intel(R) Xeon(R) CPU E5-2699 v4 @ 2.20GHz > 1 master + 7 workers > TPC-DS at 3TB data scales > Hive version : 2.2.0 >Reporter: Ferdinand Xu > Attachments: Hybrid_Grace_Hash_Join.xlsx, screenshot-1.png > > > Most configurations are using default value. And the benchmark is to test > enabling against disabling hybrid grace hash join using TPC-DS queries at 3TB > data scales. Many queries related to N-way join has performance degradation > over three times test. Detailed result is attached. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (HIVE-17783) Hybrid Grace Hash Join has performance degradation for N-way join using Hive on Tez
[ https://issues.apache.org/jira/browse/HIVE-17783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16202623#comment-16202623 ] Sergey Shelukhin commented on HIVE-17783: - Hmm, we observed this with LLAP (which has advantage of table sharing in non-hybrid case), but not with containers to the best of my knowledge. It's possible it's slower even w/o accounting for sharing. It's also possible that mapjoin table size was configured to be too small so there was too much needless spilling... [~gopalv] any input? We could disable it by default if it's hard to tune. Then for the cases where it can shine people can turn it on and tune the sizing. > Hybrid Grace Hash Join has performance degradation for N-way join using Hive > on Tez > --- > > Key: HIVE-17783 > URL: https://issues.apache.org/jira/browse/HIVE-17783 > Project: Hive > Issue Type: Bug >Affects Versions: 2.2.0 > Environment: 8*Intel(R) Xeon(R) CPU E5-2699 v4 @ 2.20GHz > 1 master + 7 workers > TPC-DS at 3TB data scales > Hive version : 2.2.0 >Reporter: Ferdinand Xu > Attachments: Hybrid_Grace_Hash_Join.xlsx > > > Most configurations are using default value. And the benchmark is to test > enabling against disabling hybrid grace hash join using TPC-DS queries at 3TB > data scales. Many queries related to N-way join has performance degradation > over three times test. Detailed result is attached. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (HIVE-17783) Hybrid Grace Hash Join has performance degradation for N-way join using Hive on Tez
[ https://issues.apache.org/jira/browse/HIVE-17783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16201380#comment-16201380 ] Ferdinand Xu commented on HIVE-17783: - CC [~wzheng] [~sershe] > Hybrid Grace Hash Join has performance degradation for N-way join using Hive > on Tez > --- > > Key: HIVE-17783 > URL: https://issues.apache.org/jira/browse/HIVE-17783 > Project: Hive > Issue Type: Bug >Affects Versions: 2.2.0 > Environment: 8*Intel(R) Xeon(R) CPU E5-2699 v4 @ 2.20GHz > 1 master + 7 workers > TPC-DS at 3TB data scales > Hive version : 2.2.0 >Reporter: Ferdinand Xu > Attachments: Hybrid_Grace_Hash_Join.xlsx > > > Most configurations are using default value. And the benchmark is to test > enabling against disabling hybrid grace hash join using TPC-DS queries at 3TB > data scales. Many queries related to N-way join has performance degradation > over three times test. Detailed result is attached. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
