hi-

this isn't really a question, as i've already resolved the issue, but i'd be interested to know if anyone else has experienced similar problems and i also thought this might make a contribution to the aggregate knowledge base (kinda sounds like sand and gravel, doesn't it?).

i have a script that parses the log created by a agilent lab device and inserts the output into a database. initially running under activestate's perl on win2k, i started experiencing problems with memory leaks ... by commenting out lines i eventually isolated the problem to a line in a function that performed a substring extraction. i redesigned the way that function worked only to run into the problem again with a chomp. at this point, i suspected a problem with activeperl and win2k.

i moved the log and the script to one of my linux boxes, only to run into the same problem. on a hunch, i decided to move the back end database from mysql to postgresql, and this resolved the problem. the striking thing about this is that the database access in this script is rather mundane ... a query determines whether a given set of results have been previously stored rather early in the script and after that it's simply a series of inserts into the three tables that comprise the database. the procedure in which i had the problem had nothing to do with either. it just determines the charactistics of the line read in from the log to marginally add to the predictability of the parse operation (this is a very complex log).

the script uses dbi and the dbd driver for the relevant database.

as i said, i'd be curious to know if anyone here has run into something similar. i posted the problem to the activestate win32 perl users list, and have recieved a couple of reponses from individuals who had similar problems, one of whom was using a different database driver.

it's not always possible to change the database (he said with a wry grin).

Edit: chipmunk 2001-07-01

Replies are listed 'Best First'.
Re: memory leaks and mysql
by tadman (Prior) on Jul 02, 2001 at 02:52 UTC
    What you might be experiencing is an artifact of the data transfer mechanism. When using SELECT on large result sets, the DBI::mysql driver, or rather, the underlying C driver, tends to load in the entire result into RAM. I'm supposing this is a probable cause considering that you are working with log files which can tend to get quite large quickly.

    In DBI + 'SELECT *' - Memory Use Galore?, I experienced massive memory utilization even though I thought I was retrieving the result set row by row. MySQL's driver, unless instructed otherwise, will retrieve the entire result set and parcel it out to you row by row from the memory buffer.

    kschwab was helpful enough to point out that you are expected to set an option on your statement handle which forces incremental transfer:
    $sth->{"mysql_use_result"} = 1;
    However, this renders that particular database connection unusuable until you finish with that $sth. If you need to retrieve from one and insert into another, make two DB handles, one for the incremental read, and the other for all the INSERTs.

    PostgreSQL likely has a different driver mechanism that does not suffer from this particular artifact.
[reply]
[d/l]
      thanks - that's very helpful.
[reply]

Back to Meditations