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

Monks,

I'm currently using Sybase::BCP to upload a set of rows to a database. I need to do this more than once, and hence I've written the following sub to allow me to do this easily: 

sub bcpInsert
  {
    my ($table, $_rows) = @_;

    # configure BCP for the push
    BCP_SETL(TRUE);
    my $bcp = new Sybase::BCP $user, $pass, $server;
    $bcp -> bcp_init($table, "", "", DB_IN);
    $bcp -> bcp_meminit((1 + $#{@$_rows[0]}));
             
    # perform the db upload
    my $count;
    foreach my $row (@$_rows) {
       $bcp -> bcp_sendrow($row);
       $bcp -> bcp_batch unless (++$count % 100); # commit every 100 r
+ows
    }
    $bcp -> bcp_batch; # commit the last batch of data
    $bcp -> bcp_done;

    warn "Debugging: bcpInsert returning ..\n";
}

[download]

Now, this uploads data to the tables perfectly, and I'm happy with that portion of the script. However, as the sub returns, the system slows down to almost a halt, and top reports iowait tending to 100%.
I've tried outputting a line of text immediately following this sub, and it can take up to 5 minutes for this line to print following the return of the sub.

I'm calling the sub as follows: 

# build an array of arrays for BCP to upload
&bcpInsert("custdata", \@data);
print "Upload complete\n";

[download]

Any suggestions about how to get round this problem would be appreciated.

Thanks ..
-- Foxcub

Replies are listed 'Best First'.
Re: Sybase::BCP & iowait
by robartes (Priest) on Nov 12, 2002 at 14:55 UTC
    Let me state upfront that I have no experience whatsoever with Sybase::BCP (or Sybase for that matter).

    That said, if you are sure that the disk writes happen when you return from the sub (i.e. after your warning message), than I suspect that they are due to the destruction of one of your lexically scoped variables. $count is an unlikely candidate, so that leaves $bcp. Perhaps the destructor of Sybase:BCP is doing the spurious disk writes - it might be committing your changes. Is there no specific method to commit your changes to the DB? Then again, calling such a method would only move the disk writes a bit earlier in time, so that would not help much. BTW, can the amount of data you are entering into the DB justify the amount of disk activity you observe?

    Not much help, I'm afraid, but perhaps this might point you in the right direction (in a vague, fuzzy kind of way :) ). Have a look at the Sybase::BCP destructor.

    CU
    Robartes-

    Update: You might of course simply be experiencing a swapstorm, as fglock suggests, in which case you are probably out of luck.

[reply]
      The $bcp -> bcp_batch command commits the data - in this case every 100 rows.

      The amount of data could cause a block on a disk write - I've seen similar with Data::Dumper when trying to output a hash of similar size to disk.

      The memory usage doesn't seem to be excessive .. this was one of the first things I looked at, but it simply appears to block on a disk write.

      I'll do as you suggest and look at the BCP destructor; hopefully that'll shed a little light on the issue.

      Cheers for the help ..
      -- Foxcub

[reply]
[d/l]
Re: Sybase::BCP & iowait
by mpeppler (Vicar) on Nov 12, 2002 at 16:24 UTC
    And another thing - if you are using a reasonably recent version of Sybase you should probably consider recoding this using Sybase::CTlib (and/or Sybase::BLK). The old DBlib API does not support bcp-ing data into DOL (row level locking) tables, and does not support wide varchar columns, etc.

    Using Sybase::CTlib your routine becomes something like this: 

    sub bcpInsert
  {
    my ($table, $_rows) = @_;

    my $bcp = new Sybase::CTlib $user, $pass, $server, 
                 undef, {CON_PROPS => {CS_BULK_LOGIN => CS_TRUE}};
    $bcp -> blk_init($table, $#{@$_rows[0]}, 0, 0);

    # perform the db upload
    my $count;
    my $rows;
    my $totrows = 0;
    foreach my $row (@$_rows) {
       $bcp -> blk_rowxfer($row);
       $bcp -> blk_done(CS_BLK_BATCH, $rows) 
          unless (++$count % 100); # commit every 100 rows
       if($rows != 100) {
           warn "blk_done(BATCH) only committed $rows rows";
       }
       $tot_rows += $rows;
    }
    $bcp -> blk_done(CS_BLK_ALL, $rows); # commit the last batch of da
+ta
    $tot_rows += $rows;
    $bcp -> blk_drop;

    warn "Debugging: bcpInsert loaded $tot_rows rows\n";
    warn "Debugging: bcpInsert returning ..\n";
}

    [download]

    Michael

[reply]
[d/l]
Re: Sybase::BCP & iowait
by fglock (Vicar) on Nov 12, 2002 at 14:56 UTC

    You might also be running out of RAM, and the system slows down while it accesses virtual memory.

    Does it also happen when you try with a few records?

[reply]
      No, it doesn't seem to .. it only seems to happen when I call bcpInsert for the second time (ie, more than once - I just tried taking out all but one of the calls).

      I'll check everything's scoped correctly in the sub .. though it's strange that it manages to upload the data and only dies on the second return.

      Thanks for the idea ..
      -- Foxcub

[reply]
Re: Sybase::BCP & iowait
by mpeppler (Vicar) on Nov 12, 2002 at 16:05 UTC
    A couple of issues:

    First, although your are creating a Sybase::BCP object, you are really using methods in Sybase::DBlib (which are inherited by Sybase::BCP).

    To start off I'd suggest changing the Sybase::BCP reference to Sybase::DBlib and see if that improves things.

    Still, that shouldn't create the problems that you are seeing (or at least I don't think so!)

    In any case all of the IO due to the bcp operation should be done by the time bcp_batch and bcp_done return. Is the database server on the same box, or are you going through the network?

    Other than the possible swap storm mentioned earlier I don't see what could be causing this problem...

    Michael

[reply]
      Michael,

      I've changed the BCP reference to DBlib; though this has had little effect on the problem.

      The db server is not local - it's accessed via the network.

      Is it possible that the DB is getting swamped and it's this that I'm waiting for, do you think?

      Thanks for the help.
      -- Foxcub

[reply]
        Is it possible that the DB is getting swamped and it's this that I'm waiting for, do you think?
        No, I don't think so. bcp_batch() shouldn't return until all the data has been transfered to the server, and the server has acknowledged that it has committed all the rows.

        I think the problem is elsewhere...

        Michael

[reply]
Re: Sybase::BCP & iowait
by krisahoch (Deacon) on Nov 12, 2002 at 15:31 UTC

    Foxcub,

    I would try opening and closing the database handle only once. In other words, open and close it somewhere outside of the bcpInsert subroutine.

    Also, the code that you posted probably isn't the culprit. Please post code for the following subroutines... 

    bcp_init();
bcp_meminit();
bcp_sendrow();
bcp_batch 
bcp_done;

    [download]
    I think your problem may lie somewhere in that list

    Kristofer Hoch

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom