What should be considered when choosing a tmp_table_size value?

Question

MySQL

The notorious tuning-primer at one of my productions strongly recommends increasing the tmp_table_size value: The server uses a prefetcher to turn a large database with statistics (> 200 million rows, > 25 GB of data, > 10 GB of indexes), so many gigabytes of joins are a legitimate and expected. According to the same tuning-primer, increasing the value of this variable does not affect memory allocation: at 8 GB: at 12 GB: So why is it needed at all then (are there situations when it is beneficial to flush tmp tables to disk?), and what to be guided by, choosing its largest value (except for the amount of physical memory on the server, of course)? The manual does not shed light on this issue -

TEMP TABLES
Current max_heap_table_size = 8.00 G
Current tmp_table_size = 8.00 G
Of 3072 temp tables, 26% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables





MEMORY USAGE
Max Memory Ever Allocated : 27.27 G
Configured Max Per-thread Buffers : 11.82 G
Configured Max Global Buffers : 26.92 G
Configured Max Memory Limit : 38.75 G
Physical Memory : 49.00 G
Max memory limit seem to be within acceptable norms



MEMORY USAGE
Max Memory Ever Allocated : 26.99 G
Configured Max Per-thread Buffers : 11.82 G
Configured Max Global Buffers : 26.92 G
Configured Max Memory Limit : 38.75 G
Physical Memory : 49.00 G
Max memory limit seem to be within acceptable norms



dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size

Glad to hear any thoughts you have on this topic.

Answer 1

stumbled upon this post by accident on google. I can't help but necrophiliac.
together with tmp_table_size it is necessary to increase max_heap_table_size (the maximum size of memory tables)