Increasing the Memory Size for Memory Tables

Published January 5th, 2009

Problem Statement

One of our developers reported that when he injects a data dump from a production MySQL server, he was getting a “..table is full” message for a table using MEMORY engine type. After quick investigation, we learned that maximum memory size on the development server was set to much smaller a default value than what we use on production server. Here, we will show you how you can change the max memory allocation for memory tables

Step 1: What is your current maximum memory allocation for memory tables?

To find out what your current memory allocation is for MEMORY type tables, you can run the following queyr:

$ show variables like '%heap%';

A sample result for default allocation is shown below:

mysql> show variables like '%heap%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.01 sec)

The 16777216 bytes is equal to 16 MB (16 * 1024 * 1024 bytes), which is the default value.

Step 2: Change /etc/my.cnf to set custom memory allocation size for memory tables

To change the memory allocation size, decide what size your system can handle (i.e. you have enough free RAM available for allocation) and then set the following directive in the /etc/my.cnf file:

max_heap_table_size = 1024M

This directive must go under the [mysqld] section of your /etc/my.cnf file.

Step 3: Restart MySQL and verify the new allocation is effective or not

Now restart MySQL using service mysql restart and then run the same query as shown above to verify the new size has taken effect or not. Here is a sample output of the same query after setting the memory size to 1GB:

mysql> show variables like '%heap%';
+---------------------+------------+
| Variable_name       | Value      |
+---------------------+------------+
| max_heap_table_size | 1073741824 |
+---------------------+------------+
1 row in set (0.00 sec)

This means that now a table with MEMORY engine type can have upto 1GB of data.

Category: DBA, Performance | Tags: