RSS

Daily Archives: January 6, 2012

To Check 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 | 18874368 |
+———————+———-+
1 row in set (0.01 sec)

The 18874368 bytes is equal to 17 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 check 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 2GB:

mysql> show variables like ‘%heap%’;
+———————+————+
| Variable_name | Value |
+———————+————+
| max_heap_table_size | 2097152000 |
+———————+————+
1 row in set (0.00 sec)

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

Advertisements
 
Leave a comment

Posted by on January 6, 2012 in Uncategorized