[ensembl-dev] EFG Array Mapping inconsistent performance

Ewan Birney birney at ebi.ac.uk
Wed Aug 25 11:17:16 BST 2010 

Are you sure it is database contention as well? Sometimes it is where  
the disks for
temporary files in the analysis are, and disk I/O contention away from  
teh database.

On 25 Aug 2010, at 11:09, Andy Yates wrote:

> Hi Gavin,
>
> The thing to do is to keep on reducing your times until you get to  
> the root of the problem. Going for 10 seconds is normally a good  
> place to start but may not be the root cause. After all 1 query  
> running for 10 seconds is better than 100 queries running @ 1 second  
> per query.
>
> I'm not sure if any of the ones you've shown here are the problem. I  
> admit that there are some odd times in there like the first one  
> taking 96 seconds to examine 67 million rows to bring back a count  
> but as you said there's nothing too suspicious about them.
>
> Reduce the time limit down a bit more & see what happens.
>
> Andy
>
> On 25 Aug 2010, at 10:48, Oliver, Gavin wrote:
>
>> Hi Andy,
>>
>> Thanks a lot for the input.
>>
>> Log slow queries is turned on and set to 10 seconds.  The thing is I
>> only seem to get about 10 slow queries per test!
>>
>> I have a test running at the moment and it's around 40% complete  
>> and I
>> only have a log of 3 slow queries.  Nothing looks overly suspicious
>> about them either!  I've attached them below.
>>
>> Any help or suggestions on diagnosing the problem would be greatly
>> appreciated.  I'm fairly new to all of this!
>>
>> Best,
>>
>> Gavin
>>
>> # Time: 100825  9:47:22
>> # User at Host: pipeline[pipeline] @ localhost []
>> # Query_time: 96.946500  Lock_time: 0.000201 Rows_sent: 1
>> Rows_examined: 67813101
>> use homo_sapiens_funcgen_59_37d;
>> SET timestamp=1282726042;
>> SELECT COUNT(*) FROM xref x, object_xref ox, external_db e, probe p,
>> array_chip ac, array a WHERE x.xref_id=ox.xref_id AND
>> e.external_db_id=x.external_db_id AND e.db_
>> name ='homo_sapiens_core_Transcript' and
>> ox.ensembl_object_type='ProbeSet' and ox.ensembl_id=p.probe_set_id  
>> and
>> ox.linkage_annotation!='ProbeTranscriptAlign' and p.ar
>> ray_chip_id=ac.array_chip_id and ac.array_id=a.array_id and
>> a.name='ADXCRCG2a520319';
>> # Time: 100825  9:48:07
>> # User at Host: pipeline[pipeline] @ localhost []
>> # Query_time: 44.790518  Lock_time: 0.000145 Rows_sent: 15
>> Rows_examined: 79472907
>> SET timestamp=1282726087;
>> SELECT distinct pf.analysis_id, a.logic_name from probe_feature pf  
>> left
>> join analysis a on pf.analysis_id=a.analysis_id;
>> # Time: 100825  9:48:57
>> # User at Host: pipeline[pipeline] @ localhost []
>> # Query_time: 40.791321  Lock_time: 0.000326 Rows_sent: 25
>> Rows_examined: 31018844
>> SET timestamp=1282726137;
>> SELECT ps.probe_set_id, ps.name, a.name, count(p.probe_id) FROM  
>> probe p,
>> probe_set ps, array a, array_chip ac WHERE a.array_id=ac.array_id and
>> ac.array_chip_id=p.arra
>> y_chip_id and p.probe_set_id=ps.probe_set_id and a.name in
>> ("ADXCRCG2a520319") GROUP BY p.probe_set_id, a.name;
>>
>> -----Original Message-----
>> From: Andy Yates [mailto:ayates at ebi.ac.uk]
>> Sent: 25 August 2010 10:25
>> To: Oliver, Gavin
>> Cc: dev at ensembl.org
>> Subject: [SPAM] - Re: [ensembl-dev] EFG Array Mapping inconsistent
>> performance - Email found in subject
>>
>> Hi Gavin,
>>
>> There are 2 ways of stopping LOBs from creating on disk temp tables
>>
>> 1). Don't use them in a query (so get them after the event)
>> 2). Convert a CLOB to a varchar/char field depending on the size of  
>> the
>> field & deviation of the data length
>>
>> Really you have to be sure that LOBs are involved in a query;  
>> looking at
>> the EFG schema the only text fields which could cause a problem are  
>> the
>> cigar lines. If you can enable the slow query log on your MySQL  
>> server &
>> set the timeout to greater than 10 seconds that would help to tie  
>> down
>> the queries which are causing a problem rather than assuming it's  
>> LOBs.
>>
>> Andy
>>
>> On 25 Aug 2010, at 10:12, Oliver, Gavin wrote:
>>
>>> Hi all,
>>>
>>> I've been running some tests on our server, annotating a test  
>>> batch of
>> around 50 Affy probesets (25-mer probes, 11 probes per set).
>>>
>>> Running the same batch of probesets has completed in anywhere from  
>>> 30
>> mins to just over 2 hours!
>>>
>>> The load on the server does not seem to be the issue.  I have run a
>> number of mysql tuners to optimize things and the only complaint I am
>> seeing is that 50% of our temporary tables are being created on disk
>> rather than in memory.  Apparently this is a major cause of  
>> inconsistent
>> mySQL performance.  I have increased all relevant variables but it  
>> does
>> not seem to affect the % of temp tables being created on disk.  I  
>> know
>> that BLOB and TEXT fields cannot be stored in memory and must be  
>> created
>> on disk so I'm beginning to think this may be the problem.  Has  
>> anyone
>> else experienced similar problems or does anyone have any ideas on  
>> how
>> the BLOB and TEXT fields could be redefined to enable in-memory  
>> storage?
>>>
>>>
>>> Best,
>>>
>>> Gavin
>>>
>>> The contents of this message and any attachments to it are
>> confidential and may be legally privileged. If you have received this
>> message in error, you should delete it from your system immediately  
>> and
>> advise the sender.
>>>
>>> Almac Group (UK) Limited, registered no. NI061368.  Almac Sciences
>> Limited, registered no. NI041550.  Almac Discovery Limited,  
>> registered
>> no. NI046249.  Almac Pharma Services Limited, registered no.  
>> NI045055.
>> Almac Clinical Services Limited, registered no. NI041905.  Almac
>> Clinical Technologies Limited, registered no. NI061202.  Almac
>> Diagnostics Limited, registered no. NI043067.  All preceding  
>> companies
>> are registered in Northern Ireland with a registered office address  
>> of
>> Almac House, 20 Seagoe Industrial Estate, Craigavon, BT63 5QD, UK.
>>>
>>> Almac Sciences (Scotland) Limited, registered in Scotland no.
>> SC154034.
>>>
>>> Almac Clinical Services LLC, Almac Clinical Technologies LLC and  
>>> Almac
>> Diagnostics LLC are Delaware limited liability companies and Almac  
>> Group
>> Incorporated is a Delaware Corporation.  More information on the  
>> Almac
>> Group can be found on the Almac website: www.almacgroup.com
>>> _______________________________________________
>>> Dev mailing list
>>> Dev at ensembl.org
>>> http://lists.ensembl.org/mailman/listinfo/dev
>>
>> -- 
>> Andrew Yates                   Ensembl Genomes Engineer
>> EMBL-EBI                       Tel: +44-(0)1223-492538
>> Wellcome Trust Genome Campus   Fax: +44-(0)1223-494468
>> Cambridge CB10 1SD, UK         http://www.ensemblgenomes.org/
>>
>>
>>
>>
>> ensemblgenomes.org/
>>
>>
>>
>>
>
> -- 
> Andrew Yates                   Ensembl Genomes Engineer
> EMBL-EBI                       Tel: +44-(0)1223-492538
> Wellcome Trust Genome Campus   Fax: +44-(0)1223-494468
> Cambridge CB10 1SD, UK         http://www.ensemblgenomes.org/
>
>
>
>
>
> _______________________________________________
> Dev mailing list
> Dev at ensembl.org
> http://lists.ensembl.org/mailman/listinfo/dev

More information about the Dev mailing list