Database cache bins are now fixed size — no more unlimited growth

To keep Drupal simple to install, with simple system requirements, Drupal uses the database not only for storing content, but also for caching.

A database is not designed for caching though. A cache is by definition (see https://en.wikipedia.org/wiki/Cache_(computing)) a fixed-size storage. When it's full, data is evicted (there are many ways to choose which cache items should be evicted).

However, Drupal's database-based caching has traditionally not imposed any limitations. In Drupal 7, many actions cleared all caches (thus making caching far less effective). And many things were uncacheable due to lacking infrastructure. Those flaws have fortunately been fixed in Drupal 8, but now there's another consequence: cache bins in the database can grow to excessive sizes — many tens of thousands of entries (even hundreds of thousands), and gigabytes in size!

To curtail this, we introduced limitations. During cron runs, stale cache items are already purged. But as of now, we also limit the number of cache items in each database cache bin table to at most 5,000 rows by default.

You can override this default in settings.php, for example to 50,000:

$settings['database_cache_max_rows']['default'] = 50000;

And you can even override it for a particular cache bin in settings.php, for example to 500:

$settings['database_cache_max_rows']['bins']['config'] = 500;

To see the current size of cache tables, you can use a query like the following:

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 = '***DATABASE_NAME***' and table_name LIKE 'cache_%'  ORDER BY (data_length + index_length) DESC limit 10;

Note: Replace ***DATABASE_NAME*** with your actual database name, for example drupal9.

#2896473: Add monitoring/reporting of cache table pruning might add reporting about that directly into the UI from Drupal in the future.