Hi, I'd like to let you know that I've been analyzing this issue for a few weeks now, and last night I probably found the cause.

I'm writing this post because others may have encountered the same problem. We're talking about a J 5.4 site, the latest version of PBuilder, Helix 2.2.1, and the latest version of the Celestia template.

The website was very slow and caused me many problems, especially during peak periods. It was significantly impacting the server, taking up a lot of MySQL CPU and memory.

I created a staging area to understand the reasons. At times, it even crashed the server, forcing me to restart it. After further analysis, I enabled logging in the staging area to help me understand the source of the problem. So, in the PHP 8 additional directives, I added this line:

[php-fpm-pool-settings]
slowlog = /var/www/vhosts/domain.ext/slow.log
request_slowlog_timeout = 1s

Through the log, I was able to determine that the problem stemmed from 404 errors. Whenever the template's error.php was called, the problems began.

The problem, however, wasn't in the template, because the slowness (28-30 seconds to load the 404 page) was recorded even when using the default Cassiopeia theme.

From further analysis, I then understood that the problem stemmed from router.php located in the "Components / com_sppagebuilder / router.php" folder.

The problem arose from a conflict on a multilingual site, and disappeared if:

• I turned off the system language filter
• or, I turned off the SP PageBuilder component.

When a 404 URL arrives, Joomla asks its components "Who owns this URL?" PageBuilder responds, assumes it's its own, and begins analyzing it. From the log analysis, this router executes a function to search the database for the article and its tags. This search, combined with the language filter logic, searches for associations for a nonexistent page, creates an explosive database query, and exhausts memory, causing a timeout.

If I temporarily deleted the router.php file, the problem went away and the pages loaded very quickly.

I then analyzed the file with some tools and realized it wasn't a fatal error. The timeout problem was due to the system simply waiting for a function.

Looking at this piece of code (which causes the problem, from what I understand, in multilingual Joomla sites):

private static function getCollectionTypeFromAlias($alias)
{
    // ...
    $articlesCount = \SppagebuilderHelperArticles::getArticlesCount(); // <-- Esegue una Query
    $articles = \SppagebuilderHelperArticles::getArticles($articlesCount); // <-- CARICA TUTTI GLI ARTICOLI IN MEMORIA!
    // ...
    // ... poi scorre TUTTI gli articoli uno per uno ...
    // ...

    $db = \Joomla\CMS\Factory::getDbo();
    $query = $db->getQuery(true)
        ->select('COUNT(*)')
        ->from('#__tags') // <-- Esegue un'altra query su TUTTI I TAG!
        //...
}

I realized that, from a performance standpoint, this piece of code (along with another) was causing the problem. For a single 404, this router is loading the entire database of articles and tags into memory.

If the system language filter is enabled, the query is degraded by adding complex joins to search for language associations, causing a memory drain.

I initially fixed the query (I don't know if it's a permanent fix, but since I applied it, the website hasn't had any problems and uses little CPU and memory).

I changed this part (line 136)

private static function getCollectionTypeFromAlias($alias)
    {
        if (!\class_exists('SppagebuilderHelperArticles')) {
            require_once JPATH_ROOT . '/components/com_sppagebuilder/helpers/articles.php';
        }

like this:

    private static function getCollectionTypeFromAlias($alias)
    {
        // --- START PATCH  ---
    return 'normal-source';
    // --- END PATCH  ---

        if (!\class_exists('SppagebuilderHelperArticles')) {
            require_once JPATH_ROOT . '/components/com_sppagebuilder/helpers/articles.php';
        }

Below, I modified the code from:

public static function parseRoute(&$segments)
    {

        /** @var CMSApplication */
        $app = Factory::getApplication();
        $menu = $app->getMenu();
        $item = $menu->getActive();
        $vars = array();

like this:

    public static function parseRoute(&$segments)
    {
        // --- START PATCH  ---
    if (empty($segments) || (isset($segments[0]) && $segments[0] !== 'page' && $segments[0] !== 'edit'))
    {
        return [];
    }
    // --- END PATCH  ---
        /** @var CMSApplication */
        $app = Factory::getApplication();
        $menu = $app->getMenu();
        $item = $menu->getActive();
        $vars = array();

I'm not a programmer, so I don't know if this is a permanent solution, so I ask you to check it out.

I've only encountered this issue on multilingual websites.

I can tell you that with this patch, I haven't had any slowness or CPU load issues with MySQL in the last 24 hours.