Re: [Catalyst] How to share a database connection across multiple Catalyst apps?
I guess the DBA's don't like it because Oracle starts one process per connection unless you use connection pooling. I think that's a listener setting, your DBA's should know. -- Best regards, Alex Am Mittwoch, den 08.09.2010, 16:54 +0200 schrieb Simon Miner: Thanks for the responses, Jason, I don't think reducing the number of database connections will hurt responsiveness. Even though there are 3 separate Catalyst apps, each HTTP request will only involve one of them (since they all run out of the same web server), so there should never be contention between the apps for the shared database connection. Does this make sense? Tom and Nicholas, I wish this was premature optimization, but my organization has experienced serious performance issues in the past related to multiple (Oracle) database connections per HTTP server process. Extra Oracle connections are expensive to maintain, particularly on the database server, and so we've tried to ensure that a single server process only has a single connection to a given database (since that's all it should need at any given time). I've attached a Devel::NYTProf screenshot for one of the server processes I profiled this morning. Looks like it confirms the multiple database connections. So back to initial question -- How do I update the models of my three Catalyst apps to share a single Oracle database connection? Thanks again. I really appreciate your feedback. Simon On Tue, Sep 7, 2010 at 11:40 PM, Nicholas Wehr catal...@bionikchickens.com wrote: I'm with Tom on this one. Unless you've narrowed all optimization efforts and this is all you have left - it could be worth a try.. but as Jason points out, you may not gain a thing. I'd recommend profiling your code and tracking down performance issues from that base level. Please post your results - I've very curious! -nw On Tue, Sep 7, 2010 at 7:21 PM, Tomas Doran bobtf...@bobtfish.net wrote: On 7 Sep 2010, at 18:59, Simon Miner wrote: All three of these apps run under a single Apache 1.3.42/mod_perl 1.31 server. Wow, mod_perl 1 Ok then :) It appears that each server process creates a unique database connection variable for each of these apps. Although these database connections get reused from request to request, I would like to make things even more efficient by having a single database connection variable per server process which gets shared across all 3 Catalyst apps. Why do you think that this will help or affect anything? I.e. is this not premature optimisation? Cheers t0m ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ -- -- Simon *** T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien Handelsgericht Wien, FN 79340b *** Notice: This e-mail contains information that is confidential and may be privileged. If you are not the intended recipient, please notify the sender and then delete this e-mail immediately. *** ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] How to share a database connection across multiple Catalyst apps?
Thanks for the responses, Jason, I don't think reducing the number of database connections will hurt responsiveness. Even though there are 3 separate Catalyst apps, each HTTP request will only involve one of them (since they all run out of the same web server), so there should never be contention between the apps for the shared database connection. Does this make sense? Tom and Nicholas, I wish this was premature optimization, but my organization has experienced serious performance issues in the past related to multiple (Oracle) database connections per HTTP server process. Extra Oracle connections are expensive to maintain, particularly on the database server, and so we've tried to ensure that a single server process only has a single connection to a given database (since that's all it should need at any given time). I've attached a Devel::NYTProf screenshot for one of the server processes I profiled this morning. Looks like it confirms the multiple database connections. So back to initial question -- How do I update the models of my three Catalyst apps to share a single Oracle database connection? Thanks again. I really appreciate your feedback. Simon On Tue, Sep 7, 2010 at 11:40 PM, Nicholas Wehr catal...@bionikchickens.comwrote: I'm with Tom on this one. Unless you've narrowed all optimization efforts and this is all you have left - it could be worth a try.. but as Jason points out, you may not gain a thing. I'd recommend profiling your code and tracking down performance issues from that base level. Please post your results - I've very curious! -nw On Tue, Sep 7, 2010 at 7:21 PM, Tomas Doran bobtf...@bobtfish.net wrote: On 7 Sep 2010, at 18:59, Simon Miner wrote: All three of these apps run under a single Apache 1.3.42/mod_perl 1.31 server. Wow, mod_perl 1 Ok then :) It appears that each server process creates a unique database connection variable for each of these apps. Although these database connections get reused from request to request, I would like to make things even more efficient by having a single database connection variable per server process which gets shared across all 3 Catalyst apps. Why do you think that this will help or affect anything? I.e. is this not premature optimisation? Cheers t0m ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ -- -- Simon attachment: nytprof.gif___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] How to share a database connection across multiple Catalyst apps?
Sounds like a job for DBD::Proxy or DBD::Gofer, not that I've ever used them, and I have no idea whether they would play nice with DBIC -- the DBIC folks would have a better grasp on that question. That would leave the Catalyst parts unchanged apart from configuration, which would be a good thing architecturally. --S Stuart Watt ARM Product Developer Information Balance On 9/8/2010 10:54 AM, Simon Miner wrote: Thanks for the responses, Jason, I don't think reducing the number of database connections will hurt responsiveness. Even though there are 3 separate Catalyst apps, each HTTP request will only involve one of them (since they all run out of the same web server), so there should never be contention between the apps for the shared database connection. Does this make sense? Tom and Nicholas, I wish this was premature optimization, but my organization has experienced serious performance issues in the past related to multiple (Oracle) database connections per HTTP server process. Extra Oracle connections are expensive to maintain, particularly on the database server, and so we've tried to ensure that a single server process only has a single connection to a given database (since that's all it should need at any given time). I've attached a Devel::NYTProf screenshot for one of the server processes I profiled this morning. Looks like it confirms the multiple database connections. So back to initial question -- How do I update the models of my three Catalyst apps to share a single Oracle database connection? Thanks again. I really appreciate your feedback. Simon On Tue, Sep 7, 2010 at 11:40 PM, Nicholas Wehr catal...@bionikchickens.com mailto:catal...@bionikchickens.com wrote: I'm with Tom on this one. Unless you've narrowed all optimization efforts and this is all you have left - it could be worth a try.. but as Jason points out, you may not gain a thing. I'd recommend profiling your code and tracking down performance issues from that base level. Please post your results - I've very curious! -nw On Tue, Sep 7, 2010 at 7:21 PM, Tomas Doran bobtf...@bobtfish.net mailto:bobtf...@bobtfish.net wrote: On 7 Sep 2010, at 18:59, Simon Miner wrote: All three of these apps run under a single Apache 1.3.42/mod_perl 1.31 server. Wow, mod_perl 1 Ok then :) It appears that each server process creates a unique database connection variable for each of these apps. Although these database connections get reused from request to request, I would like to make things even more efficient by having a single database connection variable per server process which gets shared across all 3 Catalyst apps. Why do you think that this will help or affect anything? I.e. is this not premature optimisation? Cheers t0m ___ List: Catalyst@lists.scsys.co.uk mailto:Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk mailto:Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ -- -- Simon -- This message was scanned by ESVA and is believed to be clean. Click here to report this message as spam. http://antispam.infobal.com/cgi-bin/learn-msg.cgi?id=7897728072.B3B1D ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] How to share a database connection across multiple Catalyst apps?
(Hopefully I'm not out of my depth here) I feel your pain WRT Oracle connections as we've had similar complaints from our DBA overlords about the number of connections our app was making here at $work. Do you have your mod_perl processes behind some sort of proxy? If you run your mod_perl application in the same apache instance that serves static content any child that is spawned, even if it's just for serving HTML or an image file, will be fully functional (including having a database connection for each catalyst app that is hosted on that server). You might consider changing how your application is deployed and use FastCGI (or mod_perl behind a proxy) so that it's easier to control the number of processes (and therefore database connections) you spin up. On Wed, Sep 8, 2010 at 10:46 AM, Stuart Watt sw...@infobal.com wrote: Sounds like a job for DBD::Proxy or DBD::Gofer, not that I've ever used them, and I have no idea whether they would play nice with DBIC -- the DBIC folks would have a better grasp on that question. That would leave the Catalyst parts unchanged apart from configuration, which would be a good thing architecturally. --S Stuart Watt ARM Product Developer Information Balance On 9/8/2010 10:54 AM, Simon Miner wrote: Thanks for the responses, Jason, I don't think reducing the number of database connections will hurt responsiveness. Even though there are 3 separate Catalyst apps, each HTTP request will only involve one of them (since they all run out of the same web server), so there should never be contention between the apps for the shared database connection. Does this make sense? Tom and Nicholas, I wish this was premature optimization, but my organization has experienced serious performance issues in the past related to multiple (Oracle) database connections per HTTP server process. Extra Oracle connections are expensive to maintain, particularly on the database server, and so we've tried to ensure that a single server process only has a single connection to a given database (since that's all it should need at any given time). I've attached a Devel::NYTProf screenshot for one of the server processes I profiled this morning. Looks like it confirms the multiple database connections. So back to initial question -- How do I update the models of my three Catalyst apps to share a single Oracle database connection? Thanks again. I really appreciate your feedback. Simon On Tue, Sep 7, 2010 at 11:40 PM, Nicholas Wehr catal...@bionikchickens.com wrote: I'm with Tom on this one. Unless you've narrowed all optimization efforts and this is all you have left - it could be worth a try.. but as Jason points out, you may not gain a thing. I'd recommend profiling your code and tracking down performance issues from that base level. Please post your results - I've very curious! -nw On Tue, Sep 7, 2010 at 7:21 PM, Tomas Doran bobtf...@bobtfish.netwrote: On 7 Sep 2010, at 18:59, Simon Miner wrote: All three of these apps run under a single Apache 1.3.42/mod_perl 1.31 server. Wow, mod_perl 1 Ok then :) It appears that each server process creates a unique database connection variable for each of these apps. Although these database connections get reused from request to request, I would like to make things even more efficient by having a single database connection variable per server process which gets shared across all 3 Catalyst apps. Why do you think that this will help or affect anything? I.e. is this not premature optimisation? Cheers t0m ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ -- -- Simon -- This message was scanned by ESVA and is believed to be clean. Click here to report this message as spam.http://antispam.infobal.com/cgi-bin/learn-msg.cgi?id=7897728072.B3B1D ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk Listinfo:
Re: [Catalyst] How to share a database connection across multiple Catalyst apps?
On 8 Sep 2010, at 19:17, Javier Arturo Rodriguez wrote: On 9/8/10 4:54 PM, Simon Miner wrote: The parameters defining the connection have to be exactly the same, including the connect attributes! If there is no appropriate database handle or if the ping method fails, a new connection is established and the handle is stored for later re-use. I agree that this is not early optimization -- just good housekeeping. Oracle SQL Net connections are indeed expensive to set up but tend to consume near-zero resources when idle, so I would not worry too much about connection reuse among apps just yet - in my book this is more of a sysadmin issue. I'm entirely unsure which side you're arguing for here. Oracle connections are expensive _to make_, but given you spend one per process per app (and your app serves just the dynamic content), who cares? I'd consider being able to treat my applications as entirely disparate disconnected entities as a good feature (especially as one app is able to say things which affect the DB connection wide - if connections were shared between apps this could be more than a single app issue)... So, in summary - yes, reconnecting per request bad, but you're using DBIC anyway - so that's not what's happening.. Reconnecting once for each app per process, fine - stop stressing about it.. Cheers t0m ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
[Catalyst] How to share a database connection across multiple Catalyst apps?
Hi, I am running 3 separate Catalyst (version '5.80022) apps that use a common (Oracle 11g) database. Each app has a model class which wraps around a common DBIC schema class, like so. - package App1::Model::DB; use strict; use base 'Catalyst::Model::DBIC::Schema'; __PACKAGE__-config( schema_class = Common::DB::Schema', ); 1; - package App2::Model::DB; use strict; use base 'Catalyst::Model::DBIC::Schema'; __PACKAGE__-config( schema_class = Common::DB::Schema', ); 1; - package App3::Model::DB; use strict; use base 'Catalyst::Model::DBIC::Schema'; __PACKAGE__-config( schema_class = Common::DB::Schema', ); 1; - This setup passes changes to the Common::DB::Schema class on to the Catalyst app model classes. All three of these apps run under a single Apache 1.3.42/mod_perl 1.31 server. It appears that each server process creates a unique database connection variable for each of these apps. Although these database connections get reused from request to request, I would like to make things even more efficient by having a single database connection variable per server process which gets shared across all 3 Catalyst apps. How do I update my model to accomplish this? Thanks. -- -- Simon ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] How to share a database connection across multiple Catalyst apps?
Hi Simon, Reducing the number of connections to the database from three to one will reduce the responsiveness and will definitely hurt when trying to debug issues (performance, etc). My recommendation is that each application have at least one unique connection to the database. jason On 09/07/2010 12:59 PM, Simon Miner wrote: Hi, I am running 3 separate Catalyst (version '5.80022) apps that use a common (Oracle 11g) database. Each app has a model class which wraps around a common DBIC schema class, like so. - package App1::Model::DB; use strict; use base 'Catalyst::Model::DBIC::Schema'; __PACKAGE__-config( schema_class = Common::DB::Schema', ); 1; - package App2::Model::DB; use strict; use base 'Catalyst::Model::DBIC::Schema'; __PACKAGE__-config( schema_class = Common::DB::Schema', ); 1; - package App3::Model::DB; use strict; use base 'Catalyst::Model::DBIC::Schema'; __PACKAGE__-config( schema_class = Common::DB::Schema', ); 1; - This setup passes changes to the Common::DB::Schema class on to the Catalyst app model classes. All three of these apps run under a single Apache 1.3.42/mod_perl 1.31 server. It appears that each server process creates a unique database connection variable for each of these apps. Although these database connections get reused from request to request, I would like to make things even more efficient by having a single database connection variable per server process which gets shared across all 3 Catalyst apps. How do I update my model to accomplish this? Thanks. -- -- Simon ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] How to share a database connection across multiple Catalyst apps?
I'm with Tom on this one. Unless you've narrowed all optimization efforts and this is all you have left - it could be worth a try.. but as Jason points out, you may not gain a thing. I'd recommend profiling your code and tracking down performance issues from that base level. Please post your results - I've very curious! -nw On Tue, Sep 7, 2010 at 7:21 PM, Tomas Doran bobtf...@bobtfish.net wrote: On 7 Sep 2010, at 18:59, Simon Miner wrote: All three of these apps run under a single Apache 1.3.42/mod_perl 1.31 server. Wow, mod_perl 1 Ok then :) It appears that each server process creates a unique database connection variable for each of these apps. Although these database connections get reused from request to request, I would like to make things even more efficient by having a single database connection variable per server process which gets shared across all 3 Catalyst apps. Why do you think that this will help or affect anything? I.e. is this not premature optimisation? Cheers t0m ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
