Re: Converting Heap to MyISAM issue
Posted by:
Rick James
Date: January 13, 2015 12:29PM
Upon second reading, I assume this is your case:
You have a SELECT that is taking a long time, and SHOW PROCESSLIST says something about converting from HEAP to MyISAM? Assuming that, then...
The SELECT apparently needed a temp table (this is common, especially with GROUP BY and ORDER BY).
It first tried to create that tmp table as a HEAP (alias MEMORY) table.
That HEAP table grew to min(tmp_table_size, max_heap_table_size), which is 8GB in your case.
At that point it converted it to MyISAM before continuing the SELECT. This is where you got alarmed.
Given enough time, it would have finished the conversion, and proceeded with the SELECT.
The SELECT would finish, and all would be well.
However...
* It would be much better to lower both of those settings, thereby allowing use of RAM for other things. In particular, it is usually better to give RAM to data/index caching (key_buffer_size for MyISAM and/or innodb_buffer_pool_size for InnoDB).
* By decreasing the values, the conversion to MyISAM would happen sooner, hence faster.
* If multiple such SELECTs are running at the same time, each would be asking for 8GB -- This could threaten to make you run out of RAM. If that happens, swapping occurs and MySQL becomes _really_ slow.
* The query must be rather bad to need that big a temp table. Let's see
EXPLAIN SELECT ...
SHOW CREATE TABLE
How much RAM do you have?
How big are the table(s)?
Usually (not always) something can be changed to avoid such a slow query. Sometimes it is a better index, sometimes it is a different formulation of the SELECT, sometimes it is something else.
What value to use for those settings? The defaults are usually OK. 1% of RAM might be a reasonable alternative, especially if you have a lot of RAM.