Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2006
    Posts
    11

    Unanswered: Insert 30 millions rows

    Hello,
    I want to move or backup 30 millions rows from a MySQL database to another one (also MySQL).
    Please give me some advices on how can I do this process in the fastest way AND especially I need to know the required time to do this process. Thanks.

  2. #2
    Join Date
    May 2006
    Posts
    11

    Insert 30 millions rows

    Hello,
    I want to move or backup 30 millions rows from a database to another one.
    Please give me some advices on how can I do this process in the fastest way AND especially I need to know the required time to do this process. Thanks.

  3. #3
    Join Date
    May 2006
    Posts
    11

    Insert 30 millions rows

    Hello,
    I want to move or backup 30 millions rows from a database to another one.
    Please give me some advices on how can I do this process in the fastest way AND especially I need to know the required time to do this process. Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    if you are planning to move data from a table in one db to another, try bcp..
    Cheers....

    baburajv

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    since it involves two different databases, I would think the best way is to export the data to a file from the source and LOAD into the Target.

    At the source :
    EXPORT TO table1.ixf of ixf select * from table1

    On the target:
    LOAD from table1.ixf of ixf savecount 1000000 insert into table1

    It is not easy to predict the time that will be needed. It will depend on a number of factors like the table and index defn, row size, your disk system, db and dbm config etc.

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    are you moving the whole database or just part of it?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nobody can tell you the required time. You will have to try it and see, and it will probably vary some from one execution to the next as network activity, CPU usage, and drive space change.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by holy_joy
    in the fastest way
    Do we get to rearchitect the system as well?

    Is this one table? Is the database on another server (I would hope so).

    what's the color of the hair of the women sitting closest to you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    Quote Originally Posted by holy_joy
    Hello,
    I want to move or backup 30 millions rows from a MySQL database to another one (also MySQL).
    Please give me some advices on how can I do this process in the fastest way AND especially I need to know the required time to do this process. Thanks.
    What is the table type? With MyISAM you can copy the files directly, but you don't want to do that with MySQL running.

    How long it takes? Well, it depends on your amount of memory, number and speed of CPUs, version of MySQL, your operating systems, number and speed of disks, buses, connections to SAN or fileservers, ...

    I would use mysqldump to dump table data from server A directly to server B. Just redirect the output to the MySQL client connected to your target server and go grab some lunch. With reasonable equipment, you should probably be able to expect 10,000 rows/second, so it might take an hour or so.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What next? Are you going to ask whether the carpet matches the drapes?

    (In the office, of course...)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I'll byte. Holy_Joy must be doing an assignment that requires them to find the best database for this problem. So far they've asked for DB2, Microsoft SQL, and MySQL. The teacher must have done the legwork for Oracle as an example for the class!

    -PatP

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I'll byte. Holy_Joy must be doing an assignment that requires them to find the best database for this problem. So far they've asked for DB2, Microsoft SQL, and MySQL. The teacher must have done the legwork for Oracle as an example for the class!

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I'll byte. Holy_Joy must be doing an assignment that requires them to find the best database for this problem. So far they've asked for DB2, Microsoft SQL, and MySQL. The teacher must have done the legwork for Oracle as an example for the class!

    -PatP

  14. #14
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    Aha, interesting. But what kind of assignment is that? Seems pretty useless to me

  15. #15
    Join Date
    May 2006
    Posts
    11
    lol PAT PHELAN!
    I hope you byted... But I don't think u have an idea of the problem here, maybe in few month u'll probably guess
    Anyway thanks for replying!!!!!!!!! it was funny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •