Re: webapp design: how to handle connection pooling
Connections obtained through a datasource ar closed to indicate that they are available for re-use. The actual underlying connection to the database is not closed. Joe Peer wrote: > Hi everybody, > > thank you for your responses! > > i forgot to say that i am already using orion's connection pooling > capability (i never thought i would give up my old connection pooling > package, but orion made it possible ;=) > > the reason why i wrote the original message to this list was that i knew i > am making something wrong, because since i upgraded to orion 1.1.37 i got > that interesting warning on my console: > > "DriverManagerConnectionPoolConnection not closed, check your code!" > (and i get lots of them ;=) > > this made me think about how to manage connections in general. i did not > know i had to CLOSE the connections (i thought a connection pool consists of > OPEN connections) > > as you can see, i still don't understand this whole db-connection stuff, so > every additional info is still very appreciated!! > > thanks, > joe > - > to Keven: i haven't tried yet, but i don't see a reason why orion should NOT > be able to pool connections to different databases > > > Keven Duffey wrote: > > My input follows: > > > > * some people say, it's best practice to put 1 connection > > > into 1 user's http > > > session and use it for all requests of that user > > This only works if you don't have a lot of users concurrently, > > say a small intranet application, that doesn't care scalibility. > > The reason is database connection can't be serialized for sure > > and connection in http session can't be replicated across your > > web server farm. Hence each user in a session requires to use > > the same machine in your web farm. This could be terrible if you > > intend to serve thousands of users concurrently. Secondly > > database licensing is an issue. Some database vendors charge you > > by concurrent opened connection. If your site has a thousand > > users concurrently, you will need to purchase a thousand licenses > > (That is the thing scares me most). > > Agreed with replier..you would NEVER want to do this. If you are not careful > you can lose the connections which will ultimately crash the database and/or > the web server. VERY bad design. > > > > > > * other people say it's best practice to use a connection for > > > every request. > > > (and if more than one methods are involved, the connection > > > should be given > > > as a method parameter) > > > > > I would rather use this per-requset approach. Only open a > > connection whenever a request comes in and needs database access. > > I can't think of a reason why passing around connection as > > parameter is a bad idea as long as it is in the same request. > > This isn't bad. There are better ways. > > > > > > * again other people argue that they don't care at all about reusing > > > connection, they just open and close them when they need/want to > > I would not like this idea since opening a database connection is > > expensive unless there is a connection pooling mechanism in place. > > This is VERY slow, and in some tests its 100's of times slower than > implementing a connection pool. For the original sender (and Conrad if you > are not aware of this), there are two methods I would choose over the others > above. The first is connection pooling. Servlets in the same web app > maintain a Servlet Context that is a good place to store "global" objects. > By making a connection pool class and putting it in the servlet context when > the web app is first loaded, the connection pool is available to all > servlets (and JSP pages). Beyond that, a connection pool allows you to open > one or more connections and keep them alive while the web app is running. > Because of this, instead of going over the network and opening a connection > on each request, then closing that connection, you are given an immediate > opened connection from the pool on every request. This class is relatively > easy to implement as well. > > Option 2 is better when you are dealing with application servers, especialy > with Orion. Orion has a very easy built-in connection pooling capability. > Generally the EJB layer would use this to get and set data in the database, > but your JSP/Servlets can do the same as well. Each Orion app server > (assuming you are using a cluster for fail-over, load-balancing and > scalability) can have its own connection pool settings. You provide the > database driver, number of connections, and login/password. The rest is done > through JDBC 2.0 DataSources. Now..I am not a pro at this yet..so I am > taking a stab at how this actually works. I still use my old connection pool > class. None the less, I am pretty sure its this easy. My only question to > anyone that knows is..can Orion (and other app servers for that matter) have > multiple connection pools per app server? On our site we maintain 3 > differ
RE: webapp design: how to handle connection pooling
you do have to release the connections back to the pool, usually with a free(Connection) command or similar. the pool then handles closing the actual Connection for you. Maybe Orion's implementation wants the Connection to be closed outside the pool, but this seems illogical to me. Jeroen T. Wenting [EMAIL PROTECTED] Murphy was wrong, things that can't go wrong will anyway > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Joe Peer > Sent: Thursday, August 10, 2000 09:21 > To: Orion-Interest > Subject: Re: webapp design: how to handle connection pooling > > > Hi everybody, > > thank you for your responses! > > i forgot to say that i am already using orion's connection pooling > capability (i never thought i would give up my old connection pooling > package, but orion made it possible ;=) > > the reason why i wrote the original message to this list was that i knew i > am making something wrong, because since i upgraded to orion 1.1.37 i got > that interesting warning on my console: > > "DriverManagerConnectionPoolConnection not closed, check your code!" > (and i get lots of them ;=) > > this made me think about how to manage connections in general. i did not > know i had to CLOSE the connections (i thought a connection pool > consists of > OPEN connections) > > as you can see, i still don't understand this whole db-connection > stuff, so > every additional info is still very appreciated!! > > thanks, > joe > - > to Keven: i haven't tried yet, but i don't see a reason why orion > should NOT > be able to pool connections to different databases > > > > > Keven Duffey wrote: > > My input follows: > > > > * some people say, it's best practice to put 1 connection > > > into 1 user's http > > > session and use it for all requests of that user > > This only works if you don't have a lot of users concurrently, > > say a small intranet application, that doesn't care scalibility. > > The reason is database connection can't be serialized for sure > > and connection in http session can't be replicated across your > > web server farm. Hence each user in a session requires to use > > the same machine in your web farm. This could be terrible if you > > intend to serve thousands of users concurrently. Secondly > > database licensing is an issue. Some database vendors charge you > > by concurrent opened connection. If your site has a thousand > > users concurrently, you will need to purchase a thousand licenses > > (That is the thing scares me most). > > Agreed with replier..you would NEVER want to do this. If you are > not careful > you can lose the connections which will ultimately crash the > database and/or > the web server. VERY bad design. > > > > > > * other people say it's best practice to use a connection for > > > every request. > > > (and if more than one methods are involved, the connection > > > should be given > > > as a method parameter) > > > > > I would rather use this per-requset approach. Only open a > > connection whenever a request comes in and needs database access. > > I can't think of a reason why passing around connection as > > parameter is a bad idea as long as it is in the same request. > > This isn't bad. There are better ways. > > > > > > * again other people argue that they don't care at all about reusing > > > connection, they just open and close them when they need/want to > > I would not like this idea since opening a database connection is > > expensive unless there is a connection pooling mechanism in place. > > This is VERY slow, and in some tests its 100's of times slower than > implementing a connection pool. For the original sender (and Conrad if you > are not aware of this), there are two methods I would choose over > the others > above. The first is connection pooling. Servlets in the same web app > maintain a Servlet Context that is a good place to store "global" objects. > By making a connection pool class and putting it in the servlet > context when > the web app is first loaded, the connection pool is available to all > servlets (and JSP pages). Beyond that, a connection pool allows > you to open > one or more connections and keep them alive while the web app is running. > Because of this, instead of going over the network and opening a > connection > on each request, then closing that connection, you are given an immediate > opened connection f
RE: webapp design: how to handle connection pooling
> > Option 2 is better when you are dealing with application servers, > especialy > with Orion. Orion has a very easy built-in connection pooling capability. > > When you suggest this method, I assume you are talking about an Orion > specific mechanism correct? This is convenient if Orion is the only server > you're using. If you ever want to migrate your app to a new > server, you have > to use that server's method or resort to the custom connection pool class > above. > > Have I grasped the situation, or is there a J2EE-based "standard" > mechanism > for connection pooling that is portable across application servers? > There is a J2EE standard for connection pooling in the works, from what I've heard, but it is not in the 1.2.1 release. Personally, I always use a 3rd party one, for exactly the reason you state: cross-server portability. I test on Orion and Tomcat, deploy on iPlanet, using different database engines as well. Jeroen T. Wenting [EMAIL PROTECTED] Murphy was wrong, things that can't go wrong will anyway
Re: webapp design: how to handle connection pooling
Hi everybody, thank you for your responses! i forgot to say that i am already using orion's connection pooling capability (i never thought i would give up my old connection pooling package, but orion made it possible ;=) the reason why i wrote the original message to this list was that i knew i am making something wrong, because since i upgraded to orion 1.1.37 i got that interesting warning on my console: "DriverManagerConnectionPoolConnection not closed, check your code!" (and i get lots of them ;=) this made me think about how to manage connections in general. i did not know i had to CLOSE the connections (i thought a connection pool consists of OPEN connections) as you can see, i still don't understand this whole db-connection stuff, so every additional info is still very appreciated!! thanks, joe - to Keven: i haven't tried yet, but i don't see a reason why orion should NOT be able to pool connections to different databases Keven Duffey wrote: My input follows: > > * some people say, it's best practice to put 1 connection > > into 1 user's http > > session and use it for all requests of that user > This only works if you don't have a lot of users concurrently, > say a small intranet application, that doesn't care scalibility. > The reason is database connection can't be serialized for sure > and connection in http session can't be replicated across your > web server farm. Hence each user in a session requires to use > the same machine in your web farm. This could be terrible if you > intend to serve thousands of users concurrently. Secondly > database licensing is an issue. Some database vendors charge you > by concurrent opened connection. If your site has a thousand > users concurrently, you will need to purchase a thousand licenses > (That is the thing scares me most). Agreed with replier..you would NEVER want to do this. If you are not careful you can lose the connections which will ultimately crash the database and/or the web server. VERY bad design. > > > * other people say it's best practice to use a connection for > > every request. > > (and if more than one methods are involved, the connection > > should be given > > as a method parameter) > > > I would rather use this per-requset approach. Only open a > connection whenever a request comes in and needs database access. > I can't think of a reason why passing around connection as > parameter is a bad idea as long as it is in the same request. This isn't bad. There are better ways. > > > * again other people argue that they don't care at all about reusing > > connection, they just open and close them when they need/want to > I would not like this idea since opening a database connection is > expensive unless there is a connection pooling mechanism in place. This is VERY slow, and in some tests its 100's of times slower than implementing a connection pool. For the original sender (and Conrad if you are not aware of this), there are two methods I would choose over the others above. The first is connection pooling. Servlets in the same web app maintain a Servlet Context that is a good place to store "global" objects. By making a connection pool class and putting it in the servlet context when the web app is first loaded, the connection pool is available to all servlets (and JSP pages). Beyond that, a connection pool allows you to open one or more connections and keep them alive while the web app is running. Because of this, instead of going over the network and opening a connection on each request, then closing that connection, you are given an immediate opened connection from the pool on every request. This class is relatively easy to implement as well. Option 2 is better when you are dealing with application servers, especialy with Orion. Orion has a very easy built-in connection pooling capability. Generally the EJB layer would use this to get and set data in the database, but your JSP/Servlets can do the same as well. Each Orion app server (assuming you are using a cluster for fail-over, load-balancing and scalability) can have its own connection pool settings. You provide the database driver, number of connections, and login/password. The rest is done through JDBC 2.0 DataSources. Now..I am not a pro at this yet..so I am taking a stab at how this actually works. I still use my old connection pool class. None the less, I am pretty sure its this easy. My only question to anyone that knows is..can Orion (and other app servers for that matter) have multiple connection pools per app server? On our site we maintain 3 different databases and each request needs a connection from one of the three..depending on where the user is on the site. I would assume this is possible..otherwise alot of sites wouldn't use app server specific database connection pooling. Anyways..for what its worth..I hope that helps. ___ Say Bye to Sl
RE: webapp design: how to handle connection pooling
> This is VERY slow, and in some tests its 100's of times slower than implementing a connection pool. For the original sender (and Conrad if you are not aware of this), there are two methods I would choose over the others above. The first is connection pooling. Servlets in the same web app maintain a Servlet Context that is a good place to store "global" objects. This way seems like a good idea because it should be portable across J2EE compliant application servers right? > Option 2 is better when you are dealing with application servers, especialy with Orion. Orion has a very easy built-in connection pooling capability. When you suggest this method, I assume you are talking about an Orion specific mechanism correct? This is convenient if Orion is the only server you're using. If you ever want to migrate your app to a new server, you have to use that server's method or resort to the custom connection pool class above. Have I grasped the situation, or is there a J2EE-based "standard" mechanism for connection pooling that is portable across application servers? Thanks, Kit Cragin VP of Product Development Mongoose Technology, Inc. www.mongoosetech.com
RE: webapp design: how to handle connection pooling
My input follows: > > * some people say, it's best practice to put 1 connection > > into 1 user's http > > session and use it for all requests of that user > This only works if you don't have a lot of users concurrently, > say a small intranet application, that doesn't care scalibility. > The reason is database connection can't be serialized for sure > and connection in http session can't be replicated across your > web server farm. Hence each user in a session requires to use > the same machine in your web farm. This could be terrible if you > intend to serve thousands of users concurrently. Secondly > database licensing is an issue. Some database vendors charge you > by concurrent opened connection. If your site has a thousand > users concurrently, you will need to purchase a thousand licenses > (That is the thing scares me most). Agreed with replier..you would NEVER want to do this. If you are not careful you can lose the connections which will ultimately crash the database and/or the web server. VERY bad design. > > > * other people say it's best practice to use a connection for > > every request. > > (and if more than one methods are involved, the connection > > should be given > > as a method parameter) > > > I would rather use this per-requset approach. Only open a > connection whenever a request comes in and needs database access. > I can't think of a reason why passing around connection as > parameter is a bad idea as long as it is in the same request. This isn't bad. There are better ways. > > > * again other people argue that they don't care at all about reusing > > connection, they just open and close them when they need/want to > I would not like this idea since opening a database connection is > expensive unless there is a connection pooling mechanism in place. This is VERY slow, and in some tests its 100's of times slower than implementing a connection pool. For the original sender (and Conrad if you are not aware of this), there are two methods I would choose over the others above. The first is connection pooling. Servlets in the same web app maintain a Servlet Context that is a good place to store "global" objects. By making a connection pool class and putting it in the servlet context when the web app is first loaded, the connection pool is available to all servlets (and JSP pages). Beyond that, a connection pool allows you to open one or more connections and keep them alive while the web app is running. Because of this, instead of going over the network and opening a connection on each request, then closing that connection, you are given an immediate opened connection from the pool on every request. This class is relatively easy to implement as well. Option 2 is better when you are dealing with application servers, especialy with Orion. Orion has a very easy built-in connection pooling capability. Generally the EJB layer would use this to get and set data in the database, but your JSP/Servlets can do the same as well. Each Orion app server (assuming you are using a cluster for fail-over, load-balancing and scalability) can have its own connection pool settings. You provide the database driver, number of connections, and login/password. The rest is done through JDBC 2.0 DataSources. Now..I am not a pro at this yet..so I am taking a stab at how this actually works. I still use my old connection pool class. None the less, I am pretty sure its this easy. My only question to anyone that knows is..can Orion (and other app servers for that matter) have multiple connection pools per app server? On our site we maintain 3 different databases and each request needs a connection from one of the three..depending on where the user is on the site. I would assume this is possible..otherwise alot of sites wouldn't use app server specific database connection pooling. Anyways..for what its worth..I hope that helps.
RE: webapp design: how to handle connection pooling
Here is my input on this interesting topic. > * some people say, it's best practice to put 1 connection > into 1 user's http > session and use it for all requests of that user This only works if you don't have a lot of users concurrently, say a small intranet application, that doesn't care scalibility. The reason is database connection can't be serialized for sure and connection in http session can't be replicated across your web server farm. Hence each user in a session requires to use the same machine in your web farm. This could be terrible if you intend to serve thousands of users concurrently. Secondly database licensing is an issue. Some database vendors charge you by concurrent opened connection. If your site has a thousand users concurrently, you will need to purchase a thousand licenses (That is the thing scares me most). > * other people say it's best practice to use a connection for > every request. > (and if more than one methods are involved, the connection > should be given > as a method parameter) > I would rather use this per-requset approach. Only open a connection whenever a request comes in and needs database access. I can't think of a reason why passing around connection as parameter is a bad idea as long as it is in the same request. > * again other people argue that they don't care at all about reusing > connection, they just open and close them when they need/want to I would not like this idea since opening a database connection is expensive unless there is a connection pooling mechanism in place. Conrad
Re: webapp design: how to handle connection pooling
> * some people say, it's best practice to put 1 connection into 1 > user's http > session and use it for all requests of that user This should not be done because you end up having one connection/session. Moreover connection objects are not serializable. > * other people say it's best practice to use a connection for every > request. > (and if more than one methods are involved, the connection should be > given > as a method parameter) This is safe, but resource hungry. > * again other people argue that they don't care at all about reusing > connection, they just open and close them when they need/want to > > who is right? or, how do you handle it? This is also resource hungry. The suggested approach is to setup Orion for connection pooling, and get connections using the datasource as usual. Subbu = __ Do You Yahoo!? Kick off your party with Yahoo! Invites. http://invites.yahoo.com/
