List:General Discussion« Previous MessageNext Message »
From:B. Keith Murphy Date:July 23 2007 5:59pm
Subject:Re: performance of extended insert vs. load data
View as plain text  
I think you will find the load data infile will work faster. I am performing testing right
now in preparation for a migration from 4.1 to 5.0 but I am confident that will be the
case. 

Keith 
----- Original Message ----- 
From: "Sid Lane" < jakes.dad@stripped > 
To: mysql@stripped 
Sent: Monday, July 23, 2007 1:44:53 PM (GMT-0500) America/New_York 
Subject: performance of extended insert vs. load data 

all, 

I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 
5.0one in as short a window as practically possible (throw tablespace 
in r/o, 
migrate data & repoint web servers - every minute counts). 

the two approaches I am considering are: 

1. write a program that outputs the Oracle data to a fifo pipe (mknod) and 
running a "load data infile" against it 

2. write a program that dynamically builds extended insert statements up to 
length of max_allowed_packet (similar to mysqldump -e) 

is either one significantly faster than the other? I know I could benchmark 
it but I was hoping someone could save me writing #2 to find out if it's not 
the way to go... 

are there additional (faster) approaches I have not thought of? 

FWIW these are 95% innodb (5% myisam are static reference tables & can be 
done in advance). 

thanks! 


-- 
B. Keith Murphy 
Database Administrator 
iContact 
2635 Meridian Parkway, 2nd Floor 
Durham, North Carolina 27713 
(o) 919-433-0786 
(c) 850-637-3877 
Thread
performance of extended insert vs. load dataSid Lane23 Jul
  • Re: performance of extended insert vs. load dataPerrin Harkins23 Jul
  • Re: performance of extended insert vs. load datamos23 Jul
    • Re: performance of extended insert vs. load dataPerrin Harkins23 Jul
  • ordering datesRoss Hulford12 Sep
    • Re: ordering datesMichael Dykman12 Sep
    • Re: ordering datesPhilip Hallstrom12 Sep
    • RE: ordering datesJerry Schwartz12 Sep
Re: performance of extended insert vs. load dataB. Keith Murphy23 Jul
  • Re: performance of extended insert vs. load dataMogens Melander24 Jul
  • Re: performance of extended insert vs. load dataRavi Prasad24 Jul