List:General Discussion« Previous MessageNext Message »
From:Mogens Melander Date:July 24 2007 12:52am
Subject:Re: performance of extended insert vs. load data
View as plain text  
Shure, load data is way faster than full inserts.

I was thinking:

while $warnings -lt 100%
do
  dump ora-data | mysql database
done

swap IP-addr.


On Mon, July 23, 2007 19:59, B. Keith Murphy wrote:
> 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
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>


-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

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