[ensembl-dev] EFG Array Mapping inconsistent performance

Wed Aug 25 11:09:43 BST 2010

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/