Very slow CMS and content management (with Neo and SuperTable)

[janreges]

Describe the bug

Hi,

for our projects we use Craft CMS (PRO) and usually we need the client to compose the individual pages himself from the individual visual blocks. We implemented the management of the structured content of these blocks using Neo and SuperTable plugins.

Note: I am creating this ticket in the Craft CMS, Neo and SuperTable GitHub repositories. I am interested in the opinion of the authors and architects of Craft CMS and these plugins. After investigating these performance issues, it is evident that some optimizations and solutions to these problems can be implemented in the Craft CMS code and then subsequently in the individual plugins.

Facts about our project

• 209 fields in Settings -> Fields section - 20 of them are of Asset type (typically images for visual components). We have all assets in only one common volume, which is on one local filesystem.
• 1 Neo field for the content editor, which contains 142 block types (65 are first level blocks and 77 are subblocks)
• 6 fields are of type SuperTable and these are also used within the Neo block types
• We have Craft CMS, Neo and SuperTable plugins in the latest versions

Issues we have and perceive from the Craft CMS perspective

• When a page containing the ahead mentioned Neo block is created or edited, the form takes 2.8 seconds to generate on the backend - for this, 2064 database queries are made, 123 MB of memory is allocated, 28127 events are generated and the log contains 4639 records. This is, of course, a huge overhead and makes the CMS difficult for users to use.
• Note: the duration times above are from a locally dockerized project on a powerful Ryzen processor (HEDT). PHP-FPM has an active opcache and an optimized composer autoload dump. If running on server CPUs (e.g. Intel Xeon E5-2699v4), the times are 2x worse. The reason is that the performance of the mentioned server CPU per 1 core is half compared to HEDT and the server has only 2400 MHz DDR4 ECC memory compared to 3600 MHz on the tested PC. In the case of the production server, it takes 6 seconds to load the form to create or edit a page, so it is understandably frustrating and very annoying.

Our investigation of the problems and findings

• If the SuperTable plugin is disabled, the form loads in 1.5 seconds, the number of DB queries is reduced from 2064 to 673, the memory allocation is reduced from 123 to 71 MB, the number of events is reduced from 28127 to 14352, and the number of logs is reduced from 4639 to 2409.
• If both SuperTable and Neo plugins are disabled, the form will load in 0.3 seconds, the number of DB queries will be reduced to 114, memory allocation to 17 MB, the number of events will be reduced to 1338 and logs to 297.
• So it is quite evident that if you have a larger number of fields and use both Neo and SuperTable, extreme and very often excessive numbers of DB queries, events and logs are executed, causing huge overhead. And unfortunately this happens even when loading a form to create a new page.

How can you help us

We would like to hear an informed opinion from the authors/architects of the existing code on the possible causes of these problems, but our investigation of the existing code shows that:

• The Neo and SuperTable plugins, and perhaps even the Craft CMS level fields, lack some form of internal instance cache to ensure that even if a field is used within a form dozens or hundreds of times within a single run of a PHP script (e.g. in Neo/SuperTable blocks), that they are only initialized once. That is, even if an instance of a field/element is instantiated hundreds of times in a run of a single instance of a PHP script, that initialization and retrieval of information from the DB or filesystem is done only once.
• Unfortunately, the Neo plugin is not tailored to higher tens or hundreds of block types. Editing such a Neo field (prescription of block types) freezes JavaScript in the browser for tens of seconds after loading. Managing and configuring these types of blocks is only done by the developers on the DEV environment, so the client doesn't come into contact with it, but it is annoying.
• In Craft CMS code, certain methods of some classes/services are called hundreds or thousands of times within one instance of PHP script execution - it depends on how one models content management. For example, we found that in our example, when creating a form to manage a page, the getRootFolderByVolumeId($volumeId) method is called over 1,000 times, causing over 1,000 identical queries to the database. We have therefore implemented a simple composer patch below that will reduce the number of related identical duplicate DB queries from 1,000 to units and likewise reduce the number of events or logs by thousands. A sample of this patch is at the end of this ticket. Even this one small optimization sped up page generation with page editing by 15-30%. However, this patch does not address the situation where the volume settings are modified as part of the script run, so it is not an ideal solution.
• For the Neo or SuperTable plugins, a form of "lazy loading" would be useful in some critical places, both at the block type configuration level (in the Settings section) and within the page content forms. Even though the user only uses 0 to 10% of the content types within the management of these blocks or content management on a given page, a complete initialization of all of them is performed with each request.
• The Craft CMS as a whole lacks a sophisticated cache that would be built on, for example, tagging the cache and then also have selective cache invalidation based on the tags. Before switching to Craft CMS, we used our own CMS built on the Nette framework, which also had a great taggable cache that worked with both storing to a local filesystem, e.g. Redis (https://doc.nette.org/en/caching#toc-invalidation-using-tags). In Craft CMS and YII, the cache options seem to be only expiration-based, which is insufficient for a large part of the scenarios inside the CMS.
• Because for most projects that use Craft CMS, the CMS administration part (Settings, fields, sections, etc.) is completely disabled on production projects (CMS configuration happens more on DEV environments and is versioned). Therefore, it is completely unnecessary to have a series of DB queries, event/log throws related to loading something that is practically unchangeable in the production CMS, while doing normal content management on most forms in the CMS. The invalidation of this cache could occur by default in cache-flush commands within deployments. However, for this cache to be truly meaningful, it is not just about caching DB queries, but rather entire serialized instances of some objects that must be created over and over again for most requests in the CMS during normal operation and take tens or hundreds of milliseconds.

How to proceed?

We really like the Craft CMS and a number of useful plugins and we are trying to promote them. Unfortunately we have now run into some very fundamental performance issues.

So the first thing we thought of was to start investigating these causes ourselves, think of and implement some optimizations and then send pull requests.

As authors, do you please have an opinion on how to solve this situation? We'd be happy to help with optimizations, but if any of the Craft CMS architects have already had some thoughts and ideas on these improvements, we'd like to know so we don't do double work. This will also increase the chances that even if we design and implement some specific optimizations ourselves, they will be incorporated into future versions faster and without major comments.

You may also be self-aware that, based on some historical architectural decisions, some forms of efficient caching are now not applicable at all. For example, I imagine that serializing some complexly loaded objects into the cache won't work because the architecture requires that lifecycle of their initialization throws a number of events on which other functionality depends.

I would be very grateful for any thoughts and ideas on how to grasp and implement these necessary optimizations.

If needed, I can also provide a complete configuration of our CMS, including a database dump to your e-mail address.

Thank you very much for your time and support.

Patch getRootFolderByVolumeId()

--- /dev/null
+++ ../src/services/Assets.php
@@ -549,10 +549,18 @@
      */
     public function getRootFolderByVolumeId(int $volumeId): ?VolumeFolder
     {
-        return $this->findFolder([
-            'volumeId' => $volumeId,
-            'parentId' => ':empty:',
-        ]);
+		static $cache = [];
+		if (isset($cache[$volumeId])) {
+			return $cache[$volumeId];
+		}
+
+		$folder = $this->findFolder([
+			'volumeId' => $volumeId,
+			'parentId' => ':empty:',
+		]);
+		$cache[$volumeId] = $folder;
+
+		return $folder;
     }

     /**

Craft CMS version

4.4.13

PHP version

8.1.18

Operating system and version

Debian 11 Bullseye

Database type and version

MariaDB 10.11.2

Image driver and version

GD 8.1.18

Installed plugins and versions

• Neo 3.7.9
• Supertable 3.0.9
• Redactor 3.0.4
• Typed linked field 2.1.5
• Other plugins (not much relevant to these performance issues - Blitz, Field Manager, Retour, Formie, Feed Me, Navigation, User Activity, SEOmatic, Icon Picker)

Steps to reproduce

See description.

Craft CMS version

4.4.13

Plugin version

3.0.9

Multi-site?

Yes

Additional context

No response