CODEWITHSUNDEEP
mysqlmysql-5.5
michael nesterenko
michael nesterenko

Reputation: 14449

mysql memory table can not grow bigger than 2G in size

I have set max_heap_table_size to 5G (5368709120), but my memory table can not grow further than 2G in size. Mysql process is 64 bit, so it has plenty of memory and also there is physical memory available. But despite that fact I got an error ERROR 1114 (HY000) at line 1: The table 'my_cache' is full

There is size growth over time:

data_mb index_mb total
2048.00 1178.28 3226.28
2048.00 984.57  3032.57
2048.00 792.84  2840.84
1795.40 599.13  2394.53
1218.23 407.41  1625.64
665.04  221.68  886.71

What is wrong?

UPD I increased max_heap_table_size to 10G and it worked and there was no error!

Growth stats:

2048.00 1563.72 3611.72
2048.00 1370.00 3418.00
2048.00 1178.28 3226.28
2048.00 984.57  3032.57
2048.00 792.84  2840.84
1795.40 599.13  2394.53

What happened?

Upvotes: 4

Views: 541

Answers (2)

timestee
timestee

Reputation: 1096

  1. Run show variables like 'max_heap_table_size' to check the size limit.
  2. Then set max_heap_table_size=xxxxxxx;
  3. Truncate the table and issue Alter table on it to make the value effective for this table.truncate table table_name;,alter table table_name ENGINE=MEMORY;
  4. Better to change the max_heap_table_size value in mysql config file, avoid the changes dismiss after mysql service restart.

Upvotes: 1

Hitesh Mundra
Hitesh Mundra

Reputation: 1588

  • MEMORY tables are never converted to disk tables. To ensure that you don’t accidentally do anything foolish, you can set the max_heap_table_size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX_ROWS table option in the CREATE TABLE statement

What says percona
Here is an example

Upvotes: 0

Related Questions