I am very new in c3p0 integration...
I have these settings with c3p0-0.9.5-pre5.jar, hibernate-c3p0-3.5.6-Final.jar, hibernate-core-3.5.6-Final.jar and mchange-commons-java-0.2.6.3.jar jars like below...
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.max_size">5</property>
<property name="hibernate.c3p0.timeout">40</property>
<property name="hibernate.c3p0.idle_test_period">30</property>
<!--<property name="hibernate.c3p0.max_statements">50</property>-->
<property name="hibernate.c3p0.maxStatementsPerConnection">5</property> <!--Instead of max_statements-->
<property name="hibernate.c3p0.validate">true</property>
<property name="hibernate.connection.pool_size">25</property>
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.automaticTestTable">con_test</property>
<property name="hibernate.c3p0.privilegeSpawnedThreads">true</property>
<property name="hibernate.c3p0.contextClassLoaderSource">library</property>
<property name="hibernate.c3p0.maxAdministrativeTaskTime">30</property>
<property name="hibernate.c3p0.numHelperThreads">20</property>
The problem is, application generates thousands of threads and keep those as Time waited Threads.
I have print some of those threads by a loop ..
"<br/>" + c++ +". "+ t.getState() + " (" + t.isAlive() + ") : " + t.getName();
results is below...
147. TIMED_WAITING (true) : C3P0PooledConnectionPoolManager[identityToken->1hge86f9r1gp0vs6si1few|2feccbb3]-HelperThread-#0
148. TIMED_WAITING (true) : C3P0PooledConnectionPoolManager[identityToken->1hge86f9r1gp0vs6si1few|8d0e89c]-HelperThread-#3
149. WAITING (true) : Reference Handler
150. TIMED_WAITING (true) : C3P0PooledConnectionPoolManager[identityToken->1hge86f9r1gp0vs6si1few|8d0e89c]-HelperThread-#2
151. TIMED_WAITING (true) : C3P0PooledConnectionPoolManager[identityToken->1hge86f9r1gp0vs6si1few|1045f6be]-HelperThread-#8
152. TIMED_WAITING (true) : C3P0PooledConnectionPoolManager[identityToken->1hge86f9r1gp0vs6si1few|1045f6be]-HelperThread-#19
153. TIMED_WAITING (true) : C3P0PooledConnectionPoolManager[identityToken->1hge86f9r1gp0vs6si1few|3b0c81d2]-HelperThread-#17
154. TIMED_WAITING (true) : C3P0PooledConnectionPoolManager[identityToken->1hge86f9r1gp0vs6si1few|2feccbb3]-HelperThread-#3
155. TIMED_WAITING (true) : C3P0PooledConnectionPoolManager[identityToken->1hge86f9r1gp0vs6si1few|2feccbb3]-HelperThread-#1
156. TIMED_WAITING (true) : C3P0PooledConnectionPoolManager[identityToken->1hge86f9r1gp0vs6si1few|70cef37d]-HelperThread-#19
This is increasing very first when retrieve data from database by application.
App developed by Java, Struts-1, Hibernate, Oracle(BD).
How can I remove/kill these threads
One way or some other, if you are seeing thousand of these Threads, you are leaking DataSources. That is, your application is constructing c3p0 DataSources, each of which has its own complement of Threads, then it is losing or dereferencing or replacing them without first close()ing them.
A pooled DataSource should be constructed once, placed somewhere with shared availability, and reused over and over again. If, unusually, a DataSource needs to be reconstructed for some reason, you need to close() c3p0 DataSources or their Threads will live forever.
Perhaps the most common error that leads to this sort of thing are applications that hot-redeploy. If on app initialization a DataSource is created, in a shutdown hook in the redeploy cycle you must take car that the same DataSource gets destroyed.
Note that in the list of Threads above, you show many Threads from different DataSources (as they have different identity tokens after the common VMID portion before the |). You are definitely creating but not close()ing lots of DataSources.