Hive Query slowness

[#1]

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.

[#2 · Accepted solution]

Couple of things you can check

1. Check if the dataset has changed from previous run to current run.
2. 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
3. If you have tez-ui, that is the best place to start checking the details on where the time is spent.
4. 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.
5. 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).

[#3]

@Yukti Agrawal

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

[#4]

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

[#5]

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

[#6]

@Sindhu Please suggest anything.

[#7 · Accepted solution]

Couple of things you can check

1. Check if the dataset has changed from previous run to current run.
2. 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
3. If you have tez-ui, that is the best place to start checking the details on where the time is spent.
4. 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.
5. 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).

[#8]

@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?

[#9]

@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.

[#10]

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

[#11]

@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.