v6 - Performance issues when loading treeview

[AljosaB]

We recently upgraded Fluent to v6. Our site has a lot of pages. When opening CMS (/admin), page tree gets loaded (/admin/pages/treeview) and it takes like 30 seconds to finish (was 3s on v5) and sometimes it even hits time-out.

Trace

/vendor/tractorcow/silverstripe-fluent/src/Extension/FluentVersionedExtension.php.TractorCow\Fluent\Extension\FluentVersionedExtension->getCurrentVersionNumbers()
/vendor/tractorcow/silverstripe-fluent/src/Extension/FluentVersionedExtension.php.TractorCow\Fluent\Extension\FluentVersionedExtension->updatePrePopulateVersionNumberCache()	
...
/vendor/silverstripe/framework/src/ORM/Hierarchy/Hierarchy.php.SilverStripe\ORM\Hierarchy\Hierarchy->prepopulateTreeDataCache()
...
/vendor/silverstripe/cms/code/Controllers/CMSMain.php.SilverStripe\CMS\Controllers\CMSMain->SiteTreeAsUL()

Queries

FluentVersionedExtension->getCurrentVersionNumbers() gets called 2 times generating running 2 queries

SELECT
   BaseTable.RecordID AS LatestID,
   MAX(SiteTree_Localised_Versions.Version) AS LatestVersion 
FROM
   SiteTree_Localised_Live AS BaseTable 
   INNER JOIN
      SiteTree_Localised_Versions 
      ON BaseTable.RecordID = SiteTree_Localised_Versions.RecordID 
      AND SiteTree_Localised_Versions.Locale = 'sl_SI' 
   INNER JOIN
      SiteTree_Versions 
      ON SiteTree_Versions.RecordID = SiteTree_Localised_Versions.RecordID 
      AND SiteTree_Versions.Version = SiteTree_Localised_Versions.Version 
WHERE
   BaseTable.Locale = 'sl_SI' 
   AND SiteTree_Versions.WasPublished = 1 
GROUP BY
   LatestID;

SELECT
   BaseTable.RecordID AS LatestID,
   MAX(SiteTree_Localised_Versions.Version) AS LatestVersion 
FROM
   SiteTree_Localised_Live AS BaseTable 
   INNER JOIN
      SiteTree_Localised_Versions 
      ON BaseTable.RecordID = SiteTree_Localised_Versions.RecordID 
      AND SiteTree_Localised_Versions.Locale = 'sl_SI' 
   INNER JOIN
      SiteTree_Versions 
      ON SiteTree_Versions.RecordID = SiteTree_Localised_Versions.RecordID 
      AND SiteTree_Versions.Version = SiteTree_Localised_Versions.Version 
WHERE
   BaseTable.Locale = 'sl_SI' 
GROUP BY
   LatestID;

Each of these 2 queries takes around 12s to finish, ~25s together, which makes CMS too slow.

[tractorcow]

Hi @AljosaB thanks for the report. It's been a few years since we initially architected this solution, but if I had to do it again, I would probably entirely avoid touching versioned table as much as possible. The queries for versioned table (whether joins / subqueries) have always been reasonably slow and inefficient in silverstripe, the problem being compounded with the way that fluent added its own layer of complexity.

This problem normally doesn't manifest in sites until the versioned / versioned_localised table gets to a certain size, by which time the system is already well in production.

The immediate advice i would give you is to look at trimming your versioned tables as much as you can. Can you give me an idea for how many rows you are currently querying to get 12s query times?

In the mid term, maybe we can look at some small tweaks to fluent to make it less dependant on those slow queries.

Another option is to try fluent v5. I personally don't use 6, since the last silverstripe fluent project I touched was before 6 was released, and it has a lot less integration with the newer silverstripe versioning (e.g. versioned admin).

In the long term, migrate from silverstripe + fluent. I have toyed with the idea of writing a fluent module for laravel nova. :)

[AljosaB]

Thank you for the response and all the advice! Here is my table record count:

SiteTree_Localised_Versions - 1,956,971
SiteTree_Localised_Live - 88,530
SiteTree_Versions - 2,033,424

We use Lumberjack module (https://github.com/silverstripe/silverstripe-lumberjack) a lot, that's why these table are quite big.
Queries seems to be the part of some new feature... the function name suggests it has something to do with pre-populating version number cache and I wonder if it's really necessary to do it for all pages when loading tree view where you only see few of those pages? Also it happens again when you navigate to some page and treeview gets called again.

Let me know what kind of tweaks you come up with, and I'll try to trim versions tables a little bit.

[tractorcow]

Yes those numbers are very similar to another project I had a while back (millions of rows), where trimming the row count automatically gave a huge performance boost.

Why not try something like https://github.com/axllent/silverstripe-version-truncator (you may need to tweak it for localised_versions).

if it's really necessary to do it for all pages when loading tree view where you only see few of those pages?

I'd probably agree with that. I'd have to review that code to check why it's there and see if it can be deferred / removed.

[t3hn0]

Hi, any update on this? We're still experiencing this issue.

[tractorcow]

Hi @t3hn0 the best recommendation I have is to downgrade to fluent 5. Fluent 6 was created as a new major version to support some of the versioning features in later minor versions of silverstripe 4.x. See #639 for notes.

Personally I dislike how much silverstripe has come to rely on versioned, so I still use fluent 5.x in my personal projects. You might not have all the latest features but you'll get better performance.