Size of tmp_table_size & max_heap_table_size stuck on 32M

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

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 |
+------------------+
1 Like
2

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.

1 Like
3

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

Shopping forum

1 Like
4

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?

1 Like
5

Hi @Ivan_Groenewold ,

Thank you for your help.

I just tried but unfortunately the result remains unchanged.

I put 128M at the beginning and I restarted Mysql then I tried with 512M but nothing either, the execution time remains the same.

First test

tmp_table_size                  = 128M
max_heap_table_size             = 128M

Second test

tmp_table_size                  = 512M
max_heap_table_size             = 512M

I don’t know if I should increase even more?

1 Like