Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive Query slowness

Solved Go to solution

Hive Query slowness

Contributor

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Hive Query slowness

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

8 REPLIES 8

Re: Hive Query slowness

@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

Contributor

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

Re: Hive Query slowness

New Contributor

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

Contributor

@Sindhu Please suggest anything.

Re: Hive Query slowness

Contributor

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

Re: Hive Query slowness

Contributor

@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

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.

Re: Hive Query slowness

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