Re: [CDBI] C::DBI Memory usage

On 24/05/06, Perrin Harkins <[email protected]> wrote:
> On Wed, 2006-05-24 at 13:33 +0100, Aaron Trevena wrote:
> > I'm assuming that CDBI objects are hash based, and over time the size
> > of those hashes will grow but none of the objects are in scope for
> > more than a few minutes and so shouldn't be using memory - are there
> > any global's that could be grabbing memory over time?
>
> If you do a query with a large result set, the entire thing will be
> loaded into memory.  The process will remain that size after the rows go
> out of scope.  It's not an object index thing; it's just how Perl works.

But in that case the process would never need to assign that memory
from fresh, it should be able to re-use the memory already allocated -
which after 12 hours will be a shedload more than required. Also I
don't have that many records - except in one table, into which I only
insert (from the daemon, it's read elsewhere).

> One way to avoid this is to do paging with LIMIT clauses or the
> equivalent for your database.  Some databases support fetching rows as
> you need them, usually using cursors, but keep in mind that the current
> DBD::Pg doesn't support this:
> http://search.cpan.org/~dbdpg/DBD-Pg-1.49/Pg.pm#Cursors

I don't think I have any queries more than 20 records so I shouldn't
have to worry - I've also removed any relationships that could result
in pulling in large numbers of records - all the HasMany relationships
are now removed in cdbi classes (and added in Maypole wrappers for the
benefit of the web interface, along with the maypole actions, etc)

I'm thinking the problem lies in a packaged scoped hash somewhere that
is updated frequently and just keeps on grabbing more memory and not
returning it. My suspicion is that it's in C::DBI somewhere.

My next step is attempting to rule out CDBI by replacing all CDBI use
in the long-lived parent process and seeing if that affects memory
allocation, before trying matt's suggestion with dbi.

Any other suggestions much appreciated as this is holding back a
pretty neat project that my client has allowed me to release as open
source : Schedule::Chronic::Constrained - A Distributed Constrained
Scheduler

Cheers,

A.