Mysql – Converting Heap to MyISAM issue on temp table defined as innodb

heapMySQLtable

I was having an issue on the aws aurora brand of mysql 5.6. I was creating temp tables with the memory engine from some selects … ie

create temporary table mytemp table engine memory as select * from table

Some of my queries end up creating a temptable with a few hundred thousand rows (three int columns) and in the process enter a state of "converting heap to MyISAM" which I've read is horrible for performance especially when I am using the temp tables as part of a bigger join against innodb tables later in the mysql session. (convert to myisam, then convert back to innodb to join etc)

So, after some research I changed all my temp table engines to use innodb thinking this would resolve the problem…

create temporary table mytemp table engine innodb as select * from table

When I watch the queries go by after the change to innodb I see that some are still entering a state of "converting heap to MyISAM" . I was under the understanding that if I define the temp table as innodb that this would not happen.

Is there something I'm doing wrong here? IF this is a performance problem, what is the best way to deal with what I'm seeing?

Best Answer

"Temporary table" is an overloaded term.

CREATE TEMPORARY TABLE - a table you create and manage.
An internal temp table used in a big, complex, SELECT.

The latter case causes the message "converting heap to MyISAM". Here's the flow:

The SELECT creates a temp table (for subquery, GROUP BY, etc),
starts it as MEMORY, then
finds that it is too big ("a few hundred thousand rows" -- bigger than both tmp_table_size and max_heap_table_size).

It is normal. It should usually be ignored. The temp table was never InnoDB, so no conversion there happened. (In 8.0, tmp tables will be InnoDB, not MyISAM or MEMORY.)

If you would like to show us the query, together with SHOW CREATE TABLE and EXPLAIN SELECT, we may have tips on improving it.

If you create your own large MEMORY table, you are taking RAM away from other things (eg, innodb_buffer_pool_size), thereby slowing down the system in general.

Do not try to simulate internal temp tables in InnoDB. There is too much baggage you can't avoid, but it can. (Redo log, undo log, transactional semantics, etc.)

Related Solutions

Mysql – Converting live MyISAM table to InnoDB

Assuming you don't have any triggers on that table, the simplest way to do this without locking the table is to use pt-online-schema-change.

Make sure you have enough extra disk space to accommodate this change. The table will be larger when you convert it to InnoDB, and you need to maintain both copies of the table during the conversion. For example, if your table is 100 GB make sure you have at least 200+ GB of free disk space.

You should test this out in a non-production environment first to make sure it works for you and to find out exactly how much disk space you need.

The syntax for your case is quite simple.

First do a dry run to verify that it will work:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --dry-run

Then execute it:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --execute

Mysql – InnoDB to MyISAM Conversion Problem

Setting skip-innodb does disable the InnoDB Storage Engine, but that does not convert the tables.

You need to script the conversion of all the tables.

STEP #1

Comment out the skip-innodb

[mysqld]
#skip-innodb

STEP #2

service mysql restart

STEP #3

Execute the Following Script snf View Results

CONVERT_SCRIPT=/tmp/InnoDBConversionToMyISAM.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE engine='InnoDB' AND table_schema <> 'mysql'"
mysql -uroot -p -ANe"${SQL}" > ${CONVERT_SCRIPT}

STEP #4

View the script

vi /tmp/InnoDBConversionToMyISAM.sql

less /tmp/InnoDBConversionToMyISAM.sql

If you like the contents, then ...

STEP #5

mysql> source /tmp/InnoDBConversionToMyISAM.sql

STEP #6

Uncomment out the skip-innodb

[mysqld]
skip-innodb

STEP #7

service mysql restart

STEP #8

There is no STEP 8. That's it !!!