Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I set the table tmp size to a higher value in my my.cnf file like this:

tmp_table_size = 64M
max_heap_table_size = 64M

But it's not working. I used this script to check it. Even after I reboot, it is still showing that 16M is the default value.

What am I doing wrong?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

You have to make sure the settings go under the [mysqld] group in /etc/my.cnf

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

then do one of two things:

OPTION 1 : Restart mysql

service mysql restart

OPTION 2 : Set it globally for new incoming connections (restart not required)

SET GLOBAL tmp_table_size = 1024 * 1024 * 64;
SET GLOBAL max_heap_table_size = 1024 * 1024 * 64;

Give it a Try !!!

share|improve this answer
    
worth noting that SET GLOBAL ... does not persist a service restart, (at least in 5.0, not tried this in 5.5). –  Oneiroi Mar 22 '12 at 15:48
    
@Oneiroi That why I said first add the variables to my.cnf under the [mysqld] group header. Then, set them dynamically in case a restart was not possible during normal business hours... –  RolandoMySQLDBA Mar 22 '12 at 15:50
    
ah that's what I get for skim reading, my bad. –  Oneiroi Mar 22 '12 at 15:54
    
@RolandoMySQLDBA Thanks mate... It worked –  EApubs Mar 22 '12 at 16:25

Remove the spaces from your my.cnf.

[mysqld]
...
tmp_table_size=64M
max_heap_table_size=64M
...

Should resolve the issue, I've been caught out by this in the past myself.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.