Review main Abandoned

72243[BUGFIX] Reduce memory usage in row updater

Sybille Peters CR 0 V 0 Hard +250 −57 updated 12/03/21, 09:44

Commit message

Previously, DatabaseRowsUpdateWizard fetched all rows for each table.
This could result in out of memory situations for a large number of
records if the fetched content exceeded the available memory limit
for PHP.

The database records are now fetched in chunks of 1000. Exact
calculations for available memory and required memory for each
row are difficult, but it is now much less likely that
a memory limit is reached.

If the row updater upgrade wizard does abort, it is possible to
resume by calling the row updater again. For this reason, the
current table and uid were stored in the registry. This way it
is possible for the row updater to resume (roughly) where he left
off. This was already the case before the patch and has not changed.

Reasons for aborting may be max execution time or (still) memory
limit (though the memory limit problem is now less likely).

Additionally, this patch adds tests to check the new behaviour.

Review

  • Code review CR 0
  • Verified V 0

Issues 1

Files 5

Review log 1724

  1. Sybille Peters uploaded patch set 1 11/20/21, 19:59

  2. Sybille Peters marked it work in progress 11/20/21, 19:59

  3. core-ci voted on patch set 1 V +1 11/20/21, 20:13 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19592

  4. Sybille Peters uploaded patch set 2 11/27/21, 13:06

  5. Sybille Peters updated the commit message in patch set 3 11/27/21, 13:10

  6. Sybille Peters uploaded patch set 4 11/27/21, 13:15

  7. Sybille Peters marked it ready for review 11/27/21, 13:26

  8. Sybille Peters uploaded patch set 5 11/27/21, 13:47

  9. core-ci voted on patch set 2 V -1 11/27/21, 14:24 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19855

  10. core-ci voted on patch set 3 V -1 11/27/21, 14:24 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19856

  11. core-ci voted on patch set 4 V -1 11/27/21, 14:24 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19858

  12. core-ci voted on patch set 5 V -1 11/27/21, 14:27 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19861

  13. Sybille Peters uploaded patch set 6 11/27/21, 15:36

  14. core-ci voted on patch set 6 V +1 11/27/21, 15:56 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/19871

  15. Stefan Bürk commented on patch set 6 11/27/21, 16:13

    Thanks for the patch, nice work, but minor things. Please see my comments, hopefully they explains themself.

    DatabaseRowsUpdateWizard.php:219

    'getRowsForTable()' will always return a statement, better check here for rowCount() as early break condition. Another solution would be to check directly in 'getRowsForTable()' the row count and return null of no records are found, which would make this break check also superfluous. That will also make '$hasResults' superfluous in L220, L222 and the break condition at the end. (L281-284)

    DatabaseRowsUpdateWizard.php:220 Unresolved

    Superfluous - see comment above;

    DatabaseRowsUpdateWizard.php:222 Unresolved

    Superfluous - see comment above;

    DatabaseRowsUpdateWizard.php:284 Unresolved

    Superfluous - see comment above;

    DatabaseRowsUpdateWizard.php:325 Unresolved

    Eventually check here if resultset has records, and if not return 'null' instead, thus early breaking loop in L216, making all further checks as commented above superfluous. Further check if it is a result set (ResultStatement), as we do not want statement here (it should be executed). As that, we cann add real return typehint of '?ResultStatement' here.
  16. Stefan Bürk commented on patch set 6 CR -1 11/27/21, 18:13

    Leave my future comments for now. -1 for now, explanation will follow.
  17. Stefan Bürk commented on patch set 6 11/27/21, 19:06

    Reply Unresolved

    -1 stays. I have done 4 test runs with the hog test from patchset 1, which verifies what I have thought and is now proven. ``` MAX_ROWS: 300000 (3 Hunderttausend ) [memory_limit: 2G] ======================================================= Peak memory usage (Start of test): 129MB [LIMIT: 2G] Peak memory usage (After database insertion of 300000 records): 129MB [LIMIT: 2G] Peak memory usage (After row updater): 129MB [LIMIT: 2G] MAX_ROWS: 3000000 (3 Millionen) [memory_limit: 2G] ================================================== Peak memory usage (Start of test): 129MB [LIMIT: 2G] Peak memory usage (After database insertion of 3000000 records): 129MB [LIMIT: 2G] Peak memory usage (After row updater): 129MB [LIMIT: 2G] MAX_ROWS: 10000000 (10 Millionen) [memory_limit: 2G] ==================================================== Peak memory usage (Start of test): 129MB [LIMIT: 2G] Peak memory usage (After database insertion of 10000000 records): 129MB [LIMIT: 2G] Peak memory usage (After row updater): 129MB [LIMIT: 2G] MAX_ROWS: 10000000 (10 Millionen) [memory_limit: 256M] ====================================================== Peak memory usage (Start of test): 129MB [LIMIT: 256M] Peak memory usage (After database insertion of 10000000 records): 129MB [LIMIT: 256M] Peak memory usage (After row updater): 129MB [LIMIT: 256M] ``` Mainloop should and is not memory hungry in the way it is implemented (which was the main topic of this patch / commit message). A very rare condition could be that the database takes lonker than 'max_execution_time' to generate a resultset, even if results are loaded one by one without taking memory. But if someone has so many records, they should either increase the execution time (NOT RECOMMENDED) or do the updates from command line anyway. As doing it from the browser can be a "endless" task. So, it is interessting where your memory issue had come from, maybe from implementing class handler doing row based updates, which holds/gather states ? This should be found and cleaned there, not in the mainloop. Do you have more information or can you retest and debug which is executing and taking the memory ? But changing the mainloop here is not the goal, and I vote against it to change it as it do no really fix the issue, but work around it. Which is not a solution.
  18. Sybille Peters commented on patch set 6 11/27/21, 19:20

    DatabaseRowsUpdateWizard.php:219 Reply Unresolved

    Yes, I know this is awkward. I had already struggled with that. I wanted to get a working solution, but it is not pretty. I was not sure how to safely check if the result has 0 rows. Will look into it further ... I had this before in the getRowsForTable function: if (!$statement || ($statement instanceof Result && $statement->rowCount() === 0) || ($statement instanceof ResultStatement && $statement->columnCount() === 0) || ($statement instanceof Statement && $statement->rowCount() === 0) ) { // empty result return 0; }
  19. Sybille Peters marked it work in progress 11/27/21, 23:01

  20. Benni Mack wrote 11/29/21, 10:19

    Change destination moved from master to main
  21. Benni Mack updated the commit message in patch set 7 11/29/21, 14:06

  22. Sybille Peters commented on patch set 7 CR -1 11/29/21, 15:06

    Was so far only reproducible on my development system. Seems to be a local issue. Would like to keep as WIP to investigate.
  23. core-ci voted on patch set 7 V +1 11/29/21, 16:43 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/20089

  24. Sybille Peters abandoned the change 12/03/21, 09:44

    Memory problem was most likely local issue. Would be interesting to analyze further but should not clutter up core queue