Review main Merged

66896[BUGFIX] Handle large number of pages in linkvalidator

Sybille Peters CR +2 V +2 Hard +310 −158 updated 11/28/21, 01:25

Commit message

This patch handles problems which occured with a large number of pages
and also for a large number of records to be checked for broken links.
The problems previously resulted in "Prepared statement contains too
many placeholders" exceptions and consumption of too much RAM and
(possibly) out of memory errors.

Technical details about the implementation follows because this is
handled slightly differently than in other areas of the core.

LinkValidator used named placeholders to build database queries,
and the EditableRestriction class also used a number of placeholder.

Technically 'doctrine/dbal' explodes named placeholders for array
values with quote type 'PARAM_INT_ARRAY' or 'PARAM_STR_ARRAY' to
single unnamed placeholders for each value, using one placeholder
for each array value. If multiple array value placeholder are used
with a large number of values this could quickly emit the "Prepared
statement contains too many placeholders" error as the limit is
reached. The limit differs beetween the various database systems.
One way to work around this issue is to do database operations in
chunks, but it could become quite difficult to calculate the chunk
size beforehand.

Array values are mainly used for 'in()' or 'notIn()' query expressions,
which naturally do not not have direct limits for the value count in it
through all supported dbms systems. This opens the possibility to use
direct value lists for these expressions. There are other dbms
limitations depending on the used dbms (for example on mariadb/mysql
'max_allowed_packets', max query size setting for SQLite), which may be
adjusted in some cases. However we could not safely expect that these
values are monitored and adjusted, so we still need to do database
operations in chunks, but make it a little easier for 'chunksize'
calculations.

During testing on an instance with a large number of pages, it was
found that LinkValidator used up a lot of memory, reaching the
configured memory limit of 2G on that system, which itself was very
high already (and far beyond the recommended 256MB limit).
Instead of collecting unlimited results in an array before checking
links against this array, doing this for each record directly avoids
excessive memory consumption during link checking.

It was decided to do this in this patch directly instead of a separate
patch, as this patch otherwise would not be testable in real
instances.

This patch does the following:

* introduce two method to implode value arrays in QueryHelper
* replace placeholder in EditableRestriction with quoted values
* replace placeholder in BrokenLinksRepository with quoted values
* introduce chunk database operations in BrokenLinkRepository
* introduce chunk database operations in LinkAnalyzer
* move $results array and call checkLinks() on it to the inner
  loop to process this for each record (instead of accumulating the
  results in a huge array.

In the future we need to investigate and find a more generic way to
handle such scenarios for larger instances. This may take some time
and investigation to do this properly and in a reusable way. To not
stall the bugfix further, we are doing the workarounds in this patch
to solve these issues in the meantime.

Review

  • Code review CR +2 Done
    • Christian Kuhn +2
    • Stefan Bürk +1
  • Verified V +2 Done
    • Christian Kuhn +2
    • Stefan Bürk +1
    • core-ci +1 CI

Issues 1

Files 5

Review log 4971

Show 3346 earlier messages
  1. Sybille Peters uploaded patch set 1 11/24/20, 18:08

  2. Sybille Peters wrote 11/24/20, 18:09

    Topic set to Linkvalidator
  3. TYPO3com voted on patch set 1 V +1 11/24/20, 18:19 Completed build in 11m 11s on Tue, 24 Nov, 06:19 PM Test Summary: 32391 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-7554

  4. Sybille Peters rebased onto patch set 2 11/24/20, 18:29

  5. TYPO3com voted on patch set 2 V +1 11/24/20, 18:41 Completed build in 11m 43s on Tue, 24 Nov, 06:41 PM Test Summary: 32391 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-7556

  6. Sybille Peters commented on patch set 2 CR -1 11/26/20, 13:38

    I found another place in core which gets the limit like this: $maxBindParameters = PlatformInformation::getMaxBindParameters( $connection->getDatabasePlatform() ); extensionmanager/Classes/Domain/Repository/BulkExtensionRepositoryWriter.php will change this patch accordingly
  7. Sybille Peters uploaded patch set 3 11/26/20, 13:56

  8. Mathias Brodala commented on patch set 3 11/26/20, 14:06

    LinkAnalyzer.php:157

    I feel like the whole query logic should be moved INSIDE the repository and not expose such low level details. What do you think?

    LinkAnalyzer.php:166

    Can we move this part outside of the loop to do it only once per table just like before?
  9. TYPO3com voted on patch set 3 V -1 11/26/20, 14:07 Completed build in 10m 34s on Thu, 26 Nov, 02:07 PM Test Summary: 114 of 32635 failed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-7602

  10. Sybille Peters commented on patch set 3 11/26/20, 14:29

    LinkAnalyzer.php:157 Reply

    Yes, we already moved a lot of query functionality into BrokenLinkRepository. I just thought this was beyond the scope of this patch. Usually if I create patches which do not just solve 1 thing I get comments to not munch it all together. We could do this separately.
  11. Sybille Peters commented on patch set 3 11/26/20, 14:30

    LinkAnalyzer.php:166 Reply

    That sounds good. There are still errors in the patch. I am working on another version.
  12. Mathias Brodala commented on patch set 3 11/26/20, 14:32

    LinkAnalyzer.php:157 Reply

    And I would probably have been one of the people who complain. ;-) Yes, you are absolutely right, continue with trying to keep this one as small as possible.
  13. Sybille Peters uploaded patch set 4 11/26/20, 14:32

  14. Sybille Peters commented on patch set 4 11/26/20, 14:34

    BrokenLinkRepository.php:43

    PlatformInformation is used, but this is marked as @internal. However, other system extensions also do it this way. Not sure what to do here.
  15. Mathias Brodala commented on patch set 4 11/26/20, 14:42

    BrokenLinkRepository.php:43 Reply

    It's fine to use within TYPO3 system packages since any change here can easily be taken care of by TYPO3 developers. It's just that 3rd party developers should not rely on this.
  16. TYPO3com voted on patch set 4 V +1 11/26/20, 14:43 Completed build in 10m 22s on Thu, 26 Nov, 02:43 PM Test Summary: 32394 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-7603

  17. Sybille Peters commented on patch set 4 11/26/20, 14:50

    LinkAnalyzer.php:166 Reply

    I am not sure if this is a significant performance gain. It would make the code a little more unreadable and makes it more error prone (in future). Maybe there is a better way to do this, but this is what I came up with: if ($considerHidden) { if ($restrictions === null) { $restrictions = $queryBuilder->getRestrictions() ->removeAll() ->add(GeneralUtility::makeInstance(DeletedRestriction::class)) ->getRestrictions(); } else { $queryBuilder->setRestrictions($restrictions); } }
  18. Mathias Brodala commented on patch set 4 11/26/20, 15:59

    LinkAnalyzer.php:166 Reply

    The current state is already good but I don't see how moving the QueryBuilder preparation out of the loop would make it less readable. But I won't insist on this if you have a plan.
  19. Sybille Peters uploaded patch set 5 12/06/20, 15:19

  20. TYPO3com voted on patch set 5 V +1 12/06/20, 15:31 Completed build in 09m 56s on Sun, 6 Dec, 03:31 PM Test Summary: 32413 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-7863

  21. Sybille Peters uploaded patch set 6 11/03/21, 20:31

  22. core-ci voted on patch set 6 V -1 11/03/21, 20:47 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/18829

  23. Sybille Peters rebased onto patch set 7 11/03/21, 20:49

  24. Sybille Peters uploaded patch set 8 11/03/21, 21:05

  25. core-ci voted on patch set 7 V -1 11/03/21, 21:05 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/18831

  26. Sybille Peters rebased onto patch set 9 11/03/21, 21:09

  27. core-ci voted on patch set 8 V +1 11/03/21, 21:25 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/18836

  28. core-ci voted on patch set 9 V +1 11/03/21, 21:56 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/18839

  29. Stefan Bürk commented on patch set 9 CR -1 11/03/21, 22:57

    on it

    LinkAnalyzer.php:155

    type cast not needed, getMaxBindParameters() has strong return type and returns int, further calculation creates an int too. Furthermore we can move that line to the top of the outer foreach. No need for function calls on a object instance in the loop, recreating the same result in each foreach loop.

    LinkAnalyzer.php:157

    unneeded/unintended linebreak

    BrokenLinkRepository.php:179

    We should add parenthesis here to make the calculation clear, without have to re-think mathematiclly operator priority order if someone revisit this code later.

    BrokenLinkRepository.php:233

    Same as above: We should add parenthesis here to make the calculation clear, without have to re-think mathematiclly operator priority order if someone revisit this code later.
  30. Stefan Bürk uploaded patch set 10 11/03/21, 22:57

  31. Stefan Bürk commented on patch set 10 CR +1 11/03/21, 23:06

    LinkAnalyzer.php:155 Reply

    Done

    LinkAnalyzer.php:157 Reply

    Done

    BrokenLinkRepository.php:179 Reply

    Done

    BrokenLinkRepository.php:233 Reply

    Done
  32. Stefan Bürk wrote 11/03/21, 23:07

    Removed Code-Review+1 by Stefan Bürk <stefan@buerk.tech>
  33. core-ci voted on patch set 10 V +1 11/03/21, 23:13 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/18842

  34. Stefan Bürk uploaded patch set 11 11/03/21, 23:19

  35. Stefan Bürk commented on patch set 11 11/03/21, 23:19

    LinkAnalyzer.php:166 Reply

    I'm here with Mathias. Pushed another patch set to address this.
  36. Sybille Peters commented on patch set 11 11/03/21, 23:34

    LinkAnalyzer.php:166 Reply

    What is the status now? The thing is - I wrote this a while ago and now just fixed the conflicts. Also, was not sure if this is the "right" way to do it, see also https://forge.typo3.org/issues/93305
  37. core-ci voted on patch set 11 V +1 11/03/21, 23:36 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/18843

  38. Stefan Bürk uploaded patch set 12 11/05/21, 20:08

  39. Stefan Bürk commented on patch set 12 CR -1 11/05/21, 20:12

    Changed to prepare statements in the outer loop as suggested/mentioned by Mathias Brodala. Tests only triggers first case (even if I trim down the max statement manually ). So the the continued execution are *not* tested by me now. Have to think about it how to make a test without inserting 10th tousands of records in a testcase. @Sybille maybe you have a larger installation for a first manual test of this in generall ?
  40. core-ci voted on patch set 12 V +1 11/05/21, 20:32 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/18969

  41. Sybille Peters uploaded patch set 13 11/06/21, 12:57

  42. Sybille Peters commented on patch set 13 CR -1 11/06/21, 13:03

    @Stefan Tested with my site with 39403 pages. It crashed because I forgot to also chunk in BrokenLinkRepository::getNumberOfBrokenLinksForRecordsOnPages(). We need to chunk all occurrances with QueryBuilder and ->in() large list of pids. I applied same procedure to getNumberOfBrokenLinksForRecordsOnPages(), but I noticed there is an EditableRestriction there which may also add named parameters. I added some todos to address that and other things in the commit message. In order to test manually, you have to load the list (with "infinite" and large number of tables) AND also check links with "infinite" and large number of pages - I don't really want to do that because will check all external URLs - may put a block on that first (proxy?).
  43. core-ci voted on patch set 13 V +1 11/06/21, 13:10 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/18981

  44. Sybille Peters commented on patch set 13 11/06/21, 13:12

    Reply

    > AND also check links with "infinite" and large number of pages - I don't really want to do that because will check all external URLs - may put a block on that first (proxy?). ... or use debugger and set breakpoint. It will generate the list of pages
  45. Sybille Peters commented on patch set 13 11/16/21, 16:39

    .phpunit.result.cache:1

    This file should be removed.
  46. Stefan Bürk commented on patch set 13 CR -1 11/17/21, 13:44

    That whole chunking is a solution around a design flaw in dbal (in my opionen). And in combination with placeholder in EditableRestriction this is not feasonable. As these code parts do not really benefit from prepared statements, using placeholder is generally not false, but with the flaw in dbal not really handable. I'm on it, currently testing an alternative and will incorporate it here later.
  1. Benni Mack marked it work in progress 11/18/21, 10:51

  2. Stefan Bürk uploaded patch set 14 11/19/21, 19:03

  3. core-ci voted on patch set 14 V +1 11/19/21, 19:17 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19571

  4. Stefan Bürk uploaded patch set 15 11/19/21, 21:56

  5. Stefan Bürk uploaded patch set 16 11/19/21, 22:08

  6. core-ci voted on patch set 15 V +1 11/19/21, 22:09 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19579

  7. core-ci voted on patch set 16 V +1 11/19/21, 22:23 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19580

  8. Sybille Peters commented on patch set 16 V +1 11/20/21, 08:28

    This now tests perfectly and fixes not just the initial problem but some more, including a pretty ugly memory hog problem which will eat up memory before dying ungracefully. The memory hog was previously prevented by the "too many placeholder" problem, because that crashed prematurely, but MUST be fixed with this patch - otherwise we make the problem worse. I tested the following: 1. load linkvalidator in BE with large number of pages (my site where the original problem with "too many placeholders") is reproducible 2. check small number of pages / links in BE 3. deactivate external links and check entire site via scheduler / cli: typo3/sysext/core/bin/typo3 scheduler:run -i 55 -f The problems that are solved are: 1."too many placeholders" (as described in issue) 2. crash with out of memory (with a memory limit of 2G!) when checking links because everything was written into large array, see https://review.typo3.org/c/Packages/TYPO3.CMS/+/66896/16/typo3/sysext/linkvalidator/Classes/LinkAnalyzer.php#188 3. another DB limitation which I can't remember right now I am also confident this is a good solution as I already made similar changes to "brofix" which has been runnning for months on large site without problems and my implementation (which worked for my site) has now even been improved further by Stefan to also handle some more possible problems and edge cases (which did not apply on my site). I will also code review later ...
  9. Stefan Bürk uploaded patch set 17 11/21/21, 14:57

  10. core-ci voted on patch set 17 V +1 11/21/21, 15:13 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19606

  11. Stefan Bürk commented on patch set 17 11/21/21, 15:14

    .phpunit.result.cache:1 Reply

    Done
  12. Sybille Peters marked it ready for review 11/21/21, 15:15

  13. Sybille Peters voted on patch set 17 V +1 11/21/21, 15:19

  14. Sybille Peters updated the commit message in patch set 18 11/21/21, 16:21

  15. Sybille Peters voted on patch set 18 V +1 11/21/21, 16:22

  16. core-ci voted on patch set 18 V +1 11/21/21, 16:36 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19610

  17. Stefan Bürk wrote 11/21/21, 21:38

    Removed reviewer Stefan Bürk.
  18. Christian Kuhn uploaded patch set 19 11/27/21, 23:53

  19. core-ci voted on patch set 19 V -1 11/28/21, 00:10 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19912

  20. core-ci commented on patch set 19 11/28/21, 00:17 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19912

  21. core-ci commented on patch set 19 11/28/21, 00:42 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19912

  22. core-ci voted on patch set 19 V +1 11/28/21, 01:21 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19912

  23. Stefan Bürk voted on patch set 19 V +1 CR +1 11/28/21, 01:25

  24. Christian Kuhn voted on patch set 19 V +2 CR +2 11/28/21, 01:25

  25. Christian Kuhn merged patch set 20 11/28/21, 01:25