3

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?

6
+100

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

  • Thank, you. I will most likely accept your answer but would like to wait for some other opinions or answers. :) – Matija Milković Dec 22 '14 at 21:55
  • Did my answer ever help you ? – RolandoMySQLDBA Dec 27 '14 at 15:54
  • @RolandoMySQLDBA: I believe the focus on the question is the Converting HEAP to MyISAM status, i.e. do you know whether or not setting the two variables to the same value will prevent Converting HEAP to MyISAM (since there should never be a temporary table larger than the HEAP table size)... or will internal temporary tables not respect tmp_table_size? – pinkgothic Jan 5 '15 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.