1

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?

1

"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:

  1. The SELECT creates a temp table (for subquery, GROUP BY, etc),
  2. starts it as MEMORY, then
  3. 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.)

  • Thanks for the response Rick. I'll see about copying and pasting the query, but it's huge and joins so many tables that it's really hard to diagnose. On your tip not to simulate internal temp tables... I found over and over again that the temp tables created automatically and internally were orders of magnitude slower to join against (results from subselects that were necessary) because they didn't have any indexes on them. When I create them myself I index them and then the joins are much much faster. – Josh Nov 4 '16 at 21:41
  • Before 5.6 this was inefficient: FROM ( SELECT ... ) JOIN ( SELECT ... ) because there were no indexes on these "derived tables". Hence the "orders of magnitude". But 5.6+ will create the 'best' index on the fly. That may (or may not) be better than you trying to do it yourself. – Rick James Nov 4 '16 at 21:52
  • Yeah, so far all my tests point to the temp tables that I manually create with indexes on them running orders of magnitude faster then the derived ones that mysql creates.... on both mysql 5.7 and aws aurora mysql 5.6.something. BTW, aurora mysql doesn't seem to use the same indexes as mysql 5.6 or 5.7 on a lot of my queries. I end up having to put in index hints. – Josh Nov 5 '16 at 1:37
  • Aurora is an branch of MySQL; I don't know the details. – Rick James Nov 5 '16 at 3:09
  • I'm surprised that 5.7 did not do as good a job as your code. Would you care to start an new thread, and focus on that? Please include the queries, EXPLAIN SELECT ... and SHOW CREATE TABLE. – Rick James Nov 5 '16 at 3:10
0

why use a temporary table in the first place? A temp table only exists in YOUR connection and is invisible for anyone else. It is meant for small local data. Your data is not small, use a normal table. DROP it when you are done.

  • I am using a temporary table because I can index it and then when I join it back to other tables the performance is significantly improved... Queries that would normally run for a couple minutes run in < a couple seconds with the temp table creation. – Josh Nov 2 '16 at 13:44
  • I could create a normal table but that could end up creating hundreds if not thousands of new tables and that seemed like a worse idea then creating the temp tables for the session where they are used and then have mysql handle the dropping of them when the session closed etc. – Josh Nov 2 '16 at 13:46

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.