Connection pool API with better performance

Question

1º parte of the question:

My application is receiving lots of data from a TCP socket and I need to store the data in a MySQL database. Now, I'm just creating a new connection each time I want to query the DB and after the query I'm closing the connection.

I have just one thread to receive the data and store in the database, but I have other threads that are querying the database (they create also new connections), but those ones are slower (queries are more complex). The problem is:

• When I have just the thread for receiving and store the data, the data is stored relativly fast (I think it could be faster);
• When I have also the threads with slower queries, the storage becomes too slow...

Is It possible that the connections are blocking? (I'm creating and closing connections each time I want to access the DB).

2º parte of the question:

In my research to solve the problem above, I saw that I should use a connection pool, because this could improve the access time to the database.

So I found 5 possibilities to implment that:

1. http://commons.apache.org/dbcp/
2. http://www.mchange.com/projects/c3p0/
3. http://www.javamex.com/tutorials/synchronization_concurrency_semaphore2.shtml
4. http://www.snaq.net/java/DBPool/
5. http://www.roseindia.net/tutorial/java/jdbc/jdbcconnectionpooling.html

As I don't have time to test each of them, I would like to know which one (in your opinion) could present a better performance and less problems to solve... (I saw somewhere in the Web that dbcp can give more problems instead of solving them...)

Answer 1

Well depending on the queries and on the quantity of data you retrieve or insert, it may be normal to have a certain performance impact, resulting in slower overall performance.

I think you should try to optimize queries/DB before anything else.

Regarding connection pooling, i have used BoneCP and found it to be very good and fast.