PDA

View Full Version : [SOLVED] Optimal MySQL connections



cl333r
November 16th, 2008, 03:00 AM
Hi folks,
I'm using Java on the server-side to update/delete items from a MySQL database. What's the best approach of doing this?
Should I keep one connection open and do everything through it?
Or should I open a new connection for every query/update (in a separate thread)?
I would appreciate any comments/examples..

PS: by "best approach" I mean as resistant to heavy load (user usage) as possible.

tinny
November 16th, 2008, 08:33 PM
Hi folks,
I'm using Java on the server-side to update/delete items from a MySQL database. What's the best approach of doing this?
Should I keep one connection open and do everything through it?
Or should I open a new connection for every query/update (in a separate thread)?
I would appreciate any comments/examples..

PS: by "best approach" I mean as resistant to heavy load (user usage) as possible.

Does this application run in a Servlet container?

E.g. Tomcat, Jetty, Glassfish etc.....?

Most (I think all) Servlet containers provide some sort of connection pooling functionality. (Let the container do it for you)

drubin
November 17th, 2008, 10:42 PM
Does this application run in a Servlet container?

E.g. Tomcat, Jetty, Glassfish etc.....?

Most (I think all) Servlet containers provide some sort of connection pooling functionality. (Let the container do it for you)

Huge +1 on the connection pooling they can cut down on huge amounts of connecting time.

cl333r
November 17th, 2008, 10:49 PM
Thanks a lot guys! I started using the DBCP from the Jakarta project:
http://commons.apache.org/dbcp/

PS: I'm using GlassFish v2

tinny
November 18th, 2008, 12:10 AM
Thanks a lot guys! I started using the DBCP from the Jakarta project:
http://commons.apache.org/dbcp/

PS: I'm using GlassFish v2

Glassfish has connection pooling built in

http://docs.sun.com/app/docs/doc/820-5709/abyen?l=en&a=view

cl333r
November 18th, 2008, 02:35 PM
I read a bit about it and here's my impression:
It's good that GlassFish has pooling built-in but to actually use it you have to configure it. So every time deploying on another machine you have to remember to configure the pooling mechanism on the server-side.
Since I'm using my own one (from Jakarta) I don't have to remember to do that and to me as a developer that sounds easier deployment.
Am I missing something?
If the only "drawback" is that I'm not using the built-in one, then I stay with the Jakarta project solution.

tinny
November 18th, 2008, 08:25 PM
I read a bit about it and here's my impression:
It's good that GlassFish has pooling built-in but to actually use it you have to configure it. So every time deploying on another machine you have to remember to configure the pooling mechanism on the server-side.
Since I'm using my own one (from Jakarta) I don't have to remember to do that and to me as a developer that sounds easier deployment.
Am I missing something?
If the only "drawback" is that I'm not using the built-in one, then I stay with the Jakarta project solution.

Usually a Web application will only have one production environment, so you will only need to set this up once.

Personally I would trust the application server to do a better job of connection pooling than what I could ever do by using a thrid party API.

drubin
November 18th, 2008, 08:28 PM
Usually a Web application will only have one production environment, so you will only need to set this up once.

Personally I would trust the application server to do a better job of connection pooling than what I could ever do by using a thrid party API.

People do generally not move production servers often. IF they do get moved I think worrying about copying the configs of your server to the new one is the least of your worries.