[Bioperl-pipeline] database connections

[Shawn Hoon]

On Wednesday, March 19, 2003, at 01:09 AM, Brian Osborne wrote:

> Shawn,
>
> That's what I thought. I'll put this into faq.xml.
>
> So make release_dbconnection = 1 in PipeConf?
>
Ok but make sure we flag that the caveat is that if your sequences are 
for example
  ensembl contigs which have objects that are lazy-fetched this will not 
work and crash your pipeline.


shawn

> Brian O.
>
> -----Original Message-----
> From: Shawn Hoon [mailto:shawnh at fugu-sg.org]
> Sent: Tuesday, March 18, 2003 11:10 AM
> To: Brian Osborne
> Cc: jeremyp at sgx3.bmb.uga.edu; bioperl-pipeline at bioperl.org
> Subject: Re: [Bioperl-pipeline] database connections
>
> 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
>>
>>
>
>
> _______________________________________________
> bioperl-pipeline mailing list
> bioperl-pipeline at bioperl.org
> http://bioperl.org/mailman/listinfo/bioperl-pipeline
>