Solved

cachedwithin and cachedafter (please help)

Posted on 2003-10-29
35
761 Views
Last Modified: 2013-12-24
I'm using cachedwithin and cachedafter in various spots.  I used to have it caching up to 100 queries in the cfadmin, but if I set it to 100 now (after I've made changes), the server runs out of memory.  Is there any way to see which query is culprit?  I've tried to pin down the time at which the server runs out of memory by having it mail me when the server gets low on memory.  I can't see any pattern.  It's killing server performance to only have 20 queries cached.    With only 20 queries cached, the server memory hovers around 100 MB.  When I bump it up to 100,  there have been times when the server has run out of virtual memory and crashed.
0
Comment
Question by:cdillon
  • 13
  • 12
  • 6
  • +2
35 Comments
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
well large queries can be problematic with memory and caching.

Query caching can eat away at your server memory

One thing to keep in mind is that every permutation of a query is cached.
so if you have a cached query like this:
select * from account where account_id = #id#

then
select * from account where account_id = 1
is cached separate from
select * from account where account_id = 2

Do you have any queries that can have large permutations.

What is the average resultset size?

CJ
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
To answer your two questions:
1. there is a query that could have many permutations, but I'm not as concerned that the server will have to release queries once in a while.  This may well fill up all 100 queries and queries might have to be released.  My concern is over server memory getting too low.

2. when I test it, the resultset sizes are below 30 results with the results being integers,  I can't know what the resultsets are in every case.

0
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
An alternative to caching might be to use maxrows, for example if your on the first result page and you show only 20 results, use maxrows="20", when your on the second page use maxrows="40" and so on.

It be nicer if you do this within the stored procedure, that's if your working stored procedures? You'd use ROWCOUNT = 20 etcetera

And that's like only if you run our out of caching space, because you can only cache so many queries in the end.

And to figure out which query is taking that long you can look at the log files of CF under cf admin panel.
You might also want to enable the following under cf admin

 Log slow pages taking longer than  seconds
To help diagnose potential problems or bottlenecks in your site, you can have ColdFusion log the names of any pages that take longer than a specified length of time to return. When enabled, any output will be written to the "server.log".
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
I need all the rows that come out, there is no option of paging because the results are in a pulldown menu.  I'm mostly concerned with the memory allocation aspect of this problem.  If I can cache more queries, the pages load significantly faster.  The problem is that it crashes the server.
0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
quick question.  How are you testing this?  Are you sure its a cached query issue or maybe a memory leak in some new code that when it is called enough times, in combination with the 100 cached queries is causing the server to crash.

Queries with resultsets in the 30s or in fact under 100 should not cause problems with caching on normal loads.  Now if you have high volume/load/traffic and the cache's hit ratio is low enough that it is releasing queries pretty fast then you have a performance problem due to load and need to look at upgrading servers or clustering.

Can you describe your site load/traffic and the volume on the query pages?

I agree with Tacobell777: look at the log files, log slow pages.   You can also turn on debug to see what queries are returning (exact resultset sizes, query times and whether they were cached or not)

CJ
0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
if its just with getting ur processing done faster - then i wld recommend using procedures & packages.

once a package is laoded in ur memory it remains there & u can see a significant improvement in the performance of ur site. i did that & since then i never bothered abt caching queries .. as everythign was done at backend level & packages took care of the caching problem :)

this may mean a big change in ur code & u might have to write some sql blocks in ur DB - but will solve ur issues once & for all. try doing this for one simple query on ur site & see the difference for urself !

Just a suggestion:)

K'Rgds
Anand

PS : with query caching - u'll need to carefully plan ur queries & know which queries u cld go for ... a problem I experienced when i tried caching long back - was i cld NOT use CFQueryParam with it ... so i dropped the idea of query caching & let my packages & procedures handle it for me !
0
 
LVL 4

Expert Comment

by:procept
Comment Utility
Hi,

what version of CF are yo using?  I agree that it might be an issue with other code than the queries. I have an app where I permamently cache around 50,000 records and the server is not getting memory probs. Of course if your server has only little memory from the start (like just meeting the system requirements), that could become a problem.

What are the execution times of the uncached queries? If you have long running queries, it could be an option to serialize the recordsets into a WDDX packet and save that on disk for future re-use. (Not recommended for fast queries, the time to read the packet and to de-serialize would be longer than the query execution time. ;-))

HTH,

Chris
0
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
If its a dropdown you need and the content of it does not change often, why not retrieve the data and write it to a file as an array, and use this as an include. maybe this might be the way for you to go (for now anyway) I'd get to the bottom of the problem myself, but that's something only you can do because it can be so many things.
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
re: questions
I'm testing this by setting the caching to cache only 20 queries - works fine, then I set it to 100- overflows memory, system crashes.  We have two servers, one for db and one for coldfusion.  The coldfusion server is the one that crashes.  The db server is overloaded and generally the CPU of the coldfusion server sees very little use.  We are using coldfusion 5.  The cf server has 512 MB,  the db server doesn't crash but is pretty slow.  It's a design problem that I inherited but we'll someday correct the design.  The queries that I chose to cache are fairly slow and are used throughout the system, but they have fairly few results and the results change pretty infrequently.  There are about four queries, together they probably reduce the page loading time by a couple of seconds on most pages.  The site load is pretty heavy, especially with webtrends and all the spiders.    The cached queries make a big difference until the system crashes...  I chose the queries to cache using the debugging query times and my knowledge that the results don't change very often and for one query the results change, at most, every three months.  I've invested some time making sure that the results do change when they are updated on the system by using cachedafter and making the cache date change when updated.  Makes me hesitant to go a different direction because all that work will end up not being used.





0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
Oh, one more thing, the data doesn't change often for a given query but the query being cached does change.  
For example:
select ...
from ...
where state = '#variables.state#'

I'm not concerned by the fact the query might get pushed out of the 100 queries, I am concerned that setting it to 100 queries causes the system to crash.
0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
well considering how many states there are.. and if your user load is high.. you maybe using up the 100 queries frequently and causing a lot of paging and queries moving in and out of the cache.

So you are caching just 4 queries and with low resultsets.  I am inclined to think this has more to do with site load and the added issue of 100 cached queries rather than just the cached query number.

Can you post the 4 queries here?

CJ
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
Sorry, I'm not really allowed to release the code because this is not my site.  However, the site and memory used is stable with 20 queries and has been for at least a couple of weeks.  However, it's almost guaranteed if I move it up to 100 that the site will eventually become unstable and start "thashing" and eventually crash.  

I don't know for sure that the query resultsets are low.  I have a feeling it might have to do with webtrends and/or viewing the coldfusion pages without the expected parameters...  I think I might try record the maximum resultset sizes and the query that produced them by recording only if the resultset size is bigger than the last resultset.

Does "popping" (moving queries out of the cache) cause a memory leak?
0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
Here is a good article to read:
http://www.macromedia.com/devnet/mx/coldfusion/articles/cfcaching.html
and one more:
http://www.macromedia.com/devnet/server_archive/articles/programmatic_caching_cf.html#e

Now one thing to keep in mind.  You don't know how much memory each cached query is using.  Maybe 512 MB of RAM can only handle 20 cached queries on your site.  Upping the RAM to 1 GB might help you increase your cached query limit to 30 - 40 (not exactly a 2x increase b/c this isn't a 2:1 ratio)

Are all your queries like the state query or some just a fixed resultset.  If you have a query that returns the same results no matter what user or scenario maybe you should take advantage of page caching rather than query caching and save some of the memory for your query cache:
http://www.macromedia.com/devnet/server_archive/articles/programmatic_caching_cf.html#d

You maybe just running into a hardware limit here.

CJ
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
For the <cfcache>, how does it know if your result set changes if there are no changes to the url parameters?  My impression is that it doesn't and will blindly output the old version of the page regardless of database content.  That doesn't really work for us because every page is dynamic at least somewhere in the page.
0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
I don't believe query cache management has memory leaks - if it did our site would be crashing every 2 mins with our 500,000 daily page views from about 40,000 - 60,000 unique visitors per day.  CF handles about 20% or so of the load.

One thing to note: When we had just Coldfusion running the site we had over 30 webservers being load balanced (the traffic was a bit lighter back then as we only were hitting about 150,000 page views or so a day).  Each WebServer had 2 GB of RAM.   Even then CF crashed about 1-2 times an hour.  This was due to some poorly written code but even after we fixed it, things didn't change much.  We had to offload CF's load to other app servers (like Java) Since ColdFusion never handled our site traffic well (even after fixing all the poorly written code) we moved most of our critical applications to Java.  

We do have page caching along side query caching.  We only see a CF Server crash about 1 or 2 times a day rather than 1-2 times an hour now that we added page caching and offloaded a major part of the load.  The locking was a major cause of memory leaks and is the usually the culprit for most CF apps.

CJ
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
I guess another option is to removing the caching from all queries and add them back in one by one upping the limit to 100 and see which one makes it crash.
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
By locking, do you mean <cflock>?
0
Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

 
LVL 4

Expert Comment

by:procept
Comment Utility
Hi,

if some of your queries do NOT change, store them into application variables. ust keep in mind that you need to lock all access to application vars (actually to all shared scope vars) if you are using CF 5 or earlier versions. CF MX doesn't require shared scope locking...

HTH,

Chris
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
all the queries I'm caching have variables that change based on which state you are in.
0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
>guess another option is to removing the caching from all queries and add them back in one by one upping the
>limit to 100 and see which one makes it crash.

That would be a good way of debugging it.  Since you can't post your queries we can't help you optimize them or see what could be the problem.

 
>By locking, do you mean <cflock>?
Yes CFLOCK, the absence of CFLOCK around Application, Client, SESSION variables can cause big performance/memory problems in a CF site.

about CFCACHE.  It may help.  If dynamic content doesn't change when the User revisits the page (with the same url params) then you can leverage CFCACHE.

You can read a good bit about cfcache here:
http://www.macromedia.com/devnet/mx/coldfusion/articles/cfcaching.html
and
http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Tags9.htm#1097532

CJ



 
0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
how do u maintain state?

CJ
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
it's a variable in the url
0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
then you maybe able to leverage CFCACHE.

CJ
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
The talk about application locking has me thinking...  the datasource used is always:

<cfquery datasource="#application.datasource#" name="myquery">

Is it a problem that this application variable is not locking during the read?    It is set in application.cfm:

<cfset application.datasource="mydatasource">

0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
The problem (I think) with cfcache is that there are other elements on the page that don't depend on the URL.  For instance, if there were a news item that depends on whether or not an admin has changed the story in the database or not.
0
 
LVL 4

Expert Comment

by:procept
Comment Utility
Hi,

cheekycj is quite right about locking... in CF 5 and earlier you need to lock each eand every access to server, application or session vars. No locking needed for client vars though, as they are not stored in server memory.

If you are using CF MX, then locking is not an issue, although it is considered a good practice, to still lock the shared scope vars.

As for the use of application.datasource: Yes, that can be a problem. If different queries access the var at the exact same moment, you might experience memory problems, or even server crash. Unlikely as it is in a small application, if the app is heavily frequented, it definitely is an issue.

A couple of ways to solve this:
1) Query inside lock
<cflock scope="application" timeout="5" throwontimeout="no" type="readonly">
   <cfquery datasource="#application.datasource#" name="myQuery">
 ...
</cflock>

Bad idea... as long as the query runs, no other process cann access that var for writing. If a query runs 30 secs... Application.cfm can not set the var again. You could precent that by using IsDefined() in Application.cfm, but, that needs to be locked, too. :-((

2) copy Application var to local var:
<cflock scope="application" timeout="5" throwontimeout="no" type="readonly">
   <cfset myDSN = application.datasource>
 </cflock>

<cfquery datasource="#myDSN#" name="myQuery">
...

Much better, the lock is active only for very few milliseconds, causes no delay. Requires some re-coding though.

3) Use a request var. Request variables are available to all pages and elements of a request, including custom tags and CFCs
In Application.cfm set
<cfset request.datasource = "myDatasource">
And replace all occurences of application.datasource with request.datasource. Should be a matter of seconds with a tool like "extended search and replace" like offered by Homesite or CFStudio.

HTH,

Chris


0
 
LVL 19

Accepted Solution

by:
cheekycj earned 500 total points
Comment Utility
you should do this

in your application.cfm

<cflock timeout="10" throwontimeout="No" type="EXCLUSIVE" scope="APPLICATION">
  <cfset application.datasource="mydatasource">
</cflock>

And in yoru code:

Either:

<cflock timeout="10" throwontimeout="No" type="EXCLUSIVE" scope="APPLICATION">
  <cfquery datasource="#application.datasource#" name="myquery">
  ...
  </cfquery>
</cflock>

or this way (which I think is better):

<cflock timeout="10" throwontimeout="No" type="READONLY" scope="APPLICATION">
  <cfset variables.datasource = application.datasource>
</cflock>
<cfquery datasource="#variables.datasource#" name="myquery">
  ...
</cfquery>

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
oops sorry didn't see Chris' post.

CJ
0
 
LVL 3

Author Comment

by:cdillon
Comment Utility
Thanks guys, I have a feeling locking might be causing the problem somehow.  Maybe it's only an issue when server memory gets tight.
0
 
LVL 4

Expert Comment

by:procept
Comment Utility
CJ: happens to me all the time today... meaning that I miss other ppls. answers. ;-))

CDillon: give it a try, should be a matter of minutes at most with a proper editor...

Cheers,

Chris

0
 
LVL 4

Expert Comment

by:procept
Comment Utility
CJ: happens to me all the time today... meaning that I miss other ppls. answers. ;-))

CDillon: give it a try, should be a matter of minutes at most with a proper editor...

Cheers,

Chris

0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
Chris : :-)

cdillon: I agree with Chris.  Esp with any CF version below MX (6.0) you must use CFLOCK to prevent memory leaks for all application and session variables.  I would do a search in your code on the two strings:
"session."
"application."

and make sure each is surrounded by the appropriate cflock statement.

fix the code.  Upload to your server and restart it. see if that stabalizes the site.

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
Comment Utility
0
 
LVL 4

Expert Comment

by:procept
Comment Utility
If I may add one presentation:

http://www.procept.net/index.cfm?page=coldfusion/presentations/index

The page is in German, but, you'll find a link "English version, extended and commented"... it's a powerpont presentation to view online or to download as zip. It deals with CF up to version 5 only.

But, basically  it should say the same as the MM articles. ;-)

Chris

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now