Hive Tez减速器运行异常缓慢 [英] Hive Tez reducers are running super slow

问题描述

我加入了多个表，总行数约为250亿.最重要的是，我正在做聚合.以下是我的配置单元设置，用于生成最终输出.我不太确定如何调整查询并使其运行得更快.目前，我正在反复试验，看看是否可以产生一些结果，但似乎无法正常工作.有人可以分享您对此的想法吗?谢谢.

I have joined multiple tables and the total no of rows are around 25 billion. On top of that, I am doing aggregation. Here are my hive settings as below, which I am using to generate the final output. I am not really sure how to tune the query and make it run faster. Currently, I am doing trial and error and see if that can produce some results but that doesn't seem to be working.Mappers are running faster but reducers are taking forever to finish off. Could anyone share your thoughts on this? Thank you.

    SET hive.execution.engine=tez;
    SET hive.exec.dynamic.partition.mode=nonstrict;
    SET hive.qubole.cleanup.partial.data.on.failure=true;
    SET hive.tez.container.size=8192;
    SET tez.task.resource.memory.mb=8192;
    SET tez.task.resource.cpu.vcores=2;
    SET hive.mapred.mode=nonstrict;
    SET hive.qubole.dynpart.use.prefix=true;
    SET hive.vectorized.execution.enabled=true;
    SET hive.vectorized.execution.reduce.enabled =true;
    SET hive.cbo.enable=true;
    SET hive.compute.query.using.stats=true;
    SET hive.stats.fetch.column.stats=true;
    SET hive.stats.fetch.partition.stats=true;
    SET mapred.reduce.tasks = -1;
    SET hive.auto.convert.join.noconditionaltask.size=2730;
    SET hive.auto.convert.join=true;
    SET hive.auto.convert.join.noconditionaltask=true;
    SET hive.auto.convert.join.noconditionaltask.size=8053063680;
    SET hive.compute.query.using.stats=true;
    SET hive.stats.fetch.column.stats=true;
    SET hive.stats.fetch.partition.stats=true;
    SET mapreduce.job.reduce.slowstart.completedmaps=0.8;
    set hive.tez.auto.reducer.parallelism = true;
    set hive.exec.reducers.max=100;
    set hive.exec.reducers.bytes.per.reducer=1024000000;

SQL:

SELECT D.d
      ,D.b
      ,COUNT(DISTINCT A.x)  AS cnt
      ,SUM(c)               AS sum
 FROM A
LEFT JOIN
       B
ON A.a = B.b
LEFT JOIN
       C 
ON B.b = C.c
JOIN
       D
 ON A.a >= D.d
AND A.a <= D.d
GROUP BY 1,2
CLUSTER BY D.d;

推荐答案

尚无查询计划，因此也许还有其他内容，但是这些设置无疑限制了reducer的并行性:

Do not have query plan yet, so maybe there is something else, but these settings definitely are limiting reducers parallelism:

set hive.exec.reducers.max=100;
set hive.exec.reducers.bytes.per.reducer=1024000000;

我建议增加允许的reducer数量并减少每个reducer的字节数，这将增加reducer的并行度:

I'd suggest to increase the number of reducers allowed and reduce bytes per reducer, this will increase parallelism on reducers:

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

Hive 1.2.0+还提供自动重写对计数(不同)进行优化.检查此设置，默认情况下应为true

Also Hive 1.2.0+ provides auto-rewrite optimization for count(distinct). Check this setting, it should be true by default:

hive.optimize.distinct.rewrite=true;

如果查询停留在最后一个化简器上，则连接键中会出现连接键偏斜

And if the query stuck on the last reducer, then there is a skew in join keys

这篇关于Hive Tez减速器运行异常缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！