Why is the final reduce step extremely slow in this MapReduce? (HiveQL, HDFS MapReduce)

by

If final reducer is a join then it looks like skew in join key.
First of all check two things:

check that b.f1 join key has no duplicates:

select b.f1, count(*) cnt from B b 
 group by b.f1 
having count(*)>1 order by cnt desc;

check the distribution of a.f1:

select a.f1, count(*) cnt from A a
 group by a.f1  
order by cnt desc
limit 10;

This query will show skewed keys.

If there is a skew (too many rows with the same value) then join skewed keys separately, use union all:

SELECT a.f1, f2, ..., fn
  FROM ( select * from A where f1 = skewed_value) as a --skewed
  LEFT JOIN B as b
  ON a.f1 = b.f1
WHERE {PARTITION_FILTER}
UNION ALL
SELECT a.f1, f2, ..., fn
  FROM ( select * from A where f1 != skewed_value) as a --all other
  LEFT JOIN B as b
  ON a.f1 = b.f1
WHERE {PARTITION_FILTER}

And finally if there is no issues with skew and duplication, then try to increase reducers parallelism:
Get current bytes per reducer configuration

set hive.exec.reducers.bytes.per.reducer;

typically this will return some value about 1G. Try to divide by two, set new value before your query and check how many reducers will it start and performance. Success criteria is more reducers has started and performance improved. 

set hive.exec.reducers.bytes.per.reducer=67108864;

The less the bytes per reducer the more reducers will be started, increasing parallelism;

UPDATE: Try to enable map-join, your second table is small enough to fit in memory, mapjoin will work without reducers at all and it will be no problem with skew on reducers.

How to enable mapjoin: https://stackoverflow.com/a/49154414/2700344

More Related Contents:

  1. Execute Hive Query with IN clause parameters in parallel
  2. Is Mongodb Aggregation framework faster than map/reduce?
  3. how many mappers and reduces will get created for a partitoned table in hive
  4. Speed up the loop operation in R
  5. One could use a profiler, but why not just halt the program? [closed]
  6. Spark functions vs UDF performance?
  7. Adding a redundant assignment speeds up code when compiled without optimization
  8. Why is this SSE code 6 times slower without VZEROUPPER on Skylake?
  9. How many Include I can use on ObjectSet in EntityFramework to retain performance?
  10. Faster way to find the first empty row in a Google Sheet column
  11. Why is SSE scalar sqrt(x) slower than rsqrt(x) * x?
  12. Haskell foldl’ poor performance with (++)
  13. Which Intel microarchitecture introduced the ADC reg,0 single-uop special case?
  14. Storing Documents as Blobs in a Database – Any disadvantages?
  15. Can modern x86 implementations store-forward from more than one prior store?
  16. Cellfun versus Simple Matlab Loop performance
  17. Simulating Slow Internet Connection [closed]
  18. What is the difference between spark.sql.shuffle.partitions and spark.default.parallelism?
  19. How to speed up Compile Time of my CMake enabled C++ Project?
  20. Getting total row count from OFFSET / FETCH NEXT
  21. Content-Length header versus chunked encoding
  22. How to find pair with kth largest sum?
  23. Hive unable to manually set number of reducers
  24. Does Scala support tail recursion optimization?
  25. MongoDB ‘count()’ is very slow. How do we refine/work around with it?
  26. Memory Allocation/Deallocation Bottleneck?
  27. Move all odd positioned element to left half and even positioned to right half in-place
  28. How can the rep stosb instruction execute faster than the equivalent loop?
  29. Performance of function slice parameter vs global variable?
  30. SET vs. SELECT – What’s the difference?

Leave a Comment