Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Reston, VA
    Posts
    59

    Unanswered: Perl Script for MySQL Conversion

    hello all,

    i'm using a Perl script to read an old database then convert to a new schema. For every record read (currently around 15k, but in production 100k), the script could generate anywhere between 1 and 4 queries. I cannot use any of the DB utilities for conversion or upload because the new schema is so different from the old (i.e. one table entry is broken out into man).

    when the script is run, my cpu usage jacks up to almost 100% (specifically, mysqld process). with so many queries i understand the load difference.....however, what other choice do i have for the conversion? is there a better way to run this operation without causing so much strain on the box?

    thanks

  2. #2
    Join Date
    Aug 2003
    Posts
    11
    If your only doing the query once whys it matter?

  3. #3
    Join Date
    Aug 2003
    Posts
    13
    while he(she? I'll use he and please correct me if I'm wrong) runs the program, his computer will pretty much be unusable for other things. Also, the program will run forever. And this may be a program that is useful to other people so making it faster would be a plus.

  4. #4
    Join Date
    Aug 2003
    Location
    Reston, VA
    Posts
    59
    Yeah, one thing I was doing was a query for every record to check for existance. however, i can accomplish that with one query that I read into a hash table instead. it'll be a large hash and maybe suck up some memory, but the tradeoff would be less strain on the box.

    I do have to run this conversion and it'll probably happen more than once. I may use 'pset' to put the priority down. I'm told that if enough processes get backlogged that the box could theoretically go down. We may just buy a new box for this application (or a test box). My main concern was doing this on a production web server (scary thought).

    So, I have the hash and pset to help on my problem. Either (any) of you have better ideas to accomplish this goal?

    1.) Read Database Records from DB
    2.) Perform Conversion Operations (change fields as necesary)
    3.) Insert old record into new table or tables (the old record is now broken out into 3 tables) - this would be a great time for MySQL to support 'transactions' as I'm dealing with errors by logging and keeping my fingers crossed)

    Anyway, thanks for the ideas, but definately keep them coming. I'll let you all know if I figure something out.

Posting Permissions

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