FAQ
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20100907/c966cb57/attachment.htm

Search Discussions

  • Jason L. Froebe at Sep 8, 2010 at 2:02 am
    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/
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20100907/3f769c60/attachment.htm
  • Tomas Doran at Sep 8, 2010 at 2:20 am

    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
  • Nicholas Wehr at Sep 8, 2010 at 3:40 am
    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 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/
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20100907/02ebadc6/attachment.htm
  • Simon Miner at Sep 8, 2010 at 2:54 pm
    Skipped content of type multipart/alternative-------------- next part --------------
    A non-text attachment was scrubbed...
    Name: nytprof.gif
    Type: image/gif
    Size: 21346 bytes
    Desc: not available
    Url : http://lists.scsys.co.uk/pipermail/catalyst/attachments/20100908/93e8f80f/nytprof.gif
  • Stuart Watt at Sep 8, 2010 at 3:46 pm
    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
    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 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

    --
    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/
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20100908/78f55618/attachment.htm
  • Brian Phillips at Sep 8, 2010 at 5:34 pm
    (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 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 <
    catalyst@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 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

    --
    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/
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20100908/0737792d/attachment.htm
  • Javier Arturo Rodriguez at Sep 8, 2010 at 6:15 pm

    On 9/8/10 4:54 PM, 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?
    Sounds like a job for Apache::DBI -- it will cache one connection per
    process and hand it over to the next app that requests it as long as
    it's valid -- but keep in mind that the connection attributes MUST be
    the same for all three Catalyst apps for the magic to happen. From the
    perldoc:
    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.
    Thanks again. I really appreciate your feedback.

    Simon
    Hope this helps,

    -Javier Arturo Rodriguez
    On Tue, Sep 7, 2010 at 11:40 PM, Nicholas Wehr
    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 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


    _______________________________________________
    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/
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20100908/e6df4bc8/attachment.htm
  • Tomas Doran at Sep 9, 2010 at 1:15 am

    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
  • Alexander Hartmaier at Sep 15, 2010 at 3:52 pm
    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
    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
    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.
    *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcatalyst @
categoriescatalyst, perl
postedSep 7, '10 at 5:59p
activeSep 15, '10 at 3:52p
posts10
users8
websitecatalystframework.org
irc#catalyst

People

Translate

site design / logo © 2021 Grokbase