?
Solved

What are advantages when using Connection Pool in MySQL?

Posted on 2009-02-16
8
Medium Priority
?
1,348 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?
0
Comment
Question by:starsystems
  • 4
  • 4
8 Comments
 
LVL 92

Accepted Solution

by:
objects earned 1500 total points
ID: 23656855
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.



0
 

Author Comment

by:starsystems
ID: 23656924
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?  
0
 
LVL 92

Assisted Solution

by:objects
objects earned 1500 total points
ID: 23657022
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.

0
Automating Your MSP Business

The road to profitability.
Delivering superior services is key to ensuring customer satisfaction and the consequent long-term relationships that enable MSPs to lock in predictable, recurring revenue. What's the best way to deliver superior service? One word: automation.

 

Author Comment

by:starsystems
ID: 23657722
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.
0
 
LVL 92

Assisted Solution

by:objects
objects earned 1500 total points
ID: 23657736
can u post your pool config

0
 

Author Comment

by:starsystems
ID: 23657773
Here is the image file
pool.JPG
0
 
LVL 92

Assisted Solution

by:objects
objects earned 1500 total points
ID: 23664705
It'll keep connections open and reuse them if need before thetimeout

0
 

Author Closing Comment

by:starsystems
ID: 31547665
your post is very helpful!
0

Featured Post

When ransomware hits your clients, what do you do?

MSPs: Endpoint security isn’t enough to prevent ransomware.
As the impact and severity of crypto ransomware attacks has grown, Webroot fought back, not just by building a next-gen endpoint solution capable of preventing ransomware attacks but also by being a thought leader.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The task of choosing a web design company to build a website for your business should never be taken in a light manner. Provided the fact that your website will act as a representative to your business and will be responsible for imposing an online …
While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.
Suggested Courses
Course of the Month16 days, 13 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question