Hikari and Postgres (number of connections in pgAdmin's Server Status)

[PawelP76]

This is my first post so I'd like to say thank you for your great database pool.

Before I've been using tomcat-dbcp set up with defaults and found that sometimes there invalid connection returned resulting with exception. Probably the connections hadn't been used for a while and they were closed/destroyed/invalidated (don't know what was the case) and the pool returned invalid connection.
It's not the case with Hikari, I'm really glad to see that.

I'm testing HikariCP in Tomcat 7 set as a server wide resource in conf/context.xml and it works fine. However I'm curious why there are so many connections open.
On my development environment (Eclipse) I set this resource:
    <Resource name="jdbc/DataManagerDS" auth="Container"
              type="javax.sql.DataSource"
           driverClassName="org.postgresql.Driver"
          factory="com.zaxxer.hikari.HikariJNDIFactory"
          jdbcUrl="jdbcurl"
          username="user"
          password="pass"
          maximumPoolSize="1"
          idleTimeout="300000"
          connectionTimeout="300000"
          />
The most important one is maximumPoolSize. It's set to 1 as I don't want to create too many connections when writing and testing the code.
What surprises me is the number of backend processes running in the postgres server. It looks like it's maximumPoolSize (MPS) x 3
When I set MPS to 2 I get 6, if I set MPS to 5 I get 15 connections. All idle but that's irrelevant I think.

Is it only postgres specific behavior? Do other drivers (Oracle/MySql) create specified number of connections? Don't have access to them so can't check myself.

Thanks
Paweł

[Brett Wooldridge]

Hi Pawel,

Thanks for using HikariCP.  Try these things:

* Make sure you are running the latest (2.3.2)

* Enable DEBUG level logging for HikariCP (com.zaxxer.hikari.*)

You should see some logs similar to these approximately every 30 seconds:

[Hikari Housekeeping Timer (pool HikariPool-4)] DEBUG HikariPool - Before cleanup pool stats HikariPool-4 (total=7, inUse=7, avail=0, waiting=0)

[Hikari Housekeeping Timer (pool HikariPool-4)] DEBUG HikariPool - After cleanup pool stats HikariPool-4 (total=7, inUse=7, avail=0, waiting=0)

[HikariCP connection filler (pool HikariPool-4)] DEBUG HikariPool - After fill pool stats HikariPool-4 (total=10, inUse=7, avail=3, waiting=0)

And occassionally:

[HikariCP connection closer (pool HikariPool-4)] DEBUG com.zaxxer.hikari.pool.PoolUtilities - Closing connection com.mysql.jdbc.optional.ConnectionImpl@53d16850

Try to see how these numbers compare to the database reported connections.

I'm not sure if it is useful or not, but the PostgreSQL driver also support a loglevel property that takes an int value (see https://jdbc.postgresql.org/documentation/80/connect.html).

You can also get PostgreSQL to show the current sessions:

SELECT * FROM pg_stat_activity;

Let us know what you find.

[PawelP76]

Found the problem. Misconfiguration in spring web app. Security context xml and spring xml config files were both loading the same xml with DataSource definition. Therefore two DataSource instances were created (even though it's singleton, but loaded by different class loaders I guess), that's why I had twice as much connections as there should be.

Sorry for the trouble.
Pawel

W dniu środa, 4 lutego 2015 15:03:39 UTC+1 użytkownik Brett Wooldridge napisał:

...