FAQ
Greetings, list.

I apologize if this is a bit off topic...

I'm running into a major problem with an apparant memory leak.

I need to be able to transfer about 6500 records across databases. A good
number of these records are blob types, at about 25k each.

My problem is that TOP reports my memory use as constantly climbing, and I
can't seem to release any of it. At the 1000 record mark, I'm using just
under a Gig of ram....with 5500 records to go! (I'm using SunOS 5.8 on
Sparc, if it matters).

Please look at my snippet, and offer any suggestions. I've pulled as much
unnecessary, extraneous code as possible for posting purposes (please
forgive any resulting syntax errors, etc):

sub test {
my ($query, $dbh, $qti_row, $sth, $returns, $row);

$query = qq/ USE database_name;
SELECT id, column1, picture1, picture2
FROM table
ORDER BY id;/;

dbh = DBI->connect("dbi:Sybase:server=x.x.x.x",'user','password');

$returns = $dbh->selectall_hashref($query,'id');

$dbh->disconnect();

# Trying to release some memory here...
foreach $row(keys %$returns){
# I've tried both methods:
#$returns->{$row} = undef;
undef $returns->{$row};
}
# Again, both methods tried:
#$returns = undef;
undef $returns;

#But at this point, the program hasn't released any memory.
sleep(30);

}

Of note, is that changing from selectall_hashref to selectall_arrayref only affected how much memory was used...but I still couldn't release it.

Any help at all would be appreciated. Meanwhile, I'll continue to drudge
the internet...

TIA,

Joseph

Search Discussions

  • Lbaxter at Apr 21, 2003 at 8:43 pm
    Instead of using selectall_hashref()
    Create a statement handle, execute, and fetch explicitly until fetch returns
    no more rows. See perldoc DBI for an example.
    This will actually run much faster because you will not be spending so much
    time allocating memory (which is slow on almost any system).

    Routines like selectall_hashref() are VERY expensive when dealing with large
    row sizes or large number of row, and in these cases should be avoided.

    Lincoln


    -----Original Message-----
    From: Joseph Sheppard
    Sent: Monday, April 21, 2003 4:30 PM
    To: dbi-users@perl.org
    Subject: Help freeing memory

    Greetings, list.

    I apologize if this is a bit off topic...

    I'm running into a major problem with an apparant memory leak.

    I need to be able to transfer about 6500 records across databases. A good
    number of these records are blob types, at about 25k each.

    My problem is that TOP reports my memory use as constantly climbing, and I
    can't seem to release any of it. At the 1000 record mark, I'm using just
    under a Gig of ram....with 5500 records to go! (I'm using SunOS 5.8 on
    Sparc, if it matters).

    Please look at my snippet, and offer any suggestions. I've pulled as much
    unnecessary, extraneous code as possible for posting purposes (please
    forgive any resulting syntax errors, etc):

    sub test {
    my ($query, $dbh, $qti_row, $sth, $returns, $row);

    $query = qq/ USE database_name;
    SELECT id, column1, picture1, picture2
    FROM table
    ORDER BY id;/;

    dbh =
    DBI->connect("dbi:Sybase:server=x.x.x.x",'user','password');

    $returns = $dbh->selectall_hashref($query,'id');

    $dbh->disconnect();

    # Trying to release some memory here...
    foreach $row(keys %$returns){
    # I've tried both methods:
    #$returns->{$row} = undef;
    undef $returns->{$row};
    }
    # Again, both methods tried:
    #$returns = undef;
    undef $returns;

    #But at this point, the program hasn't released any memory.
    sleep(30);

    }

    Of note, is that changing from selectall_hashref to selectall_arrayref only
    affected how much memory was used...but I still couldn't release it.

    Any help at all would be appreciated. Meanwhile, I'll continue to drudge
    the internet...

    TIA,

    Joseph
  • Joseph Sheppard at Apr 21, 2003 at 8:48 pm
    Thanks for responding, Lincoln.

    Actually, I have tried that, and noticed an improvement on memory usage
    reported, as you suggest.

    However, my problem is that even this improved memory usage is accumulating
    over the 6500 rows. I am looking for a way to reclaim all the memory that
    the data structures were using, and get back to a state close to where I was
    before the data fetch.

    To reduce the load on my servers, I want to grab and process about 100 rows
    at a time. The problem is, since I can't reclaim the space the first 100
    rows used, by the time I get halfway done, I'm using a ridiculous amount of
    memory.

    Any further suggestions, or flaws in my logic?

    Sincerely,

    Joseph


    ----- Original Message -----
    From: <LBaxter@FLEETCC.COM>
    To: <Joseph@ANC.net>; <dbi-users@perl.org>
    Sent: Monday, April 21, 2003 3:41 PM
    Subject: RE: Help freeing memory

    Instead of using selectall_hashref()
    Create a statement handle, execute, and fetch explicitly until fetch returns
    no more rows. See perldoc DBI for an example.
    This will actually run much faster because you will not be spending so much
    time allocating memory (which is slow on almost any system).

    Routines like selectall_hashref() are VERY expensive when dealing with large
    row sizes or large number of row, and in these cases should be avoided.

    Lincoln


    -----Original Message-----
    From: Joseph Sheppard
    Sent: Monday, April 21, 2003 4:30 PM
    To: dbi-users@perl.org
    Subject: Help freeing memory

    Greetings, list.

    I apologize if this is a bit off topic...

    I'm running into a major problem with an apparant memory leak.

    I need to be able to transfer about 6500 records across databases. A good
    number of these records are blob types, at about 25k each.

    My problem is that TOP reports my memory use as constantly climbing, and I
    can't seem to release any of it. At the 1000 record mark, I'm using just
    under a Gig of ram....with 5500 records to go! (I'm using SunOS 5.8 on
    Sparc, if it matters).

    Please look at my snippet, and offer any suggestions. I've pulled as much
    unnecessary, extraneous code as possible for posting purposes (please
    forgive any resulting syntax errors, etc):

    sub test {
    my ($query, $dbh, $qti_row, $sth, $returns, $row);

    $query = qq/ USE database_name;
    SELECT id, column1, picture1, picture2
    FROM table
    ORDER BY id;/;

    dbh =
    DBI->connect("dbi:Sybase:server=x.x.x.x",'user','password');

    $returns = $dbh->selectall_hashref($query,'id');

    $dbh->disconnect();

    # Trying to release some memory here...
    foreach $row(keys %$returns){
    # I've tried both methods:
    #$returns->{$row} = undef;
    undef $returns->{$row};
    }
    # Again, both methods tried:
    #$returns = undef;
    undef $returns;

    #But at this point, the program hasn't released any memory.
    sleep(30);

    }

    Of note, is that changing from selectall_hashref to selectall_arrayref only
    affected how much memory was used...but I still couldn't release it.

    Any help at all would be appreciated. Meanwhile, I'll continue to drudge
    the internet...

    TIA,

    Joseph
  • Rudy Lippan at Apr 21, 2003 at 8:49 pm

    On Mon, 21 Apr 2003, Joseph Sheppard wrote:

    Greetings, list.

    I apologize if this is a bit off topic...

    I'm running into a major problem with an apparant memory leak.

    I need to be able to transfer about 6500 records across databases. A good
    number of these records are blob types, at about 25k each.

    My problem is that TOP reports my memory use as constantly climbing, and I
    can't seem to release any of it. At the 1000 record mark, I'm using just
    under a Gig of ram....with 5500 records to go! (I'm using SunOS 5.8 on
    Sparc, if it matters).

    Please look at my snippet, and offer any suggestions. I've pulled as much
    unnecessary, extraneous code as possible for posting purposes (please
    forgive any resulting syntax errors, etc):

    sub test {
    my ($query, $dbh, $qti_row, $sth, $returns, $row);

    $query = qq/ USE database_name;
    SELECT id, column1, picture1, picture2
    FROM table
    ORDER BY id;/;

    dbh = DBI->connect("dbi:Sybase:server=x.x.x.x",'user','password');

    $returns = $dbh->selectall_hashref($query,'id');

    $dbh->disconnect();

    # Trying to release some memory here...
    foreach $row(keys %$returns){
    # I've tried both methods:
    #$returns->{$row} = undef;
    undef $returns->{$row};
    }
    # Again, both methods tried:
    #$returns = undef;
    undef $returns;

    #But at this point, the program hasn't released any memory.
    sleep(30);

    }

    Of note, is that changing from selectall_hashref to selectall_arrayref only affected how much memory was used...but I still couldn't release it.
    most malloc/free implementations do not return memory back to the kernel,
    so once your process gets to 12M it will stay at 12M until the process
    terminates even if you free() 11M of it. The next time you call malloc()
    there will be no need to requst more memory for the kernel, however,
    because your process still has a pool of 11M to use.

    For data sets of this size/type it would be best to use fetchrow_*
    methods. so

    $sth = $dbh->prepare(q{SELECT ...});
    $sth->execute;
    do_stuff() while ($row = $sth->fetchrow_arrayref({}));



    -r
  • Joseph Sheppard at Apr 21, 2003 at 8:53 pm
    Awesome!

    This is what I was beginning to suspect...

    Thank you so much for you answer!

    Sincerely,

    Joseph

    ----- Original Message -----
    From: "Rudy Lippan" <rlippan@remotelinux.com>
    To: "Joseph Sheppard" <Joseph@ANC.net>
    Cc: <dbi-users@perl.org>
    Sent: Monday, April 21, 2003 3:50 PM
    Subject: Re: Help freeing memory

    On Mon, 21 Apr 2003, Joseph Sheppard wrote:

    Greetings, list.

    I apologize if this is a bit off topic...

    I'm running into a major problem with an apparant memory leak.

    I need to be able to transfer about 6500 records across databases. A
    good
    number of these records are blob types, at about 25k each.

    My problem is that TOP reports my memory use as constantly climbing, and
    I
    can't seem to release any of it. At the 1000 record mark, I'm using
    just
    under a Gig of ram....with 5500 records to go! (I'm using SunOS 5.8 on
    Sparc, if it matters).

    Please look at my snippet, and offer any suggestions. I've pulled as
    much
    unnecessary, extraneous code as possible for posting purposes (please
    forgive any resulting syntax errors, etc):

    sub test {
    my ($query, $dbh, $qti_row, $sth, $returns, $row);

    $query = qq/ USE database_name;
    SELECT id, column1, picture1, picture2
    FROM table
    ORDER BY id;/;

    dbh =
    DBI->connect("dbi:Sybase:server=x.x.x.x",'user','password');
    $returns = $dbh->selectall_hashref($query,'id');

    $dbh->disconnect();

    # Trying to release some memory here...
    foreach $row(keys %$returns){
    # I've tried both methods:
    #$returns->{$row} = undef;
    undef $returns->{$row};
    }
    # Again, both methods tried:
    #$returns = undef;
    undef $returns;

    #But at this point, the program hasn't released any memory.
    sleep(30);

    }

    Of note, is that changing from selectall_hashref to selectall_arrayref
    only affected how much memory was used...but I still couldn't release it.
    most malloc/free implementations do not return memory back to the kernel,
    so once your process gets to 12M it will stay at 12M until the process
    terminates even if you free() 11M of it. The next time you call malloc()
    there will be no need to requst more memory for the kernel, however,
    because your process still has a pool of 11M to use.

    For data sets of this size/type it would be best to use fetchrow_*
    methods. so

    $sth = $dbh->prepare(q{SELECT ...});
    $sth->execute;
    do_stuff() while ($row = $sth->fetchrow_arrayref({}));



    -r


  • Lbaxter at Apr 21, 2003 at 8:57 pm
    Perl does garbage collection of ( deferenced references ), and my variables
    that have gone out of scope, but perl NEVER frees memory to the OS.

    You need to arrange things, so that you only allocate what you need and then
    it gets reused. Do not, for instance, try to build up that big hashref,
    that would just do what selectall_hashref was doing. Instead, process the
    data returned from each row fetch, directly to the target database. (You
    can have two database handles open to difference databases at the same
    time).

    I usually do some thing like this:

    $sth->execute();
    my $data;
    $sth->bind_columns( \$data );
    while ( $sth->fetch() )
    {
    #do something with $data
    }

    This ensures that you are just growing memory to the largest size needed to
    store the largest value of $data.

    Lincoln

    -----Original Message-----
    From: Joseph Sheppard
    Sent: Monday, April 21, 2003 4:48 PM
    To: LBaxter@FLEETCC.COM; dbi-users@perl.org
    Subject: Re: Help freeing memory

    Thanks for responding, Lincoln.

    Actually, I have tried that, and noticed an improvement on memory usage
    reported, as you suggest.

    However, my problem is that even this improved memory usage is accumulating
    over the 6500 rows. I am looking for a way to reclaim all the memory that
    the data structures were using, and get back to a state close to where I was
    before the data fetch.

    To reduce the load on my servers, I want to grab and process about 100 rows
    at a time. The problem is, since I can't reclaim the space the first 100
    rows used, by the time I get halfway done, I'm using a ridiculous amount of
    memory.

    Any further suggestions, or flaws in my logic?

    Sincerely,

    Joseph


    ----- Original Message -----
    From: <LBaxter@FLEETCC.COM>
    To: <Joseph@ANC.net>; <dbi-users@perl.org>
    Sent: Monday, April 21, 2003 3:41 PM
    Subject: RE: Help freeing memory

    Instead of using selectall_hashref()
    Create a statement handle, execute, and fetch explicitly until fetch returns
    no more rows. See perldoc DBI for an example.
    This will actually run much faster because you will not be spending so much
    time allocating memory (which is slow on almost any system).

    Routines like selectall_hashref() are VERY expensive when dealing with large
    row sizes or large number of row, and in these cases should be avoided.

    Lincoln


    -----Original Message-----
    From: Joseph Sheppard
    Sent: Monday, April 21, 2003 4:30 PM
    To: dbi-users@perl.org
    Subject: Help freeing memory

    Greetings, list.

    I apologize if this is a bit off topic...

    I'm running into a major problem with an apparant memory leak.

    I need to be able to transfer about 6500 records across databases. A good
    number of these records are blob types, at about 25k each.

    My problem is that TOP reports my memory use as constantly climbing, and I
    can't seem to release any of it. At the 1000 record mark, I'm using just
    under a Gig of ram....with 5500 records to go! (I'm using SunOS 5.8 on
    Sparc, if it matters).

    Please look at my snippet, and offer any suggestions. I've pulled as much
    unnecessary, extraneous code as possible for posting purposes (please
    forgive any resulting syntax errors, etc):

    sub test {
    my ($query, $dbh, $qti_row, $sth, $returns, $row);

    $query = qq/ USE database_name;
    SELECT id, column1, picture1, picture2
    FROM table
    ORDER BY id;/;

    dbh =
    DBI->connect("dbi:Sybase:server=x.x.x.x",'user','password');

    $returns = $dbh->selectall_hashref($query,'id');

    $dbh->disconnect();

    # Trying to release some memory here...
    foreach $row(keys %$returns){
    # I've tried both methods:
    #$returns->{$row} = undef;
    undef $returns->{$row};
    }
    # Again, both methods tried:
    #$returns = undef;
    undef $returns;

    #But at this point, the program hasn't released any memory.
    sleep(30);

    }

    Of note, is that changing from selectall_hashref to selectall_arrayref only
    affected how much memory was used...but I still couldn't release it.

    Any help at all would be appreciated. Meanwhile, I'll continue to drudge
    the internet...

    TIA,

    Joseph
  • Joseph Sheppard at Apr 21, 2003 at 9:01 pm
    Thanks, Lincoln...Very good advice.

    I'm in the process of retooling my code right now.

    Sincerely,

    Joseph
    ----- Original Message -----
    From: <LBaxter@FLEETCC.COM>
    To: <Joseph@ANC.net>; <LBaxter@FLEETCC.COM>; <dbi-users@perl.org>
    Sent: Monday, April 21, 2003 3:55 PM
    Subject: RE: Help freeing memory

    Perl does garbage collection of ( deferenced references ), and my variables
    that have gone out of scope, but perl NEVER frees memory to the OS.

    You need to arrange things, so that you only allocate what you need and then
    it gets reused. Do not, for instance, try to build up that big hashref,
    that would just do what selectall_hashref was doing. Instead, process the
    data returned from each row fetch, directly to the target database. (You
    can have two database handles open to difference databases at the same
    time).

    I usually do some thing like this:

    $sth->execute();
    my $data;
    $sth->bind_columns( \$data );
    while ( $sth->fetch() )
    {
    #do something with $data
    }

    This ensures that you are just growing memory to the largest size needed to
    store the largest value of $data.

    Lincoln

    -----Original Message-----
    From: Joseph Sheppard
    Sent: Monday, April 21, 2003 4:48 PM
    To: LBaxter@FLEETCC.COM; dbi-users@perl.org
    Subject: Re: Help freeing memory

    Thanks for responding, Lincoln.

    Actually, I have tried that, and noticed an improvement on memory usage
    reported, as you suggest.

    However, my problem is that even this improved memory usage is
    accumulating
    over the 6500 rows. I am looking for a way to reclaim all the memory that
    the data structures were using, and get back to a state close to where I was
    before the data fetch.

    To reduce the load on my servers, I want to grab and process about 100 rows
    at a time. The problem is, since I can't reclaim the space the first 100
    rows used, by the time I get halfway done, I'm using a ridiculous amount of
    memory.

    Any further suggestions, or flaws in my logic?

    Sincerely,

    Joseph


    ----- Original Message -----
    From: <LBaxter@FLEETCC.COM>
    To: <Joseph@ANC.net>; <dbi-users@perl.org>
    Sent: Monday, April 21, 2003 3:41 PM
    Subject: RE: Help freeing memory

    Instead of using selectall_hashref()
    Create a statement handle, execute, and fetch explicitly until fetch returns
    no more rows. See perldoc DBI for an example.
    This will actually run much faster because you will not be spending so much
    time allocating memory (which is slow on almost any system).

    Routines like selectall_hashref() are VERY expensive when dealing with large
    row sizes or large number of row, and in these cases should be avoided.

    Lincoln


    -----Original Message-----
    From: Joseph Sheppard
    Sent: Monday, April 21, 2003 4:30 PM
    To: dbi-users@perl.org
    Subject: Help freeing memory

    Greetings, list.

    I apologize if this is a bit off topic...

    I'm running into a major problem with an apparant memory leak.

    I need to be able to transfer about 6500 records across databases. A
    good
    number of these records are blob types, at about 25k each.

    My problem is that TOP reports my memory use as constantly climbing, and
    I
    can't seem to release any of it. At the 1000 record mark, I'm using
    just
    under a Gig of ram....with 5500 records to go! (I'm using SunOS 5.8 on
    Sparc, if it matters).

    Please look at my snippet, and offer any suggestions. I've pulled as
    much
    unnecessary, extraneous code as possible for posting purposes (please
    forgive any resulting syntax errors, etc):

    sub test {
    my ($query, $dbh, $qti_row, $sth, $returns, $row);

    $query = qq/ USE database_name;
    SELECT id, column1, picture1, picture2
    FROM table
    ORDER BY id;/;

    dbh =
    DBI->connect("dbi:Sybase:server=x.x.x.x",'user','password');

    $returns = $dbh->selectall_hashref($query,'id');

    $dbh->disconnect();

    # Trying to release some memory here...
    foreach $row(keys %$returns){
    # I've tried both methods:
    #$returns->{$row} = undef;
    undef $returns->{$row};
    }
    # Again, both methods tried:
    #$returns = undef;
    undef $returns;

    #But at this point, the program hasn't released any memory.
    sleep(30);

    }

    Of note, is that changing from selectall_hashref to selectall_arrayref only
    affected how much memory was used...but I still couldn't release it.

    Any help at all would be appreciated. Meanwhile, I'll continue to
    drudge
    the internet...

    TIA,

    Joseph

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedApr 21, '03 at 8:29p
activeApr 21, '03 at 9:01p
posts7
users3
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase