B4J Question Need some help to configure C3P0

[vfafou]

Hello!
I have a MariaDB database with wait_timeout=60 sec, because the RAM is only 4GB.
The maximum possible concurrent connections are ~650 and I'm using C3P0 for pooling.
It's all OK about performance but some times (every ~25 days) I have an error that hangs the pool, like:

** BEGIN NESTED EXCEPTION **

java.io.EOFException
MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

STACKTRACE:

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1997)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2411)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2916)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:882)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:823)
    at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:193)
    at rm.rmtunnel.dbutils._vvvv1(dbutils.java:778)
    at rm.rmtunnel.pushb4a._update_exchg(pushb4a.java:5100)
    at sun.reflect.GeneratedMethodAccessor25.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
    at anywheresoftware.b4j.object.WebSocketModule$Adapter$1.run(WebSocketModule.java:126)
    at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:30)
    at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
    at anywheresoftware.b4j.object.WebSocketModule$Adapter$ThreadHandler.run(WebSocketModule.java:195)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)


** END NESTED EXCEPTION **



Last packet sent to the server was 1 ms ago.

My settings of the C3P0 are:

jo.RunMethod("setMinPoolSize", Array As Object(30))
jo.RunMethod("setMaxPoolSize", Array As Object(1012))
jo.RunMethod("setMaxIdleTime", Array As Object(55))

I have read many posts from other C3P0 users and I haven't find anything proper for my configuration.
Reading the mchange.com instructions, I'm very confused for some properties.

Does anyone have made any configuration for heavy load with small server RAM, so could help me?

UPDATE:
I've just set more parameters...

Dim jo As JavaObject = Pool
jo.RunMethod("setMinPoolSize", Array As Object(30))
jo.RunMethod("setInitialPoolSize", Array As Object(30))
jo.RunMethod("setMaxPoolSize", Array As Object(1012))
jo.RunMethod("setAcquireIncrement", Array As Object(6))
jo.RunMethod("setMaxIdleTime", Array As Object(70))    jo.RunMethod("setMaxIdleTimeExcessConnections", Array As Object(60))
jo.RunMethod("setAcquireRetryAttempts", Array As Object(50))
jo.RunMethod("setAcquireRetryDelay", Array As Object(2000))
jo.RunMethod("setPreferredTestQuery", Array As Object("Select 1"))
jo.RunMethod("setTestConnectionOnCheckout", Array As Object(False))
jo.RunMethod("setTestConnectionOnCheckin", Array As Object(True))
jo.RunMethod("setIdleConnectionTestPeriod", Array As Object(60))
jo.RunMethod("setMaxStatements", Array As Object(8))

...and set the wait_timeout=120 sec


Thank you in advance.

 

[Erel]

Let your server run for a few days and then take a memory dump: http://docs.oracle.com/javase/7/docs/technotes/tools/share/jmap.html

You can then use a tool such as http://www.eclipse.org/mat/ to check whether there are memory leaks.

 

[vfafou]

Let your server run for a few days and then take a memory dump: http://docs.oracle.com/javase/7/docs/technotes/tools/share/jmap.html

You can then use a tool such as http://www.eclipse.org/mat/ to check whether there are memory leaks.

Thank you Erel! I'll try this!

 

[vfafou]

UPDATE:

I've set the wait_timeout=14400 sec (half of default of MySQL/MariaDB).

Is the following picture reasonable after some minutes working with connection pool?

 

[Erel]

Hard to say.

Make sure to always call SQL.Close after you call Pool.GetConnection.

 

[vfafou]

Hi Erel!
Thank you for you reply...
I would like to ask you which code structure is better:

1st scenario (SQL Code in an Event called from a Client):

Private Sub Some_Event(data as map)
    Dim id As String = data.Get("id")
    Dim PSQL As SQL = Main.Pool.GetConnection

    Try
        PSQL.BeginTransaction
        PSQL.ExecNonQuery2("UPDATE table1 SET id = ?",Array As String(id))
        PSQL.TransactionSuccessful
    Catch
        PSQL.Rollback
        PSQL.Close
        Log("Error Some_Event: " & LastException)
    End Try

    PSQL.Close

End Sub

2nd scenario (SQL Code in a Sub called from an Event called from a Client):

Private Sub Some_Event(data as Map)
    Dim id As String = data.Get("id")
    Dim PSQL As SQL = Main.Pool.GetConnection

    Try
        PSQL.BeginTransaction
        MakeSomeTransactions(data,PSQL)
        PSQL.TransactionSuccessful
    Catch
        PSQL.Rollback
        Log("Error Some_Event: " & LastException)
    End Try

    PSQL.Close

End Sub

Private Sub MakeSomeTransactions(data as Map,USQL As SQL)
    USQL.ExecNonQuery2("UPDATE table1 SET id = ?",Array As String(id))
    USQL.ExecNonQuery2("UPDATE table2 SET field1 = ? WHERE id = ?",Array As String("4",id))
End Sub

One more question, please:
Which is better:
Having a Process Global SQL object or Making a new SQL object every time we need to pick a connection from the pool?

Thank you in advance.

 

[Erel]

In the first code you are calling Close twice if there is an error. This is a mistake.
Other than that there is no difference.

Having a Process Global SQL object or Making a new SQL object every time we need to pick a connection from the pool?

It is better to use a local object.

 

[vfafou]

Hello Erel!
Thank you once more for your reply!

I've noticed the mistake of PSQL.Close
I've already made many changes to my code, so the SQL object is created and being closed only from the Event process.
By this way, I'm sure that the connection will be closed from an easy controllable Sub, instead of a Sub having a bunch of code lines.
I've reviewed all my code in the class of the connected devices and I've found that I didn't close the connection after two or three transactions.
Now, the picture of post #4, has been dramatically changed and I can see clearly the magic of the connection pooling!

Thank you for all your help!