Wesley, Forgive me because I'm not overly familiar with Oracle, and this is an unproven idea....
If you can run the oracle export function from unix shell, you could do something like the following.... -- Install the mysql client on the oracle db server (will be quick and won't need any downtime) -- grant permissions on the new mysql server to allow the newly installed remote client access to it -- make sure the mysql client on the oracle server can write to the new mysql db ok -- create all the required tables on the new mysql server's db (leaving out indicies if you want to gain performance) -- In a script (using variables and loops), run something like the following from shell on the oracle server (where "t" is the table name): [shell]# Oraclexportutility --tabdelim t > t; mysqlimport multimaps t;sleep 5;rm -f t (where "Oraclexportutility --tabdelim t" is the oracle export widget that I know nothing about!!) I know that the same method works with: [shell]# mysql -B -N --execute="select * from t" multimaps > t; mysqlimport multimaps t;sleep 5;rm -f t If you can find a way to pipe the output straight to the mysql import utility without writing a copy to disk then that's even better! (I drew a blank at this one..) Also, if your tables are small enough to use a ramdisk for the temp table "t" then that's good too. You could run several of these occurences at once (although it would hammer your disks) to make the most of the bandwith available. There are lots of options to mysqlimport such as: -C, --compress Use compression in server/client protocol. -f, --force Continue even if we get an sql-error. -i, --ignore If duplicate unique key was found, keep old row. -r, --replace If duplicate unique key was found, replace old row. And (I quote) If one uses sockets to connect to the MySQL server, the server will open and read the text file directly. As I said, these are ideas & I hope it will at least give you some ideas too... Cheers, Andrew -----Original Message----- From: Wesley Brown [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 20 November 2002 20:24 To: Simon Windsor; [EMAIL PROTECTED] Subject: RE: The dreaded move from Oracle to MySQL Well, it looks like Simon has the best possible answer so far. I did a test dump into a delimited file from Oracle and imported that using the LOAD command in mysql and sustaned about 11,000 inserts a second. This is with no indexes on the mysql tables at all. I have also tried using the dbtools import function but it will ether time out or just hang on the big tables. --Simon Says---- To do this I would do the following, - on Oracle server - - Create a NFS device - - Create a FIFO pipe, using mknod - - export data from Oracle to FIFO pipe - on MySQL server - - Mount remote NFS device - - Use MySQL Load command to load data, or write your own loader in perl - - when load is finished, build the MySQL indexes Can you explain this procedure to me a little better or point me to something I can read? I still have to convince the person who manages the Oracle box to open up an NFS share. Another way we could increase load times would be to use 4 scripts to load data from 4 tables at once. We have the bandwith to handle that and the MySQL box seems to be handling request just fine at 11,000 inserts its still not tapping out both procs or killing the disk I/O. Thanks to everyone who responded so quickly! Cheers, Wes --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
