MySQL Forums
Forum List  »  InnoDB

Re: "Converting HEAP to MyIsam" with InnoDB
Posted by: marialberto mensa
Date: March 25, 2008 05:52AM

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.

I came into the same situation with an INSERT that was reading from two large tables.
I put LIMIT 5000 in each of two sub-queries, producing intermediate tables expected to be in the order of 10-20 million rows, 20 bytes per row: setting tmp_table_size=512M made disk writes unnecessary. All together the query inserted 100000000+ rows in 16 steps in about 20 minutes total, compared to more than 4 hours with tmp_table_size=64M and no LIMIT-ing.

Having the temporary directory of MySQL and the database on two different physical units also helps: on my development machine (portable PC) with only one physical disk, using tmp_table_size=64M and no LIMIT-ing made the query impractically slow (days). To effect this, set tmpdir appropriately in the my.ini file.

hope this helps, write me should you need more details
... and once more, congratulations to the MySQL Team for their great database!

Marialberto
self-taught MySQL user & abuser



Edited 1 time(s). Last edit at 03/25/2008 05:55AM by marialberto mensa.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: "Converting HEAP to MyIsam" with InnoDB
13666
March 25, 2008 05:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.