Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

We have a busy database server and when we try to clear the cache on Drupal, Drupal tries to TRUNCATE cache tables and that causes a backlog until the MySQL hits to maximum number of user connections (which is 800). This is causing problems for us.

As far as we can tell, the problem is being caused by the locks during the table truncation.

Are there any alternative methods to clear cache that can help avoid this situation?

share|improve this question
    
Sorry, but truncate is probably the fastest way to purge large amounts of data from MySQL tables. –  Mołot Feb 12 at 13:27
5  
The problem probably isn't the clearing of the caches, but the rebuilding all of them on the next page load. –  MPD Feb 12 at 13:33
    
Is this for Drupal 6 or 7? –  mikeytown2 Feb 12 at 18:53
add comment

2 Answers

While your cache bins are in MySQL, truncating tables is by far and away the quickest method to clear the cache. It's not a particularly expensive operation, so if it's backing up your db connections I would get your sysadmin to investigate what config issues might be causing that (or whether it's simply a lack of sufficient resources).

As MPD notes in the comments above, your real problem might actually be the cache rebuild after the flush has happened, though. You should be able to confirm that after a bit of testing.

You can take the pressure off the database by using redis/memcache/etc as a cache backend instead of the database. Doing that will also make considerable speed improvements to your site in general.

Your mileage will vary, of course, but it's very likely that flushing cache from memory will be a quicker operation than flushing it from the disk. As Mołot notes below, writing the new cache items to memory rather than disk will also give you a performance boost (there will still be reads and writes to MySQL, but far fewer writes in theory).

As with anything, it needs testing on the bare metal to see how any of this would affect your specific configuration.

share|improve this answer
1  
And rebuilding it to memory will be lighter than rebuilding it to database. –  Mołot Feb 12 at 13:36
    
While truncating continue, lots of query appears for fetching and updating the cache tables. Since the table is locked, those queries are waiting. That's the main problem actually. Truncating is also slower than usual because the data sits on a SAN storage, so I need a solution that doesn't lock the tables while emptying them. Can I basically delete the content of the cache tables? It doesn't matter if it'll take longer as long as the tables are not locked. –  Tim Feb 12 at 13:55
    
Sure, the end goal is to get rid of all expired data in the cache tables - Drupal isn't particularly fussed how that happens. But a core cache flush will always try to be as efficient as possible, for obvious reasons, and in the MySQL world that's usually a TRUNCATE. BTW a TRUNACTE in 5.0.3 or greater actually drops the table before re-creating it, which might help to explain where the lock is coming from. –  Clive Feb 12 at 13:59
    
To make it happen you'll probably need to write a class that inherits from DrupalDatabaseCache and overriders the clear() method to use your preferred method. Then you can use settings.php to replace the default cache class with your new one –  Clive Feb 12 at 14:00
add comment

If you are using drush cc all or clearing cache from the Performance page (admin/config/development/performance), then you end up calling drupal_flush_all_caches(). This will

  • Rebuilt the registry
  • Clear CSS and JS caches
  • Rebuild theme info
  • Rebuild entity and field info
  • Rebuilt the menu router table
  • Rebuild the action list
  • Clear all of the cache tables

The actual clearing of the cache tables is rather quick, but the other operations are pretty expensive. In addition, on the next page load (which can be when you get redirected back to the Performance page), you will rebuild all of the cache tables.

When I have seen cache clear problems, the root cause has normally been a misconfigured LAMP stack. Calculating out the max number of Apache processes to prevent system overload can help, as can having MySQL provisioned properly for buffers, max connections, etc.

In addition, in a live environment, you don't really need to rebuild everything from a drupal_flush_all_caches(); you usually just want to clear the cache tables themselves. In this case, I use a variant of this:

/**
 * Implements hook_form_FORM_ID_alter().
 */
function MYMODULE_form_system_performance_settings_alter(&$form, $form_state) {
  $form['clear_cache']['clear_forward'] = array(
    '#type' => 'submit',
    '#value' => t('Clear all forward facing caches'),
    '#submit' => array('MYMODULE_clear_forward_submit'),
  );
}

function MYMODULE_clear_forward_submit($form, &$form_state) {
  MYMODULE_clear_forward_caches();
}

function MYMODULE_clear_forward_caches() {
  $cache_tables = array(
    'cache',
    'cache_block',
    'cache_filter',
    'cache_page',
    'cache_views',
  );

  foreach ($cache_tables as $table) {
    cache_clear_all('*', $table, TRUE);
  }

  drupal_set_message(t('Cleared forward facing caches.'));
}

This will add a new submit button to the Performance page which will just clear the caches that affect the forward facing portion of your website (ie, main pages). I wouldn't use this as is; you need to adjust the table list to match your environment. Once of these days I will make this more generic and release it as a public module.

share|improve this answer
1  
From the OP's comments it looks like the problem is actually table-level locking, so anything that uses DrupalDatabaseCache::clear() (i.e. cache_clear_all('*')) is going to have the same issue. Agree that reducing the number of affected tables will help, but it might not help enough –  Clive Feb 12 at 14:07
1  
@Clive Yeah, I wrote that up while those comments were rolling in. Interesting problem. –  MPD Feb 12 at 14:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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