Release memory from script in the middle of the process

Question

I have a script that runs through hundreds if not thousands of tables from years of backups, to change content that people ask for.

as of this month, it started using so much RAM that the server kills it.

So, is there a way after each pull from the database, after I pull it and have my perl script run through the data, searching for the info to change, it resets the RAM before it pulls the next one?

So basically it resets the data it just pulled, so that all of that is not in the RAM?

I created this stuff in 2007, and I sure wish I had done it WAY different, but that would require a MAJOR overhaul and update and that is just no way I can do that now. Maybe in the future. But as of right now, I just cannot do that.

So I need a way to keep this from happening.

Please let me know if you know of a way to flush that memory.

Thank you in advance if you can point me in the way.

--- Update ---- added part of the website code that actually pulls all the data ----

        $_startingTime = time();
        $dbh->do(qq{update `tun_changes` set `status` = "working", `startedT` = ? where `unid` = ?}, undef, $_startingTime, $_tr->{unid});
        $sth2 = $dbh->prepare(qq{select * from `allmembers` where `mId` = ?});
        $sth2->execute($_tr->{mId});
        $_tregmem = $sth2->fetchrow_hashref();
        $sth2->finish();
        $dbh->do(qq{delete from `sessions` where `id` = "$_tregmem->{last_sessid}"}); #kill their logged in session, so it does not lock them up if they are logged in when we change their username...
        $_un = $_tregmem->{tusername};
        $_cust_id = $_tr->{mId};
        $_notfinished = 0;
        $_newUname = lc($_tr->{newun});
        $_csusername = $_tr->{csusername};# if customer service rep...
        $_count = 0;
        $_changeFailed = 0;
        $_changed = 0;
        $_debugChg = 1;
        if($_debugChg) {
            open(DBG,">>/home$_website_username/required/files/urgent/all_chg_uname_debug_track.txt");
            seek(DBG,0,2);
            print DBG "Checking Table at " . Format_Date_For_Viewing($_startingTime,"") . " - indented two lines at least for this record.... until _end_ is shown\n";
        }
        @_mb = $dbh->tables;
        $_tablesAffected = 0;
        foreach $_k (@_mb) {
            $_sql = "select * from $_k";
            if($_debugChg) {
                print DBG "\tTable $_k (" . duration(time() - $_startingTime) . " into action)\n";
            }
            $sth2 = $dbh->prepare($_sql);
            $sth2->execute();
            @_mb2 = @{$sth2->{NAME}};
            foreach $_k2 (@_mb2) {
                if($_debugChg) {
                    print DBG "\t\tColumn $_k2";
                }
                if($_k2 =~ /tusername/i) {
                    if($_debugChg) {
                        print DBG "\t Checking for username entries in $_k2\n";
                    }
                    $_updated = $dbh->do(qq{update $_k SET `$_k2` = ? WHERE `$_k2` = ?}, undef, $_newUname, $_un);
                    if($_updated) {
                        $_changed += $_updated;
                    } else {
                        $_changeFailed += $_updated;
                    }
                } else {
                    if($_debugChg) {
                        print DBG " - Not UN\n";
                    }
                }
            }
            $sth2->finish();
            if($_debugChg) {
                print DBG "\n";
            }
        }
        if($_debugChg) {
            print DBG "\nFinished - Duration was: " . duration(time() - $_startingTime) . "\n";
            print DBG "\n_end_\n\n";
            close(DBG);
        }

I left the debugging code so that I can see what was done, in case of failures.

Answer 1

It is really hard to tell without knowing at least something about how your program is organized.

I take it that you read data from a database into suitable data structures. I also imagine that as you keep processing you read new data into yet new data structures. All that memory is kept and I don't know of a way to get rid of it. I think you can't; Perl does not return memory to the OS at runtime as far as I know. Here are some options to keep the memory footprint of your program from growing.

• The first thing to do is to re-use your data structures. Once you are done processing one set of data, read the new one into that same array or hash (or whatever you use). If you are using complex data structures then their components may be re-used.

• It seems from your question that this may amount to too much of a change. If that is so, one other thing I can think of is to wrap suitable sections of your code into subs, so that once it is done processing it goes out of scope and frees that memory. Note, the memory will still be with the interpreter, but it can be re-used for the next such made-up sub so your footprint wouldn't grow. That kind of a change may be more feasible.

• In case your growing memory demands are due to the simple fact that there is just more data coming into a few data structures, here is another thought. Read (fetch, query) only a chunk of data at once, then when you are done processing it read the next chunk into the same array, etc. This may or may not affect the database read performance but it will lead to a reduced memory footprint of the program.

---

Update (code posted)   I am still unable to tell where most memory goes but it seems that perhaps a few data structures (arrays?) simply grow larger and larger as data grows; just more and more data. In that case the last option above may be suitable.

Answer 2

One thing that may be useful, the SQL statement "select * from $_k" is executed only to fetch the names of the columns. You might want to change that to "select * from $_k LIMIT 0" so that no data is retrieved. You should also move the $sth2->finish up before the for loop, although with the LIMIT 0 fix in place that will make very little difference

The resulting code looks like this

$_sql = "select * from `$_k` LIMIT 0";
if ( $_debugChg ) {
    print DBG "\tTable $_k (" . duration( time() - $_startingTime ) . " into action)\n";
}
$sth2 = $dbh->prepare($_sql);
$sth2->execute;
@_mb2 = @{ $sth2->{NAME} };
$sth2->finish;

I don't know whether you're using my variables declared at the top of the file or package variables (declared with our, or just undeclared if you don't have use strict 'vars' in place. It's probably worth declaring everything with my at the tightest scope possible (which is standard best-practice). That way every variable is made temporary and will be destroyed at the end of the block where it is declared. It's hard to say much more without seeing the full program

Answer 3

The issue is not readily apparent, here. It doesn't look like there is an obvious memory leak, so trying to free up allocated memory may not be the problem you need to solve. Perl can theoretically release memory back to the OS while running, but certain conditions have to be met and it's not a common occurrence, in my experience.

Setting $dbh->{mysql_use_result} = 1; after you connect to the db might help significantly.

This changes the way DBI (actually, libmysqlclient) reads incoming data from the socket, causing it to read data as needed every time you call a fetchrow* method rather than buffering it all into memory first, then handing you a row at a time from what it already has in memory, which is the default behavior.

DBD::mysql and the underlying library may be the entity responsible for bulk of the memory usage, so this is worth a shot.