yeah,it runs faster if without order clause,as it do not use filesort here. because of the huge data,it takes lots of time to copy them to tmp table or even to the tmp table on the disk when use filesort, It also led to the higher io wait! i am trying to increase the variable 'max_length_for_sort_data' to 8096,hope this can help mysql use the efficient sorting algorithm,theoretically this can reduce the I / O, i am also trying to use tmpfs filesystem as tmpdir,this can greatly improve the reading and writing speed on the disk. hope these can help. do you hava any suggestions on this?
these is only select operation in my database,and i have do some optimiztion by 'myisamchk -aS' ,but the effect is not very good. i am sure that it will be greate improved if we rewriting the select,but unfortunately, this sql is inside the completed software, and i have no rights to rewrite it,so i have the only way to optimiztion the mysql server! thanks again for your help all the best. On Tue, Dec 15, 2009 at 9:59 PM, Daisley, John (Burton) < john.dais...@llg.co.uk> wrote: > The 'order by' clause is forcing MySQL to use a temporary table; as a > test could you try running the query without the order by clause? Does it > run quicker? MySQL must use a temporary table if you 'group by' or 'order > by' a column not contained in the first table of the select, sometimes you > can get around this limitation just by rewriting the select. Give it a try, > it can make a huge difference. > > Do the tables have a lot of insert,update, delete operations? If so it may > be worth you running OPTIMIZE TABLE to rebuild indexes. Be warned it can > take some time to complete and tables are locked whilst it runs. If you have > slaves running you may want to add LOCAL to the OPTIMIZE TABLE statement so > the operation does not run on your slaves. > > I'm stuck doing some other stuff at the mo but I will try and have a proper > look at this later and will get back to you. > > Regards > > 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 > > > ------------------------------ > *From:* jingtian.seu...@gmail.com [mailto:jingtian.seu...@gmail.com] *On > Behalf Of *TianJing > *Sent:* 15 December 2009 12:43 > *To:* Daisley, John (Burton) > > *Cc:* mysql@lists.mysql.com > *Subject:* Re: mysql server optimization > > 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> > > _____________________________________________________________________ > This e-mail has been scanned for viruses by MessageLabs. > > ********************************************************************** > Confidentiality : This e-mail and any attachments are intended for the > addressee only and may be confidential. If they come to you in error you > must take no action based on them, nor must you copy or show them to anyone. > Please advise the sender by replying to this e-mail immediately and then > delete the original from your computer. > > Opinion : Any opinions expressed in this e-mail are entirely those of the > author and unless specifically stated to the contrary, are not necessarily > those of the author’s employer. > > Security Warning : Internet e-mail is not necessarily a secure > communications medium and can be subject to data corruption. We advise that > you consider this fact when e-mailing us. > > Viruses : We have taken steps to ensure that this e-mail and any > attachments are free from known viruses but in keeping with good computing > practice, you should ensure that they are virus free. > > Inspired Gaming (UK) Limited > Registered in England No 3565640 > Registered Office 3 The Maltings Wetmore Road, Burton On Trent, > Staffordshire DE14 1SE > _______________________________________________________________ > This message has been checked for all known viruses by the MessageLabs > Virus Control Centre. > -- Tianjing Tel:0755-2527-3851 MSN:tianjing...@hotmail.com <msn%3atianjing...@hotmail.com>
