EllisLab text mark
Advanced Search
     
Database Caching
Posted: 15 August 2008 06:43 PM
Joined: 2008-02-07
46 posts

I’m looking at database caching documentation and I’m trying to wonder why there’s no expire time feature for the database caching like in the general page caching. Would creating such a feature put some limitations or was it just not programmed(probably not this one tho :p)

Thanks all!

 
Posted: 16 August 2008 01:35 AM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2008-01-10
80 posts

Database caching takes a little more attention to make it work for your situation - it ideally should only “time out” when the database data changes and it needs to fetch new query details. By time out I mean that you clear that cache.

So if you’ve got a page that pulls that last 5 blog entries, it caches the query results for those last 5 blog entries and then on the back end, when you update the blog you tell it to delete that cache.

I haven’t really played with the page cache that much, but I believe its more of a catch-all cache for the whole page. Not very “smart” - you can control the database cache to a much greater degree.

 Signature 

PyroStreams | MojoBlocks
Twitter | GitHub

 
Posted: 16 August 2008 01:22 PM   [ # 2 ]   [ Rating: 0 ]
Joined: 2008-02-07
46 posts

Frogmoves, your response does make sense and got me thinking a bit. The way the database works right does infact give me a lot more control over how I want the caching want to be. I would go for the page caching but like you said it caches the whole view output but thats no good to because I have dynamic data that changes on every refresh. Since I just have about 10-15 pages getting hammered and causing like 70% of the 50% mysql cpu usage, I was thinking abt creating a cron script that would update the queries that don’t change much every 30 or so minute. That way those queries are only hitting the mysql server every 30 minutes, instead of every page load. Before starting on this, the only question I have is how should I go about comparing the cached data and new updated query?

 
Posted: 16 August 2008 02:16 PM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2008-01-10
80 posts

BeingDefined,

You’re right, the database cache seems like its for you since you have a lot of query data coming in that changes - that can really take the load off mysql when done effectively.

My question is: what is the source of the changes for the database queries? For a blog, it would be the point where you create a new post or update an old one. If it is something that is maybe randomly taking a quote of the day or something, you could create a cron script that deletes the folder with that cache data in there.

It all depends on the type of data and the point where it changes - thinking about it that way you wouldn’t need to compare the cached data and the updated query.

What kind of data are you pulling and how is it updated or changed? I (or someone else) might be able to point out an ideal solution with that data.

 Signature 

PyroStreams | MojoBlocks
Twitter | GitHub