Re: "Converting HEAP to MyIsam" with InnoDB
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.
Subject
Views
Written By
Posted
15434
August 17, 2006 09:07AM
8595
August 18, 2006 07:28AM
Re: "Converting HEAP to MyIsam" with InnoDB
13666
March 25, 2008 05:52AM
5119
April 03, 2008 08:46AM
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.