Erick Mejía
Erick Mejía

Reputation: 1

Connection Pool turns slow at the first call after an inactivity period(15 Mins)

I've set a connection pool with Java and MySQL, also with SQL Server, using 'org.apache.commons.dbcp2'. once the connection pool is connected to the server everything works perfect, the pool responses are very fast, when I stop using my APP but I keep using my Mac and return to use my APP (after 10mins for example) the pool still works perfect, the fact is that when I stop using my APP and my Mac, my Mac entries in suspended mode, after 15-20mins for example, when I wake up my Mac from suspended mode and try to use my APP, the pool lasts a lot of time to respond (2Minutes), once the pool has last that time to respond it throws a message like this:

The last packet sent successfully to the server was 1,100,000 milliseconds ago. The last packet received successfully from the server was 1,950,000 milliseconds ago.

Once it throws that message and try to make a consult to the server it says that operations are not allowed after a connection is closed, then I have to restart my APP to make it work again. I think this happens because of the inactivity of my Mac, I imagine the Connection Pool detects that the device is unused, suspended mode, then it closes the connection. I'd like the Connection Pool never closes the connection to the server unless I completely quit my APP.

I've tried a lot of methods to solve this problem, I've tried to set the pool configuration like this:

ConectionPool() {
//        JOptionPane.showMessageDialog(null, "Entra a Constructor");
        basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//also tried with MYSQL 'com.mysql.jdbc.Driver'
        basicDataSource.setUsername(user);
        basicDataSource.setPassword(pass);
        basicDataSource.setUrl(url);

        basicDataSource.setMinIdle(10);
        basicDataSource.setMaxIdle(20);
        basicDataSource.setMaxTotal(50);
        basicDataSource.setMaxWaitMillis(10000);
        basicDataSource.setDefaultAutoCommit(true);
        basicDataSource.setMaxConnLifetimeMillis(-1);
        basicDataSource.setAutoCommitOnReturn(true);
        basicDataSource.setCacheState(true);
        basicDataSource.setDefaultAutoCommit(true);
        basicDataSource.setFastFailValidation(true);
        basicDataSource.setMaxOpenPreparedStatements(-1);
        basicDataSource.setValidationQueryTimeout(10000);
        basicDataSource.setRemoveAbandonedTimeout(-1);
        basicDataSource.setAbandonedUsageTracking(true);
        basicDataSource.setFastFailValidation(true);
    }

I USE SINGLETON.

I've tried different combinations, tried different libraries. I've tried to add this to the connection string:

autoReconnect=true&failOverReadOnly=false&maxReconnects=10&socketKeepAlive=true

I also tried this:

private final String url = "jdbc:mysql://" + ip + ":" + puerto + "/" + db + "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useUnicode=yes&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&socketKeepAlive=true";

but no method I've used has solved my problem.

Please Help.

Upvotes: 0

Views: 68

Answers (0)

Related Questions