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? should the fact that the mysqld process goes up to 98% be bothersome or does it simply meant that mysqld is taking all CPU available? Thoughts..? thanks again...

"without causing so much strain on the box?" If you wanted to, you could have Perl pause between each access, read, and write, and take a break. But then, this seems just as pointless as worrying about straining your computer (at least in my mind). Don't take it the wrong way; I'm just giving my logic. But, if you are searching for a more efficient way of doing things, then it may be far more work than is worth the task at hand. And Perl may not be the language of choice in which to implement your algorithm(s). You're asking Perl and MySQL to take an entire database, read everything in it, and write it into a whole new database. I/O is I/O. It is going to make your system work. "does it simply mean that mysqld is taking all CPU available?" Yes. Your CPU (x86 I'm assuming) is essentially linear (a few tricks make some calculations parallel, but in the end its all one long pipe). Big, multi-dimensional tasks will fill up that pipe easily.

Hi Anon, Yeah, this is pretty much a one time deal, but i may have to confront similar situatiions in the future so I guess maybe I'm looking for a better 'strategy'. Perl is just something I'm used to and with DBI I felt it would be a simple tool for a conversion because, as i stated above, I cannot use mysqldump/ mysqlimport. The point about I/O is well taken. I suppose I could put a wait in and simply run the process in the background. It'll take a bit longer (maybe a day for instance), but it would accomplish what I need to get done and slow down the I/O. The problem is something I need to solve so when I do come up with something I'll let you all know. What type of "strategy" would you take to solve this problem? Another programming language? Another DB? If this was not MySQL could it just be a stored proecedure (transactions would be nice too :)? Well, I have MySQL, two DBs that need to fit (square peg, round hole) and one production box (I'm hoping that'll change). Well, I'll get back to you all when I know....

Just an update...the system administrator I work with looked at the load average while running the script and was pretty comfortable with it's level. Looks like I may have been looking at the wrong numbers. Anyway, I may not even implement for a few weeks, but it looks like I can move forward. Advice on the strategy for the future is still welcomed! Thanks again!