Template cache table growing very big, best way to keep it in line?

Question

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?

Answer 1

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 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 or Varnish