Mysql tmp_table_size and max_heap_table_size are not working

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?

Solution 1:

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