Skip navigation links

MySQL Forums :: Memory Storage Engine :: Apparently enough max_heap_table_size but "table is full"


Advanced Search

Apparently enough max_heap_table_size but "table is full"
Posted by: Ken Kakihara ()
Date: November 07, 2012 09:39PM

I am trying to load 2x10e9 data into a table created by memory storage engine.
But mysql server claims "table is full error". It will be great if some body could comment on how to avoid this.

Estimated memory required to store the data and the index is around 500GB,
according to the manual:
https://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html

> SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)
> + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)
> + ALIGN(length_of_row+1, sizeof(char*))

So, I allocated around 1.5TB memory to the max_heap_table_size so that the
mysql is allowed to use the required memory size.

But the "table is full error" persists.

If I remove the ddl for creating index, the data load it self finished without
any error. But after the data loading when I try to make btree index, the
error "table is full" happens again.

Hash index seems to be all right to create. Could somebody give me some advise
on what I should do to create btree index on memory engine table with 2x10e9 size?

This is the ddl for the table.

##############################################

create table table_a (
col1 int,
col2 int,
col3 float,
col4 float,
col5 varchar16
) ENGINE = Memory;

create index idx1 on table_a ( col1 ) using btree;
create index idx2 on table_a ( col2 ) using btree;


##############################################

my.cnf

##############################################

max_heap_table_size = 1024*1024*1024*500*3

##############################################


System:

Red hat linux 6.2, 64bit
MySQL 5.5.21
Physical memory 2.0TB

Options: ReplyQuote


Subject Views Written By Posted
Apparently enough max_heap_table_size but "table is full" 1691 Ken Kakihara 11/07/2012 09:39PM
Re: Apparently enough max_heap_table_size but "table is full" 945 Rick James 11/08/2012 11:17PM
Re: Apparently enough max_heap_table_size but "table is full" 896 Ken Kakihara 11/16/2012 10:45PM
Re: Apparently enough max_heap_table_size but "table is full" 1015 Rick James 11/17/2012 10:37PM


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.