Solved

cachedwithin and cachedafter (please help)

Posted on 2003-10-29
35
765 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
ID: 9646140
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
ID: 9646533
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
ID: 9646667
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
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 
LVL 3

Author Comment

by:cdillon
ID: 9646880
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
ID: 9647027
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
ID: 9647741
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
ID: 9648253
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
ID: 9648426
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
ID: 9650085
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
ID: 9650134
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
ID: 9651094
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
ID: 9651488
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
ID: 9651568
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
ID: 9651836
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
ID: 9651866
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
ID: 9651868
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
ID: 9651898
By locking, do you mean <cflock>?
0
 
LVL 4

Expert Comment

by:procept
ID: 9656227
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
ID: 9657956
all the queries I'm caching have variables that change based on which state you are in.
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 9658214
>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
ID: 9658235
how do u maintain state?

CJ
0
 
LVL 3

Author Comment

by:cdillon
ID: 9658277
it's a variable in the url
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 9658330
then you maybe able to leverage CFCACHE.

CJ
0
 
LVL 3

Author Comment

by:cdillon
ID: 9658362
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
ID: 9658386
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
ID: 9658616
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
ID: 9658730
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
ID: 9658747
oops sorry didn't see Chris' post.

CJ
0
 
LVL 3

Author Comment

by:cdillon
ID: 9658776
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
ID: 9658807
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
ID: 9658810
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
ID: 9658829
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
ID: 9658843
0
 
LVL 4

Expert Comment

by:procept
ID: 9658920
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

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSL Cert Issues When Testing Microsoft Remote Connection Analyzer 3 64
How to stress test an ASP.NET https website 3 80
SSL Certificate for IIS7 Site 2 58
Link failure 16 34
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

829 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