Review main Merged

88213[TASK] Drastically speed up `typo3 cleanup:orphanrecords`

Christian Kuhn CR +2 V +2 Hard +198 −199 updated 02/18/25, 11:44

Commit message

The CLI command implementation of cleanup:orphanrecords is truly awful:
It is extremely expensive and can take minutes if not hours with only
mid-sized TYPO3 instances.

Its main goal is to find and delete pages and attached records that
have no proper connection to the tree root - page rows that are not
part of a hierarchical structure, plus TCA records attached to such
invalid pages.

The code has been moved around since early TYPO3 3.x, later
implementing doctrine and symfony commands, while the algorithms has
never been refactored.

Since TYPO3 v12/v13 - even considering workspace overlays - the page
tree is strict: Each page *must* point to a pid that is either zero,
or a pages row that points to a page having pid zero, recursive. Any
pages not following this are "orphan".

The same goes for all TCA records: Their pid is either zero, or they
point to a list of pages that point to zero.

The patch rewrites the algorithm of `typo3 cleanup:orphanrecords`
leading to dramatic performance improvements.

Stats with a local test instance *before patch*:
Queries: 10.7 Mio
Runtime: 10 minutes 21 seconds

Stats with a local test instance *with patch*:
Queries: 116
Runtime: 0.2 seconds

Stats of course vary depending on instance and network capabilities.
However, casual TYPO3 instances should go down from minutes or hours
to a few seconds.

The change also removes dependency to DataHandler: Orphan pages and
records are removed from database directly.

Review

  • Code review CR +2 Done
    • Christian Kuhn +2
    • Garvin Hicking +1
    • Stefan Bürk +1
    • Benni Mack +1
  • Verified V +2 Done
    • Christian Kuhn +2
    • Garvin Hicking +1
    • Sybille Peters +1
    • Stefan Bürk +1
    • Benni Mack +1
    • core-ci +1 CI

Issues 1

Files 4

Review log 2434

Show 79 earlier messages
  1. Christian Kuhn uploaded patch set 1 02/13/25, 23:10

  2. core-ci voted on patch set 1 V -1 02/13/25, 23:16 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74125

  3. Sybille Peters commented on patch set 1 02/14/25, 15:31

    Test: It correctly detected an orphan in tt_content on a page with a not existing parent: string(21) "orphan pages count: 1" string(22) "orphan pages uids: 938" string(23) "orphan records count: 1" string(30) "orphan records: tt_content:220"

    OrphanRecordsCommand.php:93

    Shouldn't this add the DeletedRestriction? When I debug this, I get page 226 as connectedPageUids, this is a deleted page.

    OrphanRecordsCommand.php:126

    I am not as easily able to reproduce the problem with https://forge.typo3.org/issues/93419 because of course the command is faster. But I can still do it if I set a breakpoint here and then while it is waiting create a new page and a content element in the page (in previous version it was enough to create the page). So, it is not as likely to happen but can still happen. You could prevent it e.g. by using the timestamp approach as in my patch https://review.typo3.org/c/Packages/TYPO3.CMS/+/88102
  4. Christian Kuhn commented on patch set 1 02/17/25, 14:04

    Reply

    thank you! great. i meanwhile implemented a similar approach in dbdoctor which covered the algorithm with tests. let's continue with implementation here.

    OrphanRecordsCommand.php:93 Reply

    No, we should remove soft-deleted rows as well: a) the current implementation does this b) a page or other record added to a not-existing pid (or invalid pid because multiple pages form a loop) must never exist, no matter if soft-deleted or not. they should be deleted. dbdoctor does the same, btw.

    OrphanRecordsCommand.php:126 Reply

    yeah. i think we'll simply do the 'pages loop' at the end again to see if the pids in question popped up meanwhile.
  5. Christian Kuhn commented on patch set 1 02/17/25, 19:06

    OrphanRecordsCommand.php:126 Reply

    Done (will push soon).
  6. Christian Kuhn uploaded patch set 2 V -1 02/17/25, 19:07

  7. core-ci voted on patch set 2 V +1 02/17/25, 19:15 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74262

  8. Christian Kuhn uploaded patch set 3 V +1 02/17/25, 19:47

  9. Christian Kuhn uploaded patch set 4 02/17/25, 19:51

  1. core-ci voted on patch set 3 V +1 02/17/25, 19:54 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74266

  2. core-ci voted on patch set 4 V +1 02/17/25, 19:57 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74267

  3. Christian Kuhn uploaded patch set 5 V +1 02/17/25, 20:40

  4. core-ci voted on patch set 5 V -1 02/17/25, 20:47 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74269

  5. Christian Kuhn uploaded patch set 6 V -1 02/17/25, 21:27

  6. Christian Kuhn updated the commit message in patch set 7 02/17/25, 21:29

  7. Christian Kuhn uploaded patch set 8 02/17/25, 21:32

  8. core-ci voted on patch set 6 V -1 02/17/25, 21:39 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74271

  9. core-ci voted on patch set 7 V -1 02/17/25, 21:41 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74272

  10. core-ci voted on patch set 8 V -1 02/17/25, 21:43 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74273

  11. Christian Kuhn uploaded patch set 9 V -1 02/17/25, 21:44

  12. Christian Kuhn uploaded patch set 10 02/17/25, 21:44

  13. core-ci voted on patch set 9 V -1 02/17/25, 21:51 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74274

  14. core-ci voted on patch set 10 V +1 02/17/25, 21:52 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/74275

  15. Garvin Hicking commented on patch set 8 02/17/25, 21:57

    OrphanRecordsCommand.php:70

    Maybe actually keep this debugging in place for a "isVeryVebose" run, woth an additional mysql/mariadb platform check? (also at the end)

    OrphanRecordsCommand.php:109

    Did you think about maybe doing this woth a batched "uid in (:uidlist)" query? Since each delete will trigger index rebuilding (I guess?) this could speed up the process even some more?

    OrphanRecordsCommand.php:197

    fancypants. does this have a name? Is it a lollisort? It only clicked for me when I understood the "or is unchanged" condition to prevent the endless loop.
  16. Christian Kuhn commented on patch set 10 02/17/25, 22:03

    OrphanRecordsCommand.php:70 Reply

    nah. that was for debugging purposes. the new implementatino is so quick it does not matter, and that query fails on non-mysql.

    OrphanRecordsCommand.php:109 Reply

    imho not worth it: the target is usually small. if it is really huge, you need to eat it, and that should be *very* seldom.

    OrphanRecordsCommand.php:197 Reply

    that's an algorithm detail. please find better naming if you have some. it's not a sorting algorithm, but it's a list of 'allowed' tree uids. i tried to comment on that above.
  17. Benni Mack commented on patch set 10 CR +1 V +1 02/17/25, 22:16

    Thank you for this work!
  18. Garvin Hicking commented on patch set 10 CR +1 02/17/25, 22:19

    OrphanRecordsCommand.php:197 Reply

    All good, I just tipped my hat. It's commented properly, but my brain took a bit of processing time for it :-)
  19. Stefan Bürk voted on patch set 10 CR +1 V +1 02/18/25, 08:35

  20. Sybille Peters commented on patch set 10 02/18/25, 08:35

    OrphanRecordsCommand.php:119

    It is checking a lot of tables where I don't think it makes sense to check them, in particular tables with $GLOBALS['TCA'][$table]['ctrl']['rootLevel'] = 1. In my patch, I specifically excluded them (besides also excluding some specific tables), see https://review.typo3.org/c/Packages/TYPO3.CMS/+/88102/1/typo3/sysext/lowlevel/Classes/Command/OrphanRecordsCommand.php#191 e.g. sys_file, be_groups, be_users, etc. are all on pid=0 and have rootLevel=1 The goal here was to improve performance and prevent accidental deletion, but could also exclude some of those tables, I think.
  21. Sybille Peters commented on patch set 10 V +1 02/18/25, 08:41

    Test:ok. Tested the original problem of accidental deletion by setting a breakpoint after getConnectedAndOphanPages and before collecting orphans and then adding a page and tt_content element. This is detected as orphan but now prevented from deletion by the additional check.
  22. Garvin Hicking voted on patch set 10 V +1 02/18/25, 08:55

  23. Christian Kuhn commented on patch set 10 02/18/25, 11:25

    OrphanRecordsCommand.php:119 Reply

    i would probably refrain from excluding such pages. there is a --dry-run after all which i hinted in the description, users need to use backups for such low level changes anyway, and the performance is so much better that a few tables more or less are not that relevant. if you really want to follow this, we may discuss in another change if that is ok with you.
  24. Christian Kuhn voted on patch set 10 CR +2 V +2 02/18/25, 11:44

  25. Christian Kuhn merged patch set 11 02/18/25, 11:44