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?
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.
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 '14 at 13:59
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 '14 at 14:00
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
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.
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 '14 at 14:07
You can try to use drush like drush -y cc all command, or TRUNCATE/DELETE the tables manually by the following one-liner:
drush sqlq --extra=--skip-column-names "SHOW TABLES LIKE 'cache%'" | xargs -L1 -I% echo "DELETE FROM %;" | drush sqlc --extra=-v
