Re: performance of extended insert vs. load data
mysqlimport with parallel threads is worth giving a try. It is similar to 'load data infile' but with concurrent threads loading the tables. I think , it was added in mysql-5.1.18. But it is said to work with previous versions also according to the author : http://krow.livejournal.com/519655.html -Ravi 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" < [EMAIL PROTECTED] > To: mysql@lists.mysql.com 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! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of extended insert vs. load data
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" < [EMAIL PROTECTED] > > To: mysql@lists.mysql.com > 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of extended insert vs. load data
On 7/23/07, mos <[EMAIL PROTECTED]> wrote: Load data will of course be much faster. However to obtain the maximum speed you need to load the data to an empty table, because then MySQL will load the data without updating the index for every row that's added, and will instead rebuild the index only after all data has been loaded, which of course is much faster. He's using InnoDB tables, so it doesn't make much difference. MyISAM tables have a fast bulk index rebuild, but InnoDB do not. The other alternative is to remove all indexes prior to using load data and build the index manually when the data has been loaded. The easiest way to do that is with ALTER TABLE DISABLE KEYS and ENABLE KEYS. But again, not much help with InnoDB tables. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of extended insert vs. load data
At 11:44 AM 7/23/2007, Sid Lane wrote: 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! Load data will of course be much faster. However to obtain the maximum speed you need to load the data to an empty table, because then MySQL will load the data without updating the index for every row that's added, and will instead rebuild the index only after all data has been loaded, which of course is much faster. The other alternative is to remove all indexes prior to using load data and build the index manually when the data has been loaded. (Unique indexes may pose a problem because it will throw an error if it finds a duplicate index- but if you verified uniqueness in Oracle, then it won't give you a problem) The big drawback with Load Data is of course there is no exception log kept. You only know the number of rows added. With Insert you at least have a chance to log the errors yourself. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of extended insert vs. load data
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" < [EMAIL PROTECTED] > To: mysql@lists.mysql.com 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
Re: performance of extended insert vs. load data
On 7/23/07, Sid Lane <[EMAIL PROTECTED]> wrote: is either one significantly faster than the other? Yes, LOAD DATA INFILE is much faster. are there additional (faster) approaches I have not thought of? Not that I've found. I think you'd have to write directly to the C API to beat LOAD DATA INFILE. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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!
