List:General Discussion« Previous MessageNext Message »
From:John \(Burton\) Daisley Date:December 15 2009 10:54am
Subject:RE: mysql server optimization
View as plain text  
I'm fairly sure that the longblob column will prevent MySQL from being able to use 'in
memory temp tables' regardless of whether it is included in the SELECT. In an ideal world
I would move that longblob to a separate table.

How big are the tables fdata,ftype,fgroup? Can you post the results of EXPLAIN EXTENDED
for the query you posted? Can you also post the output of 'SHOW CREATE TABLE' for the
tables fdata,ftype,fgroup?



John Daisley 

Business Intelligence Developer - MySQL Database Administrator
Inspired Gaming Group Plc 

Direct Dial +44 (0)1283 519244
Telephone +44 (0)1283 512777 ext 2244
Mobile +44 (0)7812 451238

Email john.daisley@stripped

www.inspiredgaminggroup.com 


-----Original Message-----
From: TianJing <tianjing@stripped>
Sent: 15 December 2009 10:28
To: mg_sv_r@stripped
Cc: mysql@stripped
Subject: Re: mysql server optimization

i use MyISAM storage,the MYI file is more than 500Gb. most of the sql is  select with many
'and','or','order by',for example:

 SELECT
 fref,fstart,fstop,fsource,fmethod,fscore,fstrand,fphase,gclass,gname,ftarget_start,ftarget_stop,fdata.fid,fdata.gid
  FROM fdata,ftype,fgroup
  WHERE   fgroup.gid = fdata.gid
   AND ftype.ftypeid = fdata.ftypeid
  AND fdata.fref='chr18'
     AND (fbin='10000000000'
      OR fbin between '1000000000' and '1000000000'
      OR fbin between '99999999.9999999' and '100000000'
      OR fbin between '10000000.0000049' and '10000000.0000051'
      OR fbin between '1000000.0000549' and '1000000.0000551'
      OR fbin between '100000.0005529' and '100000.0005531'
      OR fbin between '10000.0055379' and '10000.0055381'
      OR fbin between '1000.0553839' and '1000.0553841')
     AND fdata.fstop>='55384910' AND fdata.fstart<='55384989'
     AND  ((fmethod = 'genotype' AND fsource = 'SoapSNP') OR (fmethod
=  'gt_novel' AND fsource = 'SoapSNP') OR (fmethod = 'SNP' AND fsource =
 'MutaGeneSys_06JUNE07') OR (fmethod = 'snp' AND fsource = 'HapMap_gt') OR  (fmethod =
'similarity') OR (fmethod = 'HSP') OR (fmethod = 'match') OR  (fmethod = 'CDS' AND
fsource = 'UCSC_1') OR (fmethod = '5\'-UTR' AND fsource  = 'UCSC_1') OR (fmethod =
'3\'-UTR' AND fsource = 'UCSC_1') OR (fmethod =  'transcription_start_site' AND fsource =
'UCSC_1') OR (fmethod =  'polyA_site' AND fsource = 'UCSC_1') OR (fmethod = 'UTR' AND
fsource =
 'UCSC_1') OR (fmethod = 'five_prime_untranslated_region' AND fsource =
 'UCSC_1') OR (fmethod = 'three_prime_untranslated_region' AND fsource =
 'UCSC_1') OR (fmethod = 'five_prime_UTR' AND fsource = 'UCSC_1') OR (fmethod  =
'three_prime_UTR' AND fsource = 'UCSC_1') OR (fmethod = 'exon' AND fsource  = 'UCSC_1')
OR (fmethod = 'mRNA' AND fsource = 'UCSC_1'))
  ORDER BY fgroup.gname;

 there is a longblob column in the table, but even i do not  select this blob 
column, the i/o is still higher.

 i have 16G memery in total, the oracle database take 1/4 of them, i try to  increasing
the key_buffer_size to 2048M and tmp_table_size to 1024M,but the  machine crashed out of
memery in one day,the mysqld takes over 18G memery!

 i use show profiles find that 'sending data' and 'copying to tmp table'
 takes lots of time.

 On Tue, Dec 15, 2009 at 5:36 PM, John Daisley <mg_sv_r@stripped> wrote:

 > What kind of queries are being run and what type of data is stored?
 >
 > There are a number of factors which causes MySQL to use on disk temporary  >
tables instead of in memory tables. (If there a BLOB or TEXT columns in the  > table
for example).
 >
 > As a starting point you could (if you have the memory in your box) try  >
increasing the values for tmp_table_size and max_heap_size, these control  > how large
an in-memory temp table can grow before it is converted to an on  > disk temp table.
 >
 > Regards
 > John
 >
 > ===================
 >
 > John Daisley
 >
 > MySQL 5.0 Certified Database Administrator (CMDBA)  > MySQL 5.0 Certified
Developer  > Cognos BI Developer  >  > Telephone: +44(0)1283 537111  >
Mobile: +44(0)7812 451238  > Email: john.daisley@stripped
 >
 > ===================
 >
 > Sent via HP IPAQ mobile device
 >
 > -----Original Message-----
 > From: TianJing <jingtian.seu217@stripped>  > Sent: 15 December 2009 03:08 
> To: mysql@stripped  > Subject: mysql server optimization  >  > Dear
all,  >  >  i am nowing having a problem with the mysql server optimization, i
have 20  >  database on a server,each database is about 80Gb,the sql seems very 
>  slow,almost > 5s.and the server i/o is so high,when i check the  > 
processlist,the 'copying to tmp table' state  takes a long time.
 >
 >  i have already use index,but the sql use lots of 'and','or','order by',  >
and  >  for some reason i can not optimization the sql,i hope to do some 
>  optimization on mysql server to mitigate this phenomenon,could any one  >
give  >  me some suggestion?
 >
 >  thanks.
 >
 >  my server is linux,8CPU and 4G memery,the my.cnf is:
 >
 >  [mysqld]
 >  port            = 3306
 >  skip-locking
 >  skip-name-resolve
 >  key_buffer_size = 16M
 >  max_allowed_packet = 1M
 >  table_open_cache = 64
 >  sort_buffer_size = 512M
 >  net_buffer_length = 8K
 >  read_buffer_size = 512K
 >  read_rnd_buffer_size = 512M
 >  myisam_sort_buffer_size = 8M
 >  table_cache = 1024
 >  log-bin=mysql-bin
 >  binlog_format=mixed
 >
 >
 >  --
 >  Tianjing
 >
 >


 --
 Tianjing

 Tel:0755-2527-3851
 MSN:tianjing217@stripped <MSN%3Atianjing217@stripped>


_____________________________________________________________________
This e-mail has been scanned for viruses by MessageLabs.
Thread
mysql server optimizationTianJing15 Dec
RE: mysql server optimizationJohn Daisley 15 Dec
  • Re: mysql server optimizationTianJing15 Dec
RE: mysql server optimizationJohn \(Burton\) Daisley15 Dec
  • Re: mysql server optimizationTianJing15 Dec
RE: mysql server optimizationJohn \(Burton\) Daisley15 Dec
  • Re: mysql server optimizationTianJing16 Dec