-
Notifications
You must be signed in to change notification settings - Fork 3k
FAQ
Q: PostgreSQL throws an exception stating setQueryTimeout(int) is not yet implemented, how do I fix it?
Q: I am getting a "com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure" exception logged in the isConnectionAlive() method of HikariPool in my logs, what is happening?
Q: I am getting strange transaction isolation behavior after changing it, what is happening?
Q: How do I properly shutdown the HikariCP DataSource?
Q: I benchmarked Other Connection Pool X and HikariCP is coming out slower, what is happening?
Q: How to I properly enable PreparedStatement caching for PostgreSQL?
Q: How to I encrypt database passwords with HikariCP?
Q: What about "pool-locking" (deadlocking)?
Q: Can I change the username/password or other pool properties at runtime?
Q: PostgreSQL throws an exception stating setQueryTimeout(int) is not yet implemented, how do I fix it?
A: Don't use the connectionTestQuery, instead allow HikariCP to use the Connection.isValid() method (the default).
Q: I am getting a "com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure" exception logged in the isConnectionAlive() method of HikariPool in my logs, what is happening?
A: Configure your HikariCP idleTimeout and maxLifeTime settings to be one minute less than the
wait_timeout of MySQL. See this article about setting MySQL timeout values.
A: Always use the JDBC Connection.setTransactionIsolation() method rather than executing SQL to change the isolation level. HikariCP must reset the isolation level for connections returned to the pool, but only does so if it detects that the isolation level has changed. Using SQL rather than the JDBC API to manipulate the isolation level prevents HikariCP from being able to detect the change, and therefore it will not reset the isolation level. This can
cause the isolation level set by one consumer to "bleed" over to another consumer of the pool.
A: Shutting down the DataSource is especially important in web application containers where applications can be hot-deployed. Call the shutdown() or close() method on the HikariDataSource instance. You can typically configure Spring or other IOC containers to specify a "destroy" method.
A: Make sure you are comparing apples-to-apples. While HikariCP strives to be high-performing it also strives to be highly-reliabile. Many other pools' default settings are geared for performance over reliability. Additionally, unless you configure the underlying JDBC driver correctly, you are likely to miss out on performance. For example, other pools include a prepared statement cache of their own, while HikariCP relies on the caching ability of the underlying JDBC driver. Unless you enable this feature in the driver, you are losing performance. As an example, see the MySQL Configuration Tips.
Not to pick on any one pool, but this topic came up in the forums Re: C3P0 vs. HikariCP. By default C3P0 errs on the side of performance by not testing connections before handing them to you. Instead they are checked by a background thread. While this will certainly boost performance, if your backend database is restarted or a network interruption occurs, your application could be handed bad connections for as long as it takes the background thread to test them all (30 seconds by default). For an apples-to-apples comparison, you would need to set the C3P0 testConnectionOnCheckout property to true.
A: The PostgreSQL JDBC driver from the PostgreSQL project, starting with version 9.4-1202 supports PreparedStatement caching. Please see the documentation provided by the PostgreSQL project.
Another alternative is the new Java NIO-based driver, pgjdbc-ng driver.
A: HikarCP does not support built-in decryption/encryption of passwords. We recommend using the jasypt library. It additionally includes many useful Spring components including decrypting versions of:
- PropertyPlaceholderConfigurer
- PropertyOverrideConfigurer
- PreferencesPlaceholderConfigurer
A: The prospect of "pool-locking" has been raised with respect to single actors that acquire many connections. This is largely an application-level issue. Yes, increasing the pool size can alleviate lockups in these scenarios, but we would urge you to examine first what can be done at the application level.
The calculation of pool size in order to avoid deadlock is a fairly simple resource allocation formula:
pool size = Tn x (Cm - 1) + 1
Where Tn is the maximum number of threads, and Cm is the maximum number of simultaneous connections held by a single thread.
For example, imagine three threads (Tn=3), each of which requires four connections to perform some task (Cm=4). The pool size required to ensure that deadlock is never possible is:
pool size = 3 x (4 - 1) + 1 = 10
Another example, you have a maximum of eight threads (Tn=8), each of which requires three connections to perform some task (Cm=3). The pool size required to ensure that deadlock is never possible is:
pool size = 8 x (3 - 1) + 1 = 17
👉 This is not necessarily the optimal pool size, but the minimum required to avoid deadlock.
A: Yes. The properties that are permissible to change at runtime are exposed on the HikariConfigMXBean. The HikariConfigMXBean is available via JMX if registerMbeans is enabled, or can be directly obtained from the HikariDataSource.