RSS

To Check maximum memory allocation for memory tables

06 Jan

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: