Should prepare_cached() manage its total size or memory usage?

From: Mark Stosberg (mark_at_summersault.com)
Date: 03/29/05 

To: dbi-users@perl.org
Date: Tue, 29 Mar 2005 15:29:06 +0000 (UTC)

Hello,

I have a database application that selects about 50,000 rows one by one,
does some process in Perl, and then executes a SELECT statement for each
one, with slight variations in the SQL and parameters.

I was using prepare_cached() on this repeatedly called SELECT statement.

Recently, as the number of SELECTs crept upwards, the script started to
die near the need with out of memory errors, like:

"Out of memory during request for 8864 bytes, total sbrk() is 415438848 bytes!"

Oddly, MRTG graphs of the machines memory usage during the run didn't show a significant
fluctuation. (The machine has 2 Gigs of RAM, plus swap).

Switching to use just a 'prepare()' resolved this issue, after some hours of
tracking down the issue.

I believe I understand what was happening-- the logic kept caching new unique
handles in the cache, until the total memory usage was "too much".

Would it be reasonable to expect DBI to perform more gracefully in this case?
(Or maybe this is more of a driver issue? I use DBD::Pg).

My preference would be that I could still use prepare_cached(), but that there
would be a maximum cache size, and the oldest unused handles would be
auto-expired.

A related question: Would the server-side-prepare feature of PostgreSQL
8.0 help with this?

Thanks!

    Mark

Relevant Pages

  • Re: linux hogging memory
    ... > We have been observing the total memory usage of the system. ... it will be in the *much* faster cache. ... James. ...
    (Fedora)
  • linux hogging memory
    ... We have been observing the total memory usage of the system. ... If we reboot the machine then memory usage drops to "acceptable" values ...
    (Fedora)