asked on

What are advantages when using Connection Pool in MySQL?

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?

ASKER CERTIFIED SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

starsystems

ASKER

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?

SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

starsystems

ASKER

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.

SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

starsystems

ASKER

Here is the image file
pool.JPG

SOLUTION

Mick Barry

membership