Size of tmp_table_size & max_heap_table_size stuck on 32M

jaigupta · December 30, 2020, 4:21pm

max_heap_table_size=128M;
tmp_table_size=128M;

mysql> SELECT &#64;&#64;max_heap_table_size;
+-----------------------+
| &#64;&#64;max_heap_table_size |
+-----------------------+
| 33554432 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT &#64;&#64;tmp_table_size;
+------------------+
| &#64;&#64;tmp_table_size |
+------------------+
| 33554432 |
+------------------+

niljoshi · December 30, 2020, 4:31pm

Hi,

After setting it in my.cnf, have you restarted the MySQL server? Can you check with “show global variables like ‘max_heap_table_size%’;”
If it’s still not worked then I would suggest to check error log after restart, you might be find something which causes this. Also provide mysql/PS version.

Oskars · December 30, 2020, 4:31pm

I had the same problem, after I restarted mysql server everything get back to normal, thanks!

Shopping forum

jaigupta · December 30, 2020, 4:31pm

I tried restarting all nodes in cluster and even then output is same.

mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+
mysql> show global variables like 'max_heap_table_size%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 33554432 |
+---------------------+----------+

I then executed following on all nodes.

SET GLOBAL max_heap_table_size=134217728;
SET GLOBAL tmp_table_size=134217728;
SET max_heap_table_size=134217728;
SET tmp_table_size=134217728;

After that output was

mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+----------------+-----------+
| Variable_name | Value |
+----------------+-----------+
| tmp_table_size | 134217728 |
+----------------+-----------+

Config has following defined

max_heap_table_size=128M
tmp_table_size=128M

But after restarting nodes, output is same as before.

mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+

There is no error in log file related with table_size

Is this a bug or I am doing something wrong?