Splunk Completes Acquisition of Plumbr Learn more

Slow JDBC Connection Acquisition

Connecting to any remote system is expensive. JDBC connections are no different, meaning that each time the JDBC connection is created, the application spends time waiting for the connection to be established. Pooling the connections will alleviate but not mitigate the problem.
Allow Plumbr to pin point the exact points in the code which are slow.

How Plumbr will help you

The screenshot above is taken by Plumbr and is exposing the root cause where the user transaction #81087165 trying to access the ThresholdController.setThreshold() service was blocked while waiting for a JDBC connection acquisition. The extract above has exposed the wait of two minutes and 47 seconds.

Plumbr has also exposed that during the last seven days, acquiring connections to the same datasource (jdbc:mysql://backend­node­3.internal:3306/production) has been problematic in 76 occasions.

Plumbr also locates the exact line in source code where the wait time occurred, as seen from the following call stack captured: 

eu.plumbr.portal.configuration.LoggingDataSource.getConnection():26
org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection():111
...
org.springframework.jdbc.core.JdbcTemplate.queryForList():849
eu.plumbr.portal.threshold.ThresholdDao.getServiceThresholds():32

Doing so, Plumbr has expose that the datasource acquisition is carried out via LoggingDataSource.getConnection() method, from line 26 through ThresholdDao.getServiceThresholds() line 32 in the business code.

The Solution

Solving the root cause usually involves either of the following solutions:

  • Introducing connection pooling in situations when no connection pools are used. Pooled connections are left connected to the database and can be shared across the different components needing database access. This approach allows to share the cost of connection establishment across all transactions requiring access to the database.
  • Initializing the connections in the pool during application startup. Whenever the pool is not (fully) initialized, first hits to it are equivalent or worse than in situations where there is no connection pool present.
  • Optimizing or removing test queries from the pool to check whether the connection has not become stale. Often the pool implementations test the connections before handing it off to the requesting thread. In certain conditions the test queries can end up taking longer than the actual query.
  • Extending the size of connections in the pool. In cases where all the available connections from the pool are already used, you can consider whether or not adding more connections to the pool can be a solution.
  • Getting rid of connection leaks. If connection is not closed, the connection pool does not know that the connection is no longer being used by the borrower thread. In such case, the connection pool will consider this connection to still be in use.
  • Last and most expensive solution requires optimizing the queries being used the most by the connections. If the threads get responses from connections faster, connections are faster returned to the pool, thus increasing the ratio of available connections in the pool.

In our example above we got away by increasing the pool capacity by 10% and solved the issue for now.