Title: How to prevent database from being DDOSed by application when cache expires?
Tags: php;mysql;codeigniter;caching;memcached
Question: We have a high traffic website much like StackOverflow with object cache in memcache. The site is built with PHP (CodeIgniter) and MySQL.
Everytime a TTL (time to live) expires on a cached object that is part of every page load, all of the page loads at that point result in a query to the database, effectively performing a DDOS on the database.
Is there some kind of way to have only a single pageload re-fetch the data and have the other page loads wait for the cache to be updated by the first one?
My first idea is to put a randomizer to work in having some of the page loads fetch the data and have others wait a second before re-examining the cache. But surely there must be a better way.
Comment: See [Avoiding stampeding herd](https://code.google.com/p/memcached/wiki/NewProgrammingTricks#Avoiding_stampeding_herd).

Comment: +1 for cache consideration! It would however result in multiple extra queries as you would have the other requests poll the database until you mark a page as 'rerendered into cache'

Comment: @eggyal Lots of interesting ideas in there, thanks.

Here is the accepted answer: You can use my algorithm from this code: https://github.com/jamm/Memory/blob/master/lib/Jamm/Memory/MemcacheObject.php#L230
Read key and TTL
If TTL is small (less than 5 seconds, for
example), try to lock special key (not that you are reading), like
'_update.{name_of_key}'
If lock was successful - calculate (or
read) new value and refresh cache
release updating key.
So only 1 process will read new value from DB.
Comment for this answer: Thanks. The concept of cache locking is new to me. I will see if I can fit this solution into our application.

Here is another answer: As to clarify my answer i'll write some code to show what i ment ( it might be a solution after all anyway... ):
if ($this-&gt;cacheExpired()){
if (!$this-&gt;isMarkedAsRegenerating())
$this-&gt;markAsRegenerating();              //..ing
$this-&gt;regenerateCache();
$this-&gt;markAsCacheRegenerated();          //..ed
} else {
while ( $this-&gt;isMarkedAsRegenerating() )
{
sleep(1); //sleep 1 second to decrease database-queries, we are preventing a DDOS you know...
}
$this-&gt;output(); //at this point, we always have a cached version of the page
The only risk involved is two pagehits at the very same time which *would make it possible to have the cache regenerated two times. It is very very unlikely but if it happens at least all other requests are still waiting.
Comment for this answer: Thanks, that's essentially the same as what others call cache locking if I understand correctly.

Here is another answer: If you have control of background tasks, such as cronjobs, you can arrange to have the work done, and data fetched, ahead of the required time, so that recent data never goes out of cache.
You could, for example, have a job run every 20 mins (or every minute for that matter), updating the cached values. While that job runs, no other database queries would be required to fetch the data.
Comment for this answer: That's a good idea. We're already doing this actually for requests that always take too long for a page load time to be acceptable. I could in fact have one cronjob periodically check the TTL of every cached item and refresh them a few seconds up front.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:01/10/14', 'FavoriteCount': 4.0, 'PostTypeId': 1, 'Tags': '<php><mysql><codeigniter><caching><memcached>', 'Title': 'How to prevent database from being DDOSed by application when cache expires?', 'ViewCount': 102, 'id': 21041628, 'user_AboutMe': '<p>Freelance web developer / designer. Node, Angular, Ionic Framework, PHP, WordPress.</p>\n\n<h1>SOreadytohelp</h1>\n', 'user_AccountId': 238478.0, 'user_DisplayName': 'jorisw', 'user_Reputation': 814.0, 'user_Views': 130.0, 'user_id': 1336408.0}}