44

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?

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Bing Hsu
  • 692
  • 1
  • 8
  • 14

5 Answers5

90

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;
Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • 1
    If you're changing it to a memory table - why are you worried about transferring the data over? You should be prepared to lose it! So just truncate it, and then alter the table... – HenchHacker Jun 28 '13 at 19:46
  • Why do you set `tmp_table_size=2G`? I left that at the default (16MiB) in my 5.5 server and I am still able to store up to my `max_heap_table_size` (~400MiB) in memory-backed tables. – Adam Monsen Feb 07 '14 at 18:46
  • You can also change the above 2 variables for the session only by doing so `SET GLOBAL tmp_table_size = 1024 * 1024 * 1024 * 2; SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024 * 2;` – Jaylen Oct 06 '14 at 16:03
  • @Mike Running `SET GLOBAL` command do not affect the session values at all. Please note in my post that I mentioned `SET GLOBAL` already. The last two lines in my post, `SET tmp_table_size = 1024 * 1024 * 1024 * 2;` and `SET max_heap_table_size = 1024 * 1024 * 1024 * 2;` are what set the session, but they must be executed within the session itself. – RolandoMySQLDBA Oct 06 '14 at 16:37
  • @RolandoMySQLDB I am sorry I had a type it should be SET SESSION tmp_table_size = 1024 * 1024 * 1024 * 2; SET SESSION max_heap_table_size = 1024 * 1024 * 1024 * 2; – Jaylen Oct 07 '14 at 02:06
  • @Mike `SET tmp_table_size = 1024 * 1024 * 1024 * 2;` and `SET SESSION tmp_table_size = 1024 * 1024 * 1024 * 2;` are the same operation. It says in http://dev.mysql.com/doc/refman/5.1/en/set-statement.html : `If no modifier is present, SET changes the session variable.` Thus, using `SESSION` is optional. I haven't typed `SET SESSION` in years. – RolandoMySQLDBA Oct 07 '14 at 14:12
  • Question: the your first `WHERE` clause has a term 1=2. What does that term mean in MySQL? Thank you. – usumoio Feb 09 '15 at 04:24
  • @usumoio, It is a boolean expression that evaluates to "false". In this case, it ensures the new table will have the same schema as the original table (`CREATE TABLE ... SELECT ...`) but without any rows, thanks to the `WHERE 1=2`. – Marc L. Oct 23 '17 at 17:47
12

max_heap_table_size is what you are looking for

cEz
  • 4,932
  • 1
  • 25
  • 38
6

The max size for the memory table is set on creation and altering and based on the max_heap_table_size value.

So when you want to raise the max size for an existing memory table you can change the max_heap_table_size and then apply it by altering the table to the same storage engine.

Example:

# Raise max size to 4GB
SET max_heap_table_size = 1024 * 1024 * 1024 * 4;

# If already a memory table, the alter will not change anything.
# Only apply the new max size.
ALTER TABLE table_name ENGINE=MEMORY;

Misunderstanding regarding tmp_table_size

The tmp_table_size variable only determines the max size for internal memory tables. Not user-defined.

As stated in the MySQL docs:

The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.

thephper
  • 2,342
  • 22
  • 21
4

If you're still having a problem, remember that the disk space a table occupies is often less than the memory requirement. Using VARCHAR (256) with a string of length 8 will consume 8 bytes on disk but because STORAGE doesn't support dynamic rows, it reserves the full 256 bytes in memory for every instance.

brasofilo
  • 25,496
  • 15
  • 91
  • 179
Tom Donnelly
  • 41
  • 3
3

Increase max_heap_table_size.

Karoly Horvath
  • 94,607
  • 11
  • 117
  • 176