Meta: 📈 Performance of PageTreeView

[ulliholtgrave]

Motivation

Currently, TreeView loading has some massive perforamce issues when we work with live data. For example, loading the pages of the city of Munich takes almost 2 minutes. This needs to improve significantly before the release.

• Add Redis-Cache support for production setup #942
• Meta: 📈 Performance #943
• Add cache for calculated values #944
• Refactoring of the page hierachy #945
• Optional: move languages from model into static objects

[timobrembeck]

The same problem probably also impacts the translation report (which takes even longer to load than the page tree)

[svenseeberg]

The culprit seem to be many small queries that are executed each time the filter() method is being used. We're doing this quite often in model methods, especially those with the @property decorator.

https://docs.djangoproject.com/en/3.2/topics/db/optimization/ provides some insights into how Django internally optimizes database access.

The following things can be done to improve performance:

• Simply reduce the amount of lookups. Maybe we do not need all info that is being pulled in in some cases?
• Improve access to required data -> less foreign key lookups.
• Add Memcached layer.

We should start with the PageTranslation and AbstractBasePageTranslation models, and then continue with Page and AbstractBasePage. Next, we should search filter() calls outside of models, then move them into the models and optimize.

[svenseeberg]

I enabled the Postgresql query log and collected the queries executed during one API request on the pages endpoint. Each SQL query is one line in the log file.

cat request-filtered.log | wc -l
5612

grep '"cms_page"' request-filtered.log | wc -l
5028

grep '"cms_pagetranslation"' request-filtered.log | wc -l
4299

grep mediafile request-filtered.log | wc -l 
2785

The result contains 68 page translations. That means we execute about 100 SQL queries for each page returned via the API.

[ulliholtgrave]

As a kinda dirty approach for reducing the database calls we implemented one example for the abstract_base_page.py. We added an additional value and then changed the get_translation() method to the following:

cached_translations = None
def get_translation(self, language_slug):
translations = self.get_cached_translation()
return next(
filter(
lambda translation: translation.language.slug == language_slug,
list(translations),
),
None,
)
def get_cached_translation(self):
if not self.cached_translations:
self.cached_translations = self.translations.all().select_related(
"language"
)
return self.cached_translations

This removes the filter() function from the property and saves up a lot of database connections.

[timobrembeck]

Since after merging #1149 the performance of the page tree is very good even in regions with many pages, I'm closing this issue now. 🎉