[cfaussie] Re: To maintain DB connections or not ... that is the question
but that is going to add the overhead of creating a database connection on every request, i'm sure there are some notes on the datadirect or even adobe site on how to address that issue On Wed, Apr 30, 2008 at 11:52 AM, Mark Picker [EMAIL PROTECTED] wrote: Hi, It's been a while so I may be remembering wrong, but I think the maintain connections caused a problem for us when using the JDBC drivers to connect to SQL2005 databases that were using the new mirror feature. Essentially it wouldn't failover the connection until the current connection had expiredwhich is useless. With the maintain connections option unticked the connection would failover to the correct principle. Cheers Mark -- Zac Spitzer - http://zacster.blogspot.com (My Blog) +61 405 847 168 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: To maintain DB connections or not ... that is the question
this can involve a lot of factors ... ... first of all, when you say access is that for read-only data or are you writing back to these (up to) 20 db's too? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: To maintain DB connections or not ... that is the question
Stumbled across this old (2004) article How are database connections handled in ColdFusion? at http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_18061 Doesn't say when not to maintain connections but provides a good general outline. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: To maintain DB connections or not ... that is the question
My gut feeling is that you should not enable this feature unless you know specifically why you want to use it. Have had trouble in the past with the DBMS running out of connections on busy servers with this setting enabled, as every client has a connection whether they are using it or not (eg. you will collect idle connections). The overhead in creating a connection per request is most likely low, especially when connecting locally or even over a LAN. May be interesting to gauge whether you are getting any performance benefits with this setting enabled / disabled just by setting some timers and running a few queries. On Sun, 2008-04-27 at 18:19 -0700, Nathan Drury wrote: Hi We have a CF app which can be using multiple DBs at any given time (e.g. user1 logs into DB1, user2 logs into DB2, etc). At any given time the application can be accessing up to 20 different DBs. I've been trying to determine whether we should be maintaining connections to these DBs or not. I've found very little information out there about it except that as a general rule you should maintain connections and that you should limit connections to be the number of maximum simultaneous requests (per CF8WACK2). However, a colleague claims to have had issues in the past with the app when maintain connections is turned on (but this may have been due to a known issue with CF6.1 - http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_18980sliceId=1). Can anyone shed some light on when NOT to maintain connections? Thanks Nathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: To maintain DB connections or not ... that is the question
Hi Andrew Our application is used by law firms to manage documents that form part of a litigation matter. The one single application can be used to support several litigation matters at a given time, with each litigation matter having its own DB. While a user can only ever be working on one matter at a time, there can be multiple users working on different matters at a given time, hence the one application accessing multiple DBs. We also host the application for law firms and have a lot more than 20 matters, but not all matters are being accessed all the time so I just said 20. Cheers Nathan On Apr 28, 12:17 pm, Andrew Scott [EMAIL PROTECTED] wrote: Nathan, Do you mind if I ask what sort of application it is, and why there would be 20 different DB's? Maybe because it is Monday morning, I don't see any reason for it. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd.www.aegeon.com.au Phone: +613 9015 8628 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Nathan Drury Sent: Monday, 28 April 2008 11:20 AM To: cfaussie Subject: [cfaussie] To maintain DB connections or not ... that is the question Hi We have a CF app which can be using multiple DBs at any given time (e.g. user1 logs into DB1, user2 logs into DB2, etc). At any given time the application can be accessing up to 20 different DBs. I've been trying to determine whether we should be maintaining connections to these DBs or not. I've found very little information out there about it except that as a general rule you should maintain connections and that you should limit connections to be the number of maximum simultaneous requests (per CF8WACK2). However, a colleague claims to have had issues in the past with the app when maintain connections is turned on (but this may have been due to a known issue with CF6.1 -http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_18980sl... ). Can anyone shed some light on when NOT to maintain connections? Thanks Nathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: To maintain DB connections or not ... that is the question
Nathan, are you talking about a perceived potential problem or one which you actually experiencing? maintaing db connection pools works really well in CF compared to other languages, mainly because developers normally don't have much responsibility or control, it just works unlike ASP.NET for example where connections can be left open etc and then you start to run out of sessions and the like z On Mon, Apr 28, 2008 at 1:41 PM, Nathan Drury [EMAIL PROTECTED] wrote: Hi Zac We do that. One DB user has full permissions for all the relevant DBs on the server. When a user logs in and selects a DB, we store the DB data in session and then pass the DB name and DB user to the queries. On Apr 28, 1:12 pm, Zac Spitzer [EMAIL PROTECTED] wrote: You can always create a db user which has access to multiple db's on the same db server and control access via CF and then specify the database in the SQL which would make things a lot easier to manage and would vastly reduce the number of connections required to the database server On Mon, Apr 28, 2008 at 1:00 PM, Nathan Drury [EMAIL PROTECTED] wrote: Hi Andrew Our application is used by law firms to manage documents that form part of a litigation matter. The one single application can be used to support several litigation matters at a given time, with each litigation matter having its own DB. While a user can only ever be working on one matter at a time, there can be multiple users working on different matters at a given time, hence the one application accessing multiple DBs. We also host the application for law firms and have a lot more than 20 matters, but not all matters are being accessed all the time so I just said 20. Cheers Nathan On Apr 28, 12:17 pm, Andrew Scott [EMAIL PROTECTED] wrote: Nathan, Do you mind if I ask what sort of application it is, and why there would be 20 different DB's? Maybe because it is Monday morning, I don't see any reason for it. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd.www.aegeon.com.au Phone: +613 9015 8628 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Nathan Drury Sent: Monday, 28 April 2008 11:20 AM To: cfaussie Subject: [cfaussie] To maintain DB connections or not ... that is the question Hi We have a CF app which can be using multiple DBs at any given time (e.g. user1 logs into DB1, user2 logs into DB2, etc). At any given time the application can be accessing up to 20 different DBs. I've been trying to determine whether we should be maintaining connections to these DBs or not. I've found very little information out there about it except that as a general rule you should maintain connections and that you should limit connections to be the number of maximum simultaneous requests (per CF8WACK2). However, a colleague claims to have had issues in the past with the app when maintain connections is turned on (but this may have been due to a known issue with CF6.1 -http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_18980sl... ). Can anyone shed some light on when NOT to maintain connections? Thanks Nathan -- Zac Spitzer -http://zacster.blogspot.com(My Blog) +61 405 847 168 -- Zac Spitzer - http://zacster.blogspot.com (My Blog) +61 405 847 168 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: To maintain DB connections or not ... that is the question
Zac Thanks for your responses. I'm just asking the question because I'd like to ensure that the app is performing as best it can. There are data intensive parts of the application that make several queries in a single request and it would be great if we could improve performance for these parts, hence my interest in maintaining connections. Cheers Nathan On Apr 28, 1:50 pm, Zac Spitzer [EMAIL PROTECTED] wrote: Nathan, are you talking about a perceived potential problem or one which you actually experiencing? maintaing db connection pools works really well in CF compared to other languages, mainly because developers normally don't have much responsibility or control, it just works unlike ASP.NET for example where connections can be left open etc and then you start to run out of sessions and the like z On Mon, Apr 28, 2008 at 1:41 PM, Nathan Drury [EMAIL PROTECTED] wrote: Hi Zac We do that. One DB user has full permissions for all the relevant DBs on the server. When a user logs in and selects a DB, we store the DB data in session and then pass the DB name and DB user to the queries. On Apr 28, 1:12 pm, Zac Spitzer [EMAIL PROTECTED] wrote: You can always create a db user which has access to multiple db's on the same db server and control access via CF and then specify the database in the SQL which would make things a lot easier to manage and would vastly reduce the number of connections required to the database server On Mon, Apr 28, 2008 at 1:00 PM, Nathan Drury [EMAIL PROTECTED] wrote: Hi Andrew Our application is used by law firms to manage documents that form part of a litigation matter. The one single application can be used to support several litigation matters at a given time, with each litigation matter having its own DB. While a user can only ever be working on one matter at a time, there can be multiple users working on different matters at a given time, hence the one application accessing multiple DBs. We also host the application for law firms and have a lot more than 20 matters, but not all matters are being accessed all the time so I just said 20. Cheers Nathan On Apr 28, 12:17 pm, Andrew Scott [EMAIL PROTECTED] wrote: Nathan, Do you mind if I ask what sort of application it is, and why there would be 20 different DB's? Maybe because it is Monday morning, I don't see any reason for it. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd.www.aegeon.com.au Phone: +613 9015 8628 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Nathan Drury Sent: Monday, 28 April 2008 11:20 AM To: cfaussie Subject: [cfaussie] To maintain DB connections or not ... that is the question Hi We have a CF app which can be using multiple DBs at any given time (e.g. user1 logs into DB1, user2 logs into DB2, etc). At any given time the application can be accessing up to 20 different DBs. I've been trying to determine whether we should be maintaining connections to these DBs or not. I've found very little information out there about it except that as a general rule you should maintain connections and that you should limit connections to be the number of maximum simultaneous requests (per CF8WACK2). However, a colleague claims to have had issues in the past with the app when maintain connections is turned on (but this may have been due to a known issue with CF6.1 -http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_18980sl... ). Can anyone shed some light on when NOT to maintain connections? Thanks Nathan -- Zac Spitzer -http://zacster.blogspot.com(MyBlog) +61 405 847 168 -- Zac Spitzer -http://zacster.blogspot.com(My Blog) +61 405 847 168 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: To maintain DB connections or not ... that is the question
if you want to test it out, do some load testing with Jmeter, there's a quck start on my blog http://zacster.blogspot.com/2008/03/quick-howto-to-setup-jmeter.html it's perversely fun to break things with load testing :) anything which is data intensive can always be sped up with caching or pre-cooking using Materialized views which i think are called Indexed views in SQL Server z On Mon, Apr 28, 2008 at 1:59 PM, Nathan Drury [EMAIL PROTECTED] wrote: Zac Thanks for your responses. I'm just asking the question because I'd like to ensure that the app is performing as best it can. There are data intensive parts of the application that make several queries in a single request and it would be great if we could improve performance for these parts, hence my interest in maintaining connections. Cheers Nathan On Apr 28, 1:50 pm, Zac Spitzer [EMAIL PROTECTED] wrote: Nathan, are you talking about a perceived potential problem or one which you actually experiencing? maintaing db connection pools works really well in CF compared to other languages, mainly because developers normally don't have much responsibility or control, it just works unlike ASP.NET for example where connections can be left open etc and then you start to run out of sessions and the like z On Mon, Apr 28, 2008 at 1:41 PM, Nathan Drury [EMAIL PROTECTED] wrote: Hi Zac We do that. One DB user has full permissions for all the relevant DBs on the server. When a user logs in and selects a DB, we store the DB data in session and then pass the DB name and DB user to the queries. On Apr 28, 1:12 pm, Zac Spitzer [EMAIL PROTECTED] wrote: You can always create a db user which has access to multiple db's on the same db server and control access via CF and then specify the database in the SQL which would make things a lot easier to manage and would vastly reduce the number of connections required to the database server On Mon, Apr 28, 2008 at 1:00 PM, Nathan Drury [EMAIL PROTECTED] wrote: Hi Andrew Our application is used by law firms to manage documents that form part of a litigation matter. The one single application can be used to support several litigation matters at a given time, with each litigation matter having its own DB. While a user can only ever be working on one matter at a time, there can be multiple users working on different matters at a given time, hence the one application accessing multiple DBs. We also host the application for law firms and have a lot more than 20 matters, but not all matters are being accessed all the time so I just said 20. Cheers Nathan On Apr 28, 12:17 pm, Andrew Scott [EMAIL PROTECTED] wrote: Nathan, Do you mind if I ask what sort of application it is, and why there would be 20 different DB's? Maybe because it is Monday morning, I don't see any reason for it. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd.www.aegeon.com.au Phone: +613 9015 8628 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Nathan Drury Sent: Monday, 28 April 2008 11:20 AM To: cfaussie Subject: [cfaussie] To maintain DB connections or not ... that is the question Hi We have a CF app which can be using multiple DBs at any given time (e.g. user1 logs into DB1, user2 logs into DB2, etc). At any given time the application can be accessing up to 20 different DBs. I've been trying to determine whether we should be maintaining connections to these DBs or not. I've found very little information out there about it except that as a general rule you should maintain connections and that you should limit connections to be the number of maximum simultaneous requests (per CF8WACK2). However, a colleague claims to have had issues in the past with the app when maintain connections is turned on (but this may have been due to a known issue with CF6.1 -http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_18980sl... ). Can anyone shed some light on when NOT to maintain connections? Thanks Nathan -- Zac Spitzer -http://zacster.blogspot.com(MyBlog) +61 405 847 168 -- Zac Spitzer -http://zacster.blogspot.com(My Blog) +61 405 847 168 -- Zac Spitzer - http://zacster.blogspot.com (My Blog) +61 405 847 168 --~--~-~--~~~---~--~~ You received this message because you are subscribed
