DBD::mysql connections

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

Dear all

Starting a new thread from this one .

Wehn I originally wrote my code, I opened a DBD::mysql connection at the start of the script, and closed it at the end of the script. This worked just fine.

But then someday I updated perl, and I think my OS (from RH7.3 to RH9) and the DBD::mysql connection would consistently close prematurely.

I read in a user-group something along the lines of the instability of the connection, and how you need to reduce the 'time' between open and close

So I re-wrote my program so that it opened and closed every time it wanted to do something, and it works just fine.

But now, after having profiled my script, it's obvious that it takes up most of the time in my script!! (%10)

So my first step in optimisation is to reduce the number of times a new DBD::mysql connection is opened and closed, but I can't remember what the original problem was, and if the connection is unstable after one request, is it possible to do anything about this?

Here's an example of one function in my DB module:

sub getNullEndIds{
    my $self = shift;
    my $dbh = DBI->connect($dbi,"monster_mirror","m1rr0r",{'RaiseError
+' => 1});
    my $sth = $dbh->prepare("SELECT id from high where end IS NULL");
    $sth->execute();
    my @id;
    while(my @dummy = $sth->fetchrow_array()){
        push(@id,$dummy[0]);
    }
    $sth->finish();
    $dbh->disconnect();
    return @id;
}
[download]

Every function just simply opens and closes WITHIN the function. My original code just opened a handle for the whole module, accessible from every function, but this was unstable

Cheers
Sam

Comment on DBD::mysql connections Download Code

Replies are listed 'Best First'.
Re: DBD::mysql connections by Joost (Canon) on Jun 17, 2004 at 15:18 UTC
If you can assume that these database methods run queries often enough to keep the dbh open (as you are doing in you example), you can also try this: `sub getNullEndIds{ my $self = shift; my $dbh = shift; $dbh = DBI->connect($dbi,"monster_mirror","m1rr0r",{'RaiseError' = +> 1}) unless $dbh->ping; # rest of method # don't close the dbh at the end of this method }` [download] And check the DBI docs for the `ping()` method. Actually I'd wrap the `DBI->connect` call in a seperate sub, so you don't have to put your connection info all over the place. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l] [select]
Re^2: DBD::mysql connections by seaver (Pilgrim) on Jun 17, 2004 at 15:25 UTC
fantastic, only create a connection if it's failed, good call. But you highlighted something: If you can assume that these database methods run queries often enough is there then, basically, a time limit to how long an idle connection stays open? and can that time limit be modified? Cheers Sam	[reply]
Re^3: DBD::mysql connections by shemp (Deacon) on Jun 17, 2004 at 16:30 UTC
If you're using MySQL, check out the variables wait_timeout and interactive_timeout query: 'show variables' to see the current values. (they are in seconds). I think my default wait_timeout is 15 minutes.	[reply]
Re^4: DBD::mysql connections by seaver (Pilgrim) on Jun 17, 2004 at 18:11 UTC
Re: DBD::mysql connections by hmerrill (Friar) on Jun 17, 2004 at 18:20 UTC
How long does it normally take this script to run? I've never run into a database connection timeout situation before. If you can, briefly describe what this script did before you changed it to connect every time you call a function - did it connect, then do lots of database stuff, and then close the connection, or are there gaps of time in between when there is no database interaction? Seems like an enormous waste of time to connect (and disconnect) to the database in every function!! There's got to be a way around that. The 'ping' solution is a step in the right direction, and that may be the best solution. Ping is designed to be very fast and efficient - you should only need to re-connect if ping is unsuccessful. Also, as someone else already pointed out, it's very inefficient and wasteful to put the db connection parameters in more than one place. You should create a myConnect (or whatever) subroutine and in that subroutine do your dbi connect and return the resulting database handle ($dbh) to the caller. In that subroutine put the connection parameters so that you have them just in one place. That way if your connection parameters change, you only need to change them in one place. HTH.	[reply]
Re: DBD::mysql connections by Anonymous Monk on Jun 17, 2004 at 14:58 UTC
What about Apache::DBI and its persistent connections? Have you tried it?	[reply]
Re^2: DBD::mysql connections by edan (Curate) on Jun 17, 2004 at 15:05 UTC
Where did the OP say that the script in question is a CGI script? There are other scripts in the world, you know. -- edan	[reply]
Re^3: DBD::mysql connections by seaver (Pilgrim) on Jun 17, 2004 at 15:09 UTC
Exactly, it is in fact, NOT a CGI script. Cheers Sam	[reply]
Re^3: DBD::mysql connections by Anonymous Monk on Jun 18, 2004 at 02:25 UTC
So what does Apache::DBI have to do with CGI? Sure Apache::DBI is specific to mod_perl, but the strategy applies to the OPs question.	[reply]