Massyn has asked for the wisdom of the Perl Monks concerning the following question:

#!/fellow/monks.pl

I'm spending a lot of time with mySQL, Oracle, and DBI. You'll probably notice that from most of the questions I ask.

I'm busy with a web based system, which currently works fine with CGI, however, for performance sake, I'd like to use mod_perl, which is my next step. I got mod_perl installed, compiled and running on my Mandrake 9.1 box with Apache 1.3.27.

Now to the question : It was said that database connections and authentications are very expensive in terms of performance. To solve the problem, you need to make a persistent connection within mod_perl. I'm very new to mod_perl, so I'd like to have some info on particularly the persistent DBI connections.

I had a look on http://perl.apache.org, but I didn't see anything about persistent connections.

If any of you could point me to some doco's, that will be a great help.

Cheers!

#!/massyn.pl

Don't -- me without telling me why. If you asked a stupid question, I wouldn't down vote you.

Replies are listed 'Best First'.
Re: Persistent DBI connection with mySQL
by PodMaster (Abbot) on Jul 18, 2003 at 10:49 UTC
    Apache::DBI

    MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"
    I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).
    ** The third rule of perl club is a statement of fact: pod is sexy.

[reply]
Re: Persistent DBI connection with mySQL
by Massyn (Hermit) on Jul 18, 2003 at 10:50 UTC
[reply]
Re: Persistent DBI connection with mySQL
by dga (Hermit) on Jul 18, 2003 at 15:26 UTC

    As mentioned already, Apache::DBI.

    There is also connect_cached() via the standard DBI.

    This allows you to select which connections that you want to be persistant (connect_cached)and which you don't (connect).

    The DBI keeps a hash of the connection options and reuses the open connection if the options are identical.

    I use this on my mod_perl sites, so that if I access a database very rarely, or with unusual options, I can single it out not to persist and yet get all the benefits of the ones that are used frequently, persisting. One example of this is a report which takes a while to run anyway since it's big and thus the connection overhead is not noticable and is only run every once in a while. If I kept the connection around and the child process lasted long enough for another call to the report, the likelihood that the same child would get the request a week from now is remote at best, so a new connection would be made anyway. Thus I go ahead and toss the connection as soon as the page is sent to the user.

    There is also prepare_cached but this reqiures some forethought as you are reusung a statement handle and you need to be aware of the effect of context of calling these from different subs for example. This is handy where the query plan takes a while to generate and you need to do the same query frequently.

[reply]
[d/l]
[select]
Re: Persistent DBI connection with mySQL
by antirice (Priest) on Jul 18, 2003 at 10:48 UTC

    Apache::DBI. All you need to do is add use Apache::DBI; before use DBI;.

    Update: Hmmm...appears I don't remember how to use Apache::DBI.

    antirice    
    The first rule of Perl club is - use Perl
    The     ith rule of Perl club is - follow rule i - 1 for i > 1

[reply]
[d/l]
[select]
      No no no no no. `perldoc Apache::DBI': 
      
NAME
    Apache::DBI - Initiate a persistent database connection

SYNOPSIS
     # Configuration in httpd.conf or startup.pl:

     PerlModule Apache::DBI  # this comes before all other modules using DBI

    Do NOT change anything in your scripts. The usage of this module is
    absolutely transparent !

      MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"
      I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).
      ** The third rule of perl club is a statement of fact: pod is sexy.

[reply]

Back to Seekers of Perl Wisdom