MySQL : perform a big data migration between tables

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?

Thanks

Since you are using InnoDB I would like to suggest the following:
SUGGESTION #1

If you do INSERT, UPDATEs, and DELETEs, bulk load the table like this:

CREATE TABLE table_new LIKE table_a;
INSERT INTO table_new SELECT * FROM table_b;
ALTER TABLE table_a RENAME table_old;
ALTER TABLE table_new RENAME table_a;
DROP TABLE table_old;

If you do nothing but INSERTs and SELECTs, load new entries into the table. Assuming the primary key of table_a andtable_b is id, perform the load like this:

CREATE TABLE table_new LIKE table_a;
INSERT INTO table_new SELECT B.* FROM table_b B
LEFT JOIN table_a A USING (id) WHERE A.id IS NULL;
INSERT INTO table_a SELECT * FROM table_new;
DROP TABLE table_new;

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:

• May 26, 2011 : About single threaded versus multithreaded databases performance

• Sep 12, 2011 : Possible to make MySQL use more than one core?

• Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian

• Apr 26, 2012 : Is the CPU performance relevant for a database server?

Give it a Try !!!

I could suggest other things such as buffers, log files, and so forth. I only addressed just these two concerns.

StackExchange Database Administrators Q#24116, answer score: 2