7

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

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
Mich Dart
  • 191
  • 1
  • 4
  • 3
    CPU won't be your problem, Disk i/o will. –  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) –  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. –  Sep 10 '12 at 12:22
  • How big is your table? –  Sep 10 '12 at 12:22
  • more than 1Gbytes –  Sep 10 '12 at 12:23
  • [This](http://stackoverflow.com/questions/2790333/insert-into-select-vs-dumping-loading-a-file-in-mysql) may be of interest. – Kermit Sep 10 '12 at 13:34
  • Unfortunately no. Maybe I should copy data with a server side script, making use of LIMIT. –  Sep 10 '12 at 14:39
  • 1
    What storage engine are you using? Innodb or myisam? Same for both tables? – atxdba Sep 11 '12 at 14:14
  • I'm using Innodb on both tables. – Mich Dart Sep 12 '12 at 07:29

2 Answers2

2

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.

Community
  • 1
RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
  • 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
  • What about the data inserted into A before A finishes getting copied to B? Won't B be incomplete? – KeatsKelleher Jul 27 '17 at 16:48
1

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

Cristian Porta
  • 1,012
  • 1
  • 7
  • 21
  • 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