MySQL Forums
Forum List  »  Optimizer & Parser

At Performance Limit for 2 GB RAM?
Posted by: Nick Matheson
Date: July 20, 2005 09:42AM

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 | |

Options: ReplyQuote


Subject
Views
Written By
Posted
At Performance Limit for 2 GB RAM?
7635
July 20, 2005 09:42AM
3354
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.