Size of tmp_table_size & max_heap_table_size stuck on 32M

Size of tmp_table_size & max_heap_table_size stuck on 32M

max_heap_table_size=128M;
tmp_table_size=128M;
mysql> SELECT @@max_heap_table_size;
+-----------------------+
| @@max_heap_table_size |
+-----------------------+
| 33554432 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@tmp_table_size;
+------------------+
| @@tmp_table_size |
+------------------+
| 33554432 |
+------------------+

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.

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


Shopping forum

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?