RE: different type column and keys for EXPLAIN
Thanks Sergey. The query is much slower with ref. Do you think if a composite index on firstname and lastname would solve it? Table has 164+ million records which makes me reluctant to create a new index due to the time required for index creation unless I am pretty sure that the new index would work. -Original Message- From: Sergey Petrunya [mailto:pser...@askmonty.org] Sent: Monday, December 14, 2009 5:58 PM To: Manish Ranjan Cc: mysql@lists.mysql.com Subject: Re: different type column and keys for EXPLAIN Manish, On Mon, Dec 14, 2009 at 05:33:43PM +0530, Manish Ranjan wrote: I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM. Please refer to the below two statements. First query is checking for lastname 'clarke' where as second query is checking for lastname 'clark'. Rest everything is same with these two queries. However, the explain output shows ref for the first query and uses only one key for the first query whereas second query uses index_merge and both keys. ... What could be the problem here. Please help. ref and index_merge are two possible plans for both of the queries. The choice whether to use ref or index_merge depends on cost calculations, which, in turn, depends on estimates of numbers of records that one will get for conditions in the WHERE clause. It seems that the storage engine reports different estimates for number of matching records for lastname='clark' and lastname='clarke', and hence the query plans are different. This is a normal situation. Does that cause any problems for you? That is, do you observe that one of the queries is unccecessarily slow (i.e. much slower than you could make it to run by using some hint?) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to not lock anything?
D. Dante Lorenso wrote: All, I am using MySQL currently, but am starting to think that maybe I don't really need to use an RDBMS. The data I am storing ends up getting indexed with Sphinx because I have full-text indexes for about 40 million records. I have an items table that is heavily updated with 40 million records every 1 or 2 days and I need all those items indexed so they can be searched. The problem that I'm having is that the table is constantly locked because an insert or delete is being performed. I am playing with InnoDB vs MyIsam and have been trying to figure out how to get the best performance. I actually don't care about dirty reads, however, and wouldn't mind if all the 40 mm records could be read/inserted/updated/deleted without any locking at all. Are there known solutions for the kind of storage I am looking for? Anyone have any pointers? Is there a MySQL Storage Engine designed for this kind of usage, or is there a another server that is commonly used along with MySQL for this type of thing? Double buffering : Have two identical tables. Update to the non active and when ready make this table the active. Now do the same updates to the old now nonactive table while the new active table can be read pretty much without disturbance. Make the two tables reside on separate disks if you dont have enough IO. Sure its dirty but it works. If you entirally rebuild your datasets from scratch use this approach : Create an empty table from live table definition (CREATE TABLE tmp SELECT * FROM livetable limit 0;) Now rebuild your dataset to table tmp. Drop live table. Rename tmp table to live table name. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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='100' OR fbin between '10' and '10' OR fbin between '.999' and '1' OR fbin between '1000.049' and '1000.051' OR fbin between '100.549' and '100.551' OR fbin between '10.0005529' and '10.0005531' OR fbin between '1.0055379' and '1.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
RE: mysql server optimization
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='100' OR fbin between '10' and '10' OR fbin between '.999' and '1' OR fbin between '1000.049' and '1000.051' OR fbin between '100.549' and '100.551' OR fbin between '10.0005529' and '10.0005531' OR fbin between '1.0055379' and '1.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
Bug fixes and MySQL versions/releases
Hi all, I was informed by our security team about a recent attack attempts related to the following MySQL issue/bug: MySQL_Check_Scramble_Auth_Bypass (see tinyurl.com/y8vjbmm fro more info): Systems Affected: MySQL 4.1 prior to 4.1.3, and MySQL 5.0. We have MySQL Server 5.0.45 on RHEL 5.3, how can I check if our version 5.0.45 is patched (apart from looking in the source code)? Regards Dimitre
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
Re: Help saving MySQL
Hi! Facundo == Facundo Garat fga...@strixsolutions.com writes: Facundo i don't really thinks this is about open source or not. Agree, this has to do about competition and that Oracle, the leader in revenue for databases are trying to buy MySQL, the leader in users just to kill it off. Facundo MySQL became more closed when Sun bought it and while i think that Facundo Oracle will try to get this even more closed IMO it's time to fork it as Facundo a really open source project. Monty could lead this. The are a lot of Facundo forks around that make MySQL even better that it is from SUN. I have already done a fork, MariaDB. This was done to engage the community that MySQL AB and Sun has been neglecting into MySQL development. The problem with a fork of an infrastructure program like GPL is that it can only survive if the owner of the copyright has good intentions, like Sun has. With Oracle as a copyright owner, there is very little chance for a fork to survice. I have written an analyse of this at: http://monty-says.blogspot.com/2009/10/importance-of-license-model-of-mysql-or.html The promises that Oracle has done regarding MySQL are not enough to protect MySQL from being killed as an Open Source project; When you analyse them, they are actually promising very little now and nothing after 5 years! It's still not too late to save MySQL and everyone that is using MySQL can help making a real difference. Please visit http://monty-says.blogspot.com/2009/12/help-saving-mysql.html and write a message to EC! Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: mysql server optimization
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 |
sql file system + optimization
Ok... in the file system, my MySQL files are located in /var/lib/mysql/ I have a database (lets say 'test') so it gets its own directory /var/lib/mysql/test/ Now, all the tables go in this folder. (I know, we *should* all know this...) if the database is on another disk, could it increase performance if this was a large database? If so, could I just symlink the test/ directory to another raid array to increase performance? Or would the increase be negligible? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: sql file system + optimization
Yes, you can symlink it. How much performance benefit you get will depend on hardware and traffic. === 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: Steven Staples sstap...@mnsi.net Sent: 15 December 2009 14:37 To: mysql@lists.mysql.com Subject: sql file system + optimization Ok... in the file system, my MySQL files are located in /var/lib/mysql/ I have a database (lets say 'test') so it gets its own directory /var/lib/mysql/test/ Now, all the tables go in this folder. (I know, we *should* all know this...) if the database is on another disk, could it increase performance if this was a large database? If so, could I just symlink the test/ directory to another raid array to increase performance? Or would the increase be negligible? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
the mystery of the missing mysql.sock file
Take one perfectly functional production server running 5.0.77. It has been up and running under load for quite some time. I am using xtrabackup for backups and suddenly three or four days ago backup stop running. Investigation shows that the socket file '/tmp/mysql.sock' is no longer there. The mysql server itself is still functioning .. you can specify --host=localhost instead of --socket=/tmp/mysql.sock so the mysql client uses tcp/ip instead of the socket file to connect to the server. - Permissions did not change on the /tmp directory..I checked. - There are no errors in the .err log. - No cron jobs clearing out /tmp. - SHOW VARIABLES LIKE '%sock%' displays the proper socket location Any thoughts? I have seen this type of thing occasionally before, but never taken the time to really investigate. I would just restart the server. I will end up having to do so in this case, but I would really like to find out WHY this happened. Is this is bug in the server code? I can't come up with any other explanation. I would love for this to be explainable! thanks, keith -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: How to not lock anything?
At 07:32 PM 12/14/2009, you wrote: All, I am using MySQL currently, but am starting to think that maybe I don't really need to use an RDBMS. The data I am storing ends up getting indexed with Sphinx because I have full-text indexes for about 40 million records. I have an items table that is heavily updated with 40 million records every 1 or 2 days and I need all those items indexed so they can be searched. The problem that I'm having is that the table is constantly locked because an insert or delete is being performed. I am playing with InnoDB vs MyIsam and have been trying to figure out how to get the best performance. I actually don't care about dirty reads, however, and wouldn't mind if all the 40 mm records could be read/inserted/updated/deleted without any locking at all. Are there known solutions for the kind of storage I am looking for? Anyone have any pointers? Is there a MySQL Storage Engine designed for this kind of usage, or is there a another server that is commonly used along with MySQL for this type of thing? -- Dante Dante, Here are a couple of recommendation for a MyISAM table. 1) Optimize the table. This gets rid off all of the holes left by deleted records. Then when rows are inserted it does NOT use a lock on the table. 2) Instead of actually deleting the rows, update a column Deleted='Y' so you don't physically delete the row because this would cause #1 to go back to using table locks when rows are inserted. If you can delay flagging these rows as deleted for a minute or so, then update these rows to Deleted=Y every few minutes. This means only one lock to the table for processing hundreds of rows. You can also look into Low Priority updates. See http://dev.mysql.com/doc/refman/5.1/en/update.html 3) At night, either delete the rows with Deleted=Y and optimize the table or copy the table without the Deleted=Y to a new table. For 20 million rows this should take only a couple of minutes on a fast machine. 4) A little used feature of MyISAM table is the Handler command. It is more difficult to use because it is low level, but it allows you to read dirty rows from a table without waiting for locks. See http://dev.mysql.com/doc/refman/5.1/en/handler.html. It does accept a Where clause and can return the rows in index order. Hope this helps. Mike -- D. Dante Lorenso da...@larkspark.com 972-333-4139 -- -- D. Dante Lorenso da...@lorenso.com 972-333-4139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: the mystery of the missing mysql.sock file
Presumably someone deleted it :-) You can try an 'lsof -U |grep mysql' to see if any processes still have it open. If the mysqld process still has it open, it's probably not the server. Why do you keep it in /tmp, btw ? My guess would be that someone (or some process) decided it was time to clean /tmp out. On Tue, Dec 15, 2009 at 4:46 PM, Keith Murphy bmur...@paragon-cs.comwrote: Take one perfectly functional production server running 5.0.77. It has been up and running under load for quite some time. I am using xtrabackup for backups and suddenly three or four days ago backup stop running. Investigation shows that the socket file '/tmp/mysql.sock' is no longer there. The mysql server itself is still functioning .. you can specify --host=localhost instead of --socket=/tmp/mysql.sock so the mysql client uses tcp/ip instead of the socket file to connect to the server. - Permissions did not change on the /tmp directory..I checked. - There are no errors in the .err log. - No cron jobs clearing out /tmp. - SHOW VARIABLES LIKE '%sock%' displays the proper socket location Any thoughts? I have seen this type of thing occasionally before, but never taken the time to really investigate. I would just restart the server. I will end up having to do so in this case, but I would really like to find out WHY this happened. Is this is bug in the server code? I can't come up with any other explanation. I would love for this to be explainable! thanks, keith -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: the mystery of the missing mysql.sock file
Thanks Johan. Yes, it shows the mysqld holding it open. Not suprising really I guess. I did check the cron jobs and the history file searching for something/someone who deleted it, but didn't find anything. Still, that is what it looks like happend. I will move the sock file to /var/run/mysql so it is out of the way. thanks, keith On Tue, Dec 15, 2009 at 11:00 AM, Johan De Meersman vegiv...@tuxera.bewrote: Presumably someone deleted it :-) You can try an 'lsof -U |grep mysql' to see if any processes still have it open. If the mysqld process still has it open, it's probably not the server. Why do you keep it in /tmp, btw ? My guess would be that someone (or some process) decided it was time to clean /tmp out. On Tue, Dec 15, 2009 at 4:46 PM, Keith Murphy bmur...@paragon-cs.comwrote: Take one perfectly functional production server running 5.0.77. It has been up and running under load for quite some time. I am using xtrabackup for backups and suddenly three or four days ago backup stop running. Investigation shows that the socket file '/tmp/mysql.sock' is no longer there. The mysql server itself is still functioning .. you can specify --host=localhost instead of --socket=/tmp/mysql.sock so the mysql client uses tcp/ip instead of the socket file to connect to the server. - Permissions did not change on the /tmp directory..I checked. - There are no errors in the .err log. - No cron jobs clearing out /tmp. - SHOW VARIABLES LIKE '%sock%' displays the proper socket location Any thoughts? I have seen this type of thing occasionally before, but never taken the time to really investigate. I would just restart the server. I will end up having to do so in this case, but I would really like to find out WHY this happened. Is this is bug in the server code? I can't come up with any other explanation. I would love for this to be explainable! thanks, keith -- Chief Training Officer Paragon Consulting Services 850-637-3877 -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: Help saving MySQL
Michael Widenius wrote: Hi! Facundo == Facundo Garat fga...@strixsolutions.com writes: Facundo i don't really thinks this is about open source or not. Agree, this has to do about competition and that Oracle, the leader in revenue for databases are trying to buy MySQL, the leader in users just to kill it off. Facundo MySQL became more closed when Sun bought it and while i think that Facundo Oracle will try to get this even more closed IMO it's time to fork it as Facundo a really open source project. Monty could lead this. The are a lot of Facundo forks around that make MySQL even better that it is from SUN. I have already done a fork, MariaDB. This was done to engage the community that MySQL AB and Sun has been neglecting into MySQL development. The problem with a fork of an infrastructure program like GPL is that it can only survive if the owner of the copyright has good intentions, like Sun has. With Oracle as a copyright owner, there is very little chance for a fork to survice. I have written an analyse of this at: http://monty-says.blogspot.com/2009/10/importance-of-license-model- of-mysql-or.html The promises that Oracle has done regarding MySQL are not enough to protect MySQL from being killed as an Open Source project; When you analyse them, they are actually promising very little now and nothing after 5 years! It's still not too late to save MySQL and everyone that is using MySQL can help making a real difference. Please visit http://monty-says.blogspot.com/2009/12/help-saving-mysql.html and write a message to EC! Regards, Monty I really agree with you. Even though the dumb regulators in my country refuse to prevent this purchase. I am really worried about OpenOffice and VirtualBox also. I've used MySQL since my system ran Windows ME, even though it was not supposed to run under ME. I have been exclusively Linux (openSUSE) since 1999. I only have VirtualBox so i can run TurboTax once a year. I've worked with oracle and I would never trust them. Can your fork run concurently with MySQl and what is the link to it? I did send an email to the EU even though it will not help this country. -- Russ [openSUSE 11.2 (2.6.31.5-0.1-desktop, x86_64] KDE 4.3.4 release 2, Intel Core 2 Dual E7200, 4 GB RAM, GeForce 8400 GS, 320GB Disc (2) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to not lock anything?
On Mon, Dec 14, 2009 at 8:32 PM, D. Dante Lorenso da...@lorenso.com wrote: I have an items table that is heavily updated with 40 million records every 1 or 2 days and I need all those items indexed so they can be searched. The problem that I'm having is that the table is constantly locked because an insert or delete is being performed. I am playing with InnoDB vs MyIsam and have been trying to figure out how to get the best performance. Problem solved: use InnoDB. Writers don't block readers and vice versa. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to not lock anything?
Writers do block readers. Just at the row level vs the table level of MyISAM. It's just much less likely for writers to block readers. keith On Tue, Dec 15, 2009 at 11:57 AM, Perrin Harkins per...@elem.com wrote: On Mon, Dec 14, 2009 at 8:32 PM, D. Dante Lorenso da...@lorenso.com wrote: I have an items table that is heavily updated with 40 million records every 1 or 2 days and I need all those items indexed so they can be searched. The problem that I'm having is that the table is constantly locked because an insert or delete is being performed. I am playing with InnoDB vs MyIsam and have been trying to figure out how to get the best performance. Problem solved: use InnoDB. Writers don't block readers and vice versa. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: How to not lock anything?
On Tue, Dec 15, 2009 at 11:58 AM, Keith Murphy bmur...@paragon-cs.com wrote: Writers do block readers. Just at the row level vs the table level of MyISAM. It's just much less likely for writers to block readers. No, they don't. Not unless you use an extreme isolation level. InnoDB uses multi-version concurrency to allow readers to work on the previous version while writers are updating. http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Count records in join
Hi, I am stuck with a suposedly simple query: - i have two tables (: PROGS id_prog name EVENTS id id_prog name How can i list all records from PROGS with a sum of how many events each have? I want to find the progs that are empty. I remember something about using NULL, but i cant remember. :-P Thanks. MV
RE: Count records in join
Hi Miguel, You'll need to use LEFT JOIN, that will show all records that match and a row in the second table will all values NULL where there is no match. Then you find all those rows that have no match in your WHERE clause. Regards, Gavin Towey -Original Message- From: Miguel Vaz [mailto:pagong...@gmail.com] Sent: Tuesday, December 15, 2009 10:43 AM To: mysql@lists.mysql.com Subject: Count records in join Hi, I am stuck with a suposedly simple query: - i have two tables (: PROGS id_prog name EVENTS id id_prog name How can i list all records from PROGS with a sum of how many events each have? I want to find the progs that are empty. I remember something about using NULL, but i cant remember. :-P Thanks. MV This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Return row even if nothing found
I have a situation where I need to always get a row returned even if no match is in the table (only 1 or many rows are acceptable). I can use: select a, b, c from mytable where a = 'yarp'; and might get 20 rows if there are matches, but I at least need 1 default row back... using : select ifnull(a,'NOTHING') as a, ifnull(b, 'NOTHING') b, ifnull(c, 'NOTHING') c from mytable where a = 'yarp'; just returns nothing... Anything I can add in here to have a recordset of at least (nothing, nothing, nothing) ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Return row even if nothing found
Cantwell, Bryan wrote: I have a situation where I need to always get a row returned even if no match is in the table (only 1 or many rows are acceptable). I can use: select a, b, c from mytable where a = 'yarp'; and might get 20 rows if there are matches, but I at least need 1 default row back... using : select ifnull(a,'NOTHING') as a, ifnull(b, 'NOTHING') b, ifnull(c, 'NOTHING') c from mytable where a = 'yarp'; just returns nothing... Anything I can add in here to have a recordset of at least (nothing, nothing, nothing) ? Sorry, no. The database can only give you data that it contains. No rows of data = no rows of results. This is a condition you will need to test for in your application and apply the appropriate adjustments to your code. However, if mytable is the child to another table (say myparent), then you can query on ... FROM myparent LEFT JOIN mytable ... and if there were no matches on the matching column then mytable would have all NULL values for its columns. Is that something you can work with? http://dev.mysql.com/doc/refman/5.1/en/join.html -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Return row even if nothing found
On Wed, 16 Dec 2009 07:39:09 Cantwell, Bryan wrote: I have a situation where I need to always get a row returned even if no match is in the table (only 1 or many rows are acceptable). I can use: select a, b, c from mytable where a = 'yarp'; and might get 20 rows if there are matches, but I at least need 1 default row back... using : select ifnull(a,'NOTHING') as a, ifnull(b, 'NOTHING') b, ifnull(c, 'NOTHING') c from mytable where a = 'yarp'; just returns nothing... Anything I can add in here to have a recordset of at least (nothing, nothing, nothing) ? You can do something like: SELECT mytable.* FROM (SELECT 1) AS dummy LEFT JOIN mytable ON id = 'something that does not exists'; It's not pretty, but it might do the trick for you. - Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL 5.5.0 has been released
Dear MySQL users, MySQL Server 5.5.0-m2, a new version of the popular Open Source Database Management System, has been released. The -m2 suffix tells this is the second milestone according to our milestone release model, also called Betony. You can read more about the release model and the planned milestones at http://forge.mysql.com/wiki/Development_Cycle The new features in this release are of beta quality. As with any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.1, we would like to direct your attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ MySQL 5.5 is based on MySQL 5.4, which won't get any further updates. So MySQL 5.5 includes several high-impact changes to address scalability and performance issues in MySQL Server. These changes exploit advances in hardware and CPU design and enable better utilization of existing hardware. For an overview of what's new in MySQL 5.5, please see the section What Is New in MySQL 5.5 below, or view it online at http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html For information on installing MySQL 5.5.0 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.5/en/installing.html For upgrading from previous MySQL releases, please see the important upgrade considerations at http://dev.mysql.com/doc/mysql-5.5-features/en/index.html Some other pointers you might like to follow are http://dev.mysql.com/doc/refman/5.5/en/configuration-changes-5-5.html http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing Following the What Is New section, this mail lists the important changes in the MySQL source code of MySQL 5.5.0. The complete list of all Bugs Fixed is not included, because it would exceed the length restrictions imposed on these mailing lists. It may be viewed online at http://dev.mysql.com/doc/refman/5.5/en/news-5-5-0.html Enjoy! On behalf of the MySQL Build Team at Sun Microsystems: Jörg Brühe, Senior Production Engineer - What Is New in MySQL 5.5 The following features have been added to MySQL 5.5: * Support for an interface for semisynchronous replication: A commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through an optional plugin component. See Section 16.2.8, Semisynchronous Replication * Support for the SQL standard SIGNAL and RESIGNAL statements. See Section 12.8.8, SIGNAL and RESIGNAL. * Enhancements to XML functionality, including a new LOAD XML statement. * Two new types of user-defined partitioning: RANGE COLUMNS partitioning is an extension to RANGE partitioning; LIST COLUMNS partitioning is an extension to LIST partitioning. Each of these extensions provides two enhancements to MySQL partitioning capabilities: 1. It is possible to define partitioning ranges or lists based on DATE, DATETIME, or string values (such as CHAR or VARCHAR). You can also define ranges or lists based on multiple column values when partitioning tables by RANGE COLUMNS or LIST COLUMNS, respectively. Such a range or list may refer to up to 16 columns. 2. For tables defined using these partitioning types, partition pruning can now optimize queries with WHERE conditions that use multiple comparisons between (different) column values and constants, such as a = 10 AND b 5 or a 2005-11-25 AND b = 10 AND c = 50. For more information, see Section 17.2.1, RANGE Partitioning, and Section 17.2.2, LIST Partitioning. * It is now possible to delete all rows from one or more partitions of a partitioned table using the ALTER TABLE ... TRUNCATE PARTITION statement. Executing the statement deletes rows without affecting the structure of the table. The partitions named in the TRUNCATE PARTITION clause do not have to be contiguous. * Key caches are now supported for indexes on partitioned MyISAM tables, using the CACHE INDEX and LOAD INDEX INTO CACHE statements. In addition, a key cache can be defined for and loaded with indexes from an entire partitioned table, or for one or more partitions. * The TO_SECONDS() function is
Re: mysql server optimization
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
