mySQL Temporary Table is Full

Question

I am trying to create and load a temporary mySQL table into memory using the following syntax but am running into a "Table is full" error:

CREATE TEMPORARY TABLE IF NOT EXISTS tmpHistory ENGINE=MEMORY SELECT * FROM history ORDER BY date ASC;

My original history InnoDB table has about 3m rows and about 300mb. I've increased both of the following server variables from their 16mb default values:

max_heap_table_size = 536870912

tmp_table_size = 536870912

I'm running mySQL on AWS r3.xlarge which is a 4-core box with 30.5GB of RAM.

I've reviewed this SO guidance but am still running into the Table is Full error. I'm new to using the Memory Engine, so any suggestions are appreciated.

Answer 1

max_heap_table_size, not tmp_table_size controls the maximum size of any subsequent MEMORY table.

MEMORY has several quirks. Perhaps this one bit you: VARCHARs are turned into CHARs. So a VARCHAR(255) takes 765 bytes for each row if it is CHARACTER SET utf8.

Why do you want to copy a perfectly good InnoDB table into a MEMORY table? To avoid disk hits? No... If innodb_buffer_pool_size is big enough, the InnoDB table will effectively live in RAM. To speed things up? Not necessarily, because InnoDB has row locks but MEMORY has only table locks.

Please provide SHOW CREATE TABLE; there may be other things to argue against what you are doing and/or explain why you got the error.