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
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
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
Shyamala
Unimity Solutions
Since there is a redirect
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
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
I am trying this thing now in
I am trying this thing now in 2015 on drupal 7 and I get the page before redirect when using CC, but no query log :(
Drupal Specialist at http://adriadrop.com/
I ran the devel module and
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
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
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
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Ah yes, just realised I
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
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
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
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
Boost, Memcache and x-cache can all co-exist on the same site because they all do different things.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his