Mysql – Converting Heap to MyISAM issue

memorymyisamMySQLtemporary-tables

At work we have a problem that has now occurred more then once.

Problem is that query is in state of converting HEAP to MyISAM and it paralyzes our server( query in this state is not able to kill itself and kill flag is not evaluated until only after the operation which seem to take FOREVER in our case)

The following global MYSQL variables appear to us:

| tmp_table_size      | 17179869184 |
| max_heap_table_size |  8589934592 |

Any ideas what we should do?

Should we perhaps adjust the sizes of tmp_table_size and max_heap_table_size to have the same value?

Would that help?

Best Answer

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

SUGGESTION #1

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

enter image description here

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)

SUGGESTION #2

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.

SUGGESTION #3

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

Related Question