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.dais...@llg.co.uk> 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.dais...@llg.co.uk > > www.inspiredgaminggroup.com > > > -----Original Message----- > From: TianJing <tianj...@genomics.org.cn> > Sent: 15 December 2009 10:28 > To: mg_s...@hotmail.com > Cc: mysql@lists.mysql.com > 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_s...@hotmail.com> > 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.dais...@butterflysystems.co.uk > > > > =================== > > > > Sent via HP IPAQ mobile device > > > > -----Original Message----- > > From: TianJing <jingtian.seu...@gmail.com> > Sent: 15 December 2009 > 03:08 > To: mysql@lists.mysql.com > 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:tianjing...@hotmail.com <msn%3atianjing...@hotmail.com> < > msn%3atianjing...@hotmail.com <msn%253atianjing...@hotmail.com>> > > > _____________________________________________________________________ > This e-mail has been scanned for viruses by MessageLabs. > > -- Tianjing Tel:0755-2527-3851 MSN:tianjing...@hotmail.com <msn%3atianjing...@hotmail.com>