http://qs1969.pair.com?node_id=443177

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

Anybody have any problems with Parallel::ForkManager or maybe even DBI eating up resources?

I currently run a script that forks upto 5 children using ForkManager with upto a total 140 children by the time it's done.

When I run a big load (133), my CPU load slowly climbs from 5 to over 20 by the time it finishes. Once the whole script is done, it drops back down to ~5-7ish (not immediately, but 2x the speed it climbed).

Any thoughts?

I do everything by the book; Opening a DBI process per child, closing and unsetting the veriable for each close.

-- philip
We put the 'K' in kwality!

Replies are listed 'Best First'.
Re: Parallel::ForkManager using up Resources?
by cazz (Pilgrim) on Mar 29, 2005 at 16:49 UTC
    Ouch. 140 children all with their own DB connections? And you create a new connection per child? Surely there is a better way.

    First off, you should probably cut down the number of children. Most database performance numbers fall off pretty quickly when you start adding a number of children doing anything more than reads.

    You should also look at using a pool of database handles. Why create and then close database connections over and over again if you don't have to? I sped up the performance of a data loading script by a few orders of magnitude by moving the dbh create/destroy outside of the main loop.

      It's a max of 5 children at once. I tried to use a pool, but couldn't figure out a way to make sure none of the children used a wrong handle. Care to share how you got past this one? I tried an array with $cnt++ % 5, but no way to guarentee each child will finish in order.

      Each child starts off opening a connection and then calls a close method which closes the connection. All that works and in order. The code is very verbose and I see that part just fine.

      -- philip
      We put the 'K' in kwality!

      Now I'm at a loss, how did you successfully create a pool of DBI handles with multiple children? I have a pool set, but as soon as my first child ends, DBI tries to clean itself up and kills all my other DBI handles with "end-of-file on communication channel during global destruction" error.

      My code (trimmed down):

      my %pool; my %dbhs; $MAX = 5; $pm->run_on_start(\&_child_start); $pm->run_on_finish(\&_child_finish); my @dbhs = start_dbh(); %pool = map { $_ => 1 } @dbhs; foreach $i ( @is ) { ($next) = grep ( defined($pool{$_}), @dbhs ); $pid = $pm->start($next) and next; $sys->set_dbh($dbhs{$next}); sleep(rand(10)); $pm->finish; } sub start_dbh() { %dbhs = map { $_ => DBI::Connect(); } 0 .. $MAX; return keys %dbhs; } Sys::set_dbh() { my $this = shift; $this->{'_dbh-child'} = shift; } sub _child_start() { my ($pid, $ident) = @_; delete($pool{$ident}); print "**** STARTED: child with PID $pid"; } sub _child_finish(){ my ($pid, $exit, $ident) = @_; print "**** FINISHED: child with PID $pid exit cide $exit"; $pool{$ident} = 1; }

      And as soon as the first child dies all my DBI handlers spew out: DBD::Oracle::db DESTROY failed: ORA-03113: end-of-file on communication channel (DBD Error: OCISessionEnd) during global destruction.

      -- philip
      We put the 'K' in kwality!

Re: Parallel::ForkManager using up Resources?
by kvale (Monsignor) on Mar 29, 2005 at 16:49 UTC
    Without seeing your code, it is hard to tell. But it sounds like you might not be waiting for your current children to die before spawning new children. Then finally at the end of the parent program, all the children are reaped.

    -Mark

      All using ForkManager. Children die and close their DB handles in order. Quickly post:
      foreach my $ln (@lines) { $pm->start && next; ** child starts my $dbh = $this->_child_db(); ... code here ** child finishes with _close_child(); $this->_close_child(); $pm->finish } $pm->wait_all_children() _child_db() { if (!$this->{'_dbh-child')) { print $$ . ": Creating Child DB Handle"; $this->{'_dbh-child'} = new DBI::Connect ......; } return $this->{'_dbh-child'}; } _close_child() { print $$ . ": Closing child DB Handle"; $this->{'_dbh-child'}->disconnect(); delete($this->{'_dbh-child'}); }

      Very rough, please don't try to compile it. All my code is on an internal system not connected to the internet.

      My code is all very verbose. Everything is working correctly and in order. All children to finish, total 6 processes max ever within top. Finish is always seen before a Start.

      -- philip
      We put the 'K' in kwality!

        Also -- I've added close and finish methods for each child process through ForkManager, too:
        $pm->run_on_start(\&_child_start); $pm->run_on_finish(\&_child_finish); sub _child_start() { my ($pid, $ident) = @_; print "**** STARTED: child with PID $pid"; } sub _child_finish(){ my ($pid, $exit, $ident) = @_; print "**** FINISHED: child with PID $pid exit cide $exit"; }

        -- philip
        We put the 'K' in kwality!

Re: Parallel::ForkManager eating up Resources?
by cazz (Pilgrim) on Mar 29, 2005 at 22:29 UTC
    You never answered my first question.

    Why are you creating & closing oracle connections? Unless there is some special reason to do that (aka, tasks running as different oracle users), don't do that. Creating & destroying oracle connections so often is where your load jump is coming from. You should switch your model from:
    foreach $task (@tasks) { fork/connect/execute/disconnect/exit }
    To:
    for($i = 0; $i < $numchilds; $i++) {fork; connect; while (get_task) { +run_task}; disconnect; exit;}

      The reason I'm doing that is cause I've read, on node Using fork with DBI to create simultaneous db connections., that DBI hates forking and you must use a new DBI connection for each child. Else you'll get DBI stepping on it's feet and causing unpredictable results.

      Initial thought of creating the script was to run a child for each system; each system runs aprox 45 seconds upto 1 minu 30 seconds before it finishes.


      for($i = 0; $i < $numchilds; $i++) {fork;  connect; while (get_task) {run_task}; disconnect; exit;}

      You're suggesting taking the pool of tasks, split them into 5 arrays and then run them?

      My tasks is updating a load of 100+ server stats in a database. I'll have to use some kind of splitting to split the array of hosts into 5 arrays (as evenly as possible) for splitting into individual forks.

      That's a possiblity...

      Thanks, I'll try that.

      -- philip
      We put the 'K' in kwality!