这个cfqueryparam内存泄漏是否有解决方案？

Updates:

I have submitted the bug to Adobe and referenced this SO question

我已将错误提交给Adobe,并引用了这个问题
In my real-world code where the problem occurred I decided to just remove my use of cfqueryparam. I am now using a custom function to format the param based on type. There are security and speed concerns that I will have to deal with but it gets the particular process working acceptably under current load.

在我发现问题的真实代码中,我决定删除我对cfqueryparam的使用。我现在使用自定义函数来根据类型格式化param。我将不得不处理安全性和速度问题,但它使特定的进程在当前负载下可以接受。
In the future I am planning on going to process that pulls the data files into temporary tables in the database. I'll then perform operations on the data and transfer data to live tables using SQL as much as possible, instead of relying on ColdFusion

将来我计划进行处理,将数据文件拉入数据库中的临时表。然后,我将对数据执行操作,并尽可能使用SQL将数据传输到活动表,而不是依赖于ColdFusion

I am having a problem with looping over queries using cfqueryparam tags while inserting data. (I have not tested with select or update queries). The looping progressively takes up more memory that is not released until the request is done. However, the problem only occurs when looping over a query while in a function.

我在插入数据时使用cfqueryparam标记循环查询时遇到问题。 (我没有使用select或update查询进行测试)。循环逐渐占用更多内存,直到请求完成后才会释放。但是,只有在函数中循环查询时才会出现此问题。

It appears to be very sensitive to the number of cfqueryparam tags used. In this example there are 15 values being inserts however in my code that actually needs this to work I am inserting an unknown number of values that can make the problem more severe.

它似乎对使用的cfqueryparam标签的数量非常敏感。在这个例子中有15个值是插入,但在我的代码中,实际上需要这个工作我插入一个未知数量的值,可以使问题更严重。

Below is code that shows the problem. Give it a datasource name (tested on MSSQL) and it will create a tmp table and insert records as example with and without being in a function. Memory usage is display before, after the non-function loop, then after the in-function loop. It also requests garbage collection and waits 10 seconds before outputting memory info to ensure it is displaying info as accurately as possible.

下面是显示问题的代码。给它一个数据源名称(在MSSQL上测试),它将创建一个tmp表并插入记录作为示例,有或没有在函数中。内存使用情况显示在非函数循环之前,之后,然后是函数内循环之后。它还请求垃圾收集并在输出内存信息之前等待10秒,以确保它尽可能准确地显示信息。

In my experience with this particular test the in-function loop resulted in over 200mb of memory being used. In my real world uses it crashes ColdFusion :-(

根据我对这个特定测试的经验,函数内循环导致使用超过200mb的内存。在我的现实世界使用它崩溃ColdFusion :-(

<cfsetting enablecfoutputonly="true">
<cfsetting requesttimeout="600">

<cfset insertCount = 100000>
<cfset dsn = "TmpDB">

<cfset dropTmpTable()>
<cfset createTmpTable()>

<cfset showMemory("Before")>
<cfflush interval="1">

<cfloop from="1" to="#insertCount#" index="i">
    <cfquery name="testq" datasource="#dsn#">
        INSERT INTO tmp ( [col1],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10],[col11],[col12],[col13],[col14],[col15] )
        VALUES ( <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR"> )
    </cfquery>
</cfloop>

<cfset showMemory("After Non-Function INSERTS")>
<cfflush interval="1">

<cfset funcTest()>

<cfset showMemory("After Function based INSERTS")>

<cfset dropTmpTable()>

<cffunction name="funcTest" output="false">
    <cfset var i = 0>
    <cfset var testq = "">
    <cfloop from="1" to="#insertCount#" index="i">
        <cfquery name="testq" datasource="#dsn#">
            INSERT INTO tmp ( [col1],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10],[col11],[col12],[col13],[col14],[col15] )
            VALUES ( <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR"> )
        </cfquery>
    </cfloop>
</cffunction>

<cffunction name="showMemory" output="true">
    <cfargument name="label" required="true">

    <cfset var runtime = "">
    <cfset var memoryUsed = "">
    <cfset requestGC("10")>
    <cfset runtime = CreateObject("java","java.lang.Runtime").getRuntime()>
    <cfset memoryUsed = (runtime.totalMemory() - runtime.freeMemory()) / 1024 / 1024>
    <cfoutput>
        <h2>#arguments.label#</h2>
        Memory Used: #Round(memoryUsed)#mb
    </cfoutput>
</cffunction>

<cffunction name="requestGC">
    <cfargument name="waitSeconds" required="false" default="0" type="numeric">
    <cfscript>
        createObject("java","java.lang.Runtime").getRuntime().gc();
        createObject("java", "java.lang.Thread").sleep(arguments.waitSeconds*1000);
    </cfscript>
</cffunction>

<cffunction name="dropTmpTable" output="false">
    <cftry>
        <cfquery datasource="#dsn#">
            DROP TABLE tmp
        </cfquery>
        <cfcatch type="database"></cfcatch>
    </cftry>
</cffunction>

<cffunction name="createTmpTable" output="false">
    <cfquery datasource="#dsn#">
        CREATE TABLE tmp(
            col1 nchar(10) NULL, col2 nchar(10) NULL, col3 nchar(10) NULL, col4 nchar(10) NULL, col5 nchar(10) NULL, col6 nchar(10) NULL, col7 nchar(10) NULL, col8 nchar(10) NULL, col9 nchar(10) NULL, col10 nchar(10) NULL, col11 nchar(10) NULL, col12 nchar(10) NULL, col13 nchar(10) NULL, col14 nchar(10) NULL, col15 nchar(10) NULL
        )  ON [PRIMARY]
    </cfquery>
</cffunction>

Just to show that memory can be released during an operation, here is example code that builds up a larger struct and shows memory used before and after the variable is overwritten and garbage collected. In my run of this memory used after population is 118mb and after overwriting and garbage collection it is 31mb.

为了表明在操作期间可以释放内存,这里是一个示例代码,它构建了一个更大的结构,并显示了在覆盖变量和垃圾收集之前和之后使用的内存。在人口为118mb之后使用的这个内存的运行中,在覆盖和垃圾收集之后它是31mb。

<cfset showMemory("Before struct creation")>
<cfflush interval="1">

<cfset tmpStruct = {}>
<cfloop from="1" to="1000000" index="i">
    <cfset tmpStruct["index:#i#"] = "testvalue testvalue testvalue testvalue testvalue testvalue testvalue testvalue testvalue testvalue">
</cfloop>

<cfset showMemory("After struct population")>
<cfflush interval="1">

<cfset tmpStruct = {}>
<cfset showMemory("After struct overwritten")>

10 个解决方案

#1

Do you have debugging on in Administrator?

你在Administrator中有调试吗?

If so, even if you've got showdebugoutput="false", CF will be keeping debug information about all of those queries, and with that many queries, the debugging information could quickly build up.

如果是这样,即使你有showdebugoutput =“false”,CF也将保留有关所有这些查询的调试信息,并且通过这么多查询,调试信息可以快速建立起来。

Also, if you've really got 80,000 rows to insert, you probably want to be doing this a different way - e.g. generating an import script that runs directly against the DB, (without CF/JDBC getting in the way).

此外,如果你真的要插入80,000行,你可能想以不同的方式做这件事 - 例如生成直接针对数据库运行的导入脚本(没有CF / JDBC阻碍)。

#2

Maybe multiple insert can help? This technique itself typically works faster, saving some time can help you save some memory.

也许多个插入可以帮助?这种技术本身通常工作得更快,节省一些时间可以帮助您节省一些内存。

Yes I've seen your note "inserting an unknown number of values", but this should work if you have constant number of fields/values in a single insterting batch.

是的我已经看到你的笔记“插入了一个未知数量的值”,但如果你在一个insterting批次中有恒定数量的字段/值,这应该有用。

#3

No idea if it will make a difference, but something to try - shrink the in-function loop, and loop round the function multiple times.

不知道它是否会产生影响,但要尝试一下 - 缩小函数内循环,并多次循环函数。

What this does with memory might help narrow down where it is being used up.

这对内存的作用可能有助于缩小它在用完的地方。

<cffunction name="funcTest" output="false">
    <cfargument name="from" />
    <cfargument name="to" />
    <cfset var i = 0>
    <cfset var testq = "">
    <cfloop from="#arguments.from#" to="#arguments.to#" index="i">
        <cfquery name="testq" datasource="#dsn#">
            ...
        </cfquery>
    </cfloop>
</cffunction>


<cfset BlockSize = 100 />
<cfloop index="CurBlock" from="1" to="#(InsertCount/BlockSize)#">

    <cfset funcTest
        ( from : CurBlock*(BlockSize-1) + 1
        , to   : CurBlock*BlockSize
        )/>

</cfloop>

#4

I encountered a similar problem.

我遇到了类似的问题。

http://misterdai.wordpress.com/2009/06/24/when-not-to-use-cfqueryparam/

The approach depends on the few things. If you can trust the data, don't use cfqueryparam's, that'll reduce memory usage a lot. From there, minimize the SQL as much as possible. I was doing quite a bit of DB work per row, so I created a stored procedure instead. The big bonus in fighting memory usage was to buffer SQL calls to the database. Create an array, append your SQL to it, then every 50 rows (personal choice after testing) do an ArrayToList on the array, inside a CfQuery tag. This limits the database traffic to less, but larger, instead of many smaller ones.

这种方法取决于少数几件事。如果您可以信任这些数据,请不要使用cfqueryparam,这将大大减少内存使用量。从那里,尽可能地减少SQL。我每行做了相当多的数据库工作,所以我创建了一个存储过程。打击内存使用的最大好处是缓冲对数据库的SQL调用。创建一个数组,将SQL附加到它,然后每50行(测试后的个人选择)在CfQuery标记内的数组上执行ArrayToList。这会将数据库流量限制为更小,但更大,而不是更小的数据流量。

After all of that, things worked for me. But I still think ColdFusion really isn't up to this type of task, more the domain of the database server itself if possible.

毕竟,事情对我有用。但我仍然认为ColdFusion真的不适合这种类型的任务,如果可能的话,更多的是数据库服务器本身的域。

#5

My first guess would be to type the values in your cfqueryparam - as in type="CF_SQL_CHAR". Why would this help? I'm not sure, but I can guess that there would be additional overhead with a non-typed variable.

我的第一个猜测是在cfqueryparam中输入值 - 就像在type =“CF_SQL_CHAR”中一样。为什么这会有帮助?我不确定,但我可以猜测,非类型变量会产生额外的开销。

#6

Assuming you are using CF8... not sure if this happens in CF7...

假设您正在使用CF8 ......不确定CF7中是否会发生这种情况......

Try turning off "Max Pooled Statements" (set it to zero) in your datasource "advanced settings"... I bet money your memory leak goes away...

尝试在数据源“高级设置”中关闭“Max Pooled Statements”(将其设置为零)...我打赌你的内存泄漏消失了...

That is where I have found the bug to be... this was causing all kinds of crashes on some CF servers until we found this... we are 100% more stable now because of this...

这就是我发现错误的地方......这导致了一些CF服务器上的各种崩溃,直到我们发现这一点......我们现在因此而100%稳定...

Patrick Steil

#7

try to prepend "variables." before each query inside of your cffunctions. I've had a similiar issue and this fixed it.

尝试预先设置“变量”。在每个查询之前你的cffunctions。我有一个类似的问题,这解决了它。

So change:

<cfquery name="testq" datasource="CongressPlus">

<cfquery name="variables.testq" datasource="CongressPlus">

Cheers,

Thomas

#8

It's been well documented all over the community that CF will not release memory until after the request is finished. Even calling the GC directly has no effect on freeing up memory during a running request. Don't know if this is by design or a bug.

整个社区都有很好的文档证明CF在请求完成之前不会释放内存。即使直接调用GC也不会影响在运行请求期间释放内存。不知道这是设计还是错误。

I haven't a clue why you would even want to do something like this in CF anyways. There is no reason for you to be inserting 80K rows into a database using CF, no matter which database engine you're using.

我不知道为什么你甚至想要在CF中做这样的事情。无论您使用哪种数据库引擎,都没有理由使用CF将80K行插入数据库。

Now, if there is a reason that you need to do this, such as you're getting the data from say an uploaded CSV or XML file; MSSQL has a TON of better ways to do this and workarounds.

现在,如果您有理由需要这样做,例如您从上传的CSV或XML文件中获取数据; MSSQL有一些TON更好的方法来做到这一点和解决方法。

One approach that I have done over the years is to create a stored procedure in MSSQL that calls BCP or BULK INSERT to read a file that contains the data to insert.

我多年来一直采用的一种方法是在MSSQL中创建一个存储过程,调用BCP或BULK INSERT来读取包含要插入的数据的文件。

The best thing about this approach is that the only thing CF is doing is handling the file upload and MMSQL is doing all the work processing the file. MSSQL has no problems inserting millions of rows using BCP or BULK INSERT and will be INFINITELY faster then anything CF can process.

关于这种方法的最好的事情是CF正在做的唯一事情是处理文件上传和MMSQL正在处理文件的所有工作。 MSSQL使用BCP或BULK INSERT插入数百万行没有问题,并且比CF可以处理的任何内容都快。

#9

The way to prevent memory leaks from cfqueryparam in a large loop of queries was to not use cfqueryparam. However a broader answer is on avoiding CF's inefficiencies and memory leaks is to not use CF in these situations. I got the particular process to an acceptable level for the load at the time but in the long run will be rewriting it in another language, probably C# directly in the database engine.

在大量查询循环中防止cfqueryparam内存泄漏的方法是不使用cfqueryparam。然而,更广泛的答案是避免CF的低效率和内存泄漏是在这些情况下不使用CF.我在当时将特定进程设置为可接受的负载级别,但从长远来看,将使用另一种语言重写它,可能直接在数据库引擎中重写C#。

#10

I have no idea if that would fix your problem but what I usually do when I have multiple inserts like this is, a loop of the SQL statement itself instead of the entire cfquery.

我不知道这是否能解决您的问题,但是当我有多个这样的插入时我通常会做的是,SQL语句本身的循环而不是整个cfquery。

So instead of having :

所以不要:

<cfloop from="1" to="#insertCount#" index="i">
    <cfquery name="testq" datasource="#dsn#">
        ...
    </cfquery>
</cfloop>

I do :

我做 :

<cfquery name="testq" datasource="#dsn#">
    <cfloop from="1" to="#insertCount#" index="i">
        ...
    </cfloop>
</cfquery>

So instead of having multiple call to the database you only have one big one.

因此,不是多次调用数据库,而是只有一个大数据库。

I have no idea how this would affect your memory leak problem, but I never experienced any memory leaks doing it that way.

我不知道这会如何影响你的内存泄漏问题,但我从来没有遇到任何内存泄漏这样做。

#1