Hi Stacy, On Wed, 2007-05-30 at 11:20 -0500, Zacher, Stacy wrote: > Hello Syed and users: > > I took a look at these files Syed mentioned below but I couldn't > determine where they would tell me about how BioMart connects to MySQL. > Could you please elaborate on this? We are running BioMart version 0.4. > The issue I am seeing from a database perspective is that we are hitting > the default max_connections for MySQL. The default is 100 (for MySQL > version 4.x). I changed this to 150 and then to 200 maximum connections > (I also changed the table_cache to a higher value than the default). > Each day I am seeing user connections in the database at the threshold > limit of 200 or almost 200 users. So it seems no matter what I change > this number to thus far, the connections seem to reach the max, all show > as "sleeping", and seem to be disconnected after 28800 seconds or 8 > hours which is the default max connection time (wait_timeout) for MySQL. > > > My question here is: > > How does BioMart handle connections to a MySQL database and ,more > importantly, clean up the connection after the user is finished with the > BioMart?
The connections are handled using CPAN DBI module. > I am wondering if we just have old connections that are not really > active and not being cleaned up appropriately. I am sure I could set the > MySQL connection time limit (wait_timeout) to some other number lower > than 8 hours but I'm trying to determine if > > A.) BioMart or the web server is not cleaning up the user connections > appropriately > B.) The connections are really valid and users have just left their > sessions opened and cached this information. > or > C.) Both > I have run a quick test on our MySql server version: 4.1.21-standard, and connection requests are being closed correctly. I am using BioMart V 0.5. The connection handling is done the same way for Query Executions. However the connection to database to retrieve the XMLs at the configuration time (installation procedure) are not closed properly which BioMart team has fixed from 0.5 onwards. > Any ideas would be greatly appreciated as far as how to tweak MySQL for > BioMart. We are running BioMart version 0.4. > > Thank you for your help. Cheers Syed > Stacy Zacher > Database Administrator > Medical College of Wisconsin > Bioinformatics Program > Email: [EMAIL PROTECTED] > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Syed Haider > Sent: Tuesday, May 29, 2007 11:21 AM > To: Vallejos, Andrew > Cc: mart-dev > Subject: Re: [mart-dev] BioMart Database Interface > > Hi Andrew > The files you may be interested in are: > > biomart-perl/lib/BioMart/Configuration/MartDBLocation.pm > biomart-perl/lib/BioMart/Configuration/DBLocation.pm > biomart-perl/lib/BioMart/Configuration/Location.pm > > Could you please write in more detail about the issue. We never had this > kind of issue on our central server. May be its something else. > > Cheers > > Syed > > > On Tue, 2007-05-29 at 11:02 -0500, Vallejos, Andrew wrote: > > > > How does BioMart handle the db connections? > -- > ====================================== > Syed Haider. > EMBL-European Bioinformatics Institute > Wellcome Trust Genome Campus, Hinxton, > Cambridge CB10 1SD, UK. > ====================================== > -- ====================================== Syed Haider. EMBL-European Bioinformatics Institute Wellcome Trust Genome Campus, Hinxton, Cambridge CB10 1SD, UK. ======================================
