Mysql tmp_table_size and max_heap_table_size are not working

Question

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?

RolandoMySQLDBA · Accepted Answer · 2012-03-22 15:38:20Z

21

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 !!!

answered Mar 22, 2012 at 15:38

RolandoMySQLDBA

16.7k3 gold badges50 silver badges84 bronze badges

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, 2012 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, 2012 at 15:50
ah that's what I get for skim reading, my bad.
– Oneiroi
Mar 22, 2012 at 15:54

Add a comment |

Oneiroi · Accepted Answer · 2012-03-22 15:47:30Z

0

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.

answered Mar 22, 2012 at 15:34

Oneiroi

2,0631 gold badge15 silver badges28 bronze badges

Add a comment |

2 Answers 2