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?
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.
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.