Loading...

XML

Word

Printable

Details

Type: Bug
Resolution: Unresolved
Priority: Blocker
Fix Version/s: Customer request
Affects Version/s: 3.3.37, 4.5.6, 4.6.2
Component/s: None
Labels:
- escalated

Customer Request:

Yes

Description

Changes introduced in https://issues.ibexa.co/browse/IBX-7172 can result in slow-running queries in projects with large ezcobj_state_link tables and in overall degraded performance.

Steps to reproduce:
1. Clean installation of Ibexa Experience v3.3.36 and v3.3.37
2. Point v3.3.36 to use the same database as v3.3.37
3. In v3.3.37 create 3 commands:
src/Command/AddContentWithObjectStatesCommand.php

<?php

namespace App\Command;

use eZ\Publish\API\Repository\ContentService;
use eZ\Publish\API\Repository\ContentTypeService;
use eZ\Publish\API\Repository\LocationService;
use eZ\Publish\API\Repository\ObjectStateService;
use eZ\Publish\API\Repository\Repository;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Style\SymfonyStyle;

final class AddContentWithObjectStatesCommand extends Command
{
    private const FOLDER_CT_IDENTIFIER = 'folder';
    private const HOME_LOCATION_ID = 2;
    public static $defaultName = 'app:add-content-with-os';

    public function __construct(
        private ObjectStateService $osService,
        private LocationService $locationService,
        private ContentService $contentService,
        private ContentTypeService $contentTypeService,
        private Repository $repository,
    )
    {
        parent::__construct();
    }

    public function execute(InputInterface $input, OutputInterface $output): int
    {
        $io = new SymfonyStyle($input, $output);

        $folderContentType = $this->contentTypeService->loadContentTypeByIdentifier(self::FOLDER_CT_IDENTIFIER);
        $homeLocation = $this->locationService->loadLocation(self::HOME_LOCATION_ID);

        $osGroups = $this->osService->loadObjectStateGroups();
        $osByGroupId = [];
        foreach ($osGroups as $osGroup) {
            $osByGroupId[$osGroup->id] = $this->osService->loadObjectStates($osGroup);
        }

        $io->progressStart(100000);
        $this->repository->sudo(function () use ($folderContentType, $homeLocation, $osGroups, $osByGroupId, $io) {
            for ($i = 0; $i<100000; $i++) {
                    $locationCreateStruct = $this->locationService->newLocationCreateStruct($homeLocation->id);
                    $contentCreateStruct = $this->contentService->newContentCreateStruct($folderContentType, 'eng-GB');

                    $folderIdentifier = uniqid('folder-');
                    $contentCreateStruct->setField('name', $folderIdentifier);
                    $contentCreateStruct->setField('short_name', $folderIdentifier);

                    $folder = $this->contentService->createContent($contentCreateStruct, [$locationCreateStruct]);
                    $this->contentService->publishVersion($folder->versionInfo);

                    foreach ($osGroups as $osGroup) {
                        $this->osService->setContentState($folder->contentInfo, $osGroup, $osByGroupId[$osGroup->id][0]);
                    }
                    $io->progressAdvance();
                }
            });

        $output->writeln('100000 Content with object states added.');

        return Command::SUCCESS;
    }
}

src/Command/AddObjectStatesCommand.php

 <?php

namespace App\Command;

use eZ\Publish\API\Repository\ObjectStateService;
use eZ\Publish\API\Repository\Repository;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

final class AddObjectStatesCommand extends Command
{
    public static $defaultName = 'app:add-os';

    public function __construct(
        private ObjectStateService $osService,
        private Repository $repository,
    )
    {
        parent::__construct();
    }

    public function execute(InputInterface $input, OutputInterface $output): int
    {
        for ($i = 0; $i<10; $i++) {
            $this->repository->sudo(function () {
                $osgIdentifier = uniqid('osg-');
                $osgCreatStruct = $this->osService->newObjectStateGroupCreateStruct($osgIdentifier);
                $osgCreatStruct->defaultLanguageCode = 'eng-GB';
                $osgCreatStruct->descriptions = [
                    'eng-GB' => '',
                ];
                $osgCreatStruct->names = [
                    'eng-GB' => $osgIdentifier,
                ];

                $osg = $this->osService->createObjectStateGroup($osgCreatStruct);

                for ($i = 0; $i<10; $i++) {
                    $osIdentifier = uniqid('os-');
                    $osCreateStruct = $this->osService->newObjectStateCreateStruct($osIdentifier);
                    $osCreateStruct->defaultLanguageCode = 'eng-GB';
                    $osCreateStruct->descriptions = [
                        'eng-GB' => '',
                    ];
                    $osCreateStruct->names = [
                        'eng-GB' => $osIdentifier,
                    ];

                    $this->osService->createObjectState($osg, $osCreateStruct);
                }
            });
        }

        $output->writeln('100 Object states added.');

        return Command::SUCCESS;
    }
}

src/Command/TestCommand.php

 <?php

namespace App\Command;

use eZ\Publish\API\Repository\LocationService;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

final class TestCommand extends Command
{
    public static $defaultName = 'app:test';

    public function __construct(
        private LocationService $locationService
    )
    {
        parent::__construct();
    }

    public function execute(InputInterface $input, OutputInterface $output): int
    {
        $start = microtime(true);
        $parentLocation = $this->locationService->loadLocation(2);
        $this->locationService->loadLocationChildren($parentLocation);

        $output->writeln('Time: ' . (microtime(true) - $start));
        
        return Command::SUCCESS;
    }
}

4. In v3.3.36 add only src/Command/TestCommand.php
5. In v3.3.37 execute:

php bin/console app:add-os

That will create 10 ObjectStateGroups, each with 10 ObjectStates

then

php bin/console app:add-content-with-os

This will create 100 000 folders in Location with ID 2. This can take some time.
6. In v3.3.37 follow the steps from https://issues.ibexa.co/browse/IBX-7172
7. Clear cache both in v3.3.37 and v3.3.36
8. Execute in both v3.3.37 and v3.3.36

php bin/console app:test

Results
In v3.3.36

php bin/console app:test

Time: 0.019958972930908

In v3.3.37

php bin/console app:test

Time: 1.7836620807648

More info
In v3.3.37 SQL generated by $this->locationService->loadLocationChildren($parentLocation); takes more than 1s to complete, when in v3.3.36: 349 µs

Designs

Attachments

Issue Links

relates to

IBX-7172 loadLocationChildren() not working with multiple objectstate permissions

Closed

Activity

People

Assignee:: Unassigned

Reporter:: Mateusz Bieniek

Votes:: 1 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 22/Mar/24 4:27 PM

Updated:: 19/Apr/24 9:50 AM