Performance Profiling Recommendations

We've seen sites that have bottlenecks on taxonomy/term/% pages, but not as bad as what you have there.

This is a Drupal 6.x site, where the node table has 131,160 rows, term_data 25,609, and term_node has 250,055 rows. This relatively large data set for taxonomy rows is due to using free tagging terms.

Note: in Drupal 7.x the table names are different (taxonomy_term_data and taxonomy_index respectively).

This is not using views, just core Drupal, so it is different.

The slow queries are:

Which takes 1.05 seconds.

Which takes 0.8 seconds. Both adding up to 2 seconds per page load.

We found that Google's crawler aggravates the issue by adding arguments like: "page=" or "from=" and that makes the URLs not unique, and therefore not cached.

Part of why Google was so aggressive was implementing a CDN. Once you do that, Google disables your ability to slow down the crawl rate in Webmaster.

We did not want to refresh the pages too often, so we added to Varnish a custom.vcl file with this in it:

For your situation, I recommend you continue to use Xhprof, and look into why the number of calls are so high. 315K for is_array() is just too much.

You need to look into where these calls are coming from.

Perhaps it is in the theme layer when the view is being rendered or something.

Or maybe you did enabled views caching for query result only and not rendered output?

Or you are doing user_load() or node_load() or other table lookups in the views* tpl.php for each field?

Thanks for the insight @kbahey

Follow up to the first reply...

In views/includes/base.inc
views::unpack_options() first thing in the function is a call to is_array()

For stats we have:
7K+ product nodes
23K product variants
1.5K taxonomy terms
16K url aliases

X-Debug+cachegrind shows that the lion share of the calling of that function is coming from
views/includes/base.inc
views/includes/handlers.inc
views_handler::init() -> unpack_options()

On further inspection of cachegrind, the handler that is calling all of that, is coming from (shortened)
drupal_get_breadcrumb()->menu_get_active_trail()->menu_build_tree()

Which that shouldn't be called again, because there is a cache->get(). So I'm going to look into why the cache is failing to be saved/returned. I'll report back tomorrow.

So 60% of the time the process is being wasted in rebuilding the menu and underlying function calls. Mostly in

menu.inc
menu_tree_check_access()
_menu_tree_check_access()
_menu_link_translate()

So this patch improves successive page loads once the menus are cached for that user.

@@ -1351,11 +1351,20 @@ function menu_tree_page_data($menu_name, $max_depth = NULL, $only_active_trail =
* A fully built menu tree.
*/
function menu_build_tree($menu_name, array $parameters = array()) {
- // Build the menu tree.
- $data = _menu_build_tree($menu_name, $parameters);
- // Check access for the current user to each item in the tree.
- menu_tree_check_access($data['tree'], $data['node_links']);
- return $data['tree'];
+ // Per user menu caching
+ global $user;
+ $cid = 'menu_build_tree='.$menu_name.':user='.$user->uid;
+ $tree_cache = cache_get($cid,'cache');
+ if($tree_cache){
+ return $tree_cache->data;
+ } else {
+ // Build the menu tree.
+ $data = _menu_build_tree($menu_name, $parameters);
+ // Check access for the current user to each item in the tree.
+ menu_tree_check_access($data['tree'], $data['node_links']);
+ cache_set($cid,$data['tree'],'cache',CACHE_PERMANENT);
+ return $data['tree'];
+ }
}