At Performance Limit for 2 GB RAM?

[Nick Matheson]

Hello-

This is my first post here. I ran through the performance posts looking for an answer but I may have missed it in there, if so I apologize. Please just point me in the right direction.

I have tracked my problem down to one table.

The table has ~200M rows with a two column (both integers) primary key. I am trying to select a group of ~10-20K rows from the 200M based on both parts of the key. The data storage for the table is about 1.7GB and the index storage is about 2.8GB. I have ~350MB set aside for the key buffer (The default setting for my-huge.cnf) and clearly this index will not fit into the buffer. Even if I increase the buffer size to 1GB (half of my RAM) I will still be unable to buffer the key.

I am seeing query performance of 10-20sec. The usability goal for these queries would be 1-2 sec. I am wondering if there is anything I am missing or can do to get the required level of performance. My instinct is saying that this may simply be due to the fact that the indexes are so large and thus being read from disk rather than from the buffer, but I was hoping for a sanity check from other more experienced users and maybe some options or alternatives.

I am including relevant info on the tables involved, the query and my server and it's settings. For my testing there were no other significant processes running on the machine.

Thanks in advance.

Nick

(Sorry about the formatting here.)

Server
---------
Dual Xeon 3.0Ghz 2-GB Ram SCSI Raid Array
Red Hat Enterprise Linux AS release 3 (Taroon Update 3)
MySQL 4.1.4

my.cnf
---------

[mysqld]
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 4

Query and Explain
----------------------

select dich_value from y_statsinfo as a, y_metadatatemp as b where stn_id = 1250 and a.metadata_id = b.metadata_id;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 12096 | |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 5 | const,test.b.metadata_id | 1 | Using where |

Table Structure
-------------------
describe y_metadatatemp;

| Field | Type | Null | Key | Default | Extra |

| metadata_id | mediumint(8) unsigned | | | 0 | |

describe y_statsinfo;

| Field | Type | Null | Key | Default | Extra |

| dich_value | enum(.....4items.......) | YES | | NULL | |
| cat_value | enum(...16items.......) | YES | | NULL | |
| metadata_id | mediumint(8) unsigned | | PRI | 0 | |
| stn_id | smallint(5) unsigned | | PRI | 0 | |