Answers.Body: <p>In addition to what Brad suggested, I'd also do some auditing to see what kind of benefit you're getting out of your caches. You could, for instance, use the <code>for</code> parameter to cache only for 24 hours or such. No reason to keep a stale cache around that isn't getting hit -> <a href="https://docs.craftcms.com/v3/dev/tags/cache.html#for" rel="nofollow noreferrer">https://docs.craftcms.com/v3/dev/tags/cache.html#for</a></p>
  
  <p>e.g.:</p>
  
  <pre><code>   {% cache using key cacheKey for 1 day %}
  </code></pre>
  
  <p>Let unused caches naturally expire after a reasonable period of time.</p>
  
  <p>If you do it this way, <em>all</em> caches will not expire at the same time. In an ideal world, Craft's <code>{% cache %}</code> tag would allow for incrementing the cache duration based on hits, so things that are getting lots of hits would say in the cache (until otherwise busted), and unused cache entries would expire.</p>
  
  <p>I don't think there is any way to do that currently, tho. So at least using <code>for</code>, it isn't an all or nothing proposition, and caches will expire individually, 24 hours after they are created.</p>
  
  <p>I will also say that you may consider using another method of caching, such as <a href="https://nystudio107.com/blog/static-caching-with-craft-cms" rel="nofollow noreferrer">FastCGI Cache</a> or <a href="https://supercool.github.io/2015/06/08/making-craft-sing-with-varnish-and-nginx.html" rel="nofollow noreferrer">Varnish</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-15T00:49:20.527Z
Answers.LastActivityDate: 2018-09-15T00:58:30.870Z
Answers.LastEditDate: 2018-09-15T00:58:30.870Z
Answers.UserDisplayName: andrew.welch
Body: <p>So we have built a rather large site for a client, which has multiple regions, locales, user privileges, etc.
  Some time ago the default cache of Craft couldn't cope anymore with keeping track. The cache clearing tasks always stalled so cache wouldn't be deleted anymore.</p>
  
  <p>This triggered us to change the way we cache our pages. Unfortunately, the template cache table in the database is getting to its limits as we haven't found a good way to clear the old caches.</p>
  
  <p><strong>How it works:</strong></p>
  
  <p>We set <code>'cacheElementQueries' =&gt; false,</code></p>
  
  <p>Every page gets its own cache (unique cachekey) which consists of:</p>
  
  <ul>
  <li>Is the user logged in yes or no?</li>
  <li>Which region is the user in? (one of 3 possibilities)</li>
  <li>Which user type does the user have? (any combination of 3 types possible)</li>
  <li>The page url</li>
  <li>The last updated date of the entry (not always used)</li>
  </ul>
  
  <p>We cache everything globally and cache some parts of pages separately, for example: the sidebar of a product page remains the same but the content changes. </p>
  
  <p>We use a combination of <em>coldcache</em> and <em>cacheflag</em> plugins.</p>
  
  <p><strong>Issue:</strong></p>
  
  <p>As we create a lot of caches and a lot of them with a certain date in the key, the database table gets cluttered and grows to big in size (max limit for our DB is 1GB and the cache table is reaching 500MB).</p>
  
  <p><strong>The question</strong></p>
  
  <p><strong><em>How can we delete old caches from the DB table?</em></strong></p>
  
  <p>The only two options I can think of now are:</p>
  
  <ol>
  <li>Set cache duration to a certain time interval (but than all caches are deleted which will make the site slow every interval).</li>
  <li>Create a plugin that runs every month with a CRON job and checks all of the cache keys and deletes everyone but the latest.</li>
  </ol>
  
  <p>Any other ideas out there?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-09-14T20:36:58.977Z
Comments.Text: FWIW, #2 is what I could go with.
Comments.UserDisplayName: Brad Bell
Tags: <cache>
Title: Template cache table growing very big, best way to keep it in line?
ThreadText: # Template cache table growing very big, best way to keep it in line?
  
  (Asked by: username_0 on 2018-09-14)
  
  So we have built a rather large site for a client, which has multiple regions, locales, user privileges, etc.
  Some time ago the default cache of Craft couldn't cope anymore with keeping track. The cache clearing tasks always stalled so cache wouldn't be deleted anymore.
  
  This triggered us to change the way we cache our pages. Unfortunately, the template cache table in the database is getting to its limits as we haven't found a good way to clear the old caches.
  
  **How it works:**
  
  We set `'cacheElementQueries' => false,`
  
  Every page gets its own cache (unique cachekey) which consists of:
  
  * Is the user logged in yes or no?
  * Which region is the user in? (one of 3 possibilities)
  * Which user type does the user have? (any combination of 3 types possible)
  * The page url
  * The last updated date of the entry (not always used)
  
  We cache everything globally and cache some parts of pages separately, for example: the sidebar of a product page remains the same but the content changes.
  
  We use a combination of *coldcache* and *cacheflag* plugins.
  
  **Issue:**
  
  As we create a lot of caches and a lot of them with a certain date in the key, the database table gets cluttered and grows to big in size (max limit for our DB is 1GB and the cache table is reaching 500MB).
  
  **The question**
  
  ***How can we delete old caches from the DB table?***
  
  The only two options I can think of now are:
  
  1. Set cache duration to a certain time interval (but than all caches are deleted which will make the site slow every interval).
  2. Create a plugin that runs every month with a CRON job and checks all of the cache keys and deletes everyone but the latest.
  
  Any other ideas out there?
  
  **Tags:** cache
  
  > **Comments:**
  > **username_1**: FWIW, #2 is what I could go with.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2018-09-15. Score: 1
  
  In addition to what username_1 suggested, I'd also do some auditing to see what kind of benefit you're getting out of your caches. You could, for instance, use the `for` parameter to cache only for 24 hours or such. No reason to keep a stale cache around that isn't getting hit -> <https://docs.craftcms.com/v3/dev/tags/cache.html#for>
  
  e.g.:
  
  ```
     {% cache using key cacheKey for 1 day %}
  
  ```
  
  Let unused caches naturally expire after a reasonable period of time.
  
  If you do it this way, *all* caches will not expire at the same time. In an ideal world, Craft's `{% cache %}` tag would allow for incrementing the cache duration based on hits, so things that are getting lots of hits would say in the cache (until otherwise busted), and unused cache entries would expire.
  
  I don't think there is any way to do that currently, tho. So at least using `for`, it isn't an all or nothing proposition, and caches will expire individually, 24 hours after they are created.
  
  I will also say that you may consider using another method of caching, such as [FastCGI Cache](https://nystudio107.com/blog/static-caching-with-craft-cms) or [Varnish](https://supercool.github.io/2015/06/08/making-craft-sing-with-varnish-and-nginx.html)
  
  
  ---
  
  