There's a max_heap_table_size limit to 16 mb so how can i change this value and where?
First, check the value of max_heap_table_size, which is in bytes. SET @@@@max_heap_table_size=yourNumberOfBytes. Let us change max_heap_table_size value. The number of bytes is 33554432, which is equal to 32 MB.
max_heap_table_size is the limit on a ENGINE=MEMORY table you CREATE . Not many people use that engine, if you do, set the max size just before doing the create. LEAST(max_heap_table_size, tmp_table_size) is the cap on how big to let certain implicit temp tables to get.
tmp_table_size : From MySQL 8.0. 28, tmp_table_size defines the maximum size of any individual in-memory internal temporary table created by the TempTable storage engine.
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;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With