OEP performance poor - page load time, parsing of internal files (oeo) & database queries (sqlalchemy2) has been slowing down recently

[jh-RLI]

Description of the issue

The OEP and its user-facing functions have never been as fast as you would expect from a modern website. Of course, we mainly work within the framework of our research projects, but we also want to build a well-known infrastructure and be open to more users. This issue is about performance in general. There are several issues that raise more specific questions. I will link to these here in the future.

My main argument as one of the main developers of this software is that we should at least keep our infrastructure up to date with reasonably recent software versions. That doesn't mean we have to update our server every week, but I don't think we've updated the infrastructure since 2016.

Which parts of the OEP are affected

• Recently, loading the website has become more and more of a noticeable problem:
  • The latency with which the OEP responds is quite inconsistent. The following 3 problems cause loading times between 20 seconds and 2-3 minutes. Whilst in some cases 10-20 seconds loading time may be reasonable, they should be reduced as much as possible as users are likely to be driven away from the OEP due to these notable shortcomings.
  • Several users reported that the "Ontology" pages are slow to load (if the page is not cached).
  • The "Database" section is also slow. Once a user clicks on any of the topics (like "model_draft" or "scenario") that contain more than ~50 records. (this also only applies if the page is not cached on the client)
  • Loading tables that include more then 100 mio. rows of data is also slow (arount 1-3 min. ) . But this is also related to the related SQL query and its technical limitations.
• Also, uploading data to the oep is one of the main use cases and works well from a technical point of view, but if the user wants to upload more than a few MB of data, this task becomes very time consuming. Recently, users have tried to upload a terrabyte of data (the last full eGon input/output dataset), and we found that the maximum upload rate is 30-40 kb/s. We should at least be able to achieve an upload rate of 1mb/s.
• The same applies to downloading data.

Possible solutions

Some of the problems may have to do with the growing functionality and database queries, others can be solved by improving the implementation (e.g. better caching and more). One of the main problems is that we have to use very old software components.

• Python is notoriously a slow language, Python 3.6 (Release Date: Dec. 23, 2016) is even slower. We are running the OEP on Python 3.6 and would like to update this to at least Python 3.10. Many of our dependencies are dropping support for Python 3.6, which is causing us to stall more and more and severely hindering development. This could also lead to a complete standstill in some areas in the future. Areas that are particularly important for development in SIROP.

• Old Postgresql databases can also be slower. We are using Postgresql 9.3, while version 16 is already available. We should at least update to version 14.

Unfortunately, I can't take on these tasks as I don't have access to the infrastructure backend. I would be happy if we can make a plan on how to proceed and what options there are.

[tillmo]

Please be more specific. Which parts are particularly slow or slower than before. I guess: loading large database tables?

[jh-RLI]

@tillmo that's right, that wasn't my best description. Now I have updated the issue as I originally intended. Please read the text above again if you have the time. This issue is more of a meta issue. I've listed the main problems, but there are also other, more specific issues here on GitHub as well. I will link them here once i have the time.

[MGlauer]

All these issues do not sound like issues that are OS-related, but sub-optimal backend design (when it comes to the data-view probably on my end). Regarding the ontology-overview in particular: As far as I understand, the ontology files are parsed for every single request. Considering that these files only change on a bi-monthly basis, I would recommend implementing a method that loads the required information into a Django-Model on start-up or manually using a django-command that can be triggered when new files are available.

[jh-RLI]

Of course I agree, but that's only part of my point. It is always possible to optimise the implementation. I can confirm that your understanding is correct and I also plan to implement the solutuion that way. It might even be enough to cache the function that reads the oeo files. Then it will only be returned when the server is restarted.

I think I mixed my argument too much with my attempt to also address the importance of some of our current problems, because that's what was asked about. I'm not saying that we can't improve implementation if we keep our current infrastructure and the software versions within it. I'm saying that we see more and more issues when we update packages (PyPi) and that we want to be open to collaboration and forcing developers to use the old Python version. I think it would be worthwhile to update this so that we can move faster and not waste a lot of time fixing bugs related to old Python package versions. As a side effect, this will also lead to a bit more performance.