Queries having joins stuck in running state in HUE

[#1]

I am trying to run a big query and it is halted in running state in hue.I have identical cluster where same query is running fine.

both the servers are identical but when i went through the query plan the only difference i saw was max per-host reservation parameter.

it is below where query is running

Max Per-Host Resource Reservation: Memory=1.30 GB
Per-Host Resource Estimates: Memory=75.94 GB

and it is below where it has stuck 

Max Per-Host Resource Reservation: Memory=143.56 MB
Per-Host Resource Estimates: Memory=3.84 GB

It is killing our time please help

[#2]

Hi

We could see the estimation value per-host are different. Impala estimation might not be accurate if the table stats are not updated.

Make sure you have ran compute stats <db>.<tablename> for all the table involved in this query

If you are facing the issue still. Please share the query profile of both the cluster and make sure data are same across both cluster.

Thanks
Jerry

[#3]

There's no relation between resource reservations and query states.

There's probably two things going on:

• You're getting different query plans on the two clusters - either the data or table definitions is different or the stats are missing or out-of-date on one cluster
• The query is being kept running because the output rows are not all fetched by hue? This can happen if the query returns more than a page of rows and the user does not scroll through the whole result set - the issue is that Hue only fetches results on demand and Impala keeps the query running until the last row is fetched by Hue.  How many rows are being returned from the query. We're looking at making this more robust - the scenario is avoidable.

As a mitigation we usually suggest setting an "idle query timeout" in Cloudera manager to automatically cancel queries that have been hanging around for a while with no client activity.

Edit: second observation was wrong. See my next post.

[#4]

Actually, scratch what I just said - that advice applies if the query is stuck in the FINISHED state. If it's stuck in the RUNNING state, it means the query is just taking a long time to produce any results. So you're probably getting a bad query plan on one cluster that is extremely slow to execute. E.g. the order of the joins chosen by the planner is inefficient. Usually computing stats on all the tables will improve the query plan.