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?
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:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With