Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Question:

I am currently working on a data migration. I need to migrate 2.5 million rows from one InnoDB table to another InnoDB table (with a different setup). I am using the following syntax to do that:

INSERT `new_table` (`new_column`,`more_columns`) 
SELECT `old_column`,`more_old_columns` FROM `old_table`;

I am wondering if MySQL/InnoDB will perform this as one giant transaction, or will MySQL/InnoDB create a transaction per row it encounters from the select? (I would basically would like to know if it will perform a COMMIT on each row).

share|improve this question

2 Answers 2

up vote 0 down vote accepted

You do not want to do one monolithic transaction because if the transaction fails for any reason, mysqld will spend a long time rolling back everything from the undo logs. There are 1023 undo logs in the system tablespace file (a.k.a. ibdata1). Here is what InnoDB looks like:

InnoDB Architecture

As I said before, there are 1023 undo logs (a.k.a. Rollback Segments). The amount of info stored to preserve the state of new_table would fill up just one undo log. It's not worth the effort and firepower to load everything as a single transaction and a single commit loading one undo log in the event of a crash that may never happen.

Please script the retrieval from old_table to do something like 10000 rows at a time. Since autocommit=1 by default, a single commit will be 10000 rows. For 2.5 million rows, that would be 250 commits.

share|improve this answer
    
Thanks a lot Rolando! I will script it do to 10.000 rows at a time, bash can do that perfectly :) –  user32311 Jan 3 at 18:01

It will all fail or it will all be succesfull meaning one giant transaction.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.