List:General Discussion« Previous MessageNext Message »
From:TianJing Date:December 15 2009 12:43pm
Subject:Re: mysql server optimization
View as plain text  
yes,you are right,the longblob is already move to a separate table fdna,it
is about 10Gb/database the fdata is about 30Gb/database ,the fgroup is about
10Gb/database.the MYI file is almost the same or much bigger than the MYD
file.

show create table for fdna is:
| fdna  | CREATE TABLE `fdna` (
  `fref` varchar(100) NOT NULL,
  `foffset` int(10) unsigned NOT NULL,
  `fdna` longblob,
  PRIMARY KEY (`fref`,`foffset`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

show create table for fdata is:
| fdata | CREATE TABLE `fdata` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `fref` varchar(100) NOT NULL,
  `fstart` int(10) unsigned NOT NULL,
  `fstop` int(10) unsigned NOT NULL,
  `fbin` double(20,6) NOT NULL,
  `ftypeid` int(11) NOT NULL,
  `fscore` float DEFAULT NULL,
  `fstrand` enum('+','-') DEFAULT NULL,
  `fphase` enum('0','1','2') DEFAULT NULL,
  `gid` int(11) NOT NULL,
  `ftarget_start` int(10) unsigned DEFAULT NULL,
  `ftarget_stop` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`fid`),
  UNIQUE KEY `fref` (`fref`,`fbin`,`fstart`,`fstop`,`ftypeid`,`gid`),
  KEY `ftypeid` (`ftypeid`),
  KEY `gid` (`gid`)
) ENGINE=MyISAM AUTO_INCREMENT=381002371 DEFAULT CHARSET=latin1 |

show create for fgroup is:
-----------------------------+
| fgroup | CREATE TABLE `fgroup` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `gclass` varchar(100) DEFAULT NULL,
  `gname` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`gid`),
  UNIQUE KEY `gclass` (`gclass`,`gname`)
) ENGINE=MyISAM AUTO_INCREMENT=240501186 DEFAULT CHARSET=latin1 |

show  create for ftype is:
| ftype | CREATE TABLE `ftype` (
  `ftypeid` int(11) NOT NULL AUTO_INCREMENT,
  `fmethod` varchar(100) NOT NULL,
  `fsource` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ftypeid`),
  UNIQUE KEY `ftype` (`fmethod`,`fsource`),
  KEY `fmethod` (`fmethod`),
  KEY `fsource` (`fsource`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 |


the index on fdata is :
--+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| fdata |          0 | PRIMARY  |            1 | fid         | A         |
463619315 |     NULL | NULL   |      | BTREE      |         |
| fdata |          0 | fref     |            1 | fref        | A
|           1 |     NULL | NULL   |      | BTREE      |         |
| fdata |          0 | fref     |            2 | fbin        | A
|      229060 |     NULL | NULL   |      | BTREE      |         |
| fdata |          0 | fref     |            3 | fstart      | A         |
231809657 |     NULL | NULL   |      | BTREE      |         |
| fdata |          0 | fref     |            4 | fstop       | A         |
463619315 |     NULL | NULL   |      | BTREE      |         |
| fdata |          0 | fref     |            5 | ftypeid     | A         |
463619315 |     NULL | NULL   |      | BTREE      |         |
| fdata |          0 | fref     |            6 | gid         | A         |
463619315 |     NULL | NULL   |      | BTREE      |         |
| fdata |          1 | ftypeid  |            1 | ftypeid     | A
|          15 |     NULL | NULL   |      | BTREE      |         |
| fdata |          1 | gid      |            1 | gid         | A         |
231809657 |     NULL | NULL   |      | BTREE      |         |

index for fgroup is:
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| fgroup |          0 | PRIMARY  |            1 | gid         | A
|   232212341 |     NULL | NULL   |      | BTREE      |         |
| fgroup |          0 | gclass   |            1 | gclass      | A
|           5 |     NULL | NULL   | YES  | BTREE      |         |
| fgroup |          0 | gclass   |            2 | gname       | A
|   232212341 |     NULL | NULL   | YES  | BTREE      |         |


the EXPLAIN EXTENDED for the query is :
+----+-------------+--------+--------+-------------------------------+---------+---------+----------------------+------+----------+----------------------------------------------+
| id | select_type | table  | type   | possible_keys                 |
key     | key_len | ref                  | rows | filtered |
Extra                                        |
+----+-------------+--------+--------+-------------------------------+---------+---------+----------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | fdata  | range  | fref,ftypeid,gid              |
fref    | 114     | NULL                 |    8 |    75.00 | Using where;
Using temporary; Using filesort |
|  1 | SIMPLE      | ftype  | eq_ref | PRIMARY,ftype,fmethod,fsource |
PRIMARY | 4       | yhchr1.fdata.ftypeid |    1 |   100.00 | Using
where                                  |
|  1 | SIMPLE      | fgroup | eq_ref | PRIMARY                       |
PRIMARY | 4       | yhchr1.fdata.gid     |    1 |   100.00
|                                              |
+----+-------------+--------+--------+-------------------------------+---------+---------+----------------------+------+----------+----------------------------------------------+



On Tue, Dec 15, 2009 at 6:54 PM, Daisley, John (Burton) <
John.Daisley@stripped> wrote:

> 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> <
> MSN%3Atianjing217@stripped <MSN%253Atianjing217@stripped>>
>
>
> _____________________________________________________________________
> This e-mail has been scanned for viruses by MessageLabs.
>
>


-- 
Tianjing

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

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