At Performance Limit for 2 GB RAM?
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 | |
Subject
Views
Written By
Posted
At Performance Limit for 2 GB RAM?
7635
July 20, 2005 09:42AM
3357
July 20, 2005 05:45PM
3221
July 22, 2005 01:33PM
3145
July 22, 2005 02:06PM
3537
July 22, 2005 02:13PM
2766
July 25, 2005 01:40PM
2950
July 26, 2005 03:47PM
2801
July 26, 2005 06:55PM
2900
July 26, 2005 07:06PM
2929
July 27, 2005 09:46AM
2798
July 30, 2005 11:44AM
3386
August 01, 2005 11:18AM
3354
August 04, 2005 07:17PM
3852
August 08, 2005 09:08AM
2872
August 02, 2005 12:59AM
3146
August 08, 2005 09:26AM
2756
August 09, 2005 07:57AM
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.