Perl dbd::mysql and child process

Fredde87 · 05-23-2008, 04:24 AM

Hi,

I am still in the early stages of learning Perl. I am having a bit of a play using a mysql databse in Perl.

I have a infinite loop which receives commands on a socket and then I call various functions and child process to process the data received. When I open the connection to the mysql database, is it better to open it at the start of the script and keep it running all the time? Or is it a better practice to open it just before I execute my query, close it and then reopen it when I need it again?

I guess I would have to open a new connection for each thread though wouldnt I as the connection can not be shared?

Thanks!

derzok · 05-23-2008, 08:01 AM

Depends on how many processes you intend on having and how often they'll occur. I have an IRC bot that keeps statistics on everything said in a chatroom. When it's in one channel without much traffic, the single connection will time out when no queries have been sent for a while (though you can change this).

So if it's receiving info on a slow time table where it might time out, connect to the database in every process. If it's going to be in constant use (like when my bot is in 14 channels) you should consider a shared connection. Especially if you've got more than one child running at a time. mysql can handle that many connections, but I'm sure you'd have better speed on a single connection.

chrism01 · 05-24-2008, 07:26 PM

Actually, last time I checked, the DBI, DBD code was not officially thread-safe, so in my threaded prog I had a separate cxn in each thr. Worked a treat.
As mentioned, if there's a long enough break between SQL statements (in a given cxn) it'll timeout.
In MYSQL use

show variables;

the last line looks like this

| wait_timeout | 28800

that's in seconds. You amend that in the my.conf file, or catch the SQL (which you should be doing anyway) and check for the explicit error:

Code:

         # Check if real SQL error or DB gone away
        if( $cfg::dbh->errstr =~ /MySQL server has gone away/ )
        {
            # Try to re-connect for later
            $error_msg = "db_update_active(): ".localtime()." ".
                                                $cfg::dbh->errstr;
            db_connect();
        }