[Bioperl-pipeline] database connections

[Shawn Hoon]

There is a configuration parameter in mySQL that limits the number of 
connections that it can
make to the database. This may be set when one starts up mysql with  
mysqld. Default is 100, way too low
for Biopipe.  500-1000 mysql connections is recommended by mySQL for 
Linux systems.
But we find that very often with many jobs in Biopipe, we nevertheless 
will encounter such errors.
But Biopipe takes care of these jobs and they eventually get retried 
and finished. I have a feeling we can still optimize stuff better
but the quick solution right now is to first check u have enough 
connections allowed and
  destroy connections once things are fetched via the 
release_dbconnection parameter.

http://www.mysql.com/doc/en/Too_many_connections.html

cheers,

shawn


On Tuesday, March 18, 2003, at 11:47 PM, Brian Osborne wrote:

> Shawn,
>
>> Firstly make sure the number of database connections allowed are high
> enough
>
> What did you mean by this? It has to be more explicit for the FAQ.
>
> Thanks again,
>
> Brian O.
>
> -----Original Message-----
> From: bioperl-pipeline-bounces at bioperl.org
> [mailto:bioperl-pipeline-bounces at bioperl.org]On Behalf Of Shawn Hoon
> Sent: Saturday, March 15, 2003 9:36 PM
> To: jeremyp at sgx3.bmb.uga.edu
> Cc: bioperl-pipeline at bioperl.org
> Subject: Re: [Bioperl-pipeline] database connections
>
>
>> Hi,
>>
>> When I run the pipeline software, if all of the nodes on our cluster
>> are
>> utilized, I start seeing database connection errors. The errors
>> basically
>> indicate that a connection to the database could not be made because
>> too
>> many connections are already open. The modules I have written do use
>> the
>> database server, but I am calling disconnect in each as soon as the
>> data
>> they need has been retrieved - so I don't think this is the problem. I
>> looked at the PipeConf file on the cvs server and I noticed this:
>>
>
> Yes we face this problem a lot.  Firstly make sure the number
> of database connections allowed are high enough. The trouble with
> IOHandlers  is that once DBI gets the database
> handle in runner.pl, it persist for the duration of the script.  Thus
> while the job is running,
> the connection is sleeping. Not very efficient. So what the
> RELEAST_DBCONNECTION does
> below is very simple. In Bio::Pipeline::IOHandler::fetch_input, it
> simply disconnects the connection:
> explicitly once it has fetched the input.
>
>     #destroy handle only if its a dbhandle
>      if($self->adaptor_type eq "DB" && $RELEASE_DBCONNECTION) {
>        $tmp->DESTROY;
>      };
> I have found this to reduce the problem quite dramatically. Give it a
> try and let me know how
> it works.
>
> The caveat is that your object cannot be 'lazy', meaning it needs to
> maintain
> the db connection and fetch the data only when called by the runnable.
> A quick workaround is to
> modify the Adaptor calls to have a option not to be lazy..
>
>> Is this perhaps the solution to my problem? Also, I am using an older
>> version of bioperl pipeline (from December), so I'd have to upgrade to
>> use
>> this it seems (I already plan to upgrade, but I may upgrade sooner if
>> this
>> is the case). Thanks.
>>
> Great to know its working for you so far :)
>
> shawn
>
>> Jeremy
>>
>>
>> _______________________________________________
>> bioperl-pipeline mailing list
>> bioperl-pipeline at bioperl.org
>> http://bioperl.org/mailman/listinfo/bioperl-pipeline
>>
>
> _______________________________________________
> bioperl-pipeline mailing list
> bioperl-pipeline at bioperl.org
> http://bioperl.org/mailman/listinfo/bioperl-pipeline
>
>