HiveServer2 is hung or unresponsive due to metastore db response

[#1]

SYMPTOM:

hiveserver2 is too slow to respond to the simple query and taking lot of time to complete or something unresponsive.

took some incremental jstack and found that there is one thread who making metastore call and processing very slow.

Thread 24233: (state = IN_NATIVE)
  - java.net.SocketInputStream.socketRead0(java.io.FileDescriptor, byte[], int, int, int) @bci=0 (Compiled frame; information may be imprecise)
  - java.net.SocketInputStream.socketRead(java.io.FileDescriptor, byte[], int, int, int) @bci=8, line=116 (Compiled frame)
  - java.net.SocketInputStream.read(byte[], int, int, int) @bci=79, line=170 (Compiled frame)
  - java.net.SocketInputStream.read(byte[], int, int) @bci=11, line=141 (Compiled frame)
  - oracle.net.ns.Packet.receive() @bci=157, line=311 (Compiled frame)
  - oracle.net.ns.DataPacket.receive() @bci=1, line=105 (Compiled frame)
  - oracle.net.ns.NetInputStream.getNextPacket() @bci=48, line=305 (Compiled frame)
  - oracle.net.ns.NetInputStream.read(byte[], int, int) @bci=33, line=249 (Compiled frame)
  - oracle.net.ns.NetInputStream.read(byte[]) @bci=5, line=171 (Compiled frame)
  - oracle.net.ns.NetInputStream.read() @bci=5, line=89 (Compiled frame)
  - oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket() @bci=11, line=123 (Compiled frame)
  - oracle.jdbc.driver.T4CSocketInputStreamWrapper.read() @bci=55, line=84 (Compiled frame)
  - oracle.jdbc.driver.T4CMAREngineStream.unmarshalUB1() @bci=6, line=429 (Compiled frame)
  - oracle.jdbc.driver.T4CTTIfun.receive() @bci=16, line=397 (Compiled frame)
  - oracle.jdbc.driver.T4CTTIfun.doRPC() @bci=116, line=257 (Compiled frame)
  - oracle.jdbc.driver.T4C8Oall.doOALL(boolean, boolean, boolean, boolean, boolean, oracle.jdbc.internal.OracleStatement$SqlKind, int, byte[], int, oracle.jdbc.driver.Accessor[], int, oracle.jdbc.driver.  Accessor[], int, byte[], char[], short[], int, oracle.jdbc.driver.DBConversion, byte[], java.io.InputStream[][], byte[][][], oracle.jdbc.oracore.OracleTypeADT[][], oracle.jdbc.driver.OracleStatement,     byte[], char[], short[], oracle.jdbc.driver.T4CTTIoac[], int[], int[], int[], oracle.jdbc.driver.NTFDCNRegistration, oracle.jdbc.driver.ByteArray, long[], int[], boolean) @bci=903, line=587 (Compiled     frame)
  - oracle.jdbc.driver.T4CPreparedStatement.doOall8(boolean, boolean, boolean, boolean, boolean, int) @bci=780, line=225 (Compiled frame)
  - oracle.jdbc.driver.T4CPreparedStatement.doOall8(boolean, boolean, boolean, boolean, boolean) @bci=23, line=53 (Compiled frame)
  - oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe() @bci=37, line=774 (Compiled frame)
  - oracle.jdbc.driver.OracleStatement.executeMaybeDescribe() @bci=106, line=925 (Compiled frame)
  - oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout() @bci=250, line=1111 (Compiled frame)
  - oracle.jdbc.driver.OraclePreparedStatement.executeInternal() @bci=145, line=4798 (Compiled frame)
  - oracle.jdbc.driver.OraclePreparedStatement.executeQuery() @bci=18, line=4845 (Compiled frame)
  - oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery() @bci=4, line=1501 (Compiled frame)
  - com.jolbox.bonecp.PreparedStatementHandle.executeQuery() @bci=68, line=174 (Compiled frame)
  - org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeQuery() @bci=4, line=375 (Compiled frame)
  - org.datanucleus.store.rdbms.SQLController.executeStatementQuery(org.datanucleus.ExecutionContext, org.datanucleus.store.connection.ManagedConnection, java.lang.String, java.sql.PreparedStatement) @    bci=120, line=552 (Compiled frame)
  - org.datanucleus.store.rdbms.scostore.JoinListStore.listIterator(org.datanucleus.state.ObjectProvider, int, int) @bci=329, line=770 (Compiled frame)
  - org.datanucleus.store.rdbms.scostore.AbstractListStore.listIterator(org.datanucleus.state.ObjectProvider) @bci=4, line=93 (Compiled frame)
  - org.datanucleus.store.rdbms.scostore.AbstractListStore.iterator(org.datanucleus.state.ObjectProvider) @bci=2, line=83 (Compiled frame)
  - org.datanucleus.store.types.wrappers.backed.List.loadFromStore() @bci=77, line=264 (Compiled frame)
  - org.datanucleus.store.types.wrappers.backed.List.iterator() @bci=8, line=492 (Compiled frame)
  - org.apache.hadoop.hive.metastore.ObjectStore.convertToFieldSchemas(java.util.List) @bci=21, line=1199 (Compiled frame)
  - org.apache.hadoop.hive.metastore.ObjectStore.convertToStorageDescriptor(org.apache.hadoop.hive.metastore.model.MStorageDescriptor, boolean) @bci=39, line=1266 (Compiled frame)
  - org.apache.hadoop.hive.metastore.ObjectStore.convertToStorageDescriptor(org.apache.hadoop.hive.metastore.model.MStorageDescriptor) @bci=3, line=1281 (Compiled frame)

ROOT CAUSE:

After further investigation, we found that metastore db was running outside of the cluster and shared by other applications.when there was a load on metastore database then it is taking too long to respond to the query. since this is happening in the compilation stage of the query which is single threaded hence the other incoming query which lands on Hiveserver2 will wait until this query is done with the compilation.

WORKAROUND:

NA

RESOLUTION:

Run Metastore database inside the cluster and don't share it with the other applications if you are running huge workload on HiveServer2. please check network between HiveServer2 node and MySQL node to see any bottleneck.