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

To echo another recent poster with a DBI problem, "I'm just not seeing it!"

I am using Class::DBI to describe various tables and add new data records to them.   As I add 1000's of new records using  ->create()  the Perl program memory size grows proportionally to record count and average data size per record.   Memory is not released until end of program.

I'd thought that with the default Class::DBI setting of   AutoCommit=>1   I'd not need to worry about 'details' like records accumulating until I did an explicit commit.   I'm afraid something more subtle is wrong (with my understanding of course).   Example program is as follows: 

use strict;
use warnings;

package MyTest1;
use base 'Class::DBI';

__PACKAGE__->set_db('Main', 'dbi:mysql:test', undef, undef, { RaiseErr
+or => 1 }  );
__PACKAGE__->table('test1');
__PACKAGE__->columns( All => qw( id logtext ) );

package main;

    printf "  db_Main is    '%s'\n", MyTest1->db_Main;
    printf "  AutoCommit is '%s'\n", MyTest1->db_Main->{AutoCommit};
    printf "  RaiseError is '%s'\n", MyTest1->db_Main->{RaiseError};

    MyTest1->db_Main->do( 'DROP TABLE IF EXISTS test1' );
    MyTest1->db_Main->do( <<EOSQL );
CREATE TABLE IF NOT EXISTS test1 (
  id              INTEGER       NOT NULL,
  logtext         VARCHAR(255)  NOT NULL,
  PRIMARY KEY (id) 
)
EOSQL

    my  $newentry;
    for( my $i=0 ; $i<10000 ; ++$i ) {
        $newentry = MyTest1->create( { id=>$i, logtext=>'faked' } );
        unless( $newentry ) {
            printf "*Error: Unable to create new entry object\n";
            last;
        }
    }

    my( $count ) = MyTest1->db_Main->selectrow_array( 
                                'SELECT count(*) FROM test1' );
    printf "  Record count  '%d'\n", $count;
    printf "  db_Main is    '%s'\n", MyTest1->db_Main;
    printf "  AutoCommit is '%s'\n", MyTest1->db_Main->{AutoCommit};

[download]
Sanity check output is: 
  db_Main is    'Ima::DBI::db=HASH(0x2343a38)'
  AutoCommit is '1'
  RaiseError is '1'
  Record count  '10000'
  db_Main is    'Ima::DBI::db=HASH(0x2343a38)'
  AutoCommit is '1'
I must be missing some pretty fundamental 'gotcha' in the interactions between Class::DBI methods, objects, data and such.   Uh, hep, hep!

Environment is WinXP, ActiveState 5.6.1 build 635, Class::DBI 0.94, DBD::mysql 2.9002, MySQL 3.23.52

Replies are listed 'Best First'.
Re: Class::DBI and create: program memory just grows and grows
by PodMaster (Abbot) on Sep 17, 2003 at 08:28 UTC
    I believe this is not related to Class::DBI but rather your version of perl. I get memory leaks with ActivePerl 5.6.1 (Build 633 on win2k -- about 2k per second). However, with ActivePerl 5.8.0 Build 804, I get no such leaks. BTW, I used Class::DBI::SQlite for testing purposes(not that it would make any difference). I'm sure if you look around you'll stumble on mention of this bug (i hope -- i vaguely recall tye(or t?) mentioning a bug related to the for loop not freeing lexicals -- this is probably the same thing).

    update: I messed around a little with Devel::Leak (adding the relevant bits around the for-loop, and changing the number of iterations to 1000) 

    5.6.1 => BEFORE(30692) AFTER(30808) => 116
5.8.0 => BEFORE(29653) AFTER(31774) => 2121
    So the loop creates approximately 2005 more scalars in perl 5.6.1, which does seem to confirm the memory leak theory.

    update: nice followup -- it's too easy to make memory leaks in perl and XS -- i'll give it a closer look later (examine my DBI/DBD-SQLite versions and so on).

    MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"
    I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).
    ** The third rule of perl club is a statement of fact: pod is sexy.

[reply]
      Interesting update, if only because (as I think you suspect) the 2005 count is close to two hash values times 1000 iterations.

      I've installed ActiveState 5.8.0 build 806 (latest) and re-run the tests.   It's ... better.   With 10K iterations and the string about 250 chars, on 5.6 it grows about 5.7MB, on 5.8 it grows only about 2.8MB.   (gee, half as much - rah)

      I've got access to a Perl 5.6.1 on RedHat 7.2 running MySQL.   Very disturbingly memory size here also seems to increase as the program runs, about 5.8MB over 50K iterations.

      It will take me awhile before I can test against the Perl 5.8.0 RedHat 8.0 I have access to.

      At this point I'll try to post to the Class::DBI maillist and see what they can say about it.   And I guess I'll just have to recode as plain DBI and hope for the best.

[reply]
      Here's the latest.   Who knows if it's just one thing or a mix, but DBI and the DBD's need to talk things over...   Somebody responded on the Class::DBI list saying he had a similar symptom, but with PostgreSQL.   And you said you were using DBD::SQLite?   Yikes.

      Here's what I posted last to the Class::DBI maillist:
      =====================================================

      Oh dear. Here I was going to add a reply with my latest discovery and now you've got me _more_ worried. No, after playing some more, maybe less (see below), but now I think I'll get _you_ more worried.

      Trying to replicate my previous program using plain DBI I ran into nearly the same memory growth problem, which astonished me. I started playing around and hit upon this. If you do the INSERT's using one prepare and multiple execute's with bind values, the memory size grows. If you instead do the INSERT's using do, even with bind values, you don't run into the problem.

      With a sinking feeling I dove into Class::DBI and found that routine _insert_row(), called by _create() called by create(), also does execute()'s using bind values. So if plain DBI has this problem, Class::DBI will also.

      Which got me freaked when I saw your report referencing the PostgreSQL driver, as I was thinking (hoping?) it was a bug in the MySQL driver.

      I wandered about a short time at one of the DBI maillists and happened upon this bug report: "Memory Leak - DBD::mysql" http://www.mail-archive.com/dbi-users@perl.org/msg17934.html . After some doing I managed to force ActiveState's PPM to remove the latest DBD-mysql 2.9002 driver and install the older DBD-mysql 2.1026 driver. I was astonished *again* to find this 'fixed' my problem with execute() and bind values using plain DBI.

      I went back and repaired all my debug attempts in the program using Class::DBI and - bliss - it works just fine with flat memory usage.

      So here I am 36 hours later wondering where to report a bug against DBD::mysql and regretting bothering y'all with a 'Class::DBI' problem.

      But, Heiko, where is your bug? ;-)
      ==============================================

      Class:DBI maillist postings at
      http://groups.kasei.com/mail/arc/cdbi-talk/2003-09/msg00164.html
      http://groups.kasei.com/mail/arc/cdbi-talk/2003-09/msg00165.html
      http://groups.kasei.com/mail/arc/cdbi-talk/2003-09/msg00166.html

[reply]

Back to Seekers of Perl Wisdom