3

I am having a big problem with a newly created Drupal 8 site for a theater.

After going live we discovered that the cache_render table grown extremely big, and extremely fast. After one weekend, it was already over 30 GB which makes a backup of the database impossible.

After some analysis and google search, I have stumbled upon cache_render database table growing in size. They say it is wise to use a caching service such as Memcache but honestly, that does not really solve the problem at all.

I have set the Cache maximum age to 1 hour and the cache_render page STILL sets the expiration date to infinity! Why?

What causes Drupal to fill up the database this much?

System information

Drupal version: 8.1.0

Modules in use:

  • List item
  • color_field 8.x-2.0-rc1
  • contact_block 8.x-1.1
  • contact_storage 8.x-1.0-beta4
  • ds 8.x-2.3
  • embed 8.x-1.0-rc2
  • entity_embed 8.x-1.0-alpha1
  • entity_reference_revisions 8.x-1.0-rc6
  • field_group 8.x-1.0-rc4
  • file_entity 8.x-2.x-dev (2016-Apr-13)Aktuell Ok
  • Google Analytics 8.x-2.0
  • google_map_field 8.x-1.1
  • imagemagick 8.x-1.0-alpha2
  • layout_plugin 8.x-1.0-alpha22
  • mailchimp 8.x-1.1
  • menu_link_attributes 8.x-1.0-beta1+5-dev (2016-Feb-23)
  • metatag 8.x-1.0-beta8
  • paragraphs 8.x-1.0-rc4
  • smtp 8.x-1.0-alpha0
  • token 8.x-1.0-alpha2
  • weight 8.x-3.0
  • yoast_seo 8.x-1.0-alpha2+2-dev (2016-Mai-20)
Improve this question
3
  • 2
    As a 'quick' fix until you solve the real problem, you should setup a cron to clear cache at least each day at midnight! (I have no idea why it is so big..) – ssibal Jun 28 '16 at 9:04
  • There is no solution on that. Because the logic behind Drupal is complex. It will be quite slow without these static caches. – Jimmy Ko Jun 28 '16 at 11:16
  • See also drupal.stackexchange.com/questions/201663/… and drupal.org/node/2526150 – malcomio Dec 26 '16 at 10:18
1

It seems like this could be related to particular blocks or other renderable objects on your site that may have many permutations. In one case, I saw 100,000+ rows for one particular views exposed filter block.

Since users were able to select many different options, the block was stored hundreds of times per page/URL in the cache_render table.

It's best to try to scan through the cid values of the table and see any patterns, e.g. with SELECT COUNT(*) FROM cache_render WHERE cid LIKE "%interesting_cid_here%";, to see if there are any major offenders.

Still trying to figure out the best way (besides periodically running drush cr) to fix this issue. For now, I opened up an issue in the Drupal Core issue queue: cache_render table becomes extremely large over time

See discussion around this issue in this Drupal Core issue: Make CACHE_PERMANENT configurable in the database cache — currently allows unlimited growth: cache DB tables of gigabytes!.

Improve this answer
0

I have set the Cache maximum age to 1 hour and the cache_render page STILL sets the expiration date to infinity! Why?

This is a core issue. You should be able to control the expiration date.

In Internal Page Cache it says:

On the Performance page, you can configure how long browsers and proxies may cache pages; that setting is also respected by the Internal Page Cache module. There is no other configuration.

But this does not work.

You have to wait until the issue is resolved. Or you could try to set your own Expires date in a response event. If there is a date in the future, this will end up as expiration date in the cache table. See the code in this answer Anonymous user cache control.

Improve this answer
6
  • The cache max age does not apply to the internal page cache on purpose. That's for external (proxy, browser, .. caching). There are certain improvements happening (e.g. limit cache to a short time for 404 pages) but it remains a problem. – Berdir Jun 29 '16 at 22:55
  • 1
    @Berdir: I agree, the max-age from page-elements like blocks and nodes are not applied on purpose. But the setting on the Performance page is different. If you set a global limit for external caches it would make sense that this also applies to the internal page cache. In fact, looking from the site user point of view, it does not make any difference where the page gets cached, internal or external, if you get outdated information. – 4k4 Jun 30 '16 at 6:43
  • I opened an issue about the confusing documentation regarding Internal Page Cache and it's relationship with this max-age configuration setting: drupal.org/node/2862894 – Brian Mar 22 '17 at 13:38
  • @Brian, I've read the issue. A better solution might be to adjust the code, not the documentation. See the added link. – 4k4 Mar 22 '17 at 13:57
  • What added link? – Brian Mar 22 '17 at 14:59
0

To keep any of the cache bins (cache tables) from growing too big you can set the maximum number of rows they can have in settings.php

// max rows for cache_render table
$settings['database_cache_max_rows']['bins']['render'] = 50000;
// or any other bin
$settings['database_cache_max_rows']['bins']['dynamic_page_cache'] = 1000;

These settings take effect on cron run

To get appropriate values for each bin you can find out the current rows occupied using a drush sql-query

drush sql-query 'SELECT table_name AS "Table", TABLE_ROWS as "Num. of Rows", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"  FROM information_schema.TABLES where table_schema = "DATABASENAME" and table_name LIKE "cache_%"  ORDER BY (data_length + index_length) DESC limit 10'

of course change DATABASENAME on above query

For more info about these settings see the change record or read the documentation in source of core/core.api.php or follow the whole issue and discussion

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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