clock

Timeout Configuration in HikariCP, DB2 and MySQL

Motivation

Michael T. Nygard warns in “Release it” [1] about the lack of timeouts caused by misconfigured database drivers and connection pools. He argues that if a connection pool gets exhausted when none of its calls return than all others threads get blocked as soon as they require a connection too. His advice is:

Scrutinize Resource Pools
Safe resource pools always limit the time a thread can wait to check out a resource.
(Michael T. Nygard, “Release it”, P. 50)

So I wondered, whether the connection pool HikariCP as well as the JDBC-driver of IBM DB2 and MySQL already ships with default timeouts (and how to adjust them).

HikariCP v2.4.1

HikariCP offers the following parameters (see “HikariCP Configuration”):

  • connectionTimeout
    Default: 30000ms
  • validationTimeout
    Default: 5000ms

IBM DB2 JDBC Driver v3.65.131 / v4.15.134

The IBM DB2 JDBC Driver offers the following parameters (see “Common IBM Data Server Driver for JDBC and SQLJ properties for all supported database products” and “DB2BaseDataSource class”):

  • blockingReadConnectionTimeout
    Default: 0 (no timeout).
  • commandTimeout
    Default: 0 (no timeout). This property was introduced in v3.64 and v4.14.
  • connectionTimeout
    Default: 0 (no timeout). This property was introduced in v3.64 and v4.14.
  • loginTimeout
    Default: 0 (timeout is the default system timeout).
  • enableTimeoutForCursors (only LUW)
    Default: 1 (controlled by the commandTimeout and queryTimeoutInterruptProcessingMode properties)

There are also several timeout related properties:

  • queryTimeoutInterruptProcessingMode
  • timerLevelForQueryTimeOut

Note that I concentrated on z/OS and LUW environments without rerouting nor clustering (so I skipped z/OS data sharing group, DB2 pureScale instance and IBM Informix high availability cluster). For those there are numerous additional parameters to set.

Also note that com.ibm.db2.jcc.DB2DataSource already contains a connection pool. So if you’re packing this datasource into the connection pool of your choice you will get a connection pool inside a connection pool. Which is … let’s say … inappropriate. Use com.ibm.db2.jcc.DB2SimpleDataSource instead.

MySQL Connector/J v5.1.36

MySQL Connector/J offers the following parameters (see “Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J”):

  • connectTimeout
    Default: 0 (no timeout)
  • socketTimeout
    Default: 0 (no timeout)
  • netTimeoutForStreamingResults
    Default: 600s.

There are also some timeout related properties:

  • interactiveClient
  • queryTimeoutKillsConnection

Note that I once again skipped properties for load balancing environments.

Conclusion (<tl:dr/>)

HikariCP already ships with default timeouts (but you can and should adjust them to your needs). IBM DB2 JDBC Driver and MySQL Connector/J are timeout-less by default, so to satisfy Nygard’s “Scrutinize Resource Pools” advice you have to turn them on explicitly.

Reference

  1. [1] Michael T. Nygard. 2007. “Release It!: Design and Deploy Production-Ready Software”. O’Reilly UK Ltd.

Rolf Engelhard

 

Leave a Reply

Required fields are marked *.