Re: At Performance Limit for 2 GB RAM?

[Nick Matheson]

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