Avoiding InnoDB transaction overhead in table copy using INSERT ... SELECT
Instead of doing ALTER TABLE I prefer to create a new table, copy the data to it, and then move to use it. When doing so in InnoDB I always have a hard time performing:
INSERT INTO new_huge_tbl (SELECT * FROM old_huge_tbl)
Because of the natures of transactions, if at any time I need to stop this operation, the rollback isn't easy, to say the least. Is there any way I can perform this operation in InnoDB without it being a transaction?
No, it's not possible to avoid the transactional overhead in a simple way. You would perhaps have two options:
- In your own application, use many smaller transactions (of e.g. 10k rows each) to copy the data in small batches.
- Use an existing tool which does the copy for you using the same strategy. I could suggest pt-archiver from the Percona Toolkit.
Internally, when doing table copies for e.g. ALTER TABLE, InnoDB does in fact do exactly that, batching the copy into many smaller transactions.