We have a system where many devices (appservers, multiviewers, etc.) connect to a Postgres database located on one appserver to log events, and we have been using C3P0 with Hibernate for years to manage the connection pools.
However, as the system grows, hundreds of events per second can sometimes be logged by a single device, and we've noticed that after a while we see some devices exceed their maximum number of connections. For example, we can have hibernate.c3p0.max_size=40, and still see as much as a hundred connections.
Other settings like min_size and timeout are set the same way in the same file and we see them take effect like they should, so I am pretty sure the problem isn't with how the configurations are defined.
I've been doing research and trying to fix this issue for 2 weeks now. At some point I suspected that there was something wrong with the way every thread gets its own Hibernate session and somehow maybe that would cause a new connection pool to be opened for every thread instead of reusing the same pool, but I read C3P0's source code and it seems the pools really are managed by username/password, not by session, and we use the same username/password for every session, so now I'm quite puzzled and running out of ideas.
Here are some of the configurations in case it can be of any help:
hibernate.connection.driver_class=org.postgresql.Driver
hibernate.connection.username=***
hibernate.connection.password=***
hibernate.connection.url=jdbc:postgresql://localhost/eventlog
hibernate.c3p0.min_size=10
hibernate.c3p0.max_size=40
hibernate.c3p0.acquire_increment=10
hibernate.c3p0.max_statements=200
hibernate.c3p0.timeout=3600
hibernate.c3p0.idle_test_period=1800
Our code is too huge (and quite frankly, old and ugly) for me to copy-paste every relevant part here but I'll do my best to provide snippets as needed to advance the discussion. For now I'm looking for a place to start. Thanks!
close()es Connections asynchrously, via its internal thread pool. by default, that pool is small, probably too small for your application. if the thread pool get backlogged, you might have lots of Connections logically closed from c3p0's perspective but physically still open, waiting for the asynchronous close to actually happen. try adding something likehibernate.c3p0.numHelperThreads=15, and see if that helps. – Steve Waldman