Memory leak when doing many INSERTs

Eric Deutsch edeutsch at systemsbiology.org
Fri Sep 14 13:29:15 EDT 2001



Thanks a million, Brian!  This helps a lot, although it doesn't fix things
completely.  For one particular test of rapid firing INSERTs, it reduces the
leak rate from 16 MB/min to only 2 MB/min.  After a little more testing, I
now find that the leak rate is independent of the SQL statement length.  In
fact, I now find that that leak rate is 60 bytes per statement.  I After
floundering around in the code for a while, I added

         if (cmd->userdata) free(cmd->userdata);

into the block you sent, but that did not affect anything.

If you have any other ideas where there might be a fixed leak rate of 60
bytes per statement, I'd greatly appreciate it.  I know that doesn't sound
like a big rate, but I'm doing millions of INSERTs to various tables mixed
in with SELECTs during a single load process.

many thanks!
Eric


> -----Original Message-----
> From: bounce-freetds-127899 at franklin.oit.unc.edu
> [mailto:bounce-freetds-127899 at franklin.oit.unc.edu]On Behalf Of Brian
> Bruns
> Sent: Friday, September 14, 2001 4:56 AM
> To: TDS Development Group
> Subject: [freetds] RE: Memory leak when doing many INSERTs
>
>
> Ok this appears to affect only ctlib (which DBD::Sybase uses), which is
> why we didn't catch it last time leaks were checked (which I believe was
> preformed under dblib).
>
> The following replacement for ct_cmd_drop in src/ctlib/ct.c should fix it.
> It's a hold over from the days when query length was fixed.
> (Warning: untested code)
>
> CS_RETCODE ct_cmd_drop(CS_COMMAND *cmd)
> {
>      tdsdump_log(TDS_DBG_FUNC, "%L inside ct_cmd_drop()\n");
>      if (cmd) {
>         if (cmd->query) free(cmd->query);
>         free(cmd);
>      }
>      return CS_SUCCEED;
> }
>
> I'll try to test it and get it checked in this evening.
>
> Brian
>
> On Thu, 13 Sep 2001, Eric Deutsch wrote:
>
> >
> > Hi again, I've done a little more investigating, and I've come
> back to plead
> > for help.  Situation is the same as below:
> >
> > If I do in perl with DBD::Sybase 0.91:
> >
> > $querystring="INSERT INTO leaktest
> (intfield,charfield,realfield) VALUES (
> > 20,'chicken',3.1415 )";
> > while ( 1 == 1 ) {
> >   $dbh->do($SQLstring);
> >   print ".";
> > }
> >
> > and watch my process with `top` it grows forever at a rate of 6 MB per
> > minute.  However, if I do the following in a test C program:
> >
> >    static char sql_query[]   = "INSERT INTO dbaccesstests
> > (intfield,charfield,realfield) VALUES ( 20,'butter',3.1415 )";
> >    while ( 1 == 1 ) {
> >       rc = run_query(tds,sql_query);
> >       if (rc != TDS_SUCCEED) {
> >          fprintf(stderr, "run_query() failed\n");
> >          return 1;
> >       }
> >       fprintf(stdout, ".");
> >       fflush(stdout);
> >    }
> >
> > I can run this program for many minutes without a single kB
> being added to
> > the resident memory of the process.  I'm using the run_query()
> function from
> > t0004.c.
> >
> > So it appears that the FreeTDS C API functions aren't leaking
> as such, but
> > somehow the interaction between Perl DBI/DBD and FreeTDS is.
> >
> > As a further test, I did a loop where the SQL command was:
> >
> > $querystring="INSERT INTO dbaccesstests (intfield,charfield,realfield)
> > VALUES (
> > 20,'butter',3.1415 ) /*
> >
> ******************************************************************
> **********
> >
> ******************************************************************
> **********
> >
> ******************************************************************
> **********
> >
> ******************************************************************
> **********
> >
> ******************************************************************** */";
> >
> > and as I guessed, the leak rate tripled!  Thus is looks very much like
> > somewhere a copy of the query string is being made and not freed.
> >
> > I really don't know how to go about figuring this out.  Anyone out there
> > able to help or repro?  I can send actual C and Perl programs used.
> >
> > many thanks,
> > Eric
> >
> >
> >
> >
> > > -----Original Message-----
> > > From: bounce-freetds-127899 at franklin.oit.unc.edu
> > > [mailto:bounce-freetds-127899 at franklin.oit.unc.edu]On Behalf Of Eric
> > > Deutsch
> > > Sent: Monday, September 10, 2001 6:33 PM
> > > To: TDS Development Group
> > > Subject: [freetds] Memory leak when doing many INSERTs
> > >
> > > Hi all, I've noticed what appears to be some memory leaking while
> > > doing lots
> > > of INSERTS.  I'm using Perl 5.6.0 + DBD::Sybase 0.91 +
> FreeTDS (both 0.51a
> > > and latest CVS) under Red Hat 7.0 connecting to SQL Server 2000.
> > >
> > > If I do:
> > >
> > > $querystring="INSERT INTO leaktest
> (intfield,charfield,realfield) VALUES (
> > > 20,'chicken',3.1415 )";
> > > while ( 1 == 1 ) {
> > >   $dbh->do($SQLstring);
> > >   print ".";
> > > }
> > >
> > > after a suitable setup, and watch my process with top, the memory
> > > used grows
> > > and grows at a rate of maybe 5 MB/min.  There's no difference if I use
> > > prepare() and execute().
> > >
> > > I have some real code (as opposed to the above example) that is
> > > parsing and
> > > loading some huge datasets from files into a my DB and after one
> > > particularly large set, my process was killed because it
> hogged all the
> > > client machine's memory (probably after running for an hour).
> > > This is a bit
> > > of a snag in what I'm trying to do.  I could start breaking up
> > > the work, but
> > > this is awkward for various reasons.
> > >
> > > Has anyone encountered/studied/noticed this before?  Any advice?
> > > I suppose
> > > I could try taking Perl out of the loop to see if it's a FreeTDS
> > > issue or a
> > > Perl DBI + DBD::Sybase issue.
> > >
> > > I'm assuming it is not unreasonable to expect a Perl script
> to be able to
> > > run INSERTs until SQL Server explodes without running into memory
> > > issues, is
> > > that true?
> > >
> > > thanks,
> > > Eric
> > >
> > >
> > >
> > >
> > >
> > > ---
> > > You are currently subscribed to freetds as:
> [edeutsch at systemsbiology.org]
> > > To unsubscribe, forward this message to
> > > $subst('Email.Unsub')
> > >
> >
> >
> > ---
> > You are currently subscribed to freetds as: [camber at ais.org]
> > To unsubscribe, forward this message to
> $subst('Email.Unsub')
> >
>
>
> ---
> You are currently subscribed to freetds as: [edeutsch at systemsbiology.org]
> To unsubscribe, forward this message to
> $subst('Email.Unsub')
>




More information about the FreeTDS mailing list