Why does my MySQL server keep converting HEAP to MyISAM

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.

Answer 1

Could be a few things.

Most obvious would be your TMP_TABLE_SIZE variable is to small and MySQL must create the temporary table on disk and not in memory. Using the following query will show if MySQL is creating disk tmp tables.

mysql> SHOW STATUS LIKE '%tmp%';

Other issue could be that the table that should go to a temp table is BINARY or TEXT table which the memory engine does not support and therefor you would get a MyISAM table created on disk.

Temporary tables created on disk are MyISAM tables. HEAP is the old name for MEMORY tables so you must be running on a pre MySQL 5.x version of MySQL.