Change max_heap_table_size value?

Question

Limiting max_heap_table_size to 16 mb, so how can I change this value and where?

Answer 1

I am sure max_heap_table_size is set to 16 MB by default. Therefore, I first checked what it was installed on by running the query:

select @@max_heap_table_size;

And then you can run the query to install it above:

set @@max_heap_table_size=NUMBER_OF_BYTES;

Answer 2

If you cannot change the value of your heap, try this

Add this to mysql / etc / my.cnf

 [mysqld] tmp_table_size=2G max_heap_table_size=2G 

this will include 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 check the above variables with

 SELECT @@max_heap_table_size; 

you may notice that they do not change after the SET GLOBAL... statements. This is because the settings apply only to new connections to the server. Create a new connection and you will see the value update.