[ensembl-dev] [SPAM] - Re: EFG Array Mapping inconsistent performance - Email found in subject

[Oliver, Gavin]

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/