I am not sure if the following within TM, since it seems to me that TM has done its job once a menu is generated, but nevertheless:
I have taxonomy_menu enabled, as well as Entity cache.
This makes that every term is loaded on every page. This takes 1-20 msec per term. (See below list from Devel Query log.)

- Can this be avoided? Should every term be loaded?
- Can we make that the whole vocabulary is loaded before fetching every term? In that way we have 1 DB-call per TM-enabled vocabulary instead of 1 DB-call for every term.

1.57117DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_entity_taxonomy_term WHERE cid IN (:cids_0)
0.78117DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_entity_taxonomy_term WHERE cid IN (:cids_0)
0.68117DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_entity_taxonomy_term WHERE cid IN (:cids_0)
1.54117DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_entity_taxonomy_term WHERE cid IN (:cids_0)
1.03117DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_entity_taxonomy_term WHERE cid IN (:cids_0)
17.22117DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_entity_taxonomy_term WHERE cid IN (:cids_0)
10.58117DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_entity_taxonomy_term WHERE cid IN (:cids_0)
0.56117DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_entity_taxonomy_term WHERE cid IN (:cids_0)

Comments

hles’s picture

hles commented

That's something I've been wanting to change for a long time. I think this is much better but I have not considered yet all the eventual consequences related to the code.

johnv’s picture

johnv
Primary language Dutch
commented

have you already a rough idea how to acomplish that? We might come up with a plan, together.

hles’s picture

hles commented

I may have jumped the gun on this one. I didn't read carefully and I was talking about something else. At the moment, I don't see why the terms would be loaded on everypage. I mean once the menu is generated, terms are not loaded anymore, only menu links ? Maybe I didn't get it, if so tell me.

johnv’s picture

johnv
Primary language Dutch
commented

You are right. I blame this on core's includes/menu.inc .
After taking a closer look, the whole vocabulary is read term-by-term by function _menu_load_objects() in menu_build_tree().

In most cases, the menu can be generated without loading every term. Only in some features, loading all this is necessary (for instance when you want to show icons or images loaded in the term.)

Still, this might be a usecase for adding some indicator 'you are about to load a taxonomy_menu'.
Still investigating....

johnv’s picture

johnv
Primary language Dutch
commented
Title: Better performance by loading all terms at once. » Too many calls to _menu_load_objects(), when building a menu with an enabled taxonomy_menu
Category: feature » bug

I'll post a link to my patch, avoinding loading the terms unnecesarily.
These are the result with a taxonomy_menu of only 56 terms:
Pre: Executed 353 queries in 247.42 ms. Page execution time was 1022.89 ms. Memory used at: devel_boot()=1.08 MB, devel_shutdown()=15.11 MB, PHP peak=16.25 MB
Post: Executed 225 queries in 202.47 ms. . Page execution time was 935.32 ms. Memory used at: devel_boot()=1.08 MB, devel_shutdown()=14.61 MB, PHP peak=15.5 MB.

johnv’s picture

johnv
Primary language Dutch
commented

See #1978176: Build menu_tree without loading so many objects for my patch to a more efficient menu load.

johnv’s picture

johnv
Primary language Dutch
commented
Title: Too many calls to _menu_load_objects(), when building a menu with an enabled taxonomy_menu » Memory/Performance problems, when building a menu with a large taxonomy_menu

IMO this is the root cause for several other issues like:
#848018: Performance Issue
#990512: Introduce depth limit for very large and deep vocabularies
#1380422: Allowed memory size of X bytes exhausted (tried to allocate Y bytes)
They all suffer from timing/memory problems because every term in the menu is loaded.
"Too many calls to _menu_load_objects()"

The following issue tries to correct this in the Drupal core menu system:
#1978176: Build menu_tree without loading so many objects

sam hermans’s picture

sam hermans commented

Adding a screenshot of what you allready know by now... query count per page load :(

Jordan Samouh’s picture

Jordan Samouh commented

@johnv, I do not think that your patch brings a real solution for taxonomy menu

Each time you refresh your page with menu display, the menu_check_access is called and load taxonomies entities...

bramvandenbulcke’s picture

bramvandenbulcke commented

Ok, I remember inheriting a website with Taxonomy Menu that was super slow, like 10 seconds per page. I managed to fix that by using a cache warmer during the night.

Now I'm building a Drupal Commerce website with around 150 mostly nested taxonomy items. I needed a menu, so I used Taxonomy Menu. With a patch it's even multilingual. It's not perfect (I have some ghost terms in the menu in the database) but the functionality is great.

But the performance is, again, a real problem. And because I will have authenticated users this time, a cache warmer won't be a solution.

I read through all the threads on d.org about "Taxonomy Menu" and "performance" and have some options:

  • Disable the Taxonomy Menu option "synchronise changes to this vocabulary": https://www.drupal.org/node/2393173#comment-9435121. This option isn't mentioned in this thread and I'm not certain this will give a performance boost.
  • Patch menu.inc in Drupal core: https://www.drupal.org/node/1978176#comment-11094363. Patching core! That will be my last option, if all other options fail.
  • Ask my hosting company for some caching. Tips are welcome here.
  • Create the menu manually. The menu will be quiet static, so I don't bother. But then I have to know that the reason for the bad performance is Taxonomy Menu and not the menu system in Drupal.

I would like to receive some feedback. I can do lots of testing but some guidance would be nice.

johnv’s picture

johnv
Primary language Dutch
commented

@Bram, patching core is the only way to improve core. (Hacking core and not publishing the patch is not.)

bramvandenbulcke’s picture

bramvandenbulcke commented

@johnv. The first option I will try is the hosting option. I hope that option will be sufficient!

gaëlg’s picture

gaëlg
he/him
Primary language French
Location Lille, France
commented

I got this problem on an old website I had to maintain. One way to mitigate the problem is to load all the terms at once, so that they get in cache before the menu is computed. This is not ideal because terms are still unnecessarily loaded, but it's very faster because they get all loaded with one SQL query instead of one per term. In my case the execution time got divided per 4 or 5.

/**
 * Implements hook_init().
 */
function my_module_init() {
  taxonomy_term_load_multiple(array(), array('vid' => array('42')));
}
vladimiraus’s picture

vladimiraus
Primary language English
Location Brisbane 🇦🇺
commented
Status: Active » Closed (outdated)

Thank you for your contributions.
Drupal 7 is no longer supported.
Closing this issue as outdated.

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.