Managing Large Cache Render Tables in Drupal

Issue

On Drupal sites, the cache_render table and overall database rapidly increase in size and fill the disk space.

Cause

Drupal uses a robust and thorough caching API. Render caching is enabled by default for elements such as pages, entities, blocks, and views. Items in this cache do not expire

Resolution

Upgrade to the latest version of Drupal core. If an upgrade is not possible, apply one of the following mitigation options

Mitigation options

If an upgrade to the latest version of Drupal core is not possible, or if the cache_render table remains large, you must manage its size manually through one of the following methods:

• Occasionally truncate the cache_render table
• Selectively delete items from the cache_render table based on created date

Truncate the cache_render table

To run the command from a MySQL command prompt, enter the following command:

truncate table cache_render;

Selectively delete items from the cache_render table based on created date

From a MySQL prompt, run:

SELECT COUNT(expire) as 'EXPIRE' from cache_render WHERE created < 'TIMESTAMP';

 Replace TIMESTAMP with the date in Unix Epoch time. Select one of the following options to obtain the date for "yesterday" in Unix Epoch time:

date -v -1d +'%s'
expr `date +'%s'` - 86400

To determine the number of involved rows, run the following command from a MySQL prompt:

SELECT COUNT(expire) as 'EXPIRE' FROM cache_render WHERE created < '1491593775';
+---------------+
| count(expire) |
+---------------+
|         27022 |
+---------------+

To modify the timestamp or delete the rows, use the following command to delete all rows from the cache_render table created before a specific date:

DELETE FROM cache_render WHERE created < 'TIMESTAMP';