Connection Pooling not working as expected .maxpool and settimeout having issue
jptech8 opened this issue · 5 comments
I could see below works well but when i set setMaxPoolSize and connectTimeout , it is not working as expect . I can see poo.init() manages all conenction request but maxpool not managing the request based o load . Could you help and have connection pooling working and establish conenction when it has high and connection close managed properly without explicity mentioning conn.close() ?
const ibmdb = require('ibm_db');
const connectionString = DATABASE=your_database;HOSTNAME=your_hostname;PORT=your_port;PROTOCOL=TCPIP;UID=your_username;PWD=your_password;
const pool = new ibmdb.Pool();
pool.init(2 connectionString)
pool.setMaxPoolSize(5, connectionString)
pool.setConnectTimeout(30, connectionString)
const db2 =()=>{
pool.open(connectionString, function( err, conn) {
return new Promise(async(resolve,reject)=>{
try {
if(err){console.log(error)}
resolve(conn)
conn.close()
}catch(error){console.log(error)}
})
}
db2().then((conn)=>{conn.query(SELECT * FROM TABLE)})
@jptech8 setMaxPoolSize is just to restrict number of active connections with Database server. It do not allow more than setMaxPoolSize concurrent connections with db2server. If all connections in pool is occupied and active connection has reached setMaxPoolSize, ibm_db will not allow any new connection and application need to wait till a connection get available by conn.close().
conn.close() - is the mechanishm to inform ibm_db that application is done with this connection and it should be made available for next connection request. If conn.close() is not called, application may execute another SQL using this conn.
pool.init(N, cn) - just initializes the pool with N number of active connections synchronously, so that next request of poo.open() can get connection from pool immediately. If pool.init(5) has initialized 5 connections, calling pool.open() 5 times, without any conn.close() will get 5 connections from pool immediately but pool.open() for 6th connection will have to go on Db2 server, establish a fresh connection and give to the application. It will work if 6 <= maxpoolsize. If maxpoolsize is also 5, then pool.open() will wait till connection timeout to get any connection available by conn.close().
Now, could you please clarify - what do you mean my maxpool not managing the request based on load? - load management is job of db2server, not client. Db2 server has Work Load Management (WLM) feature for that.
How connection close can get managed automatically without explicitly mentioning conn.close() ? - How, client can know this connection is no longer required by application without any signal to it? conn.close() gives that signal.
As I have understood from your post, I think you can use pool = new ibmdb.Pool( {idleTimeout : 60, autoCleanIdle : true} ); to manage the connections in pool.
When autoCleanIdle is set to true, ibm_db will check which connections are lying unused in pool for more than idleTimeout seconds - close all such connections and remove from pool. This way unused connections from pool get cleaned time to time and number of active connections management can be achieved. I assume, you want to refer "connection management" by mention of "load management".
I think use of idleTimeout and autoCleanIdle in your application along with reasonable value of maxPoolSize (must be < 128) should solve your purpose. Automatic cleaning of idle connections get handled by pool.cleanUp internally based on values of idleTimeout and autoCleanIdle. Please try it.
Thanks.
@bimalkjha , Thanks for the headsup .
Let me explain how i did testing Mssql and IBMdb2 might give an idea about load test .
MSSQL with connection pool {min:1,max:10}
#I did load test and able to see min 1 of connection established and increased request load to 5000 concurrent request .
Result :- Could see result all 10 (max) established and released close connection back to pool with nestat tools command in my pods nestat -an
IBMDB2 with connection pool {poolSize:3,maxPoolSize:10}
#I did load test and able to see min 3 of connection established as PoolSize is locked as 3 and increased request load to 5000 concurrent request
Result :- Could see still same poolSize 3 established and no increase of established connection not more than 3 with same netstat tools command in my pods nestat -an
By this way , i assume IBMDB2 eventhough we set maxPoolSize to 10 , it is not gradually increased its established or TIMEWAIT to 10 . It still uses PoolSize =3 established connction unlike MSSQL uses connection pooling .
i used {idleTimeout autoCleanIdle} as suggested . Anyother way to check how IBMDB2 library could help to test maxpoolsize is used with 5000 concurrent request ? Advice on below settings
Current pool settings const Db2Config = {
//other connection details
maxPoolSize:10
idleTimeout:60,
autoCleanIdle:true,
connectTimeout:60
}
const pool = new ibmdb.pool(Db2Config)
pool.init(3,Db2Config)
@jptech8 If we check this code of Pool() constructor, we can see that only idleTimeout, autoCleanIdle, maxPoolSize, connectTimeout and systemNaming keywrods are read from the options object passed to new ibmdb.Pool({}). so poolSize:3 passed in pool option get ignored by ibm_db.
The maxPoolSize value can be set using pool.setMaxPoolSize (10) too and it do not change automatically. It is documented here: https://github.com/ibmdb/node-ibm_db/blob/master/APIDocumentation.md#-7-setmaxpoolsizen
You see 3 connections due to call of pool.init(3,Db2Config). If you are firing 5000 concurrent connection requests, all requests(4990) might be hanging as maxpoolsize is 10. Please do not do that. ibm_db can make more than 128 concurrent connections as it allocates new connection handle for each connection and Db2 ODBC driver can have only 128 connection handles under an environment handle. So, we do not recommend to set maxpoolsize value >= 128. Also, simultaneous connection requests get queue and established one by one. That's why we recommend to use pool. You can test using a connection request of <=10 and then check. You can see it is growing after some time as actual connection takes some time.
You can update database connection info in file ibm_db\test\config.json and then run node test\test-max-pool-size.js to verify the maxPoolSize.
To see the actual contents of pool and how it is growing, you can enable ibm_db logs by calling ibmdb.debug(true) function at start of your application. It will print lots of log on console.
Thanks.
I don't see gradual increase of connection in nestat . however i set the connection pooling as suggested . Hoping this should take more concurrent request and awaiting results from actual usuage . Please close the call .Thanks for the help