question Re: hiveserver2 error in Support Questions

hiveserver2 error

ryu — Thu, 27 May 2021 21:04:09 GMT

Hi experts,

Many of the devs are getting the following error when trying to connect with squirrel:

Error: org.apache.thrift.transport.TTransportException: java.net.SocketTimeoutException: Read timed out

SQLState: 08S01

ErrorCode: 0

I believe the issue may be because there are too many concurrent hive connections.

Can someone help suggest how should i configure this to allow more hive connections?

Also I find it weird that I can connect through beeline using the edge node of that cluster but Squirrel is giving an error.

Thanks,

Any help is much appreciated.

Re: hiveserver2 error

heya — Fri, 28 May 2021 03:10:21 GMT

all connections from squirrel got the issues?

Re: hiveserver2 error

Shelton — Fri, 28 May 2021 08:51:44 GMT

@ryu
Please have a look at this document it could help you

https://serverliving.wordpress.com/2016/05/05/step-by-step-connect-squirrelsql-hiveserver2/

Re: hiveserver2 error

LeetKrew93 — Fri, 28 May 2021 09:23:27 GMT

Could you please monitor the number of connections at the time you are getting this exception ?

Based on the number of connections, you should tune HMS and HS2 => https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/cdh_ig_hive_metastore_configure.html#concept_jsw_bnc_rp

Also, how many HS2 instances are installed on your cluster as Cloudera recommends splitting HiveServer2 into multiple instances and load balancing them once you start allocating more than 16 GB to HiveServer2. The objective is to adjust the size to reduce the impact of Java garbage collection on active processing by the service.

Re: hiveserver2 error

ryu — Fri, 28 May 2021 20:36:05 GMT

Squirrel connecting to production cluster is a bit better but i noticed there are fewer existing connections.
I will do more research on what the others suggested in the comments.

Thanks for the response.

Re: hiveserver2 error

ryu — Fri, 28 May 2021 21:05:27 GMT

@LeetKrew93 We currently have 2 instances of HMS and HS2.

I believe if the jdbc string includes all the zookeepers, doesn't the zookeeper help load balance for hive?

Also I believe for our dev cluster, there are lots of connections. Is there a way to increase the max number of connections or should i create an extra hive instance?

Re: hiveserver2 error

ryu — Fri, 28 May 2021 21:55:54 GMT

So i just counted our connections and it seems like we have around 40 connections going on at the same time.

So when should or how do we know how many hive instances we should have?

Re: hiveserver2 error

ryu — Fri, 28 May 2021 23:25:05 GMT

Even after I increased the hiveserver2 heap memory and restarted hive, I don't see any connections but still Squirrel is having issues connecting to the dev cluster.
But our Prod cluster seems to work fine.

Any help is much appreciated.

Thanks,

Re: hiveserver2 error

LeetKrew93 — Sat, 29 May 2021 00:28:05 GMT

@ryu , as a general rule of thumb Cloudera recommends that you determine the total number of HS2 servers on a cluster by dividing the expected maximum number of concurrent users on a cluster by 40. For example, if 400 concurrent users are expected, 10 HS2 instances should be available to support them.

Also, here are some HS2 tuning best practices that you can visit => https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/admin_hive_tuning.html#hs2_perf_best_practices

Regards,

Aditya

Re: hiveserver2 error

LeetKrew93 — Sat, 29 May 2021 00:43:01 GMT

@ryu , could you please upload HS2 and HMS logs at the time you are hitting below exception while trying to make a connection to hive using Squirrel :

++++++

Error: org.apache.thrift.transport.TTransportException: java.net.SocketTimeoutException: Read timed out

SQLState: 08S01

ErrorCode: 0

++++++

Did you find anything suspicious from the Squirrel's log end ?

Also, let us know if it is intermittent or not ?

Regards,

Aditya

Re: hiveserver2 error

ryu — Sat, 29 May 2021 01:44:23 GMT

Most of the time I get this time out, even after restarting hive.

This happens on our dev cluster. I am able to use beeline to connect to our hive in our dev cluster.

Our production cluster does not seem to have this issue.

from the squirrel logs I see this...

2021-05-28 20:38:10,136 [Thread-1] WARN net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData - DatabaseMetaData.getTables(...) threw an error when called with tableNamePattern = null. Trying tableNamePattern %. The error was: java.sql.SQLException: java.net.SocketTimeoutException: Read timed out
2021-05-28 20:38:40,145 [Thread-1] ERROR net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo - failed to load table names
java.sql.SQLException: java.net.SocketTimeoutException: Read timed out
at org.apache.hive.jdbc.HiveDatabaseMetaData.getTables(HiveDatabaseMetaData.java:656)
at net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData.getTables(SQLDatabaseMetaData.java:1008)
at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.privateLoadTables(SchemaInfo.java:1212)
at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.loadTables(SchemaInfo.java:412)
at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.privateLoadAll(SchemaInfo.java:303)
at net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo.initialLoad(SchemaInfo.java:179)
at net.sourceforge.squirrel_sql.client.session.Session$1.run(Session.java:261)
at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.apache.thrift.transport.TTransportException: java.net.SocketTimeoutException: Read timed out
at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:129)
at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86)
at org.apache.thrift.transport.TSaslTransport.readLength(TSaslTransport.java:376)
at org.apache.thrift.transport.TSaslTransport.readFrame(TSaslTransport.java:453)
at org.apache.thrift.transport.TSaslTransport.read(TSaslTransport.java:435)
at org.apache.thrift.transport.TSaslClientTransport.read(TSaslClientTransport.java:37)
at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86)
at org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:429)
at org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:318)
at org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:219)
at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:77)
at org.apache.hive.service.cli.thrift.TCLIService$Client.recv_GetTables(TCLIService.java:321)
at org.apache.hive.service.cli.thrift.TCLIService$Client.GetTables(TCLIService.java:308)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.apache.hive.jdbc.HiveConnection$SynchronizedHandler.invoke(HiveConnection.java:1363)
at com.sun.proxy.$Proxy5.GetTables(Unknown Source)
at org.apache.hive.jdbc.HiveDatabaseMetaData.getTables(HiveDatabaseMetaData.java:654)
... 8 more
Caused by: java.net.SocketTimeoutException: Read timed out
at java.base/java.net.SocketInputStream.socketRead0(Native Method)
at java.base/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:168)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
at java.base/java.io.BufferedInputStream.fill(BufferedInputStream.java:252)
at java.base/java.io.BufferedInputStream.read1(BufferedInputStream.java:292)
at java.base/java.io.BufferedInputStream.read(BufferedInputStream.java:351)
at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:127)
... 27 more

Re: hiveserver2 error

ryu — Tue, 01 Jun 2021 04:05:50 GMT

yeah we currently have 2 HS2 instances.

For some reason our production seems to be working fine with Squirrel.

Our dev seems to be timing out after running simple queries such as "show databases".

Beeline seems to work fine on our dev cluster.

The only difference I can think of is that our dev cluster has an external mysql server whereas the production cluster, mysql server is installed on one of the nodes.

Am I missing some squirrel drivers or something? Wondering why it is just squirrel that seems to have issues running queries against our dev hiveserver2.

Any help is much appreciated.

Thanks,

Re: hiveserver2 error

ryu — Thu, 03 Jun 2021 22:54:35 GMT

Also Squirrel seems to be connecting to the dev cluster.

It just times out when running a query such as "show databases".

If squirrel stays connected for a long time, I noticed that the query will eventually return results instead of timing out.

Per cloudera "https://docs.cloudera.com/documentation/enterprise/latest/topics/cdh_ig_hive_metastore_configure.html#concept_jsw_bnc_rp"

It says that minimum 4 dedicated cores to HS2 and 4 for hive metastore.

The server that hosts hs2 and metastore only has a total of 8 cores.

Can this be a reason for the performance issue?

Any help on this is much appreciated.

Thanks,