有这个cfqueryparam内存泄漏的解决方案吗？ [英] Is there a solution to this cfqueryparam memory leak?

查看：191
本文介绍了有这个cfqueryparam内存泄漏的解决方案吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

更新：




  • 我已将问题提交给Adobe， / p>


  • 在我的现实世界中出现问题的代码中，我决定删除对cfqueryparam的使用。我现在使用自定义函数来根据类型格式化参数。


  • 在未来，我计划开发一个新的应用程序，将数据文件拉入数据库中的临时表的过程。然后，我将对数据执行操作，并使用SQL尽可能多地将数据传输到活动表，而不是依赖ColdFusion。






在插入数据时，我遇到了使用cfqueryparam标记循环查询的问题。 （我没有测试与选择或更新查询）。循环逐渐占用更多的内存，直到请求完成才释放。然而，问题只发生在当在一个函数中循环查询时。



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



下面是显示问题的代码。给它一个数据源名称（在MSSQL上测试），它将创建一个tmp表，并插入记录作为示例，包含和不包含在函数中。内存使用情况显示在非功能循环之前，之后，在功能循环之后。它还请求垃圾回收，并在输出内存信息之前等待10秒钟，以确保它正在尽可能准确地显示信息。



在我对这个特定测试的经验中，循环导致使用超过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 =1to =＃insertCount＃index =i> 
< cfquery name =testqdatasource =＃dsn＃& 
 INSERT INTO tmp（[col1]，[col2]，[col3]，[col4]，[col5]，[col6]，[col7]，[col8]，[col9]，[col10] ] 
 VALUES（< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR>，< cfqueryparam value =TestValuecfsqltype = CF_SQL_CHAR> ;,< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR> ;,< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR> ;,< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR >，< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR>，< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR> ;,< cfqueryparam value =TestValuecfsqltype =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 =funcTestoutput =false> 
< cfset var i = 0> 
< cfset var testq => 
< cfloop from =1to =＃insertCount＃index =i> 
< cfquery name =testqdatasource =＃dsn＃> 
 INSERT INTO tmp（[col1]，[col2]，[col3]，[col4]，[col5]，[col6]，[col7]，[col8]，[col9]，[col10] ] 
 VALUES（< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR>，< cfqueryparam value =TestValuecfsqltype = CF_SQL_CHAR> ;,< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR> ;,< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR> ;,< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR >，< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR>，< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR> ;,< cfqueryparam value =TestValuecfsqltype =CF_SQL_CHAR> ;，< / cfquery> 
< / cfloop> 
< / cffunction> 
 
< cffunction name =showMemoryoutput =true> 
< cfargument name =labelrequired =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> 
使用的内存：#Round（memoryUsed）#mb 
< / cfoutput> 
< / cffunction> 
 
< cffunction name =requestGC> 
< cfargument name =waitSecondsrequired =falsedefault =0type =numeric> 
< cfscript> 
 createObject（java，java.lang.Runtime）。getRuntime（）。gc（）; 
 createObject（java，java.lang.Thread）。sleep（arguments.waitSeconds * 1000）; 
< / cfscript> 
< / cffunction> 
 
< cffunction name =dropTmpTableoutput =false> 
< cftry> 
< cfquery datasource =＃dsn＃> 
 DROP TABLE tmp 
< / cfquery> 
< cfcatch type =database>< / cfcatch> 
< / cftry> 
< / cffunction> 
 
< cffunction name =createTmpTableoutput =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，col10 nchar（10）NULL，col12 nchar（10）NULL，col8 nchar（10）NULL，col9 nchar ）NULL，col14 nchar（10）NULL，col15 nchar（10）NULL 
）ON [PRIMARY] 
< / cfquery> 
< / cffunction>

只是为了显示内存可以在操作过程中释放，下面是一个示例代码， struct并显示在变量被覆盖和垃圾收集之前和之后使用的内存。在我的运行这个记忆使用后人口是118mb，覆盖和垃圾收集后，它是31mb。

 < cfset showMemory 在结构创建之前）> 
< cfflush interval =1> 
 
< cfset tmpStruct = {}> 
< cfloop from =1to =1000000index =i> 
< cfset tmpStruct [index：＃i＃] =testvalue testvalue testvalue testvalue testvalue testvalue testvalue testvalue testvalue testvalue> 
< / cfloop> 
 
< cfset showMemory（struct population）> 
< cfflush interval =1> 
 
< cfset tmpStruct = {}> 
< cfset showMemory（After struct overwritten）>


解决方案

防止cfqueryparam内存泄漏循环的查询是不使用cfqueryparam。然而更广泛的答案是避免CF的低效率和内存泄漏是在这些情况下不使用CF。我使特定的进程到当时的负载可接受的水平，但在长期将被重写它在另一种语言，可能是C＃直接在数据库引擎。


Updates:

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

  • 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.

  • 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

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.

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.

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.

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 :-(

<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.

<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")>

解决方案

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内存泄漏的解决方案吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
高性能WEB开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆