Rule of thumb for tmp_table_size and max_heap_table_size MySQL properties

The title pretty much sums up the question itself: Is there a rule of thumb regarding the values for tmp_table_size and max_heap_table_size MySQL properties?

I've had a serious performance degradation which was caused by the fact MySQL used disk space to filesort a JOIN result.

Increasing tmp_table_size and max_heap_table_size to 3G solved the issue but this is more of a trial and error approach.

Is there a more solid way to calculate an appropriate value for these two?

I recommend a simple 1% of memory for each of those.

-
max_heap_table_size is the limit on a ENGINE=MEMORY table you CREATE. Not many people use that engine, if you do, set the max size just before doing the create.

-
LEAST(max_heap_table_size, tmp_table_size) is the cap on how big to let certain implicit temp tables to get. These are tables used inside SELECTs (etc) to handle GROUP BY, ORDER BY, subqueries, etc.

Since those temp tables can be created by every connection, and possibly multiple temp tables per query, 1% of RAM is a rather "safe" limit. When MySQL runs out of RAM, the OS swaps things -- Swapping MySQL is terrible for performance.

Also, in setting these too large, you are stealing RAM from other uses (eg, buffer_pool), thereby potentially slowing down all queries.

Other Rules of Thumb

See also SQL_BIG_RESULT.

StackExchange Database Administrators Q#209411, answer score: 10