I want to migrate data between 2 InnoDB tables.
Currently I'm running this query:
INSERT INTO table_a SELECT * FROM table_b;
If the dataset grows, what's the best way to avoid CPU overload?
|show 5 more comments|
Since you are using InnoDB I would like to suggest the following:
If you do INSERT, UPDATEs, and DELETEs, bulk load the table like this:
If you do nothing but INSERTs and SELECTs, load new entries into the table. Assuming the primary key of
SUGGESTION #2 : Tune InnoDB for Multiple CPUs
Make sure you are using MySQL 5.5. If you have MySQL 5.1.38 or above, you must install the InnoDB Plugin. If you have MySQl 5.1.37 or prior, just upgrade to MySQL.
Once you have do that (or if you already have MySQL 5.5), you must tune InnoDB for Multiple CPUs. Rather than reinvent the wheel, here are my past posts on how and why to do so:
Give it a Try !!!
I could suggest other things such as buffers, log files, and so forth. I only addressed just these two concerns.
In this case, normally, the best solution is a mysqldump using the
tab option produce 2 file, one file -table_a.sql- that contains only the table create statement and the oher file -table_a.txt- contains tab-separated data.
Now you can create your new table
Then you simply load data in your new table via
LOAD DATA is usually 20 times faster than using INSERT statements.
Hope this helps