c3p0:JDBC DataSources/Resource Pools

Tracker: Bugs

5 close c3p0 connection does not release the connection - ID: 2828170

Last Update: Comment added ( shaoxianyang )

Details:

Hi, I wrote a java program, where i create a ComboPooledDataSource. Then,
i just execute one sql over and over. while the program runs, i monitor
the mysql administrator user connection. The test is single threaded, and
just execute some query over and over. So at most, the program only need 1
open connection at any given time. I set the connection pool min size to
be 5, and max to be 30. If I run the query for many round, I see the open
connections ramp up. Ideally, I would hope there is only 5 connections
(only 1 is used to serve my query). However, the open connection quickly
ramp up to more than 5, sometimes, it runs to max connection very quickly.
I wrote the test, because one time i see more than max connections being
generated, and it hangs my mysql server. Therefore, i am very concerned on
this.

This is my test case, please take a look and let me know if it is valid:

@Test
public void testC3P0() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setUser("root");
ds.setPassword("root");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/podcasts_catalog");
ds.setMinPoolSize(5);
ds.setMaxPoolSize(30);
ds.setAcquireIncrement(1);

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

for (int i = 0; i < 2000000; i++) {
try {
conn = ds.getConnection();
String sql = "select * from category order by pos";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
// while (rs.next()) {
// System.out.println("name: " + rs.getString("name"));
// }
} finally {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (conn != null && !conn.isClosed())
conn.close();
}
//sleep(1); (if do not thread.sleep 1ms, then connection ramp up to
maximum size almost immediately. if sleep for 1ms, it will ramp up more
than min, but not reaching to max so quickly)

}

DataSources.destroy(ds);

}

The only reason that connections ramp up i can think of, is that
conn.close() does not really release the actual connection, so that when
new query executed, it accquires new connection from pool.

Submitted:

shaoxian yang ( shaoxianyang ) - 2009-07-28 02:39

Priority:

Status:

Open

Resolution:

None

Assigned:

Nobody/Anonymous

Category:

None

Group:

None

Visibility:

Public

Comments ( 2 )

Date: 2009-07-28 08:47 Sender: shaoxianyang Thanks for your comment, swaldman. How soon will those asynchronous close operation finish? From what I observe in the test case above (with running many iteration without any interview between each round, which takes around 200s. The connetions never goes down during that period. The open connection only goes up close/equal to max size. Is it reasonable? The other thing I notice is that the test takes longer to run with connection pool than without it. Isn't that connection pool supposed to make your jdbc query faster? I agree with you that letting the process pause for around 100ms will not ramp up the open connections. I will still double check that tomorrow. But i think I observed that before. Basically, I am more worrying about highly concurrent system, where multiple query requests just come in over and over. If every connection takes around 1 second to close, that is slow. How do I verify the previous used connection is back to use again? Any particular logging I can turn on to observe this? As for your comment on depending on "particulars of the Thread implementation of your JVM and its scheduling policy". Why is that, because the multithread implementation of pool? I know my conn.close() is faulty. That was a test, not production ready code. Thanks for pointing out though. Shaoxian Yang
Date: 2009-07-28 06:02 Sender: swaldman hi, connections are refurbished and released asyncronously. so in a hard loop it is possible that the Thread running the loop would prevent the release, although the IO ops in executing your query might give some space for the async release to occur. But perhaps your JDBC driver does local caching of the repeated query, or you are using an in-memory database. (However, caching would be kind of bad, though, unless you've turned autocommit off.) That the explicit Thread.sleep() should helps indicates that this is the issue.. If you Thread.sleep() for 100 ms rather than 1 ms, I'd expect you'd see no ramping at all. In a more realistic scenario, where DB transactions involve significant latency for network IO and/or DB query time, there is no need to sleep or yield explicitly, client threads naturally take pauses that permit asynchronous maintenance tasks. Obviously, all of this depends on the particulars of the Thread implementation of your JVM and its scheduling policy. BTW you are close, but not quite there, in assuring that there would be no Connection leaks in this code. Consider what happens if rs.close() fails on an SQLException. (It can happen.)

Date: 2009-07-28 08:47
Sender: shaoxianyang

Thanks for your comment, swaldman.

How soon will those asynchronous close operation finish? From what I
observe in the test case above (with running many iteration without any
interview between each round, which takes around 200s. The connetions
never goes down during that period. The open connection only goes up
close/equal to max size. Is it reasonable?

The other thing I notice is that the test takes longer to run with
connection pool than without it.
Isn't that connection pool supposed to make your jdbc query faster?
I agree with you that letting the process pause for around 100ms will not
ramp up the open connections. I will still double check that tomorrow.
But i think I observed that before.

Basically, I am more worrying about highly concurrent system, where
multiple query requests just come in over and over. If every connection
takes around 1 second to close, that is slow. How do I verify the
previous used connection is back to use again? Any particular logging I
can turn on to observe this?

As for your comment on depending on "particulars of the Thread
implementation of your JVM and its scheduling policy". Why is that,
because the multithread implementation of pool?

I know my conn.close() is faulty. That was a test, not production ready
code. Thanks for pointing out though.

Shaoxian Yang

Date: 2009-07-28 06:02
Sender: swaldman

hi,

connections are refurbished and released asyncronously. so in a hard loop
it is possible that the Thread running the loop would prevent the release,
although the IO ops in executing your query might give some space for the
async release to occur. But perhaps your JDBC driver does local caching of
the repeated query, or you are using an in-memory database. (However,
caching would be kind of bad, though, unless you've turned autocommit off.)
That the explicit Thread.sleep() should helps indicates that this is the
issue.. If you Thread.sleep() for 100 ms rather than 1 ms, I'd expect you'd
see no ramping at all. In a more realistic scenario, where DB transactions
involve significant latency for network IO and/or DB query time, there is
no need to sleep or yield explicitly, client threads naturally take pauses
that permit asynchronous maintenance tasks.

Obviously, all of this depends on the particulars of the Thread
implementation of your JVM and its scheduling policy.

BTW you are close, but not quite there, in assuring that there would be no
Connection leaks in this code. Consider what happens if rs.close() fails on
an SQLException. (It can happen.)

c3p0:JDBC DataSources/Resource Pools

Tracker: Bugs

Comments ( 2 )

Log in to comment.

Attached File

No Files Currently Attached

Change

No changes have been made to this artifact.

About SourceForge

Find Software

Develop Software

Community

Help