#109089Reference index scales very poorly, slows down saving records in backend and should use a lock
Description
The sys_refindex table is updated on every save from the backend and, depending on the number of relations between records, can dramatically slow down performance when saving records in the backend.
In our case, this is caused by some heavily used categories that are assigned to almost all records in multiple tables. While saving a record that has one or more categories assigned, the reference indexer iterates through all relations to each of these categories, individually fetches the related database record and compiles information about it into an array. With around 12.000 relations, each category would take up to 7 seconds, leading up to a minute of processing time while saving solely for updating the refindex when there were multiple categories assigned to a record.
I was able to implement a workaround by overwriting \TYPO3\CMS\Core\Database\ReferenceIndex and instead of fetching each record using BackendUtility::getReord() I change it to one query per table. This reduced the time taken while saving records by a factor of 10.
There is even a todo comment in that class pointing to the fact that this is a known issue and should be optimized.
I changed \TYPO3\CMS\Core\Database\ReferenceIndex::compileReferenceIndexRowsForRecord from this (beginning at line 594):
if ($field->isType(TableColumnType::INLINE, TableColumnType::GROUP, TableColumnType::SELECT)
&& $field instanceof RelationalFieldTypeInterface
&& $field->getRelationshipType() === RelationshipType::ManyToMany
) {
foreach ($itemArray as $itemKey => $item) {
// Get rid of soft-deleted foreign records, those should not create refindex entries
// @todo: It would be better if RelationHandler would not return soft-deleted MM rows. Unsure
// how to do that since RH only works on the MM table, but it could then also return
// the full foreign row along the way.
// @todo: Expensive. This code could be optimized to fetch multiple records at once per foreign
// table, or make RH return full foreign rows in some hopefully efficient way.
$foreignSideRecord = BackendUtility::getRecord($item['table'], (int)$item['id']);
into this
if ($field->isType(TableColumnType::INLINE, TableColumnType::GROUP, TableColumnType::SELECT)
&& $field instanceof RelationalFieldTypeInterface
&& $field->getRelationshipType() === RelationshipType::ManyToMany
) {
$recordUids = [];
$records = [];
foreach ($itemArray as $itemKey => $item) {
$recordUids[$item['table']] = $recordUids[$item['table']] ?? [];
$recordUids[$item['table']][] = $item['id'];
}
foreach ($recordUids as $table => $uids) {
$query = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
$query->getRestrictions()->removeAll()->add(new DeletedRestriction());
$query->select('*')->from($table)->where($query->expr()->in('uid', $uids));
$rows = $query->fetchAllAssociative();
foreach ($rows ?: [] as $row) {
$records[$table][$row['uid']] = $row;
}
}
foreach ($itemArray as $itemKey => $item) {
// Get rid of soft-deleted foreign records, those should not create refindex entries
// @todo: It would be better if RelationHandler would not return soft-deleted MM rows. Unsure
// how to do that since RH only works on the MM table, but it could then also return
// the full foreign row along the way.
// @todo: Expensive. This code could be optimized to fetch multiple records at once per foreign
// table, or make RH return full foreign rows in some hopefully efficient way.
$foreignSideRecord = $records[$item['table']][(int)$item['id']] ?? null;
So far this works, though I am a bit worried about side effects. The problem is, that overwriting the ReferenceIndex class via XClass is not a solid solution, partly because a lot of its scope is private. I can’t really rely on this workaround for long.
If this cannot be fixed in the near future, do you have any suggestions what to change otherwise to avoid this issue without overwriting parts of the core. Could updating the reference index be deferred to after saving is finished? Could one exclude the sys_category table from the reference index without causing issues in the frontend?
In addition for it being an annoyance for backend users, the fact that saving takes too long also introduces another problem: In some cases we encountered a problem when two backend users saved a record at the same time (before one saving process was finished). This would cause duplicate key violations when inserting rows to sys_refindex, because having multiple users update the reference index at the same time basically causes a race condition.
To avoid that, I also added a lock to the ReferenceIndexUpdater (which has the downside of it slowing down responses even more):
<?php
namespace Vendor\Package\XClass\Core\DataHandling;
use Vendor\Package\Utility\GeneralUtility;
use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
use TYPO3\CMS\Core\Authentication\BackendUserAuthentication;
use TYPO3\CMS\Core\Locking\LockFactory;
use TYPO3\CMS\Core\Locking\LockingStrategyInterface;
/**
* Fixes an issue that caused a UniqueConstraintViolationException when inserting data into sys_refindex
* when multiple users would save records simultaneously.
*/
class ReferenceIndexUpdater extends \TYPO3\CMS\Core\DataHandling\ReferenceIndexUpdater
{
public function update(): void
{
$locker = GeneralUtility::makeInstance(LockFactory::class)->createLocker('XClass_ReferenceIndexUpdater');
if ($locker->acquire(LockingStrategyInterface::LOCK_CAPABILITY_EXCLUSIVE)) {
try {
parent::update();
$locker->release();
} catch (UniqueConstraintViolationException $e) {
$locker->release();
/** @var BackendUserAuthentication $user */
$user = $GLOBALS['BE_USER'] ?? NULL;
if ($user) {
$user->writelog(5, 0, 2, null, $e->getMessage() . ': ' . $e->getQuery()->getSQL() . ' ' . serialize($e->getQuery()->getParams()), $e->getTrace());
}
} catch (\Throwable $e) {
$locker->release();
throw $e;
}
}
}
}