fork-ing : my lack of understanding

ethrbunny has asked for the wisdom of the Perl Monks concerning the following question:

Im trying to use Parallel::ForkManager to split off some slow processes in hopes of increasing overall performance. The 'main' task is one that should lend itself well to parallel activity: examining 'client data' in a db by way of 10-15 queries each of which reports some value. There are ~100 'clients' in the db. The current application takes each client in sequence, runs the series of queries and saves the results in a table.

The vast majority of the processing time is spent waiting for the DB server. The server is running MySQL on an 8 way box with 8G of RAM. The box running the application(s) is a dual core with 1G of RAM.

Here's what I did and what confused me:
I used the sample(s) from Parallel::ForkManager to rewrite my app so that instead of looping through the list of available 'clients' it gets them all in an array and calls

 foreach $row ( @{$ref} ) {
     my $pid = $pm->start(@$row->[0]) and next;
     my $out = `./alert_main.pl -h $row->[0] -p $passId`;
     print $out;
     print "\n";
     $pm->finish($row);
 }
[download]

So it spins off the number of processes that ForkManager is called with, each runs, exits and the next one starts. I see it doing its work via the callbacks that ForkManager provides. All well and good. Here's where Im confused:
I run the application with 'max_procs' set to 1 and I get a total run time thats equivalent to my old 'one at a time' method. When I change 'max_procs' to 8 it takes more than 4 times longer to complete. I watch the CPU activity on the DB box and MySQL is (apparently) using all 8 procs. The total load runs at around 600%. The box that the app is running on never goes above 5%.

What am I doing wrong?

Comment on fork-ing : my lack of understanding Download Code

Replies are listed 'Best First'.
Re: fork-ing : my lack of understanding by Joost (Canon) on Jul 19, 2007 at 20:52 UTC
If I read your code correctly, you're forking a new process for each row, then in that process you start another process (the `./alert_main.pl line) that presumably does something with that row. That strikes me as very inefficient. You'll probably get much better efficiency if you set up each sub-process to handle a more significant chunk of data (for instance, process 20% of the total number of rows). Fork() and exec() are pretty fast on most UNIX systems, but not THAT fast. Also, inlining alert_main.pl will probably make the system a LOT more efficient. Also, unless ./alert_main.pl actually does something with the database this is never going to improve the performance, if the bottleneck is in waiting for the rows to arrive. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]
Re^2: fork-ing : my lack of understanding by ethrbunny (Monk) on Jul 19, 2007 at 22:11 UTC
Also, unless ./alert_main.pl actually does something with the database this is never going to improve the performance, if the bottleneck is in waiting for the rows to arrive. ./alert_main.pl makes the calls to the db and waits for the response. I was hoping that I could speed up the whole app by running n copies of 'alert_main.pl' simultaneously each of which would have a separate piece of the whole process.	[reply]
Re^3: fork-ing : my lack of understanding by Joost (Canon) on Jul 19, 2007 at 22:49 UTC
It's possible that some sort of locking / transaction scheme is making concurrent queries slower than running consecutive queries. The answer probably lies in examining what alert_main.pl is doing exactly "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re: fork-ing : my lack of understanding by ikegami (Patriarch) on Jul 19, 2007 at 21:36 UTC
First of all print `...`; is silly. Just use `system`. `foreach $row ( @{$ref} ) { my $pid = $pm->start(@$row->[0]) and next; system 'alert_main.pl', '-h', $row->[0], '-p', $passId; print "\n"; $pm->finish($row); }` [download] Second, `system ...; exit;` is also silly. Just use `exec`. `foreach $row ( @{$ref} ) { my $pid = $pm->start(@$row->[0]) and next; exec 'alert_main.pl', '-h', $row->[0], '-p', $passId; $pm->finish($row); # In case exec fails. }` [download] By using `exec`, you only fork once per row instead of twice. Unfortunately, that's probably not enough to get rid of your problem.	[reply] [d/l] [select]
Re^2: fork-ing : my lack of understanding by ethrbunny (Monk) on Jul 19, 2007 at 22:13 UTC
I'll switch to 'exec' and give it another go-round. I figured this was inefficient but it was just a test to see if this concept would work.	[reply]
Re: fork-ing : my lack of understanding by roboticus (Chancellor) on Jul 20, 2007 at 11:00 UTC
ethrbunny: I've never used Parallel::ForkManager, so I can't comment on your code. But it may be that you're beating your database into submission. It could be thrashing badly. When you run operations in parallel, it's common to see the average runtime of each query increase, but when it increases overmuch, it's time to start looking at your queries. You should take a look at your database structure (relationships, table definitions, triggers, etc.) and compare it with your query to see if you're doing things efficiently. Are you using your indexes effectively? It may be that you can significantly shrink the runtime by computing a table of partial results and then querying that, for example. ...roboticus	[reply]