You have tmp_table_size set to 16G and max_heap_table_size set to 8G. Way Too Big !!!
If you have temp tables that exceed 8G, you can do one of three(3) things
Use smaller values. Set tmp_table_size and max_heap_table_size to 16K. That way, the temp table will go to disk faster. BTW Yes, they should be the same value. If you don't set these small, this is what happens:
- Temp Table Grows to 8G in memory
- mysqld suspends the query processing
- mysqld offloads temp table to disk (all 8G
- mysqld resumes filling the temp table
- mysqld continue query processing
CAVEAT: You may need to map disk-based temp tables to a separate disk. You can use tmpdir to point all such temp tables to that designated volume. The reason I am giving you a head-up on this ?
According to MySQL 5.0 Certification Study Guide
Page 408,409 Section 29.2 Bulletpoint 11 says:
If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.
Thus, you need tons of room for temp tables on disk. I have mentioned this before (See my old post "Site Offline" MySQL server failing to start and stop)
Tune other per-connection settings. If you setting join_buffer_size and sort_buffer_size, this can change the execution plan of the query for the better.
Tune your query. If you can change the query to take advantage of indexes or create indexes that will support the query you have, you can generate temp tables faster and, if possible, smaller.
UPDATE 2014-12-27 11:05 EST
You originally asked
Should we perhaps adjust the sizes of tmp_table_size and max_heap_table_size to have the same value?
Not necessarily. When used together, max_heap_table_size puts a cap on how large and in-memory temp table can be before offloading to disk. The only exception is when a table is create using
CREATE TABLE ... ENGINE=MEMORY; For more information on this, please read MySQL Documentation on How MySQL Uses Internal Temporary Tables