B4J Question ConnectionPool recommended max size

[Jmu5667]

Hi All

Does anyone know what the recommended ConnectionPool max size is. Building non UI servers apps that will have a lot of socket connections open, each connection may have the use of a MS SQL server connection from the connection pool.

Need to know the do's and dont's so I can design the threading correctly.

Regards

John.

 

[Erel]

It is really hard to say. You must test it.

 

[Jmu5667]

It is really hard to say. You must test it.

I currently have it set to 100 with 90 clients app belting in data, server doing SQL queries and sending back the result to the client apps. I ask the question so I can manage the scaliblity of the server. I may try 1000 concurrent connections.

I'll let you know how I get on.

 

[Erel]

Make sure to close the SQL connection as soon as it is not needed. This way multiple clients can use the same connection.

 

[Jmu5667]

Make sure to close the SQL connection as soon as it is not needed. This way multiple clients can use the same connection.

Ha, just doing that as we speak.

 

[Jmu5667]

So here is 500 connections. connection pool set to 1100

I will let this run overnight and see what happens. Each socket connection send some data in an each socket thread gets a pool connection, does and async request. Request event is fired, data from resultset is sent to client socket connection, resultset is closed, pool connection is closed. And this will loop on for ever. I am doing this to see how the memory is managed on the server side.

Until tomorrow then

 

[Erel]

Make sure that your database server can indeed open 1100 connections. If I remember correctly the MySQL server running this forum is set with a limit of 50 connections. The queries are fast enough that 1000+ clients can browse the forum without exceeding this limit.

 

[Jmu5667]

Make sure that your database server can indeed open 1100 connections. If I remember correctly the MySQL server running this forum is set with a limit of 50 connections. The queries are fast enough that 1000+ clients can browse the forum without exceeding this limit.

Using ms sql server. Will check server settings when i get to the office

 

[Jmu5667]

And after 15 hours ....

 

[Jmu5667]

MS SQL Server Properties

 

[Jmu5667]

After 88 hours

 

[alwaysbusy]

@Jmu5667 I know this is an old thread, but have you ever come to any conclusions/solutions? e.g. did you find out what a good connectionpool size was for e.g. a 1000 users? I'm having a similar problem now with some of my jServer apps where I suddenly hit a heap out-of-memory error. My temporary solution is now a cron job that restarts the app every few days, but I do not like this at all. My pool size is now set to 100 for the jServer WebApp and 25 for the API jServer, which I thought was enough to handle it.

 

[Pendrush]

The best solution is to check actual (live) pool connections.
For example: I have web server with 100k-150k daily unique users. 10/15 pages per user (over 1M pages open each day, almost 2M in peek days).
I have multiple connections to database from each page (5-30 per page).
My max pool size is 30, min is 2. I've never seen over 10 live connection (actual pool connections).
Also pool connections close it self every hour, and new connections is created. That prevent any memory leak which can happen with connection.
That is my configuration not with B4J app, but Ktor/Kotlin using HikariCP with MariaDB, and I just want to explain my pool configuration.

 

[alwaysbusy]

The best solution is to check actual (live) pool connections.
For example: I have web server with 100k-150k daily unique users. 10/15 pages per user (over 1M pages open each day, almost 2M in peek days).
I have multiple connections to database from each page (5-30 per page).
My max pool size is 30, min is 2. I've never seen over 10 live connection (actual pool connections).
Also pool connections close it self every hour, and new connections is created. That prevent any memory leak which can happen with connection.
That is my configuration not with B4J app, but Ktor/Kotlin using HikariCP with MariaDB, and I just want to explain my pool configuration.

Thanks! I'm also using HikariCP (v3.3.1). Which version are you using? I'm asking because some versions seem to have a bug where dead connections are not removed from the pool.

These are my settings B4J. Recommanded for mySQL, but maybe I'm missing something. Is there a special one you need to set to close the pool connections every hour like you do?

    pool.Initialize("com.mysql.jdbc.Driver", jdbcUrl, login, password)
    Dim jocp As JavaObject = pool
    jocp.RunMethod("setMaximumPoolSize", Array(poolSize))
    jocp.RunMethod("addDataSourceProperty", Array("cachePrepStmts", True))
    jocp.RunMethod("addDataSourceProperty", Array("prepStmtCacheSize", 250))
    jocp.RunMethod("addDataSourceProperty", Array("prepStmtCacheSqlLimit", 2048))
    jocp.RunMethod("addDataSourceProperty", Array("useServerPrepStmts", True))
    jocp.RunMethod("addDataSourceProperty", Array("useLocalSessionState", True))
    jocp.RunMethod("addDataSourceProperty", Array("useLocalTransactionState", True))
    jocp.RunMethod("addDataSourceProperty", Array("rewriteBatchedStatements", True))
    jocp.RunMethod("addDataSourceProperty", Array("cacheResultSetMetadata", True))
    jocp.RunMethod("addDataSourceProperty", Array("cacheServerConfiguration", True))
    jocp.RunMethod("addDataSourceProperty", Array("elideSetAutoCommits", True))
    jocp.RunMethod("addDataSourceProperty", Array("maintainTimeStats", True))
    Dim thres As Long = 60000
    jocp.RunMethod("setLeakDetectionThreshold", Array(thres))

Alwaysbusy

 

[Jmu5667]

@Jmu5667 I know this is an old thread, but have you ever come to any conclusions/solutions? e.g. did you find out what a good connectionpool size was for e.g. a 1000 users? I'm having a similar problem now with some of my jServer apps where I suddenly hit a heap out-of-memory error. My temporary solution is now a cron job that restarts the app every few days, but I do not like this at all. My pool size is now set to 100 for the jServer WebApp and 25 for the API jServer, which I thought was enough to handle it.

I run this code every minute, use it on all my b4j server apps. In relation to poolsize(SQL), grab a pool connection, do work, release asap. This works for me.

Sub process_gc
    
    Dim jo As JavaObject = Me
    
    jo.RunMethod("do_garbage_collection",Null)
    
End Sub


#if java

    import java.util.*;    
     
    public void do_garbage_collection()    {            
        Runtime rs = Runtime.getRuntime();
        rs.gc();
      }
        
    public long getAllocatedFree() {
        Runtime rs = Runtime.getRuntime();
        return rs.freeMemory();
    }
    
    public long getAllocatedTotal() {
        Runtime rs = Runtime.getRuntime();
        return rs.totalMemory();
    }
    
    public long getUsed() {
        Runtime rs = Runtime.getRuntime();
        return rs.totalMemory() - rs.freeMemory();
    }
    
    public long getTotal() {
        Runtime rs = Runtime.getRuntime();
        return rs.maxMemory();
    }
    
#End If

 

[alwaysbusy]

I run this code every minute

I'll try that! This would be in a BackgroundWorker I would assume?

 

[Jmu5667]

I'll try that! This would be in a BackgroundWorker I would assume?

You assume correctly my friend

 

[Pendrush]

Thanks! I'm also using HikariCP (v3.3.1). Which version are you using?

I'm using HikariCP v5.0.0 and MariaDB Java Client v2.7.4.

maxLifetime
This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool. We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. The minimum allowed value is 30000ms (30 seconds). Default: 1800000 (30 minutes)

My curent config:

object HikariDs {
    private val config = HikariConfig()
    init {
        config.driverClassName = "org.mariadb.jdbc.Driver"
        config.jdbcUrl = "jdbc:mariadb://localhost:3306/MY_DATABASE_NAME?useSSL=false"
        config.username = "MY_USERNAME"
        config.password = "MY_PASSWORD"
        config.minimumIdle = 2
        config.maximumPoolSize = 30
        config.addDataSourceProperty("cachePrepStmts", "true")
        config.addDataSourceProperty("prepStmtCacheSize", "250")
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048")
        config.maxLifetime = 3600000
    }
    val dataSource = HikariDataSource(config)
}