A time to consider how to improve the efficiency of building the menu nodes

[marksweb]

It's been raised on discord that the _build_nodes process from the menu system can cause problems for projects with many pages/languages.

To save people looking to discord for the details;

We have migrated to 4.1 and have a large number of pages (18k) across 17 languages. I have noticed an issue when starting up the server.
The admin pages are available instantly. But there is a huge lag (hour +) when viewing a page in a language for the first time. Once the page is eventually rendered all subsequent pages that havent been loaded before are fine (no lag).
If I was then to manually adjust the url of a pages that has been rendered there is a similar length lag. Again once a language has been loaded for the first time all other pages in that language are fine.
I set the logging to the maximum level and can see what is causing this in the console. When loading a page/language combo for the first time this query is fired:

 SELECT 
    cms_pagecontent.id, 
    cms_pagecontent.language, 
    cms_pagecontent.title, 
    cms_pagecontent.page_title, 
    cms_pagecontent.menu_title, 
    cms_pagecontent.meta_description, 
    cms_pagecontent.redirect, 
    cms_pagecontent.page_id, 
    cms_pagecontent.creation_date, 
    cms_pagecontent.created_by, 
    cms_pagecontent.changed_by, 
    cms_pagecontent.changed_date, 
    cms_pagecontent.in_navigation, 
    cms_pagecontent.soft_root, 
    cms_pagecontent.template, 
    cms_pagecontent.limit_visibility_in_menu, 
    cms_pagecontent.xframe_options, 
    cms_page.id, 
    cms_page.created_by, 
    cms_page.changed_by, 
    cms_page.creation_date, 
    cms_page.changed_date, 
    cms_page.reverse_id, 
    cms_page.navigation_extenders, 
    cms_page.login_required, 
    cms_page.is_home, 
    cms_page.application_urls, 
    cms_page.application_namespace, 
    cms_page.languages, 
    cms_page.is_page_type, 
    cms_page.node_id, 
    cms_treenode.id, 
    cms_treenode.path, 
    cms_treenode.depth, 
    cms_treenode.numchild, 
    cms_treenode.parent_id, 
    cms_treenode.site_id 
FROM 
    cms_pagecontent 
    INNER JOIN cms_page ON (cms_pagecontent.page_id = cms_page.id) 
    INNER JOIN djangocms_versioning_version ON (cms_pagecontent.id = djangocms_versioning_version.object_id AND (djangocms_versioning_version.content_type_id = 10)) 
    INNER JOIN cms_treenode ON (cms_page.node_id = cms_treenode.id) 
WHERE 
    (cms_pagecontent.language = 'en' AND cms_pagecontent.page_id IN (SELECT U0.id FROM cms_page U0 INNER JOIN cms_treenode U1 ON (U0.node_id = U1.id) WHERE U1.site_id = 1) AND djangocms_versioning_version.state IN ('published')) ORDER BY cms_treenode.path ASC, djangocms_versioning_version.state ASC;

In our case this brings back 18k rows. This query is then run for each row returned:

(0.047) SELECT "cms_pageurl"."id", "cms_pageurl"."slug", "cms_pageurl"."path", "cms_pageurl"."language", "cms_pageurl"."page_id", "cms_pageurl"."managed" FROM "cms_pageurl" WHERE ("cms_pageurl"."page_id" = 4810 AND "cms_pageurl"."language" IN ('en', 'ar', 'de', 'es', 'fr', 'he', 'hi', 'it', 'ja', 'ko', 'nl', 'pl', 'pt', 'ru', 'tr', 'zh-hans', 'zh-hant')); args=(4810, 'en', 'ar', 'de', 'es', 'fr', 'he', 'hi', 'it', 'ja', 'ko', 'nl', 'pl', 'pt', 'ru', 'tr', 'zh-hans', 'zh-hant');

Only after all 18k queries have run does the page then render for the first time. As mentioned - once a language has loaded once it is fine. I guess becuase it is not running this mamouth query each time.
Is this loading every possible page for each language a configurable option that can be switched off because the volume of pages will only increase and therefore the lag to view a page after starting the server will only continue to expand and get longer.

The code causing this is here and it know's that it's slow/problematic as the doc string starts...

    def _build_nodes(self):
        """
        This is slow. Caching must be used.
        One menu is built per language and per site.

This code has existed as it is since the beginning of the project, coming from 2010

AI suggestion

With such old code we've no experts in this area, so I've taken some input from Jetbrains AI;

The _build_nodes function is composed of several steps, each with potential for optimization. Here are some ideas:

1. Caching Improvements: You already are using cache to store the final_nodes result, which is an excellent strategy. Consider using a more fine-grained caching strategy. For example, you could cache individual menu.get_nodes(self.request) results, as these calls might also be expensive.
2. Parallelize get_nodes Calls: If the get_nodes function calls are independent of each other and can be run concurrently, consider using multithreading to run them in parallel. This would greatly improve performance when dealing with a large number of menus.

from concurrent.futures import ThreadPoolExecutor

with ThreadPoolExecutor() as executor:
    future_to_menu = {executor.submit(menu.get_nodes, self.request): menu_class_name for menu_class_name in self.menus}
    for future in concurrent.futures.as_completed(future_to_menu):
        menu_class_name = future_to_menu[future]
        try:
            nodes = future.result()
        except NoReverseMatch:
    # rest of your code...

3. Optimize _build_nodes_inner_for_one_menu Call: As this function is called inside the for-loop for self.menus, its performance directly impacts the speed of _build_nodes. If there are ways to make _build_nodes_inner_for_one_menu faster, the entire function will be faster as well.
4. Avoid Unnecessary Operations: The CacheKey.objects.create() operation happens every time the function is called, even if the cache was hit and returned early. Consider moving it up under the condition if cached_nodes and self.is_cached: as an else clause. This avoids hitting the database needlessly when the cache is hit.

Before implementing these improvements, make sure to profile your code to identify where the bottlenecks really are. Just because a function is slow, doesn't mean the problem is in that function itself; it may be that it's calling another, super-slow function, or the slowness could be due to I/O, network latency, a slow database query, etc. Use a tool like cProfile for profiling Python code.
Also, always test performance improvements to ensure they're actually having the desired impact. It's common to think something will make code faster, but actually it makes it slower (or has no impact). Use a tool like timeit to measure execution time.
And remember, the key to speeding up a slow function is to find the slowest part of that function and make it faster. No amount of optimizing the fast parts of your function will make up for a small part of it that's super slow.

[marksweb]

Point 4 is an interesting one. And is a part of the system that I've never really liked - many years ago the presence of cache keys in the database caused me problems in a load test due to duplicate keys.

The database is being his for cache related matters when _build_nodes does if cached_nodes and self.is_cached: - is_cached does a database filter. Then if that doesn't return a result from the database, we do a CacheKey.objects.create()

This is creating keys in the format key = f"{prefix}menu_nodes_{self.request_language}_{self.site.pk}" so there's not a lot of data in play, just strings.

It'd be worth looking to see if we can collect all these cache key objects together and bulk create them.

[benzkji]

The root of everything IMHO is: A query is executed 18k times, because there exist 18k pages. No additional caching, multithreading, or whatever else optimization can help against that? Not at real expert as well, but maybe joining the pageurl table as well, and afterwards operate on the obviously larger queryset might be an option? Just thinking loud...

[marksweb]

@benzkji Yes, I haven't got as far as finding the code that actually gets the pages yet. I imagine there will be more to think about when I get to that point.

[Aiden-RC]

Just a comment here, this wasn't an issue for us with the same amount of data ( if not more ) on v3, it's only since v4 that we've had this issue, i know djangocms-versioning has it's own CMSMenu class, theres a potential this issue is caused here?

[benzkji]

@fsbraun might tell you more. There were efforts to "standardize" menu code, so versioning, non-versioning, and possibly alternate versioning would use the same code.

[fsbraun]

I was just looking at this, and to me, it seems that both the cms_menus in the core has and versioning misses a prefetch for the PageURL model.

@Aiden-RC You could check what happens to performance if you set DJANGOCMS_VERSIONING_ENABLE_MENU_REGISTRATION to False.

The quick fix might be looking at the queryset here:

https://github.com/django-cms/djangocms-versioning/blob/8f5c0aef531dec37d191ff2642e95db46815fa53/djangocms_versioning/cms_menus.py#L105-L112

It seems to me that there's a select related missing for page__urls.