How to make the mysql MEMORY ENGINE store more data?

Question

I want to alter a table from INNODB to MEMORY ENGINE.

So I typed this command:

alter table sns ENGINE=MEMORY;

Then the MySQL shows

ERROR 1114 (HY000): The table '#sql-738_19' is full 

The data size for the table is 1GB, and I have 8GB Memory.

I checked my.cnf, and I didn't find where to change the max_size setting. Shouldn't I be able to store more data?

Answer 1

You should adjust the way you make and load the table

CREATE TABLE sns_memory SELECT * FROM sns WHERE 1=2; ALTER TABLE sns_memory ENGINE=MEMORY; INSERT INTO sns_memory SELECT * FROM sns; DROP TABLE sns; ALTER TABLE sns_memory RENAME sns; 

This will get around any imposed limits by tmp_table_size and max_heap_table_size.

Just the same, you need to do two things:

Add this to /etc/my.cnf

[mysqld] tmp_table_size=2G max_heap_table_size=2G 

this will cover mysql restarts. To set these values in mysqld right now without restarting run this:

SET GLOBAL tmp_table_size = 1024 * 1024 * 1024 * 2; SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024 * 2; 

If you are checking the above variables with

SELECT @@max_heap_table_size; 

or

SHOW VARIABLES LIKE 'max_heap_table_size'; 

you may notice that they don't seem to change following the SET GLOBAL... statements. This is because the settings only apply to new connections to the server. Make a new connection, and you'll see the values update or you could change it within your session by running:

SET tmp_table_size = 1024 * 1024 * 1024 * 2; SET max_heap_table_size = 1024 * 1024 * 1024 * 2;