Why do I keep getting Converting HEAP to MyISAM on my mysql server

Question

We've been having some issues with our MySQL server intermittently. It keeps throwing out a lot of these queries concurrently (thus stacking them up in my processlist). We are using a MyISAM db and connection pooling through Glassfish v3 for a Grails Application.

db_user myhost:35143 db Query 39

converting HEAP to MyISAM /* mysql-connector-java-5.1.7 ( Revision: ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'

We speculate that there are temp tables being created through GORM (the grails ORM) that are causing these queries. Why do I keep getting Converting HEAP to MyISAM on my mysql server?

Answer 1

About the converting HEAP to MyISAM message, the following answer writes:

This happens when the temporary tables created by MySQL in the intermediate steps of the query processing grow too big to be stored in memory. As far as I know, when doing this MySQL writes temporary MyISAM tables irrespective of the db engine you use (it just a temp table that will be deleted at the end of the query). It is no problem except for the time the process takes.

The simplest solution is to increase tmp_table_size, may be temporarily if you are doing maintenance work. If this is not enough, split in chunks your query using LIMIT and OFFSET and step-up the OFFSET to cover the all table you read in your query. If you are reading several large tables in the query and need to limit each of them, do not forget to cover all the combinations of the tables: e.g. if you use TWO tables and want to split each in THREE parts you will need repeating the OFFSET-ted query SIX times with the different combinations of increasing values of OFFSET. A batch file will come at hand to make all of this easier.

So MySQL is creating these in-memory tables (and potentially converting them into temporary MyISAM tables), not Grails. But my understanding is that they may result from too heavy queries.