Home / server / Questions / 372445
Accepted
THpubs
Asked: 2012-03-23 07:07:19 +0800 CST

Mysql tmp_table_size and max_heap_table_size are not working

  • 772

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?

mysql

2 Answers

  1. Best Answer

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

    • 20

  2. 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.

    • 0