cfqueryparam memory leak?

[Bob_Dively]

This is a rather complicated question, so bear with me. I have a CFM template that executes some business rules on ~2 million rows in an Oracle table. The code pulls in querysets of several 1000 rows at a time, performs some calculations and executes a few update queries for each row. The code is mostly a series of cffunctions with output=NO to minimize whitespace returned. So far so good. The code executes properly over subsets of the data but when run against the full data set, an out of memory error is thrown at some point around 100k rows processed and a 500 error is returned to the browser.

Okay, that's the background. Here's the head-scratching part. When I run a tool to monitor for Java memory leaks (in this case I'm using JRockit Mission Control 2.0.1 and have CF running on JRocket JRE jrockit-R27.2.0-jdk1.4.2_13), I notice that the amount of memory consumed by object of type coldfusion.tagext.sql.QueryParamtag seems to grow unchecked. Explicit garbage collection via Mission Control does not free the memory. When an out of memory error is finally thrown and the page execution halts, the objects are released and automatic garbage collection frees the memory.

Anyone have any clue what's happening here? I assume that coldfusion.tagext.sql.QueryParamtag has something to do with cfqueryparam tags, but I have no idea how to explicitly release that memory. I am explicitly destroying query objects at the end of functions that contain queries by cfset queryname="", but doing so doesn't seem to have any effect on the query param objects.

For reference, I'm running CFMX7 7.0.2.142559 on Win2003 Server. As I noted above, I'm using BEA's JRocket JRE 1.4.2_13, although the same behavior occurs with the native CF JRE.

[Newsgroup_User]

> This is a rather complicated question, so bear with me. I have a CFM template
> that executes some business rules on ~2 million rows in an Oracle table. The

I'd strongly recommend removing CF from the equation in processes like
this. Get the DB to do the DB's work.

I know this is not an answer to the question that you asked, but I don't
think CF - a web scripting language - is going to be a suitable (let alone
"best") tool for the job, doing this sort of processing.

Of course this is not always possible, but it *usually* is.


> coldfusion.tagext.sql.QueryParamtag seems to grow unchecked. Explicit garbage
> collection via Mission Control does not free the memory. When an out of memory
> error is finally thrown and the page execution halts, the objects are released
> and automatic garbage collection frees the memory.

That doesn't seem very good at all.

What are your settings for "Maintain connections" and "Max Pooled
Statements" (and how does the latter compare with the OPEN_CURSORS setting
on the DB server?

What version are your JDBC drivers?

What happens if you force the connections to the DB to close (I'm not sure
if the "Disable Connections" setting in CF Admin does this (or whether that
functioanlity is achievble via the API?).

Does this happen with the default JVM (you can at least observe the memory
blowout, if not the actual culprit).

If you run the process on a subset of records so as to only bloat it (and
then complete), but not cause it to error, will the run-of-the-mill GC
release the memory?

What are your JVM config settings?

--
Adam

[Bob_Dively]

> I know this is not an answer to the question that you asked, but I don't
> think CF - a web scripting language - is going to be a suitable (let alone
> "best") tool for the job, doing this sort of processing.

Hi Adam. Yes, I agree with you on that, but I don't have any choice in the matter. Of course, if CF simply cannot deal, then the decision makers involved in the process may be forced to do something different.

> What are your settings for "Maintain connections" and "Max Pooled
> Statements" (and how does the latter compare with the OPEN_CURSORS setting
> on the DB server?

Maintain connections is checked and Max Pooled Statments is 2000. The OPEN_CURSORS parameter is set to 300, which is low because I'm running this on a development box.

> What version are your JDBC drivers?

v3.3

> What happens if you force the connections to the DB to close (I'm not sure
> if the "Disable Connections" setting in CF Admin does this (or whether that
> functioanlity is achievble via the API?).

Do you mean force the connections closed while the page is processing?

> Does this happen with the default JVM (you can at least observe the memory
> blowout, if not the actual culprit).

Yep. I installed the JRockit JVM for its profiling functionality, but the default Sun JVM does the exact same thing.

> If you run the process on a subset of records so as to only bloat it (and
> then complete), but not cause it to error, will the run-of-the-mill GC
> release the memory?

Yes. As long as the processing completes before the out-of-memory error, the objects are destroyed and GC releases the memory as expected.

> What are your JVM config settings?

Straight vanilla setup from the installation.

[Newsgroup_User]

> Hi Adam. Yes, I agree with you on that, but I don't have any choice in the
> matter. Of course, if CF simply cannot deal, then the decision makers involved
> in the process may be forced to do something different.

Well it's something to keep in mind if all else fails, as you say. Strange
decision for them to make, though. Obviously you're not describing the
whole situation, so there could well be a good reason for it.


> Maintain connections is checked and Max Pooled Statments is 2000. The
> OPEN_CURSORS parameter is set to 300, which is low because I'm running this on
> a development box.

That could be part of your problem (I'm just guessing here). For a test,
try hacking the Max Pooled Statements down to 250 (ie: less than the
open_curors). I'm surprised you're not getting "max cursors exceded"
errors here. I often do, when the CF setting is higher than the Oracle
one.


> > What happens if you force the connections to the DB to close (I'm not sure
> > if the "Disable Connections" setting in CF Admin does this (or whether that
> > functioanlity is achievble via the API?).
>
> Do you mean force the connections closed while the page is processing?

> > If you run the process on a subset of records so as to only bloat it (and
> > then complete), but not cause it to error, will the run-of-the-mill GC
> > release the memory?
>
> Yes. As long as the processing completes before the out-of-memory error, the
> objects are destroyed and GC releases the memory as expected.

Yep. Run some processing... close connection... open connection... run
some more processing. This is contingent of being able to do the
connection handling with code, though; which I'm sure is possible, but not
sure how.


> > What are your JVM config settings?
>
> Straight vanilla setup from the installation.

EEW. OK, doing that sort of lifting is not going to work on the default
amount of RAM allocation, I would have thought.

Try upping the Xmx value to 1024m (as long as that's not more than 75% of
the free RAM on the box).

Try experimenting with some of the other GC options. I'm out of my depth
now as to what to suggest trying, but there's stacks of stuff out there, a
good list of them being here:
http://blog.pixl8.co.uk/index.cfm/2006/12/29/Coldfusion-Tuning-links#more

--
Adam