Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've googled all over the interwebs but can't find any documentation on this. how do i set my query cache to never expire?

share|improve this question
1  
Why would you want to do this? –  EEAA Jul 14 '11 at 1:56
    
my table is readonly, meaning it will never be updated. –  laurenceputra Jul 14 '11 at 2:46
    
If you are to be using a no-expiry cache, why not simply load the whole table in memory ? –  CloudWeavers Jan 4 '12 at 15:56

2 Answers 2

up vote 1 down vote accepted

The answer, as far as I know, is that you can't influence this aspect of MySQL's query cache. MySQL manages the query cache itself, and for the most part it works well.

But if you could, it shouldn't really make any noticeable difference. By enabling the query cache, you've gone from MySQL having to parse and run a particular common identical query 100% of the time, to having to do it only let's say 0.01% of the time.

You won't gain much by extending the timeout, because it won't make any difference to the 99.99% of identical requests that are already served well by the cache, and the only ones it can make a difference to is the 0.01% of requests where the cache has expired.

share|improve this answer
    
nah, the second it expires, i'm screwed, because my server's load is very high. nvm, i'm using cassandra as memcache on disk already. thanks. –  laurenceputra Jul 15 '11 at 4:05

I'm not aware of any time-expiration within mysql query cache. As far as I know, expiration is done only when:

1) Table is changed (mysql doesn't check if it could affect your query - every insert/update on that table causes all queries on that table to expire)

2) Query cache is full and old and rarely used queries are expired

So I assume that you hit one of these scenarios. Did you notify any particular timeframe how long the query stays cached? You can setup a cron job that runs the query periodically, which will pre-fill the cache. While this long-running query is running, the queries from web will still use the cached data. When it completes, it will replace the data in cache.

The issue with your high load is because when the cached data expires, many clients run the non-cache query at the same time. Using cron to pre-populate the cache will guarantee that only one instance of heavy query is happening at the same time. Other approach is to do the caching in web application and allow only one instance to reload the actual data, while other will still use the cached data during that time...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.