15

There's a max_heap_table_size limit to 16 mb so how can i change this value and where?

Krueger
  • 1,178
  • 3
  • 11
  • 26

2 Answers2

21

I am pretty sure the max_heap_table_size is set to around 16MB by default. So I would first check what it is set to by running a query:

select @@max_heap_table_size;

And then you can run a query to set it higher:

set @@max_heap_table_size=NUMBER_OF_BYTES;

christophmccann
  • 4,181
  • 7
  • 42
  • 66
17

In case you cannot change your heap value try this

Add this to mysql/etc/my.cnf

[mysqld]
tmp_table_size=2G
max_heap_table_size=2G

this will cover mysql restarts. To set these values in mysqld right now without restarting run this:

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

If you are checking the above variables with

SELECT @@max_heap_table_size;

you may notice that they don't seem to change following the SET GLOBAL... statements. This is because the settings only apply to new connections to the server. Make a new connection, and you'll see the values update.

Airy
  • 5,484
  • 7
  • 53
  • 78