150k+ articles / 1700 categories performance improvements

[Ruud68]

Hi,
so I have the opportunity to migrate a J3 site with over 150000 articles in 1705 categories to Joomla 5.
performance of the current site is very bad: the customer has a lot of caching / mysql tuning in place on his 12VCPU / 64GB VPS.

Due to the bad performance with these number of articles, I needed to investigate if the move to J5 with PHP8.x and newer MariaDB would do anything on the performance: if not, Joomla is not up for the job as we cannot keep adding more hardware just to keep the site up.

So I started with the (test) upgrade:

1. remove all extensions and only keep core to be able to upgrade from J3 to J4 and next to J5
2. start upgrade

So here was issue number 1: the upgrade of the database timed out and was not able to complete: the queries executed just took to long and as some rolled back, some where stuck in an intermediate state.

so next:

1. restore J3 site
2. remove all extensions
3. purge #__content table (make a sql dump first)
4. start upgrade
5. set mysql in sql_mode="" (to be able to import the date values of 0000-00-00 00:00:00 where the now are NULL
6. add xreference varchar(50) to the #__content table
7. use 'bigdump.php' to import the 3.9GB #__content sql dump
8. run queries to change the 0000-00-00 00:00:00 dates to NULL using limit 50000 and then run them multiple times to avoid timeouts
9. remove the xreference field

So now I have a 'clean' Joomla 5 site with all content on it. it is on a very basic 1 VCPU/1GB VPS.

Now for the test:

1. I created a frontpage > index.php?option=com_content&view=categories&id=0 (so a categories view listing all categories)
2. I created some test PHP code that loads 200 articles via the ArticleModel

    $start = \time();
    $articlesModel = new \Joomla\Component\Content\Site\Model\ArticlesModel(['ignore_request' => true]);
    $params = \Joomla\CMS\Factory::getApplication()->getParams();
    echo '<pre>' . time() - $start . '</pre>';
    $articlesModel->setState('params', $params);
    $articlesModel->setState('list.limit', 200);
    echo '<pre>Articles:  ' . \count($articlesModel->getItems()) . '</pre>';
    echo '<pre>Time (seconds): ' . time() - $start . '</pre>';

the load times:

1. Frontpage: 20 seconds to display the categories
2. 200 articles: 94 seconds

I was happy that at least the server didn't crash under the load, so had my hopes up and started analyzing the load times and if they could be altered.

I added the following index: CREATE INDEX idx_catid_state ON #__content (catid, state);

The frontpage now loaded within 1 second! This is just by adding this index!

The query created by the article model was a little bit more challenging 'explain' learned me:

• MySQL’s optimizer thought it would be faster to read #__categories (c) first.
• But reading c first means more rows scanned and prevents using the best covering index on #__content.
• So MySQL did extra work: full scans, temp tables, filesorts.

So basically regardless of the number of articles needed to return it always had to go though the 150K articles to get the order

In order to instruct MySQL to stick to the order provided (#__content first!) I needed to do instead of a LEFT JOIN on the categories table a STRAIGHT_JOIN

as that is not provided by the Joomla DatabaseDriver I worked around it by altering the code in the site ArticleModel getListQuery() funtion to:

//            ->from($db->quoteName('#__content', 'a'))
            ->from(
                       $db->quoteName('#__content', 'a')
                      . ' STRAIGHT_JOIN ' . $db->quoteName('#__categories', 'c')
                      . ' ON ' . $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid')
           )
//            ->join('LEFT', $db->quoteName('#__categories', 'c'), $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid'))
            ->join('LEFT', $db->quoteName('#__users', 'ua'), $db->quoteName('ua.id') . ' = ' . $db->quoteName('a.created_by'))
            ->join('LEFT', $db->quoteName('#__users', 'uam'), $db->quoteName('uam.id') . ' = ' . $db->quoteName('a.modified_by'))
            ->join('LEFT', $db->quoteName('#__categories', 'parent'), $db->quoteName('parent.id') . ' = ' . $db->quoteName('c.parent_id'));

So basically commenting out the from and the join for the categories and adding a new ->from (with STRAIGHT_JOIN for the categories).

and for the test: the loading time for the 200 articles was down from 94 seconds to 5 seconds (!!!!!)

So basically now we are on a minimum vps and it is able to (almost) outperform the 12CPU VPS with these small changes.

So what is next:
The index I from now on will always do on any site I manage / develop, but IMO it would be a good addition for a discussion to have it in Core

The change to the ArticleModel introducing the STRAIGHT_JOIN I can do with a model override to avoid falling back to the slow query when Joomla updates, but I think this would be a good point for a discussion.

These are just two changes that already make a world of difference and I can probably find more (e.g. when going in the back-end to the Articles, it loads forever before it displays the articles, I yet need to investigate if that can be speeded up as well (but that currently has no priority for me).

I think it would be good to introduce a ->join('STRAIGHT', ....) and adjust the ArticlesModel (and maybe others) to start using that. Knowing that progress and MSSQL do not support STRAIGHT_JOIN there should be for these a fall back to INNER or LEFT.

And just to state my case here: I know that there is no issue and that it is able to perform just fine by adding extra hardware, but imagine what the impact would be of needing less hardware to get the same speed: less $$$, less environmental impact, etc.

So the +15 times speed increase is likely a lot and specifically for sites with these amounts of articles / categories, but is we can shave off even 10% of the performance we still have a good improvement

@HLeithner

[Fedik]

You can follow one of existing issue:

• [5.4] Improve article list performance for large sites #45542
• [5.4] [WiP] Improve articles lists in backend for display of and filtering by missing workflow associations + better performance #45660
• [6.0] [Performance] Access Rules smart preload, reduce memory and time use, for large sites #44078

and to a few linked to those.

[Ruud68]

Thanks @Fedik , did a quick read up and used the #45542 (the site ArticlesModel change) from @SniperSister
result before: 86 seconds
result after: 93 seconds

The other PR's I haven't tested as these are mainly for back-end.

@SniperSister could you test with change suggested by me to see if that will also make your (front-end) articles list etc. faster?
so in ./component/com_content/src/Model/ArticlesModel
replace in function getListQuery():

->from($db->quoteName('#__content', 'a'))
            ->join('LEFT', $db->quoteName('#__categories', 'c'), $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid'))

with:

            ->from(
                       $db->quoteName('#__content', 'a')
                      . ' STRAIGHT_JOIN ' . $db->quoteName('#__categories', 'c')
                      . ' ON ' . $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid')
           )