Friday, February 6, 2015

How to avoid Impala JDBC connection leaking issue

Env:

Impala 1.4

Symptom:

When using JAVA code or Jmeter to start multi-thread queries to Impala, once any query or session fails due to some reason, the connections and sessions count shown in Impala web GUI may keep increasing.
For example:
You only start 200 concurrent threads to run queries in a loop, if one or more queries fail due to " Memory limit exceeded" error, you may found "384 queries in flight" from Impala web GUI. And many of the connections are in "EXCEPTION" state.
If you have setup admission control, from time on, more and more incoming sessions may stuck in the queue, and finally they will timeout with below error messages:
"Caused by: java.sql.SQLException: Admission for query exceeded timeout 60000ms. Queued reason: number of running queries 300 is over limit 300" 

Root Cause:

The JAVA code does not clean the connection properly. Most possible reason is there is no "finally" code block to close the connection.
Here is a bad example:
Connection con = null;
try {
 Class.forName(jdbcDriverName);
 con = DriverManager.getConnection(connectionUrl);
 Statement stmt = con.createStatement();
 ResultSet rs = stmt.executeQuery(sqlStatement);
 while (rs.next()) {
 }
 con.close();
} catch (SQLException e) {
 e.printStackTrace();
} catch (Exception e) {
 e.printStackTrace();
} 
Using above logic, if any exception happens before "con.close()", the connection will be marked as "EXCEPTION" state in Impala and may take a long time to clean, expectially when impalad process is busy.

Solution:

The fix is to use "finally" block in JAVA code to make sure we close the connection under any situations.
Here is a good example:
Connection con = null;
try {
 Class.forName(jdbcDriverName);
 con = DriverManager.getConnection(connectionUrl);
 Statement stmt = con.createStatement();
 ResultSet rs = stmt.executeQuery(sqlStatement);
 while (rs.next()) {
 }
} catch (SQLException e) {
 e.printStackTrace();
} catch (Exception e) {
 e.printStackTrace();
} finally {
 try {
  con.close();
 } catch (Exception e) {}
 }



No comments:

Post a Comment

Popular Posts