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) <
[email protected]> 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 [email protected]
>
> www.inspiredgaminggroup.com
>
>
> -----Original Message-----
> From: TianJing <[email protected]>
> Sent: 15 December 2009 10:28
> To: [email protected]
> Cc: [email protected]
> 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 <[email protected]>
> 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:
> [email protected]
> >
> > ===================
> >
> > Sent via HP IPAQ mobile device
> >
> > -----Original Message-----
> > From: TianJing <[email protected]> > Sent: 15 December 2009
> 03:08 > To: [email protected] > 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:[email protected] <msn%[email protected]> <
> msn%[email protected] <msn%[email protected]>>
>
>
> _____________________________________________________________________
> This e-mail has been scanned for viruses by MessageLabs.
>
>
--
Tianjing
Tel:0755-2527-3851
MSN:[email protected] <msn%[email protected]>
