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?

Answers


No, it's not possible to avoid the transactional overhead in a simple way. You would perhaps have two options:

  1. In your own application, use many smaller transactions (of e.g. 10k rows each) to copy the data in small batches.
  2. 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.


Need Your Help

Make a timeline by using vis.js but it cannot change x-axis value

timeline vis.js

I am using vis.js to draw a timeline, like the picture below,