CODEWITHSUNDEEP
javadatabasepostgresqljdbchikaricp
GouramEats
GouramEats

Reputation: 61

Connection pooling is not reusing idle connections

I am pretty new to HikariCP, and I am running into an issue. I have a method to get a connection from the database using a HikariDataSource:

public Connection getConnection() {
        try {
            return dataSource.getConnection();
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            return null;
        }
    }

However, when I check pgAdmin (this is a PostgreSQL db btw), I see that instead of using idle connections, it creates a bunch of new connections. How can I fix this?

Below are some images of the connections table after I send a query to the db. Initial: My initial connection_activity

After a query: My connection_activity after a query

You can see that in the first image there are 19 connections, with 18 of them being idle. But, after a query, there are a bunch of new connections, with most of them being idle. Did I configure Hikari wrong? Here is my configuration:

config.setJdbcUrl("jdbc:postgresql://localhost/Postgres 13/BGWW Bot");
        config.setUsername();
        config.setPassword();
        config.addDataSourceProperty("max_connections", "120");
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.setDataSource(ds);
        config.setMaximumPoolSize(120);
        config.setMinimumIdle(15);

And finally, here is one of my methods that uses a connection from getConnection()

public float[] getReqs()
    {
        try (Connection conn = getConnection())
        {
            String getReqs = "SELECT id, swstars, swwins, swkdr FROM reqs;";

            PreparedStatement stmt = conn.prepareStatement(getReqs);
            ResultSet rs = stmt.executeQuery();

            while(rs.next())
            {
                if(rs.getInt(1) == 1)
                {
                    float[] array = new float[]{rs.getInt(2), rs.getInt(3), rs.getFloat(4)};
                    conn.close();
                    return array;
                }
            }

            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
        return null;
    }

Upvotes: 3

Views: 3864

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21043

You configuration says that the number of the connections in the pool should be between 15 and 120.

You may with this configuration also observe more that 15 connections in the idle state.

The property idleTimeout controls the retirement of a connection if it is idle for such time (default - 10 minutes).

So in other words, only if your application makes no access for 10 minutes (plus 30 seconds as the connections are checked periodically with this period), you should expect to see the pool on the minimal size.

More information see here

Upvotes: 4

Related Questions