Review main Merged

81916[BUGFIX] No "update storage index" FAL task fail with too many records

Garvin Hicking CR +2 V +2 Easy +13 −5 updated 12/19/23, 15:30

Commit message

The Indexer builds a large array of all actual files on a storage
(identifiedFileUids). If many files exists, this array can get
very large.

This array was then passed to a QueryBuilder to fetch all
records NOT IN that array. Since a NOT IN query is passed as a
string to the database, it can exceed the string size allowed
in a query, making the whole task fail.

Since a NOT IN query cannot be chunked easily, the whole
logic has been adapted in a different way.

Instead of fetching a restricted list of database records,
all records are fetched and iterated. Even with a million
of sys_file_records of a single (!) storage this will
perform alright, and be within practical usage scenarios.

Each database record is then checked for a match in the
large array of known records, and then execute the
same logic as before.

To benchmark the implications, the following test was run:

Baseline:

* sys_file with 50.736 entries
  * 16.912 marked as missing
  * 33.824 marked as existing
* Filesystem with 8.771 actual files

Tested setup via a script which:

* Resets to baseline sys_file storage
* Executes scheduler task "File Abstraction Layer: Update storage index
  (scheduler)"
* Flags 41.965 files as missing, 8.771 as found.

Script execution was performed 50 times, and a mean avery was calculated,
once once with the patch in place, once without.

Old variant (using NOT IN query): 11.787 seconds
New variant (fetching all records): 12.0544 seconds

On top of being within the same performance level, using the new method,
no database exception will be provoked (see ticket).

Review

  • Code review CR +2 Done
    • Stefan Bürk +2
    • Oliver Klee +1
    • Christian Kuhn +1
    • Jörg Bösche +1
    • Benni Mack +1
  • Verified V +2 Done
    • Stefan Bürk +2
    • Christian Kuhn +1
    • core-ci +1 CI

Issues 1

Files 1

Review log 1015

  1. Garvin Hicking uploaded patch set 1 11/23/23, 12:12

  2. core-ci voted on patch set 1 V -1 11/23/23, 12:41 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/49662

  3. Garvin Hicking uploaded patch set 2 V -1 11/23/23, 12:43

    CGL
  4. core-ci voted on patch set 2 V +1 11/23/23, 12:56 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/49677

  5. Garvin Hicking updated the commit message in patch set 3 V +1 11/23/23, 13:58

  6. core-ci voted on patch set 3 V +1 11/23/23, 14:11 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/49696

  7. Garvin Hicking updated the commit message in patch set 4 V +1 11/23/23, 15:58

    Passify commit message
  8. core-ci voted on patch set 4 V -1 11/23/23, 16:28 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/49721

  9. core-ci voted on patch set 4 V +1 11/23/23, 16:46 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/49721

  10. Benni Mack voted on patch set 4 CR +1 12/19/23, 08:19

  11. Oliver Klee voted on patch set 4 CR +1 12/19/23, 08:29

  12. Jörg Bösche voted on patch set 4 CR +1 12/19/23, 12:07

  13. Christian Kuhn voted on patch set 4 CR +1 V +1 12/19/23, 13:43

  14. Stefan Bürk voted on patch set 4 CR +2 V +2 12/19/23, 15:14

  15. Stefan Bürk merged patch set 5 12/19/23, 15:30