Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
3  
CPU won't be your problem, Disk i/o will. –  Nin Sep 10 '12 at 12:19
    
I guess you truncate the table you migrate to all the time? then you should consider only migrating the difference to the last migration (aka saving the timestamp when last migration was done and only transfer rows with timestamp greater than that) –  Najzero Sep 10 '12 at 12:19
    
Maybe you want to temporarily delete indexes before moving, then recreating them after the successful migration. –  biziclop Sep 10 '12 at 12:20
    
Yes, before the query execution I disable indexes. But still I notice disk/cpu overload. –  Mich Dart Sep 10 '12 at 12:22
1  
What storage engine are you using? Innodb or myisam? Same for both tables? –  atxdba Sep 11 '12 at 14:14
show 5 more comments

migrated from stackoverflow.com Sep 11 '12 at 13:37

This question came from our site for professional and enthusiast programmers.

2 Answers

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:

Give it a Try !!!

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

share|improve this answer
    
Let me understand your first suggestion. You are using a temp table (table_new) where data get saved. At the end, you perform an SQL query pretty similar to mine... Is yours more efficient? Will I have disk i/o problems? –  Mich Dart Oct 20 '12 at 16:21
add comment

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

share|improve this answer
    
This is a good solution, but requires a manual intervention or a shell-based command. I'll take it as valid option. Thank you –  Mich Dart Oct 20 '12 at 16:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.