3

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).

2 Answers 2

0

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.

1
  • Thanks a lot Rolando! I will script it do to 10.000 rows at a time, bash can do that perfectly :)
    – user32311
    Jan 3, 2014 at 18:01
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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