hibernatejdbcc3p0oracle18c

Hibernate - C3P0 JDBC connection pooling

I'm not a Java developer, but a systems administrator, so forgive me for any senseless statement or questions.

I suspect the C3P0 connection pooling is not set correctly in Hibernate, causing lots of Oracle DB connections (oracle<instanceID> (LOCAL-NO)) via various Java apps. Some of those connections stay on for 30 days (probably stale) until they get auto-closed or discarded. Those connections go into "sleep (S)" state and the process stack shows them at "sk_wait_data" stage. Most of them do seem to switch trying to look for data and go back to "sk_wait_data". The switching happens every few seconds. The switch causes the process to get on the CPU run queue causing increase in the system load value. I believe this is a great deal of critical system resources issue.

As per JMC, the min pool size is 1 and max varies depends on the type of the application. I suspect min=1 is dead wrong and the max should be set up properly based on the app traffic sustainability and some buffer for scalability. The usage of the C3P0 pool seems erratic and in-efficient in JMC.

So the switching of the connection states is bad and needs to be fixed. Besides seeking comments from experts on above, I've a question about the DB connection:

Would a JDBC connection move back and forth "sk_wait_data" like above or is it wrong?

I would think the min size pool connections would always be connected to the DB but don't know what their socket connection state would be?

Also on a VM having 2 Java apps, they seem to create like 10 pools. Not sure if that is right either.

Please advise.

Solution
    1. "Connections stays for 30 days":

    Set : maxConnectionAge to reasonable value: I usually set it to 18000 seconds (5 hours),this will ensure refreshing the connection anyways after this time.

    1. "Most of them do seem to switch trying to look for data and go back to "sk_wait_data":

    Most likely this a driver specific behavior, I would recommend Setting: idleConnectionTestPeriod, I normally set it to 60 seconds, this will execute a test query on the idle connections every 60 seconds.

    1. "I suspect min=1 is dead wrong"

    This value is decided based on many factors, such as the number of apps connected to the database, the continuous active load, and the average load, and most importantly, the concurrent load.

    I would recommend setting it based on the average concurrent load per second.

    1. "Would a JDBC connection move back and forth "sk_wait_data" like above or is it wrong?"

    Check point 1 and point 2, in addition, c3p0 provides a "testConnectionOnCheckout" and "testConnectionOnCheckin" properties which also ensures that connection is valid.

    1. "I would think the min size pool connections would always be connected to the DB but don't know what their socket connection state would be?"

    This is purely based on the driver's implementation, however, with the point number 4, most likely you wouldn’t need to care about it.

    1. "VM having 2 Java apps, they seem to create like 10 pools. Not sure if that is right either."

    Connection pool is created per hibernate SessionFactory(in case of hibernate), which is most likely one per app (one per JVM on desktop apps) and one per webapp in case of Web-Servers (such as tomcat) or App servers (such as Weblogic or web sphere).

    However, if an individual application is creating more than one pool, you should check with the app developers.

    Full configuration of c3p0 could be found at: https://www.mchange.com/projects/c3p0/index.html#configuration_properties

    Good Luck
    Jalal