Keeping the cache_form table to a reasonable size

Issue

The cache_form table on high-traffic Drupal 7 sites can sometimes grow extremely large, because the expired entries are not pruned aggressively enough, which normally occurs when Drupal cron runs.

Resolution

The safe_cache_form_clear module is designed to keep this situation from occurring, or to help reduce the table size if it has grown very large. The module provides a Drush command that examines a batch of 1000 entries and deletes expired entries. By default, expiration is 6 hours after the time an entry was written.

Note

All the module does, at present, is provide a Drush command. It is necessary to set up a schedule cron job that calls the command periodically to maintain a reduced table size.

First, install the safe_cache_form_clear module. Then run the Drush command below one time and note how long it took - this will determine the frequency of the scheduled cron job. In the example below, replace [sitename] with your application's site name and [env] is typically prod (Production) or test (Staging) or dev (Development).

time drush -d @sitename.env -l www.example.com safe-cache-form-clear

If the Drush command completed in less than one minute, you can setup your scheduled cron job to run every minute using the following template below. Keep in mind that other cron jobs may need to run, so account for those as well. 

--any--  * * * * *    /usr/local/bin/drush -d -l http://www.example.com @examplesite.prod safe-cache-form-clear

If the Drush command took longer than one minute to execute or you need to account for other cron jobs, setup the cron to run less frequently. For example, the cron frequency string of */10 * * * * would run the command every 10 minutes.

It's possible to adjust the number of entries examined to less than 1000 with a command like:

drush -d @sitename.env -l www.example.com php-eval 'safe_cache_form_clear(80);'

Or, to permanently adjust the number of entries examine, add the following example to your codebase:

$conf['safe_cache_form_clear_limit']=80;

If the cache_form table has ever grown out-of-control in the past (which is probably why you are here) you may find that the disk space used by the table's .ibd file is not reclaimed and you are in the situation described in My database is taking up lots of space on disk, but contains very little data. How do I fix it?. In this case the mysql query you need to run is

OPTIMIZE TABLE cache_form;

How it works

When the Drush command is run it triggers two specific queries, one query to examine and another to delete when criteria are met. Here's a simple case of deleting two expired entries from a small cache_form table with MySQL:

SELECT cache_form.cid AS cid FROM cache_form cache_form WHERE  (expire <> '0') AND (expire < '1473972800') LIMIT 1000 OFFSET 0;

DELETE FROM cache_form WHERE (cid IN ('form_form-PvSKl0a1xHksoH6p9f_wa5FTzvXAgUqjq7ZfXZKiDg4', 'form_state_form-PvSKl0a1xHksoH6p9f_wa5FTzvXAgUqjq7ZfXZKiDg4'));

The first query looks through a number of entries in the cache_form table, and finds all of the expired entries (greater than 6 hours past the time they were created) for the current time, which was Wed, 21 Sep 2016 20:55:28 GMT when this example was generated. And the second query removes the expired entries.

Cause

In high-traffic Drupal 7 sites, the cache_form table can grow extremely large before it is pruned. Additionally, the prune initiated during regular cron run is sometimes insufficient to remove enough entries to keep the table from continuing to grow.

The Drush command provided by the safe_cache_form_clear module is safe because it only ever removes entries that are expired from the cache_form table. So, as long as the last command completes each time, it's advisable to run the command frequently.