Review main Merged

93035[BUGFIX] Reduce queries when indexing many-to-many relations

Thomas Prangenberg CR +2 V +2 Normal +114 −6 updated 08/31/26, 16:35

Commit message

ReferenceIndex::compileReferenceIndexRowsForRecord() fetched every
foreign side row of an MM relation on its own through
BackendUtility::getRecord(). That is one query per relation: A
sys_category assigned to 12000 records needs 12000 queries whenever
the reference index of that category is updated, which happens on
every save of a related record and takes seconds per category.

Fetch the rows per foreign table instead, chunked to keep the number
of predicates within the limits of the database platform.

Only four values of a foreign row are evaluated afterwards: The
disabled, starttime and endtime fields, plus t3ver_state. Select
those instead of all columns. The rows of all relations of a field
are held in memory at once, so "select *" of 12000 rows of a wide
table like tt_content costs roughly 150MB, while the restricted set
stays below 10MB.

Review

  • Code review CR +2 Done
    • Benni Mack +2
    • Stefan Bürk +1
  • Verified V +2 Done
    • Benni Mack +2
    • Oliver Klee +1
    • Stefan Bürk +1
    • core-ci +1 CI

Issues 2

Files 4

Review log 1521

  1. Thomas Prangenberg uploaded patch set 1 03/02/26, 12:08

  2. Mathias Brodala commented on patch set 1 03/02/26, 12:11

    ReferenceIndexUpdater.php:164

    Who/what will unlock this if the RefIndex update is interrupted/cancelled? E.g. SIGINT (Ctrl+C) on CLI or a request cancel (Escape / PHP-FPM restart) in the browser?
  3. core-ci voted on patch set 1 V +1 03/02/26, 12:16 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/97099

  4. Thomas Prangenberg uploaded patch set 2 V +1 03/02/26, 12:59

    Forgot to release lock
  5. Thomas Prangenberg commented on patch set 2 03/02/26, 13:14

    ReferenceIndexUpdater.php:164 Reply

    To my knowledge, any flock is released on script termination. I don't know the exact behaviour of flock if the script is cancelled by a restart (e.g. php-fpm service as you mentioned) though. If this turns out to be too unreliable for you guys I'm happy to remove this from the patch, leaving that part unresolved for now, because it's not the main issue. (Originally, I also missed actually releasing the lock at any point which is now part of the patch.)
  6. core-ci voted on patch set 2 V +1 03/02/26, 13:38 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/97104

  7. Mathias Brodala commented on patch set 2 03/02/26, 14:13

    ReferenceIndexUpdater.php:164 Reply

    I'm fine with either way and am not sure myself. Let's see what others mean here.
  8. Garvin Hicking commented on patch set 2 03/02/26, 20:32

    ReferenceIndex.php:613

    We should create the connectionpool instance outside of the loop Otherwise maybe check out `DataMapProcessor::fetchDependentElements()` - this is an example of a chunked query that allows queries with a "UID IN (xxx)" list, iterating large result sets. That would allow to not fire so many queries here.
  9. Thomas Prangenberg uploaded patch set 3 V +1 03/04/26, 12:26

    add cap to predicates and use chunked query
  10. core-ci voted on patch set 3 V +1 03/04/26, 12:35 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/97316

  11. Thomas Prangenberg commented on patch set 3 03/04/26, 12:48

    ReferenceIndex.php:613 Reply

    Thanks for pointing out the example in DataMapProcessor, I updated it to match that logic and use the same chunk size by getting the max bound parameters per platform even though it looks a bit arbitrary given that it's not using placeholders. For mysql, I lowered the chunk size to match the default memory limit of the range optimizer, which I assume is more performant than a full table scan. I hope I'm correct about that and MariaDB not requiring this limit for its optimizer.
  12. Benni Mack updated the commit message in patch set 4 V +1 04/22/26, 06:42

  13. core-ci voted on patch set 4 V +1 04/22/26, 11:05 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/100768

  14. Benni Mack rebased onto patch set 5 V +1 08/15/26, 10:06

  15. core-ci voted on patch set 5 V +1 08/15/26, 10:12 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/106756

  16. Stefan Bürk uploaded patch set 6 V +1 08/31/26, 13:51

  17. core-ci voted on patch set 6 V +1 08/31/26, 13:58 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/107433

  18. Stefan Bürk commented on patch set 6 CR +1 V +1 08/31/26, 14:32

    Pushed patchset 6. I took the liberty of amending rather than only commenting, since the query part is clearly worth having and was close to ready. What changed: * Removed the lock. Two reasons beyond the blocking concern Mathias raised: the UniqueConstraintViolationException it guards against is already caught and ignored in ReferenceIndex::updateRefIndexTable() on main, 14.3 and 13.4 (18e4334bab5, #107139), so it cannot surface on any branch in the Releases line. And a blocking flock() around every backend save serializes the whole installation on a shared PHP-FPM pool, so a burst of saves can starve frontend requests. Thomas also reported in #109089 that he removed the same lock from his production instance again after complaints. I filed #110573 for the actual remaining defect. * Restricted the fetch to the columns that are used. Only four values of the foreign row are read afterwards (disabled, starttime, endtime, t3ver_state), but the rows of all relations are held at once, so "select *" of 12000 tt_content rows is around 150MB where the restricted set is under 10MB. That matters here, because the whole point is the case with many relations. * Small things: use the injected connectionPool, deduplicate the uid list, makeInstance(DeletedRestriction::class) as in the rest of the file, executeQuery() as in the other 13 call sites, comment typo. * Added a functional test for an MM field resolving into two foreign tables, which is the case the per-table grouping introduces and nothing covered yet. * Commit message rewritten, Releases extended to 13.4, Related: #88134 added. The chunk size logic is untouched. I first thought the bind parameter cap on a literal IN list was wrong, but DataMapProcessor::fetchDependentElements() uses exactly the same idiom, and MariaDBPlatform is a sibling of MySQLPlatform, so the instanceof check does give MariaDB the branch your comment intends. Only the comment wording was tightened. On the race: it is real, but a lock is the wrong shape of fix. It only covers ReferenceIndexUpdater, not ReferenceIndex::updateIndex(), which the CLI command and the lowlevel module use to write the same table, and a file or semaphore lock is per node, so it does nothing in a load balanced setup, which is where concurrent editors are most likely. The write is what should be idempotent. updateRefIndexTable() builds all new rows and writes them with one bulkInsert, and sys_refindex has a single unique key, the primary key on hash. An insert-or-ignore per row, or an upsert on hash, removes the exception at the source, needs no lock, and works across nodes. It would also allow the catch-all that #107139 added to be dropped again, which currently hides the fact that a single colliding hash discards the entire batch. I filed that as #110573.

    ReferenceIndexUpdater.php:164 Reply

    Moot in ps6 since the lock is removed, but for the record, since the answer is not the same for all three strategies: * FileLockStrategy (DEFAULT_PRIORITY 75, so the default) uses flock(). The kernel releases it when the file descriptor closes, so SIGINT, a killed worker and an FPM restart are all safe. * SemaphoreLockStrategy (priority 25) is not safe. sem_get() relies on PHP's request shutdown auto release, which a SIGKILL or an OOM kill never reaches. The semaphore then stays held and backend saving is dead on that node until someone removes it by hand. * SimpleLockStrategy (priority 50) self heals through its stale file check against max_execution_time, fallback 120 seconds. There was a second problem with the construct independent of that: acquire() returns bool, and the body was only entered when it returned true, with no else branch. SimpleLockStrategy returns false after 150 x 200ms of contention, and flock() returns false on filesystems without working locking, for example a shared NFS var directory. In those cases the reference index update was skipped completely, without a log entry and without an exception, leaving sys_refindex stale. That is worse than the duplicate key it was meant to avoid.
  19. Oliver Klee voted on patch set 6 V +1 08/31/26, 14:40

  20. Benni Mack voted on patch set 6 CR +2 V +2 08/31/26, 16:35

  21. Benni Mack merged patch set 7 08/31/26, 16:35