Java – database pool with PGPoolingDataSource

connection-poolingjavajdbcpostgresql

I'm having problems when I create a database pool with PGPoolingDataSource class, after a while the pool goes down when many users are working and shows no errors

The code of the class that creates the pool is:

public class PgConexion {
    private static PgConexion _instancia = new PgConexion(); // instancia de la clase
    private Configuracion config;
    private PGPoolingDataSource source;

    /**
     * instancia la clase y carga las opciones de configuracion
     */
    public PgConexion() {
        final URL archivo = Constantes.RUTA_CONFIG;

        if(archivo != null){
            config = new Configuracion(archivo);
        }
    }

    /**
     * regresa la instancia del pool de conexiones
     * @return
     */
    public static PgConexion getInstance() {
        return _instancia;
    }

    /**
     * crear la conexion la conexion
     * @return
     * @throws SQLException
     */
    public void crearConexion() throws SQLException{
        source = new PGPoolingDataSource(); 

        // configuracion del pool
        source.setDataSourceName("Logistica");
        source.setServerName(config.get("servidor_sql"));
        source.setPortNumber(Integer.parseInt(config.get("puerto_sql")));
        source.setDatabaseName(config.get("bd_sql"));
        source.setUser(config.get("usuario_sql"));
        source.setPassword(config.get("contrasena_sql"));
        source.setMaxConnections(30);
    }

    /**
     * devuelve la conecion a utilizar
     * @return
     * @throws SQLException
     */
    public Connection nuevaConexion() throws SQLException{
        if(source == null){
            crearConexion();
        }

        // genero la conexion de la lista del pool
        return source.getConnection();
    }

    /**
     * Cierra las conexiones y libera los recursos
     */
    public void cerrarConexion(){
        source.close();
    }
}

How do I fix this?

Best Answer

It is not a very good idea to use PGPoolingDataSource, as JDBC documentation explains.

The base problem is that the call for getConnection() will be blocked until a connection is closed when the limit of connections has been reached.

You've set the value 30 as the max amount of concurrent conections, so if a 31st is intended to be opened, it will cause a block on the Thread making the call.

Possible solutions:

  • Increase maxConnections, if you are certain about the real amount of concurrent connections upper limit. You should also check the server-side connection limit in postgresql.conf.
  • Use PGSimpleDataSource. Depending on the type of application, not using a pool of connections (thus creating the connection each time) will not be a problem.
  • If you really need a pool of connections, simply implement your own at Java level.

EDIT: You can check the amount of opened connections by simply running:

SELECT * FROM pg_stat_activity

Each row is a connection (including the one from pgAdmin and the query analyser). If you are certain that the number of connections should not raise up to the upper limit (but nevertheless it does), maybe you are having some sort of connection leak problem.

Related Topic