MySQL Forums
Forum List  »  Optimizer & Parser

Re: At Performance Limit for 2 GB RAM?
Posted by: Nick Matheson
Date: July 27, 2005 09:46AM

Jay/KimSeong-

Here is the info you requested. (Also please note that although the cardinality for y_metadatatemp is showing up as very large it is actually only about 12K.)

This is the problematic indexing and query. If I change the primary key on y_statsinfo to (stn_id, metadata_id, dich_value) the performance goes from 20 sec average as shown here down to sub 1 sec average. The same change with the Innodb engine results in a 5-10% performance increase. Please note these reference times are for 20M rows rather than the full 200M rows. I have been working with the smaller data sets in my effort to understand what is going on.

Are there any resources for general benchmarks of other servers out there. I am concerned about some IO benchmarks run on the server that show it performing more poorly than my development workstation. This should not be the case. It is so contradictory I would like to run more test to make sure I didn't misunderstand the results.

Thanks,

Nick

CREATE TABLE `y_statsinfo` (
`dich_value` enum('YY','YN','NY','NN') NOT NULL default 'YY',
`cat_value` enum('LIFR_LIFR','LIFR_IFR','LIFR_MVFR','LIFR_VFR', 'IFR_LIFR','IFR_IFR','IFR_MVFR','IFR_VFR','MVFR_LIFR',
'MVFR_IFR','MVFR_MVFR','MVFR_VFR','VFR_LIFR',
'VFR_IFR','VFR_MVFR','VFR_VFR') default NULL,
`metadata_id` mediumint(8) unsigned NOT NULL default '0',
`stn_id` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`metadata_id`,`stn_id`,`dich_value`)
) ENGINE=MyISAM

CREATE TABLE `y_metadatatemp` (
`metadata_id` mediumint(8) unsigned NOT NULL default '0'
PRIMARY KEY (`metadata_id`)
) ENGINE=MyISAM

explain select dich_value, count(dich_value) as count from y_statsinfo as a, y_metadatatemp as b where stn_id = 2500 and a.metadata_id = b.metadata_id group by dich_value;;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
| 1 | SIMPLE | a | index | PRIMARY | PRIMARY | 6 | NULL | 172800000 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 3 | test.a.metadata_id | 1 | Using index

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: At Performance Limit for 2 GB RAM?
2632
July 27, 2005 09:46AM
3076
August 04, 2005 07:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.