mysql server optimization

--0016e64b03d04408a6047abb5430
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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ï¼=8Ccould any =
one give
me some suggestion?

thanks.

my server is linux,8CPU and 4G memery,the my.cnf is:

[mysqld]
port =3D 3306
skip-locking
skip-name-resolve
key_buffer_size =3D 16M
max_allowed_packet =3D 1M
table_open_cache =3D 64
sort_buffer_size =3D 512M
net_buffer_length =3D 8K
read_buffer_size =3D 512K
read_rnd_buffer_size =3D 512M
myisam_sort_buffer_size =3D 8M
table_cache =3D 1024
log-bin=3Dmysql-bin
binlog_format=3Dmixed


--=20
Tianjing

--0016e64b03d04408a6047abb5430--

RE: mysql server optimization

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 t=
ables 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 incr=
easing the values for tmp_table_size and max_heap_size, these control how l=
arge an in-memory temp table can grow before it is converted to an on disk =
temp table.

Regards
John

===================3D

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@butterflysystems.co.uk

===================3D

Sent via HP IPAQ mobile device=0A=
=0A=
-----Original Message-----=0A=
From: TianJing
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=A0 takes a long time.

i have already use index,but the sql use lots of 'and','or','order by', an=
d
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 giv=
e
me some suggestion?

thanks.

my server is linux,8CPU and 4G memery,the my.cnf is:

[mysqld]
port          =A0 =3D 3306
skip-locking
skip-name-resolve
key_buffer_size =3D 16M
max_allowed_packet =3D 1M
table_open_cache =3D 64
sort_buffer_size =3D 512M
net_buffer_length =3D 8K
read_buffer_size =3D 512K
read_rnd_buffer_size =3D 512M
myisam_sort_buffer_size =3D 8M
table_cache =3D 1024
log-bin=3Dmysql-bin
binlog_format=3Dmixed


--
Tianjing


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: mysql server optimization

--0016e64e5756e48a1d047ac1bc12
Content-Type: text/plain; charset=UTF-8

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,gcla ss,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 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@butterflysystems.co.uk
>
> ===================
>
> Sent via HP IPAQ mobile device
>
> -----Original Message-----
> From: TianJing
> 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:tianjing217@hotmail.com

--0016e64e5756e48a1d047ac1bc12--

RE: mysql server optimization

I'm fairly sure that the longblob column will prevent MySQL from being abl=
e to use 'in memory temp tables' regardless of whether it is included in t=
he SELECT. In an ideal world I would move that longblob to a separate tabl=
e.

How big are the tables fdata,ftype,fgroup? Can you post the results of EXP=
LAIN EXTENDED for the query you posted? Can you also post the output of 'S=
HOW CREATE TABLE' for the tables fdata,ftype,fgroup?



John Daisley=20

Business Intelligence Developer - MySQL Database Administrator
Inspired Gaming Group Plc=20

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

Email john.daisley@llg.co.uk

www.inspiredgaminggroup.com=20


-----Original Message-----
From: TianJing
Sent: 15 December 2009 10:28
To: mg_sv_r@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,gcla ss,gname,ftar=
get_start,ftarget_stop,fdata.fid,fdata.gid
=A0FROM fdata,ftype,fgroup
=A0WHERE   fgroup.gid =3D fdata.gid
=A0 AND ftype.ftypeid =3D fdata.ftypeid
=A0AND fdata.fref=3D'chr18'
  =A0 AND (fbin=3D'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')
  =A0 AND fdata.fstop>=3D'55384910' AND fdata.fstart<=3D'55384989'
  =A0 AND=A0 ((fmethod =3D 'genotype' AND fsource =3D 'SoapSNP') OR (=
fmethod =3D 'gt_novel' AND fsource =3D 'SoapSNP') OR (fmethod =3D 'SNP' A=
ND fsource =3D
'MutaGeneSys_06JUNE07') OR (fmethod =3D 'snp' AND fsource =3D 'HapMap_gt'=
) OR (fmethod =3D 'similarity') OR (fmethod =3D 'HSP') OR (fmethod =3D 'm=
atch') OR=20 (fmethod =3D 'CDS' AND fsource =3D 'UCSC_1') OR (fmethod =3D =
'5\'-UTR' AND fsource =3D 'UCSC_1') OR (fmethod =3D '3\'-UTR' AND fsource=
=3D 'UCSC_1') OR (fmethod =3D 'transcription_start_site' AND fsource =3D=
'UCSC_1') OR (fmethod =3D 'polyA_site' AND fsource =3D 'UCSC_1') OR (fme=
thod =3D 'UTR' AND fsource =3D
'UCSC_1') OR (fmethod =3D 'five_prime_untranslated_region' AND fsource =3D=

'UCSC_1') OR (fmethod =3D 'three_prime_untranslated_region' AND fsource =3D=

'UCSC_1') OR (fmethod =3D 'five_prime_UTR' AND fsource =3D 'UCSC_1') OR (=
fmethod =3D 'three_prime_UTR' AND fsource =3D 'UCSC_1') OR (fmethod =3D '=
exon' AND fsource =3D 'UCSC_1') OR (fmethod =3D 'mRNA' AND fsource =3D 'U=
CSC_1'))
=A0ORDER BY fgroup.gname;

there is a longblob column in the table, but even i do not=A0 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 t=
o 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 m=
emery!

i=20use 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 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 tempora=
ry > 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 contr=
ol > how large an in-memory temp table can grow before it is converted to=
an on > disk temp table.
>
> Regards
> John
>
> ===================3D
>
> John Daisley
>
> MySQL 5.0 Certified Database Administrator (CMDBA) > MySQL 5.0 Certifi=
ed Developer > Cognos BI Developer > > Telephone: +44(0)1283 537111 > =
Mobile: +44(0)7812 451238 > Email: john.daisley@butterflysystems.co.uk
>
> ===================3D
>
> Sent via HP IPAQ mobile device
>
> -----Original Message-----
> From: TianJing > Sent: 15 December 2009 03=
:08 > To: mysql@lists.mysql.com > Subject: mysql server optimization > =
> Dear all, > >=A0 i am nowing having a problem with the mysql server o=
ptimization, i have 20 >=A0 database on a server,each database is about 8=
0Gb,the sql seems very >=A0 slow,almost > 5s.and the server i/o is so hig=
h,when i check the >=A0 processlist,the 'copying to tmp table' state=A0 t=
akes a long time.
>
>=A0 i have already use index,but the sql use lots of 'and','or','order b=
y', > and >=A0 for some reason i can not optimization the sql,i hope to =
do some >=A0 optimization on mysql server to mitigate this phenomenon,cou=
ld any one > give >=A0 me some suggestion?
>
>=A0 thanks.
>
>=A0 my server is linux,8CPU and 4G memery,the my.cnf is:
>
>=A0 [mysqld]
>=A0 port          =A0 =3D 3306
>=A0 skip-locking
>=A0 skip-name-resolve
>=A0 key_buffer_size =3D 16M
>=A0 max_allowed_packet =3D 1M
>=A0 table_open_cache =3D 64
>=A0 sort_buffer_size =3D 512M
>=A0 net_buffer_length =3D 8K
>=A0 read_buffer_size =3D 512K
>=A0 read_rnd_buffer_size =3D 512M
>=A0 myisam_sort_buffer_size =3D 8M
>=A0 table_cache =3D 1024
>=A0 log-bin=3Dmysql-bin
>=A0 binlog_format=3Dmixed
>
>
>=A0 --
>=A0 Tianjing
>
>


--
Tianjing

Tel:0755-2527-3851
MSN:tianjing217@hotmail.com


____________________________________________________________ _________
This e-mail has been scanned for viruses by MessageLabs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: mysql server optimization

--0016e64beaee9221b0047ac3bbab
Content-Type: text/plain; charset=UTF-8

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@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.daisley@llg.co.uk
>
> www.inspiredgaminggroup.com
>
>
> -----Original Message-----
> From: TianJing
> Sent: 15 December 2009 10:28
> To: mg_sv_r@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,gcla ss,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
> 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@butterflysystems.co.uk
> >
> > ===================
> >
> > Sent via HP IPAQ mobile device
> >
> > -----Original Message-----
> > From: TianJing > 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:tianjing217@hotmail.com <
> MSN%3Atianjing217@hotmail.com >
>
>
> ____________________________________________________________ _________
> This e-mail has been scanned for viruses by MessageLabs.
>
>


--
Tianjing

Tel:0755-2527-3851
MSN:tianjing217@hotmail.com

--0016e64beaee9221b0047ac3bbab--

RE: mysql server optimization

------_=_NextPart_001_01CA7D8E.DCF532AB
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

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.
=20
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.
=20
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.
=20
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
=20

________________________________

From: jingtian.seu217@gmail.com [mailto:jingtian.seu217@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=3DMyISAM DEFAULT CHARSET=3Dlatin1 |

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=3DMyISAM AUTO_INCREMENT=3D381002371 DEFAULT CHARSET=3Dlatin1 |

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=3DMyISAM AUTO_INCREMENT=3D240501186 DEFAULT CHARSET=3Dlatin1 |

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=20KEY `ftype` (`fmethod`,`fsource`),
KEY `fmethod` (`fmethod`),
KEY `fsource` (`fsource`)
) ENGINE=3DMyISAM AUTO_INCREMENT=3D16 DEFAULT CHARSET=3Dlatin1 |


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 =20 | 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=20 | 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)
wrote:


=09I'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.
=09
=09How big are the tables fdata,ftype,fgroup? Can you post the
results of EXPLAIN EXTENDED for the=20query you posted? Can you also post
the output of 'SHOW CREATE TABLE' for the tables fdata,ftype,fgroup?
=09
=09
=09
=09John Daisley
=09
=09Business Intelligence Developer - MySQL Database Administrator
=09Inspired Gaming Group Plc
=09
=09Direct Dial +44 (0)1283 519244
=09Telephone +44 (0)1283 512777 ext 2244
=09
=09Mobile +44 (0)7812 451238
=09
=09
=09Email john.daisley@llg.co.uk
=09
=09www.inspiredgaminggroup.com
=09


=09-----Original Message-----
=09From: TianJing
=09Sent: 15 December 2009 10:28
=09To: mg_sv_r@hotmail.com
=09Cc: mysql@lists.mysql.com
=09Subject: Re: mysql server optimization
=09
=09i use MyISAM storage,the MYI file is more than 500Gb. most of
the sql is select with many 'and','or','order by',for example:
=09
=09 SELECT
=09
fref,fstart,fstop,fsource,fmethod,fscore,fstrand,fphase,gcla ss,gname,fta
rget_start,ftarget_stop,fdata.fid,fdata.gid
=09 FROM fdata,ftype,fgroup
=09 WHERE fgroup.gid =3D fdata.gid
=09 AND ftype.ftypeid =3D fdata.ftypeid
=09 AND fdata.fref=3D'chr18'
=09 AND (fbin=3D'10000000000'
=09 OR fbin between '1000000000' and '1000000000'
=09 OR fbin between '99999999.9999999' and '100000000'
=09 OR fbin between '10000000.0000049' and '10000000.0000051'
=09 OR fbin between '1000000.0000549' and '1000000.0000551'
=09 OR fbin between '100000.0005529' and '100000.0005531'
=09 OR fbin between '10000.0055379' and '10000.0055381'
=09 OR fbin between '1000.0553839' and '1000.0553841')
=09 AND fdata.fstop>=3D'55384910' AND fdata.fstart<=3D'55384989'
=09 AND ((fmethod =3D 'genotype' AND fsource =3D 'SoapSNP') OR
(fmethod =3D 'gt_novel' AND fsource =3D 'SoapSNP') OR (fmethod =3D 'SNP' =
AND
fsource =3D
=09 'MutaGeneSys_06JUNE07') OR (fmethod =3D 'snp' AND fsource =3D
'HapMap_gt') OR (fmethod =3D 'similarity') OR (fmethod =3D 'HSP') OR
(fmethod =3D 'match') OR (fmethod =3D 'CDS' AND fsource =3D 'UCSC_1') OR
(fmethod =3D '5\'-UTR' AND fsource =3D 'UCSC_1') OR (fmethod =3D '3\'-UTR=
'
AND fsource =3D 'UCSC_1') OR (fmethod =3D 'transcription_start_site' AND
fsource =3D 'UCSC_1') OR (fmethod =3D 'polyA_site' AND fsource =3D 'UCSC_=
1')
OR (fmethod =3D 'UTR'=20AND fsource =3D
=09 'UCSC_1') OR (fmethod =3D 'five_prime_untranslated_region' AND
fsource =3D
=09 'UCSC_1') OR (fmethod =3D 'three_prime_untranslated_region' AND
fsource =3D
=09 'UCSC_1') OR (fmethod =3D 'five_prime_UTR' AND fsource =3D
'UCSC_1') OR (fmethod =3D 'three_prime_UTR' AND fsource =3D 'UCSC_1') OR
(fmethod =3D 'exon' AND fsource =3D 'UCSC_1') OR (fmethod =3D 'mRNA' AND
fsource =3D 'UCSC_1'))
=09 ORDER BY fgroup.gname;
=09
=09 there is a longblob column in the table, but even i do not
select this blob column, the i/o is still higher.
=09
=09 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!
=09
=09 i use show profiles find that 'sending data' and 'copying to
tmp table'
=09 takes lots of time.
=09
=09 On Tue, Dec 15, 2009 at 5:36 PM, John Daisley
wrote:
=09
=09 > What kind of queries are being run and what type of data is
stored?
=09 >
=09 > 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).
=09 >
=09 > 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.
=09 >
=09 > Regards
=09 > John
=09 >
=09 > ===================3D
=09 >
=09 > John Daisley
=09 >
=09 > 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@butterflysystems.co.uk
=09 >
=09 > ===================3D
=09 >
=09 > Sent via HP IPAQ mobile device
=09 >
=09 > -----Original Message-----
=09 > From: TianJing > 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=20mysql 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.
=09 >
=09 > 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?
=09 >
=09 > thanks.
=09 >
=09 > my server is linux,8CPU and 4G memery,the my.cnf is:
=09 >
=09 > [mysqld]
=09 > port =3D 3306
=09 > skip-locking
=09 > skip-name-resolve
=09 > key_buffer_size =3D 16M
=09 > max_allowed_packet =3D 1M
=09 > table_open_cache =3D 64
=09 > sort_buffer_size =3D 512M
=09 > net_buffer_length =3D 8K
=09 > read_buffer_size =3D 512K
=09 > read_rnd_buffer_size =3D 512M
=09 > myisam_sort_buffer_size =3D 8M
=09 > table_cache =3D 1024
=09 > log-bin=3Dmysql-bin
=09 > binlog_format=3Dmixed
=09 >
=09 >
=09 > --
=09 > Tianjing
=09 >
=09 >
=09
=09
=09 --
=09 Tianjing
=09
=09 Tel:0755-2527-3851
=09
=09 MSN:tianjing217@hotmail.com
>
=09
=09
=09
____________________________________________________________ _________
=09This e-mail has been scanned for viruses by MessageLabs.
=09
=09




--=20
Tianjing

Tel:0755-2527-3851
MSN:tianjing217@hotmail.com =20

____________________________________________________________ _________
This e-mail has been scanned for viruses by MessageLabs.

------_=_NextPart_001_01CA7D8E.DCF532AB--

Re: mysql server optimization

--0016e64cc37afb9a3f047ad39c04
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 efficie=
nt
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 optimiztio=
n
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 i=
s
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.Daisley@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 yo=
u
> can get around this limitation just by rewriting the select. Give it a tr=
y,
> it can make a huge difference.
>
> Do the tables have a lot of insert,update, delete operations? If so it ma=
y
> 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 h=
ave
> 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 prop=
er
> 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.seu217@gmail.com [mailto:jingtian.seu217@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,i=
t
> is about 10Gb/database the fdata is about 30Gb/database ,the fgroup is ab=
out
> 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=3DMyISAM DEFAULT CHARSET=3Dlatin1 |
>
> 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=3DMyISAM AUTO_INCREMENT=3D381002371 DEFAULT CHARSET=3Dlatin1 |
>
> 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=3DMyISAM AUTO_INCREMENT=3D240501186 DEFAULT CHARSET=3Dlatin1 |
>
> 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=3DMyISAM AUTO_INCREMENT=3D16 DEFAULT CHARSET=3Dlatin1 |
>
>
> 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@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.daisley@llg.co.uk
>>
>> www.inspiredgaminggroup.com
>>
>>
>> -----Original Message-----
>> From: TianJing
>> Sent: 15 December 2009 10:28
>> To: mg_sv_r@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,gcla ss,gname,ft=
arget_start,ftarget_stop,fdata.fid,fdata.gid
>> FROM fdata,ftype,fgroup
>> WHERE fgroup.gid =3D fdata.gid
>> AND ftype.ftypeid =3D fdata.ftypeid
>> AND fdata.fref=3D'chr18'
>> AND (fbin=3D'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>=3D'55384910' AND fdata.fstart<=3D'55384989'
>> AND ((fmethod =3D 'genotype' AND fsource =3D 'SoapSNP') OR (fmetho=
d =3D
>> 'gt_novel' AND fsource =3D 'SoapSNP') OR (fmethod =3D 'SNP' AND fsource=
=3D
>> 'MutaGeneSys_06JUNE07') OR (fmethod =3D 'snp' AND fsource =3D 'HapMap_g=
t') OR
>> (fmethod =3D 'similarity') OR (fmethod =3D 'HSP') OR (fmethod =3D 'matc=
h') OR
>> (fmethod =3D 'CDS' AND fsource =3D 'UCSC_1') OR (fmethod =3D '5\'-UTR' =
AND
>> fsource =3D 'UCSC_1') OR (fmethod =3D '3\'-UTR' AND fsource =3D 'UCSC_1=
') OR
>> (fmethod =3D 'transcription_start_site' AND fsource =3D 'UCSC_1') OR (f=
method =3D
>> 'polyA_site' AND fsource =3D 'UCSC_1') OR (fmethod =3D 'UTR' AND fsourc=
e =3D
>> 'UCSC_1') OR (fmethod =3D 'five_prime_untranslated_region' AND fsource =
=3D
>> 'UCSC_1') OR (fmethod =3D 'three_prime_untranslated_region' AND fsource=
=3D
>> 'UCSC_1') OR (fmethod =3D 'five_prime_UTR' AND fsource =3D 'UCSC_1') OR
>> (fmethod =3D 'three_prime_UTR' AND fsource =3D 'UCSC_1') OR (fmethod =
=3D 'exon'
>> AND fsource =3D 'UCSC_1') OR (fmethod =3D 'mRNA' AND fsource =3D '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
>> 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 TEX=
T
>> columns in the > table for example).
>> >
>> > As a starting point you could (if you have the memory in your box) tr=
y
>> > increasing the values for tmp_table_size and max_heap_size, these con=
trol
>> > how large an in-memory temp table can grow before it is converted to =
an
>> on > disk temp table.
>> >
>> > Regards
>> > John
>> >
>> > ===================3D
>> >
>> > 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@butterflysystems.co.uk
>> >
>> > ===================3D
>> >
>> > Sent via HP IPAQ mobile device
>> >
>> > -----Original Message-----
>> > From: TianJing > 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 serve=
r
>> 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 =3D 3306
>> > skip-locking
>> > skip-name-resolve
>> > key_buffer_size =3D 16M
>> > max_allowed_packet =3D 1M
>> > table_open_cache =3D 64
>> > sort_buffer_size =3D 512M
>> > net_buffer_length =3D 8K
>> > read_buffer_size =3D 512K
>> > read_rnd_buffer_size =3D 512M
>> > myisam_sort_buffer_size =3D 8M
>> > table_cache =3D 1024
>> > log-bin=3Dmysql-bin
>> > binlog_format=3Dmixed
>> >
>> >
>> > --
>> > Tianjing
>> >
>> >
>>
>>
>> --
>> Tianjing
>>
>> Tel:0755-2527-3851
>> MSN:tianjing217@hotmail.com <
>> MSN%3Atianjing217@hotmail.com >
>>
>>
>> ____________________________________________________________ _________
>> This e-mail has been scanned for viruses by MessageLabs.
>>
>>
>
>
> --
> Tianjing
>
> Tel:0755-2527-3851
> MSN:tianjing217@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 anyo=
ne.
> 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 necessaril=
y
> those of the authorâ€=99s employer.
>
> Security Warning : Internet e-mail is not necessarily a secure
> communications medium and can be subject to data corruption. We advise th=
at
> 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 computin=
g
> 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.
>



--=20
Tianjing

Tel:0755-2527-3851
MSN:tianjing217@hotmail.com

--0016e64cc37afb9a3f047ad39c04--