# Size of tmp\_table\_size & max\_heap\_table\_size stuck on 32M

**URL:** <https://forums.percona.com/t/size-of-tmp-table-size-max-heap-table-size-stuck-on-32m/3311>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [March 3, 2014, 2:13am UTC](https://forums.percona.com/t/size-of-tmp-table-size-max-heap-table-size-stuck-on-32m/3311 "2014-03-03T02:13:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jaigupta](https://avatars.discourse-cdn.com/v4/letter/j/a183cd/32.png) [@jaigupta](https://forums.percona.com/u/jaigupta)\
**Post date:** [March 3, 2014, 2:13am UTC](https://forums.percona.com/t/size-of-tmp-table-size-max-heap-table-size-stuck-on-32m/3311/1 "2014-03-03T02:13:29Z")

</div>

Size of tmp\_table\_size & max\_heap\_table\_size stuck on 32M

```auto
max_heap_table_size=128M;
tmp_table_size=128M;

```

```auto
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 |
+------------------+

```

---

<div class="post-metadata">

**Author:** ![niljoshi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/niljoshi/32/16889_2.png) [@niljoshi](https://forums.percona.com/u/niljoshi)\
**Post date:** [March 4, 2014, 4:48am UTC](https://forums.percona.com/t/size-of-tmp-table-size-max-heap-table-size-stuck-on-32m/3311/2 "2014-03-04T04:48:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Oskars](https://avatars.discourse-cdn.com/v4/letter/o/ecb155/32.png) [@Oskars](https://forums.percona.com/u/Oskars)\
**Post date:** [March 5, 2014, 1:14am UTC](https://forums.percona.com/t/size-of-tmp-table-size-max-heap-table-size-stuck-on-32m/3311/3 "2014-03-05T01:14:50Z")

</div>

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

* * *

[Shopping forum](http://shoppingforums.net)

---

<div class="post-metadata">

**Author:** ![jaigupta](https://avatars.discourse-cdn.com/v4/letter/j/a183cd/32.png) [@jaigupta](https://forums.percona.com/u/jaigupta)\
**Post date:** [March 5, 2014, 1:58am UTC](https://forums.percona.com/t/size-of-tmp-table-size-max-heap-table-size-stuck-on-32m/3311/4 "2014-03-05T01:58:07Z")

</div>

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

```auto
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.

```auto
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

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

```

Config has following defined

```auto
max_heap_table_size=128M
tmp_table_size=128M

```

But after restarting nodes, output is same as before.

```auto
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?

---

<div class="post-metadata">

**Author:** ![ChristopherHS](https://avatars.discourse-cdn.com/v4/letter/c/9de053/32.png) [@ChristopherHS](https://forums.percona.com/u/ChristopherHS)\
**Post date:** [June 22, 2022, 1:44pm UTC](https://forums.percona.com/t/size-of-tmp-table-size-max-heap-table-size-stuck-on-32m/3311/5 "2022-06-22T13:44:47Z")

</div>

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

```auto
tmp_table_size = 128M
max_heap_table_size = 128M

```

Second test

```auto
tmp_table_size = 512M
max_heap_table_size = 512M

```

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