HikariCP and PostgreSQL

|
Programming/ Java
| Programming Java

HikariCP and PostgreSQL

Introduction

HikariCP is a simple, solid, high-performance, “zero-overhead” production ready JDBC connection pool at last. At roughly 130Kb, the library is very light.

Artifacts

Java 11

1
2
3
4
5
<dependency>
   <groupId>com.zaxxer</groupId>
   <artifactId>HikariCP</artifactId>
   <version>5.0.0</version>
</dependency>

Java 8

1
2
3
4
5
<dependency>
   <groupId>com.zaxxer</groupId>
   <artifactId>HikariCP</artifactId>
   <version>4.0.3</version>
</dependency>

About Pool Sizing

Configuring a connection pool is something that developers often get wrong. There are several, possibly counter-intuitive for some, principles that need to be understood when configuring the pool.

When we look at what the major bottlenecks for a database are, they can be summarized as three basic categories: CPU, Disk, Network. We could add Memory in there, but compared to Disk and Network there are several orders of magnitude difference in bandwidth.

Don’t over-provision your database. The formula below is provided by the PostgreSQL project as a starting point, but I believe it will be largely applicable across databases. You should test your application, i.e. simulate expected load, and try different pool settings around this starting point:

1
connections = ((core_count * 2) + effective_spindle_count)

Don’t be tricked into thinking, “SSDs are faster and therefore I can have more threads”. That is exactly 180 degrees backwards. Faster, no seeks, no rotational delays means less blocking and therefore fewer threads [closer to core count] will perform better than more threads. More threads only perform better when blocking creates opportunities for executing.

Production Ready Configuration

1
2
3
4
5
6
7
8
9
10
11
12
13
<property name="hibernate.hikari.jdbcUrl" value="<jdbcUrl>" />
<property name="hibernate.hikari.username" value="<username>" />
<property name="hibernate.hikari.password" value="<password>" />
<property name="hibernate.hikari.autoCommit" value="true" />
<property name="hibernate.hikari.minimumIdle" value="10" />
<property name="hibernate.hikari.maximumPoolSize" value="25" />
<property name="hibernate.hikari.keepaliveTime" value="60000" />
<property name="hibernate.hikari.idleTimeout" value="120000" />
<property name="hibernate.hikari.leakDetectionThreshold" value="150000" />
<property name="hibernate.hikari.maxLifeTime" value="180000" />
<property name="hibernate.hikari.connectionTimeout" value="3000" />
<property name="hibernate.hikari.validationTimeout" value="2500" />
<property name="hibernate.hikari.registerMbeans" value="true" />

Workaround for Missing Configurations

If missing the following configuration items：

1
2
3
4
<property name="hibernate.hikari.keepaliveTime" value="60000" />
<property name="hibernate.hikari.idleTimeout" value="120000" />
<property name="hibernate.hikari.leakDetectionThreshold" value="150000" />
<property name="hibernate.hikari.maxLifeTime" value="180000" />

We can workaround it by tunning in the PostgreSQL server side:

1
2
3
4
5
6
alter system set tcp_keepalives_idle=10;
alter system set tcp_keepalives_interval=2;
alter system set tcp_keepalives_count=3;
alter system set idle_in_transaction_session_timeout='10s';

SELECT pg_reload_conf();

In sessions connected via a Unix-domain socket, TCP keep-alive parameters are ignored and always reads as zero.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
$ psql postgres

postgres=# show tcp_keepalives_idle;
 tcp_keepalives_idle
---------------------
 0
(1 row)

postgres=# show tcp_keepalives_interval;
 tcp_keepalives_interval
-------------------------
 0
(1 row)

postgres=# show tcp_keepalives_count;
 tcp_keepalives_count
----------------------
 0
(1 row)

postgres=# show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout
-------------------------------------
 0
(1 row)

postgres=# alter system set tcp_keepalives_idle=10;
ALTER SYSTEM
postgres=# alter system set tcp_keepalives_interval=2;
ALTER SYSTEM
postgres=# alter system set tcp_keepalives_count=3;
ALTER SYSTEM
postgres=# alter system set idle_in_transaction_session_timeout='10s';
ALTER SYSTEM
postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout
-------------------------------------
 10s
(1 row)

postgres=# show tcp_keepalives_count;
 tcp_keepalives_count
----------------------
 0
(1 row)

postgres=# show tcp_keepalives_interval;
 tcp_keepalives_interval
-------------------------
 0
(1 row)

postgres=# show tcp_keepalives_idle;
 tcp_keepalives_idle
---------------------
 0
(1 row)

Let’s check these parameters with TCP connection:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$ psql --host=127.0.0.1

postgres=# show tcp_keepalives_idle;
 tcp_keepalives_idle
---------------------
 10
(1 row)

postgres=# show tcp_keepalives_interval;
 tcp_keepalives_interval
-------------------------
 2
(1 row)

postgres=# show tcp_keepalives_count;
 tcp_keepalives_count
----------------------
 3
(1 row)

postgres=# show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout
-------------------------------------
 10s
(1 row)

Get Rid of Staled Connections

1
2
3
4
5
6
7
8
9
10
11
12
13
select clock_timestamp(), datname, pid, state, application_name, state_change from pg_stat_activity;

SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = 'regress'
      AND pid <> pg_backend_pid()
      AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
      AND application_name !~ '(?:psql)|(?:pgAdmin.+)'
      AND state_change < current_timestamp - INTERVAL '5' MINUTE;

select statement_timestamp(), datname, pid, state, application_name, state_change from pg_stat_activity;

select clock_timestamp(), datname, pid, state, application_name, state_change from pg_stat_activity;

Reference