question Re: Hive Query slowness in Support Questions

Hive Query slowness

Yukti — Thu, 15 Dec 2016 16:15:32 GMT

The hive query which is used by my batch is taking too much time to run. Earlier when i fire the same query it took around 5 minutes and now it is taking around 22 minutes. I cant change the query. Please suggest the correct way to investigate this issue or kindly suggest any resolution.

Re: Hive Query slowness

ssubhas — Thu, 15 Dec 2016 16:28:25 GMT

@Yukti Agrawal

First check if the query creates an application and if there any issues at the application end from Resource Manager UI.

Re: Hive Query slowness

Yukti — Thu, 15 Dec 2016 18:02:46 GMT

@Sindhu Please find the full logs from Resource Manager UI. rm-ui-logs.txt

Re: Hive Query slowness

rbalamohan — Thu, 15 Dec 2016 18:59:15 GMT

Couple of things you can check

Check if the dataset has changed from previous run to current run.
Not sure how you are running your query. For e.g, if you are using hive cli, you can use "hive --hiveconf hive.tez.exec.print.summary=true". This should print the pre-execution (compilation, job submission), DAG execution times after the job is complete. That can give hints on where the time is spent
If you have tez-ui, that is the best place to start checking the details on where the time is spent.
It would be good to share the query and "explain <sql>" output with "--hiveconf hive.explain.user=false". If possible, share "explain formatted <sql>" output which dumps the plan information in JSON format.
Check if vertices are running slow due to resource constraints (i.e, some tasks would have started, but others are in waiting mode as resources are not available in queue or in cluster).

Re: Hive Query slowness

Yukti — Mon, 19 Dec 2016 22:30:09 GMT

@Rajesh Balamohan The thing is nothing is changed in code or in the volume of data. There is no change made on cluster in terms of configuration or installation of new things. And since there is no change made than how suddenly the job is getting slow?

Re: Hive Query slowness

Yukti — Mon, 19 Dec 2016 22:33:35 GMT

@Sindhu Please suggest anything.

Re: Hive Query slowness

Senthidataguy — Mon, 19 Dec 2016 23:00:43 GMT

Hi Yukti Agrawal,

Based on the logs that you have attached, it seems that,

1. HA is enabled in your cluster

2. One of your NameNode is down

Reason : Hive is not able to reach the active NameNode or None of your NameNodes are active at the time of the process.

Suggestion :

1. If NN automatic failover is not enabled, try to manually make the standby NN as active.

2. If NN automatic failover is enabled, try to make Hive aware that NN HA is enabled. The NameNode's IP should be replaced by NameNode's Namespace (HA way of reaching NameNode).

Log Snippet :

org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.ipc.StandbyException): Operation category READ is not supported in state standby

Best Regards,

Adharsh

Re: Hive Query slowness

knarendran — Thu, 22 Dec 2016 20:55:45 GMT

@Yukti Agrawal

There is a chance that your job might be waiting for resources to be released by other jobs running in the cluster. Its worth checking in RM UI once you execute the query until the state changes to "RUNNING" - where most of the time is being spent.

Re: Hive Query slowness

mr_masud_sarkar — Mon, 07 Aug 2017 16:34:15 GMT

@Rajesh Balamohan, I am also facing slowness as days progresses, initially the query was taking 15 mins to complete, which now takes 50 mins to complete. Unlike the problem stated here, my dataset is also growing in size. I am attaching the dumps of formatted explain. Could you please look into it and suggest if anything suspicious found. TIA.

Re: Hive Query slowness

DE_2021 — Thu, 16 Sep 2021 00:36:12 GMT

Hi Masud,

I am also facing the same issue now. What is the resolution find out for hive slowness.

Please let me know that helps me.

Thanks

Re: Hive Query slowness

VidyaSargur — Thu, 16 Sep 2021 06:11:02 GMT

@DE_2021, as this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post.

Re: Hive Query execution issue

lav — Mon, 24 Jan 2022 10:38:01 GMT

Hi,

when I run the hive query it showing the below error

Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

But this error is not showing all the time it got succeed with some of the users some times it got failed. Could you please suggest the reason and how to overcome this.

need urgent. could you please help us.