MySQL Forums
Forum List  »  Memory Storage Engine

Problems with using memory tables and hash indexes
Posted by: Aaron Settles
Date: March 12, 2007 01:18PM

I've run into an odd problem that I can't seem to figure out or find similar problems. I have a table that I want to use the MEMORY storage engine and it works fine as long as I limit to around 11M rows. Here's what I'm seeing on my end. If I load the data from a file into the memory engine with less than 11M rows, the data load takes around 7 minutes. When I try to load a dataset with 13M rows as soon as it gets up to around 11.7M rows, the loading of the data slows down to a crawl and just keeps getting slower and slower. Does someone know of any limitations of using the HASH indices in the MEMORY storage engine? If I change all of my indices over to a BTREE index, the data load for all 13M takes about 20 minutes, but it's a consistent performance. The machine I'm running on has 32G of RAM, Solaris 10, the Mysql Max 5.0.27 64bit. I've tried tweaking the key_buffer, max_heap_table_size, tmp_table_size variables, but none of them seem to make a difference. For reference, here is the DDL for the table I'm referring to:

CREATE TABLE `db`.`device_index_stage` (
`device_index_id` int(10) NOT NULL auto_increment,
`parent_index_id` int(10) default NULL,
`device_index` int(23) default NULL,
`device_mac` varchar(17) default NULL,
`us_index` int(8) default NULL,
`device_ip` varchar(128) default NULL,
`device_state` int(1) default NULL,
`device_master_id` int(5) default NULL,
`updated_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY USING HASH (`device_index_id`),
UNIQUE KEY `device_index_stage_mac_master` USING HASH (`device_mac`,`device_master_id`),
KEY `device_index_stage_parent_index_id` USING HASH (`parent_index_id`),
KEY `device_index_stage_device_index` USING HASH (`device_index`),
KEY `device_index_stage_device_mac` USING HASH (`device_mac`),
KEY `device_index_stage_device_ip` USING HASH (`device_ip`),
KEY `device_index_stage_device_master_id` USING HASH (`device_master_id`),
KEY `device_index_stage_updated_time` USING HASH (`updated_time`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

Options: ReplyQuote


Subject
Views
Written By
Posted
Problems with using memory tables and hash indexes
5093
March 12, 2007 01:18PM


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.