search

backdrop / backdrop-issues

Issue tracker for Backdrop core.
144 stars 40 forks source link

Consider exposing `form_cache_expiration` and/or providing an option so that it can be cleared along with other caches. #3191

Open klonos opened 6 years ago

klonos commented 6 years ago

I was recently asked to investigate a warning about low disk space because of the database growing. The culprit turned out to be the cache_form table, which apparently had reached close to a couple of GBs in size. Turns out that this is a known/common problem:

cache_form is never cleared on some sites (D7 core issue, reported in March 2012, still open at the time of writing)

https://drupal.stackexchange.com/questions/69803/cache-form-table-size-is-enormous

The cache_form table is a little funny, and behaves in a slightly different manner than other cache tables.

If you take a look at drupal_flush_all_caches() you will see that cache_form isn't cleared. This is to protect in-progress forms from being nuked.

The system_cron() function does take care of pruning out old data from cache_form along with the other cache tables.

You really should run cron on all Drupal sites...

In my case, cron was running just fine. The cache_form table was simply not being cleared, and it had reached 9000+ rows. It was just spambots 😞

While troubleshooting, I had to "manually" fix things by truncating the cache_form table (ssh -> mysql commands and queries etc.). This seems to be a far too common problem for us to ignore. We should be providing the common Joe with the tools to perform cleanup via the UI.

Here's a few thoughts/suggestions:

Here's a D7 contrib module that does most of these things: https://www.drupal.org/project/optimizedb

There is well-known issue with fast-growing table cache_form. This table stores Drupal form cache and it isn’t cleared when you flush all cache or run cron. Also you can struggle with problem of fragmentation with InnoDB tables on active delete/insert operations. This module provide functionality to solve this problem. Also its provide additional tools for maintenance database.

...this module seems to have originated by https://www.drupal.org/project/db_cache_clear

The project is originated by issues #2382227: Add option to clear cache_form expired records and #2409377: Add option to clear all cache tables using 1 button and by cron

Update: Module OptimizeDB should solve the above issues.

Perhaps related:

klonos commented 6 years ago

https://api.drupal.org/api/drupal/includes%21form.inc/function/form_set_cache/7.x

function form_set_cache($form_build_id, $form, $form_state) {

  // The default cache_form expiration is 6 hours. On busy sites, the cache_form
  // table can become very large. A shorter cache lifetime can help to keep the
  // table's size under control.
  $expire = variable_get('form_cache_expiration', 21600);

...

https://api.backdropcms.org/api/backdrop/core%21includes%21form.inc/function/form_set_cache/1

function form_set_cache($form_build_id, $form, $form_state) {
  // 6 hours cache life time for forms should be plenty.
  $expire = 21600;

...

Why do we have the 6.x version of the comments again?

...and it seems that we do not have any config property to replace the 'form_cache_expiration' variable that the respective D7 code has in place. Am I missing something?

klonos commented 6 years ago

So, the cache expiration is hard-coded to 6 hours. Should we expose this setting somewhere in the UI?

klonos commented 6 years ago

...the d.org issue has been set to "major", but no solution over so many years (understandable; it means that it is a hard nut to crack), if we cannot figure out a solution sooner, then I think that at the very least we should provide workarounds and ease admins lives. Averagely tech-savvy people should not have to go through things like this:

Had to kill the mysqldump process and used myphpadmin to empty cache_form. This is a critical bug.

klonos commented 6 years ago

...another module mentioned in that core d.org issue:

Safe cache_form Clear

Drupal's core cron can have problems clearing out the cache_form table when it grows extremely large. This module works around this by limiting the number of items expired from the cache in a single run, dealing with a manageable chunk each time.

Sounds that we should be implementing some throttling/batching when cron attempts to clean up tables, since it seems to be chocking on big ones.

klonos commented 6 years ago

...which is backed by this comment in the d.org core issue:

FWIW I think implementing a different approach to cache_clear_all is worth investigation here.

The issue is that DELETE FROM cache_form WHERE expire <> 0 AND expire < :timestamp could hit MySQLs lock limit, depending on the configured value for innodb_buffer_pool_size and the size of the records.

See this post for more information.

So a fix would require deleting the expired rows in chunks, instead of in one pass.

klonos commented 6 years ago

...comment by David_Rothstein:

@larowlan's comment in #46 seems like a more likely explanation for the problems most people here are experiencing. If so, it would be good to get a patch for that. @webservant316's comment in another issue pointed to the existence of https://www.drupal.org/project/safe_cache_form_clear (edit: and other people in this issue linked to it above also), and perhaps the code in that module could be used as inspiration for a core patch.

jenlampton commented 3 years ago

I just ran into this too, and I have opened a port request for the https://www.drupal.org/project/optimizedb module.

I've also added links to the possibly related issues: