On this particular DB server we have some very very large tables, and large temp tables being created (many GB in some cases). However I am apprehensive about turning the max_heap_table_size and tmp_table_size up too far… We have a TON of free memory on the system (like 50 GB unused for example) and want many more tables to be created in memory. How far can I safely go with these values?
Here is what it looks like now:
Current max_heap_table_size = 512 M
Current tmp_table_size = 512 M
Of 282433 temp tables, 44% 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