Phoenix - Query Timeout

[#1]

I have a table in HBase created via Phoenix. The table has approxmiately 20 million records. I'm connecting to Phoenix via:

phoenix-sqlline.py hbasemaster:2181:/hbase-unsecure

I'm trying to run a count as follows:

select count(columnname) from tablename;

When I run that SQL, Phoenix reports a timeout

org.apache.phoenix.exception.PhoenixIOException: org.apache.phoenix.exception.PhoenixIOException: Failed after attempts=36, exceptions:

java.net.SocketTimeoutException: callTimeout=60000, callDuration=60317: row ....

I've tried changing the hbase.rpc.timeout via Ambari, but that doesn't seem to be the issue. The default timeout in Ambari was set to 1m30s and I changed it to 2m. The timeout reported by Phoenix is 60s before and after the change, so I don't think that's the culprit anyway.

What setting do I need to change to allow for longer running queries? Is there something else that I should be looking at?

[#2]

Try increasing hbase.rpc.timeout in hbase-site.xml

[#3]

I tried that already, as I indicated in my original post. It did not work.

[#4]

Right, sorry about that, but that's near certainly where the error is coming from. The question on my mind is why sqlline isn't picking up the configuration value. @Terry 's suggestion is a good one. Depending on the version of HDP you're running, HBASE_CONF_DIR would also work.

[#5]

There are phoenix parameters as well:

https://phoenix.apache.org/tuning.html

Its weird I don't see an applicable timeout. Query timeout would make sense but that is ten minutes and not 1 minute.

phoenix.query.timeoutMs

[#6]

The difference is that Michael showed a SocketTimeoutException in the timeout. That's most likely the HBase RPC framework limiting the lifetime of the socket, not the Phoenix application-level timeout you listed (which is still good to remember!)

[#7]

But he said that one was 90s before and changing it didn't help. Ah well lets see what he comes up with.

The issue might be that he changed it on the server side but should have changed on the client. Perhaps the default is 60s?

[#8]

Which it is: So most likely the phoenix client does not take the hbase-site.xml from ambari but uses default values.

hbase.rpc.timeout

Description

This is for the RPC layer to define how long HBase client applications take for a remote call to time out. It uses pings to check connections but will eventually throw a TimeoutException.

Default

60000

[#9]

Oops. I missed that part 🙂

[#10]

I discovered several posts via Google that suggested the problem was hbase.rpc.timeout. When I changed the value of this via Ambari, it had no effect. As I responded below, the problem seemed to be that I was missing the HBASE_CONF_DIR environment variable so it was using the default settings instead of the Ambari configured settings.

[#11 · Accepted solution]

There is an odd environment variable used for the HBase configuration directory. Try setting HBASE_CONF_PATH=/etc/hbase/conf in your shell session and see if the timeout value gets picked up.

[#12]

This was the issue. Well more specifically HBASE_CONF_DIR. Running phoenix_utils.py showed HBASE_CONF_PATH and HBASE_CONF_DIR as equal to ".". I set my HBASE_CONF_DIR environment variable to to use "/etc/hbase/conf" and now the query seems to work ok.

[#13]

FWIW, this should be fixed in recent versions of Apache Phoenix and HDP.

[#14]

Hi @Terry Padgett @Josh Elser,

I am using DBVisulizer to connect to Phoenix. Mine is a kerberos enabled cluster and getting below error messages. Can suggestions?

An error occurred while establishing the connection: Long Message: callTimeout=600000, callDuration=620131: Details: Type: org.apache.phoenix.exception.PhoenixIOException Error Code: 101 SQL State: 08000

Not sure how I can pass conf directory for DBVisulizer (added all conf files to Resoucres directory of DbViz but didn't helped)

[#15]

Hi All,

I set phoenix timeout is 3hr, rpc 3hr and query alive is also 3hr but for bigger query whenever phoenix calltimeout occurs my hbase region server also goes down, I don not know what is happening.

Please help me out on this. @Terry Padgett

[#16]

Below KB article helped me to resolve the issue.

https://community.hortonworks.com/content/supportkb/49037/phoenix-sqlline-query-on-larger-data-set-f...

[#17]

Frustrating that that link is hidden behind a 'paywall'.  I have an account but I am not allowed to view without contacting sales