Cache clear problems

chrism2671's picture

I'm trying to track down an issue with our installation where when the cache is cleared, there is a spike in MySQL queries/connections (around about 170 concurrent), which leaves the site inaccessible for about 5 minutes.

If I take a snapshot of the site and do this on my laptop, I have the same problem (max CPU usage until the processlist is cleared out).

This means that enabling a module means 5 minutes of downtime.

On our server we run memcache and x-cache. It is quite a powerful VM, usually with a load average below 1 (~50k impressions a day), but this rockets to over 20 when the above mentioned problem occurs.

Curiously, my laptop exhibits the same behaviour when caching is turned off. It does not have memcache/opcode cache.

Running PHP5, MySQL 5, X-Cache and Memcache.

I think the problem might be related to table locking, but can't be certain!

Any thoughts much appreciated!

Comments

I would run the devel module

Jamie Holly's picture

I would run the devel module and enable query output and then see what queries are running exceptionally long time when you dump the cache. You could do this on your laptop so you aren't screwing with the production site.


HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.

Hi, You need to understand

Shyamala's picture

Hi,

You need to understand the Queries that executed to rebuild your Cache.

1) A good point to start would be to log slow queries in Mysql and start tuning them.
2) Understand the complex queries runing on your pages using Devel

Since there is a redirect

dalin's picture

Since there is a redirect involved when you submit a form, it might help to enable the feature in devel where it pauses before that redirect so you can see the queries that happened during form submit.

--
Dave Hansen-Lange
Technical Manager
Advomatic LLC
Great White North Office
Canada

I ran the devel module and

chrism2671's picture

I ran the devel module and checked the queries. It reports there are over 11,000 queries involved in a cache clear. I think this is the problem. The vast majority seem to be related to the menu system- that is, menu_router etc. I converted these tables to innoDB but this didn't seem to speed it up.

I've pasted the devel output here:

http://drupal.pastebin.com/m5f903e71

Apologies it's a little crummy rendering, it didn't paste very well!

Hmmmm this kind of smells

Jamie Holly's picture

Hmmmm this kind of smells like a module doing some bad stuff in there. I run a site with about 70 modules and a pretty big custom menu and dumping the cache on there still we only get 600 queries.

One thing you will see which makes it look like a lot of menu system queries is this:

module_rebuild_cache

That's if you are on the module page. Drupal reads and writes/updates a row in the system table for every module, enabled or disabled, so you will have a call to that routine for every module. That way if you upgrade a module the changes are seen in the module list without having to dump the cache.

The least pain staking way to track these problems down I have found (though a little time consuming given your page load times) is to start disabling modules one by one, or if you have a ton of modules you can disable them 5-10 at a time and use the process of elimination, but the goal is to see when you get a rather big performance boost. That way you know which module to look into.


HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.

Holy crap 11,000 would indeed

dalin's picture

Holy crap 11,000 would indeed be the problem. But the pastebin shows 218 queries which sounds pretty reasonable to me. If you've got PHP debugging setup on your local machine you should be able to find the offender fairly quick. If not you may need to hack at db_query with some backtrace statements to see what is calling these queries.

--
Dave Hansen-Lange
Technical Manager
Advomatic LLC
Great White North Office
Canada

Ah yes, just realised I

chrism2671's picture

Ah yes, just realised I pasted the wrong result! I will do some debugging and report back!

Thanks for all the help, much appreciated!

There is a known problem in

moshe weitzman's picture

There is a known problem in Drupal6 with menu_rebuild(). That runs when you change module status. See http://drupal.org/node/251792

Bulk Insert

mikeytown2's picture

Looking at http://api.drupal.org/api/function/_menu_router_build/6 I think this could benefit from a bulk insert script that I wrote for boost; We can use it to make the period of instability to be very small. Here is the entry function along with the utility function.

<?php
/**
* Get URLs from url alias table
<em>
*/
function boost_crawler_add_alias_to_table() {
 
// Insert batch of html URL's into boost_crawler table
 
global $base_url;
  if (!
variable_get('boost_crawl_url_alias', FALSE)) {
    return
TRUE;
  }
 
$count = BOOST_CRAWL_DB_IMPORT_SIZE;
 
$chunks = 0;
 
$loop_counter = 0;

 
// Get maximum packet size for mysql
 
$max_packet = db_result(db_query("SHOW VARIABLES WHERE Variable_name = 'max_allowed_packet'"));
 
$max_packet = $max_packet > 512 ? $max_packet : 524288;
 
$max_chunk = $max_packet/512;

 
$total = db_result(db_query("SELECT COUNT(</em>) FROM {url_alias}"));
 
$loaded = variable_get('boost_crawler_loaded_count_alias', 0);
  if (
$total > $loaded) {
   
$list = db_query_range("SELECT dst FROM {url_alias}", $loaded, $count);
   
$data = array();
    while (
$url = db_result($list)) {
     
$url = $base_url . '/' . $url;
     
$md5 = md5($url);
     
$data[$chunks][] = $url;
     
$data[$chunks][] = $md5;
     
$loop_counter++;
      if (
$loop_counter > $max_chunk) {
       
$chunks++;
       
$loop_counter = 0;
      }
    }
    foreach (
$data as $values) {
     
boost_db_insert_multi('boost_crawler', array('url', 'hash'), array("'%s'", "'%s'"), $values, FALSE);
    }
   
variable_set('boost_crawler_loaded_count_alias', $loaded + $count);
    return
FALSE;
  }
  else {
    return
TRUE;
  }
}
?>

<?php
/**
* Insert many records into the database.
*
* NOTE Be aware of the servers max_packet_size variable.
*
* @param $table
*   The name of the table.
* @param $fields
*   array of field names to be updated
* @param $placeholders
*   array of db_query placeholders; like %d or '%s'
* @param $values
*   array of values you wish to be inserted. If you have 3 fields then the
*   array should be structured like
*    array($field_1_value_A, $field_2_value_A, $field_3_value_A,
*           $field_1_value_B, $field_2_value_B, $field_3_value_B);
* @param $suppress
*   bool. TRUE to supress db_query errors
* @return
*   returns db_query() result.
*/
function boost_db_insert_multi($table, $fields, $placeholders, $data, $suppress = FALSE) {
 
// Get the number of rows that will be inserted
 
$rows = count($data)/count($fields);
 
// Build the values placeholders string.
 
$values = '(' . implode(', ', $placeholders) . ')';
 
// Create what goes in the IN ()
 
$v = $values;
 
// Add the rest of the place holders
 
for ($i = 1; $i < $rows; $i++) {
   
$v .= ', ' . $values;
  }
 
// Build the fields part of this query
 
$fields = implode(', ', $fields);
 
$query = "INSERT INTO {$table} ($fields) VALUES $v";
 
// Run the query
 
if ($suppress) {
    return @
db_query($query, $data);
  }
  else {
    return
db_query($query, $data);
  }
}
?>

Actually I have been

chrism2671's picture

Actually I have been considering installing Boost as I thought it might alleviate this problem. We used Boost extremely successfully for an extended period of time but abandoned it due some slightly weird caching problems (I reported some here http://drupal.org/node/305071 and have swapped it out for memcache and x-cache, which provides 'adequate' performance. I would swap back to boost in an instant if there was a good reason to though.

Apples to Oranges to Pears

dalin's picture

Boost, Memcache and x-cache can all co-exist on the same site because they all do different things.

--
Dave Hansen-Lange
Technical Manager
Advomatic LLC
Great White North Office
Canada

High performance

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds:

Hot content this week