Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive Query slowness

avatar
Rising Star

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 SOLUTIONS

avatar
Rising Star

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

View solution in original post

avatar

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

View solution in original post

11 REPLIES 11

avatar
@Yukti Agrawal

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

avatar
Rising Star

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

avatar

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

avatar
Rising Star

@Sindhu Please suggest anything.

avatar
Rising Star

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

avatar
Rising Star

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

avatar
New Contributor

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

avatar
New Contributor

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

 

avatar
Community Manager

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



Regards,

Vidya Sargur,
Community Manager


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community: