How and why tmp_table_size and max_heap_table_size are bounded.

Posted in: Technical Track


In some cases, MySQL creates internal temporary tables while processing queries. On the base of the dimensions of the resultset MySQL will use the MEMORY engine AND/OR the MyISAM engine.
The difference is that MEMORY engine will handle the table in memory, while MyISAM will write it on disk. A table created using the MEMORY engine can be automatically converted by the MySQL server if it exceed the defined threshold.

And there are others circumstances which could create temporary tables using MEMORY but that can go to MyISAM (so disk) if too large:

  • If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue;
  • DISTINCT combined with ORDER BY may require a temporary table;
  • In the case of the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

There are some conditions which will force the temporary table to use MyISAM :

  • Presence of a BLOB or TEXT column in the table;
  • Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes;
  • Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL;

The tables explicitly created with CREATE TABLE ENGINE MEMORY use ONLY the max_heap_table_size system variable to determines how large the table is permitted to grow and there is no conversion to on-disk format.

Why am I still doing a post on this?

Because I still see MySQL configurations that DBAs seem to use as separate assignments without taking into account the consequences of over-allocation and what impact it has on MySQL server memory usage.

Let us take this from the start, the manual. In the manual section we can find the tmp_table_size definition:

Command-Line Format 	--tmp_table_size=#
Config-File Format 		tmp_table_size
Option Sets Variable 	Yes,tmp_table_size
Variable Name 			tmp_table_size
Variable Scope 			Both
Dynamic Variable 		Yes
Permitted Values
Type 	numeric
Default 		system dependent
Range 			1024-4294967295

What we need to take in account, and in mind, are these two elements:

Default 		system dependent
Range 			1024-4294967295
Command-Line Format 	--max_heap_table_size=#
Config-File Format 		max_heap_table_size
Option Sets Variable 	Yes, max_heap_table_size
Variable Name 			max_heap_table_size
Variable Scope 			Both
Dynamic Variable 		Yes
Permitted Values
Type      	numeric
Default 		16777216
Range 			16384-4294967295

We can easily identify that both can be dynamically modified (Dynamic Variable Yes), that both the variable could be assign at global scope or per session (Variable Scope Both).

Finally that max_heap_size is by default 16M while tmp_table_size is system dependent.

Let us to try to clarify this digging in the code doing:

<mysql_source_dir>/sql ->;
[r[email protected] sql]# grep tmp_table_size *.*

and we found this file: sys_var_thd_ulonglong sys_tmp_table_size(&vars, "tmp_table_size", &SV::tmp_table_size);

From that my understanding is that both starts with a value of 16MB full-stop.

In the statement is:

static sys_var_thd_ulonglong	sys_tmp_table_size(&vars, "tmp_table_size",&SV::tmp_table_size);

We have no explicit reference to allocation. The place were we have it is in

{"tmp_table_size", OPT_TMP_TABLE_SIZE,
"If an internal in-memory temporary table exceeds this size, MySQL will"
" automatically convert it to an on-disk MyISAM table.",
&max_system_variables.tmp_table_size, 0, GET_ULL,
REQUIRED_ARG, 16*1024*1024L, 1024, MAX_MEM_TABLE_SIZE, 0, 1, 0},
{"max_heap_table_size", OPT_MAX_HEP_TABLE_SIZE,
"Don't allow creation of heap tables bigger than this.",
&max_system_variables.max_heap_table_size, 0, GET_ULL,
REQUIRED_ARG, 16*1024*1024L, 16384, MAX_MEM_TABLE_SIZE,

In the MySQL 5.5 and above, we have a clearer place and assignment in

static Sys_var_ulonglong Sys_tmp_table_size(
"If an internal in-memory temporary table exceeds this size, MySQL "
"will automatically convert it to an on-disk MyISAM table",
VALID_RANGE(1024, (ulonglong)~(intptr)0), DEFAULT(16*1024*1024),
static Sys_var_ulonglong Sys_max_heap_table_size(
"Don't allow creation of heap tables bigger than this",
SESSION_VAR(max_heap_table_size), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(16384, (ulonglong)~(intptr)0), DEFAULT(16*1024*1024),

But the result is the same, MySQL start from 16MB, no matter what system you have, or if it does, is very well hide from search.

Now let see what the manual say about how this two variables and their relation:

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.)

If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.

Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

This variable does not apply to user-created MEMORY tables.

Well this one seems clear enough for everyone but to avoid confusion, check the code. So again:

[[email protected] sql]# grep tmp_table_size *.*

but this time we open the file

if (thd->variables.tmp_table_size == ~ (ulonglong) 0) // No limit
share->max_rows= ~(ha_rows) 0;
share->max_rows= (ha_rows) (((share->db_type() == heap_hton) ?
thd->variables.max_heap_table_size) :
thd->variables.tmp_table_size) /

Here is were we find the min() functions used from MySQL.


So as the manual state MySQL will take the LOWER value assign to the two variables, as the good value for tmp_table_size (only).

To be more clear, if we have in the my.cnf:

tmp_table_size      = 32M
max_heap_table_size = 16M

MySQL will assign to tmp_table_size the value of 16M and to max_heap_table_size 16M.

if we have:

tmp_table_size      = 16M
max_heap_table_size = 32M

MySQL will assign to tmp_table_size the value of 16M and to max_heap_table_size 32M.

This needs to take in account because I still see at client site a lot of :

tmp_table_size = 32M
max_heap_table_size = 16M

Which makes no sense.

Finally, this variable has huge possible impact on memory consumption.
Just consider that having:

max_connctions = 600
tmp_table_size = 32M
max_heap_table_size = 32M

These settings could potentially generate a memory usage on the server of (600 x 16MB) = 9.3 GB.

Given that the variables are dynamic, and that could be assign at session level, it is good practics to increase the value of tmp_table_size only when is really needed, in the session before performing the operation which will require it.

Seems all to me but If I have miss something here, corrections are welcome.



Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

With nearly three decades of experience, Marco is still fascinated by technology and its evolution, but his passion has since evolved with an emphasis on the human interaction — whether he is helping to develop his team’s capabilities or his relationships with his clients, Marco enjoys helping people personally and professionally. His colleagues and clients can always rely on him to “find a needle in a haystack” when others are unable to identify a solution. He credits this ability to using reverse brainstorming — starting with the root of an issue and working back. When he isn’t working, Marco can be found spending time with his family and playing sports.

7 Comments. Leave new

George Petsagourakis
May 4, 2011 11:52 am

I see that you say:

> These settings could potentially generate a memory usage on the server of (600 x 16MB) = 9.3 GB.

When the settings shown above are both 32M.
Where did you come up with the 16M for the calculation?


16M is the default at install


Is it a good practice to have tmpfs of size e.g. 2GB as mysql tmp dir location, what do you think will happen if tmpfs is full apart from only that mysql query will fail..


Always change mysql tmpdir to /var/lib/mysqltmp or some otehr location specially when your data dir is on a SAN/DAS/faster disk. I have seen /tmp being filled up many times due to mysql specially when running optimise or alter.


I wish to bookmark this particular blog, “How and why
tmp_table_size and max_heap_table_size are bounded” on my own internet site.
Do you care in the event that Ido it? Thank you -Remona


I think there is an error in this line:

These settings could potentially generate a memory usage on the server of (600 x 16MB) = 9.3 GB.

Because the example was about 32MB:
max_connctions = 600 (btw, here there is a typo error, should be max_connections)
tmp_table_size = 32M
max_heap_table_size = 32M

So the line should be this:

These settings could potentially generate a memory usage on the server of (600 x 32MB) = 19.2 GB.



Actually, memory consumed will be much bigger as tmp_table_size is not the only buffer which is allocated per session :) read_buffer*,net_buffer*,join_buffer*,sort_buffer*,binlog_cache*, thread_stack are also allocated per thread.

There’s also a bug in MySQL 8 (fixed in 8.0.16 if I remember correctly) which still uses /tmp as a default storage location for temporary tables, even though we explicitly say to MySQL to use a different one (‘innodb_tmpdir’ parameter)


Leave a Reply

Your email address will not be published. Required fields are marked *