[ensembl-dev] EFG Array Mapping inconsistent performance

[Andy Yates]

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/