MySQL : perform a big data migration between tables

Question

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

Answer 1

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

Answer 2

In this case, normally, the best solution is a mysqldump using the --tab option like this:

mysqldum --tab=/path/to/serverlocaldir --single-transaction <database> table_a

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

create table table_b like table_a;

Then you simply load data in your new table via LOAD DATA without care for the table's name.

LOAD DATA INFILE '/path/to/serverlocaldir/table_a.txt' 
  INTO TABLE table_b FIELDS TERMINATED BY '\t' ...

LOAD DATA is usually 20 times faster than using INSERT statements.

Hope this helps