MySQL Forums
Forum List  »  Memory Storage Engine

Re: Apparently enough max_heap_table_size but "table is full"
Posted by: Rick James
Date: November 17, 2012 10:37PM

I have not heard of a MEMORY table anywhere near that big.

I have to guess that there is a hard limit somewhere in the code.

Neither MyISAM, nor InnoDB has a limit that you would hit; either would keep the data and indexes in RAM whenever practical.

Assuming, say, 100GB of stuff other than this table would need RAM, I would suggest trying
If InnoDB: innodb_buffer_pool_size = 1900000M and key_buffer_size = 50M
If MyISAM: key_buffer_size = 500000M
Once you have some more specifics, those can be tuned better. Suggest you try a 2x10e7 table, then do SHOW TABLE STATUS. Again, extrapolation should be approximately linear.

SHOW TABLE STATUS give you Data_length and Index_length.
innodb_buffer_pool_size, if possible, should be a little more than the sum of those. But it must not be so big that there is swapping.
key_buffer_size only needs to cover the Index_length. Un-allocated RAM is used for data caching in MyISAM.

Linux's "swappiness" should probably be low, perhaps 5.

In MyISAM, a data row like what you described will be 18 to 35 bytes depending on the varchar and whether any of the fields are NULLable. You have not said what the indexes are like, so I can't predict their size. One index on one INT or FLOAT might be about 14 bytes/row.

An InnoDB row may average about 70 bytes, including the PRIMARY KEY; secondary indexes are extra. Do include a PRIMARY KEY, if possible.

Options: ReplyQuote

Written By
Re: Apparently enough max_heap_table_size but "table is full"
November 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.