We help IT Professionals succeed at work.

What are advantages when using Connection Pool in MySQL?

Medium Priority
1,806 Views
Last Modified: 2012-05-06
Hi everyone, I have a small question about MySQL.

I have writen a web application base on JSP technology. I use MySQL-JDBC to connect to MySQL database server (version 5.0.45). When configure the instance of MySQL, I set the concurrent user connection to 15.

In the development process, sometimes I have the error "too many connection" in my JSP pages. I use the following query:
   mysql>show full processlist
and found that my process is over 100. I consider that every times the number of process is higher than 100, my application will in trouble.
(Note: I run this command many times, and I see that maybe in 3-5 minutes the process will decrease. I don't know this is the "garbage collection" action of Java or MySQL or other ).
To fix this error, I found some information about Connection Pooling.

Finally I want to know:
1. If I use Connection Pooling for my application what is the advantages and disadvantages?
2. How do I setup connection polling for my application?
3. Moreover, how does the process garbage collection (both in Java and MySQL) work?
Comment
Watch Question

Java Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
advantage is that connections don't need to be constantly reopened, and instead they can be reused. Makes it faster to get a connection, plus reduces the need for garbage collection.
you usually setup your connection pool as a data source that is used by your application.
garbage collection refers to Java vm freeing up unused memory. For db connections you need to expicitly close them yourself.



Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Can you explain for me should I close the connection (with connection.close method) or there is any method to release the connection from client to connection pool?. And when the connections from poll to the database server are closed?  
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
you use the same close() method you use to close your existing connections. From the perspective of your application there is no change to how you use connections.
When connections are closed by the pool is setup in the [pool configuration.

Author

Commented:
Thank you so much. I have new trouble when use connection pool.
In Glassfish I set my concurrent connection for Connection pool is 1.
In my code I create 2 connections and use pool to reused.

I run the code following:
conn1.open()
{
resultSet1.....
}
conn1.close();  
***************
conn2.open()
{
resultSet2.....
}
conn2.close();  

And use the following code to monitor mySQL processlist: (in cmd mode)
mysql>show full processlist;

1. I restart my SQL services.
2. Then I run "show full processlist;" and the number of process is 1 (with the database is NULL (this process use for execute "show full processlist;" command))
3. Run the code open conn1, my process will increase to 2.
4. Run to the line close conn1, my process will decrease to 1.
5. Run the code open conn2, my process will increase to 3.
6. Run to the line close conn2, my process will decrease to 1.

I don't understand how does the pool work. Please explain for me.
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
can u post your pool config

Author

Commented:
Here is the image file
pool.JPG
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
It'll keep connections open and reuse them if need before thetimeout

Author

Commented:
your post is very helpful!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.