Review main Merged

83729[TASK] Use refindex in RootlineUtility, drop addRootLineFields

Christian Kuhn CR +2 V +2 Hard +544 −125 updated 04/29/24, 07:42

Commit message

tl;dr:

This patch switches from using RelationHandler to ReferenceIndex
in (mostly) frontend request related RootlineUtility. It is the
first patch actively using ReferenceIndex in frontend and has
the potential to significantly reduce database query load on
not-yet-cached pages in frontend, while creating better results
at the same time. The patch obsoletes
$GLOBALS['TYPO3_CONF_VARS']['FE']['addRootLineFields']. This
is just the tip of the iceberg from a refactoring chain that has
been prepared over a period of about three years already.

The long story:

Rendering times of complex pages in the frontend in general scale
linearly with the number of executed database queries (plus some
offset), if no extension needs extensive calculation on the PHP side,
even if the database is server-side local and not a remote instance,
which adds additional TCP round-trip times per query.
With thousands of queries being triggered by RootlineUtility, this
area is the most promising code path to optimize when response times
of not-fully-cached pages should be reduced.

RootlineUtility is *the* main performance bottleneck in
non-trivial TYPO3 instance frontend rendering: When rendering menus,
each single entry needs to know its page row details and its path in
the rootline, using RootlineUtility. This can quickly lead to
thousands of DB queries with bigger instances on cold caches,
catapulting the rendering times from milliseconds to seconds. This is
worse when dealing with language overlays and workspaces.

RootlineUtility does two main things:
It first resolves the rootline of the given pages-uid. This is
an expensive operation since it needs a query per page level.
We'll change this towards a recursive query soon. Afterwards, it
resolves some relations attached to the pages. This is - in default
instances - at least the 'media' field. This is done using
class RelationHandler per single field in question, which fires
at least one query per page row and per field.

As such, beside the expensive rootline determination itself, we also
have an expensive operation to resolve relations of each pages row.

Resolving relations attached to pages in a more performant way
is what this patch is about.

The list of relations being resolved can be extended using
$GLOBALS['TYPO3_CONF_VARS']['FE']['addRootLineFields']. By default,
only field 'media' is resolved, resulting in one additional query
per resolved page row. Instances tend to add more relations to
pages, and when adding these to 'addRootLineFields', they trigger
additional RelationHandler queries by RootlineUtility. Also,
RootlineUtility *ignores* 'addRootLineFields' when resolving
relations when requesting non-default (sys_language_uid != 0) pages,
and simply resolves *all* possible relation fields. This includes
'category' and probably some of the TCA fields ext:seo adds to pages
TCA. RelationHandler needs *at least* one query per field, more with
localizations and even more when rendering a workspaces preview.

This situation makes relation resolving of page rows in
RootlineUtility more expensive than resolving the rootline itself.
One option would be to skip resolving page relations in
RootlineUtility, but that wouldn't help us much when instances
actually need this data (which is likely).
A better solution is to supply relations within pages in a more
performant way, ready for TypoScript and extensions to use.

The patch switches from using RelationHandler within
RootlineUtility to query the ReferenceIndex instead: sys_refindex
contains all fields relevant for frontend since #103748. It allows
querying *all* relations of *all* page fields with just *one* query.

This allows us to remove 'addRootLineFields' by just always
resolving all relations with close to no overhead, and turns into
an advantage for integrators: One detail less to take care of.

The patch establishes a nifty - but very well indexed - query within
RootlineUtility to fetch relations. With 'categories' (an MM foreign
relation) being attached to pages by default, it is a UNION query of
two SELECT queries, with their result being sorted out in a rather
effective result handling loop.

The patch comes with a todo list within RootlineUtility, but in
general, the result list is *better* than the result of the previous
single field based RelationHandler solution. This is verified by the
previously establish test data sets:
* hidden, starttime, endtime are resolved.
* Workspace deleted placeholder rows are removed automatically.

There are a couple of details we may want to improve with further
v13 patches, but in general, this new approach should work well
already.

Future:

* This patch is a base to unlock a series of further performance
  related changes: When CTE's on RootlineUtility are established, it
  will allow fetching relations of not only one page, but of multiple
  pages with one query. Combined with a couple of other changes, the
  query load in frontend will ultimately collapse.

* The patch is the base for ReferenceIndex usage in the frontend:
  We can potentially remove the doomed DatabaseQueryProcessor and
  other shenanigans for tt_content within the frontend rendering
  chain by always providing "correct" relation information
  automatically without overhead. Other pending patches are already
  preparing a central place taking care of tt_content for its
  relations to be resolved in a similar effective way.

* Resolving relations of all fields attached to page rows voids the
  'parent count' values of these fields. Those are a different
  de-normalization of relations "if it's not 0, we have some". With
  RootlineUtility dropping 'addRootLineFields' and always resolving
  relations, we can finally start dropping these 'count' fields, can
  release DataHandler from updating these fields, and can remove
  them from DB altogether.

Numbers:

There was a recent blog post by Julian [1] looking at query load of
bigger menus. With measurements for this patch, we confirm the
general numbers Julian came up with in his test setup. Thank to the
published test setup at [2], we ported it to TYPO3 v13, and slightly
extended it to measure query load when all pages are localized, and
we're performing a request to a non-default language.

* The main query count on a "cold" instance calling default language
  does *not* change with this patch. The reason is, default TYPO3
  includes only 'media' as relation that has a 'foreign_field' in
  RootlineUtility, which leads to only one query, which is now
  substituted by the new refindex query. The advantage of the new
  solution provided by the patch is to resolve *all* relation with
  the same query load.

* When calling a localized page, the query load is at ~10200 queries
  on a non-cached-page frontend request. This shrinks to ~7900 with
  the patch being applied. That's a reduction of 1/5 by this patch,
  with further options up our sleeves.

[1] https://www.in2code.de/aktuelles/menue-vergleich-der-techniken/
[2] https://github.com/julianhofmann/typo3-menu-comparison

Review

  • Code review CR +2 Done
    • Christian Kuhn +2
    • Anja Leichsenring +1
    • Stefan Bürk +1
    • André Buchmann +1
    • Benni Mack +1
  • Verified V +2 Done
    • Christian Kuhn +2
    • Anja Leichsenring +1
    • Stefan Bürk +1
    • André Buchmann +1
    • Benni Mack +1
    • core-ci +1 CI

Issues 7

Files 9

Review log 70124

Show 5299 earlier messages
  1. Christian Kuhn uploaded patch set 1 04/10/24, 11:17

  2. core-ci voted on patch set 1 V -1 04/10/24, 11:25 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55678

  3. Christian Kuhn uploaded patch set 2 V -1 04/10/24, 11:25

  4. core-ci voted on patch set 2 V +1 04/10/24, 11:30 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55680

  5. Christian Kuhn uploaded patch set 3 V +1 04/10/24, 11:47

  6. core-ci voted on patch set 3 V +1 04/10/24, 11:52 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55681

  7. Christian Kuhn uploaded patch set 4 V +1 04/10/24, 13:09

  8. core-ci voted on patch set 4 V +1 04/10/24, 13:14 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55682

  9. Christian Kuhn uploaded patch set 5 V +1 04/11/24, 07:14

  10. core-ci voted on patch set 5 V +1 04/11/24, 07:19 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55735

  11. Christian Kuhn uploaded patch set 6 V +1 04/12/24, 09:13

  12. core-ci voted on patch set 6 V +1 04/12/24, 09:46 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55845

  13. Christian Kuhn uploaded patch set 7 V +1 04/12/24, 13:27

  14. core-ci voted on patch set 7 V +1 04/12/24, 13:33 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55872

  15. Christian Kuhn uploaded patch set 8 V +1 04/12/24, 15:40

  16. core-ci voted on patch set 8 V +1 04/12/24, 15:47 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55875

  17. Nikita Hovratov commented on patch set 8 04/12/24, 23:15

    RootlineUtility.php:349

    just a note: type select doesn't have the option `foreign_field`. Could be cleaned up.
  18. Christian Kuhn uploaded patch set 9 V +1 04/14/24, 08:45

  19. core-ci voted on patch set 9 V +1 04/14/24, 08:51 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55940

  20. Christian Kuhn commented on patch set 9 04/14/24, 10:27

    RootlineUtility.php:349 Reply

    thx. i see. i'll have a closer look at this when time is right.
  21. Christian Kuhn uploaded patch set 10 V +1 04/14/24, 10:35

  22. core-ci voted on patch set 10 V +1 04/14/24, 10:40 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55941

  23. Christian Kuhn uploaded patch set 11 V +1 04/14/24, 12:26

  24. core-ci voted on patch set 11 V +1 04/14/24, 12:31 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55948

  25. Christian Kuhn uploaded patch set 12 V +1 04/14/24, 13:16

  26. core-ci voted on patch set 12 V +1 04/14/24, 13:21 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55952

  27. Christian Kuhn uploaded patch set 13 V +1 04/15/24, 06:47

  28. core-ci voted on patch set 13 V +1 04/15/24, 06:53 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55979

  29. Christian Kuhn uploaded patch set 14 V +1 04/15/24, 08:54

  30. core-ci voted on patch set 14 V +1 04/15/24, 08:58 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55985

  31. Christian Kuhn uploaded patch set 15 V +1 04/15/24, 11:24

  32. core-ci voted on patch set 15 V -1 04/15/24, 11:30 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55989

  33. Christian Kuhn uploaded patch set 16 V -1 04/15/24, 11:34

  34. core-ci voted on patch set 16 V +1 04/15/24, 11:39 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/55990

  35. Christian Kuhn uploaded patch set 17 V +1 04/16/24, 12:58

  36. core-ci voted on patch set 17 V +1 04/16/24, 13:03 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56081

  37. Christian Kuhn uploaded patch set 18 V +1 04/16/24, 14:00

  38. core-ci voted on patch set 18 V +1 04/16/24, 14:05 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56088

  39. Christian Kuhn uploaded patch set 19 V +1 04/16/24, 15:35

  40. core-ci voted on patch set 19 V +1 04/16/24, 15:41 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56092

  41. Christian Kuhn uploaded patch set 20 V +1 04/17/24, 09:49

  42. core-ci voted on patch set 20 V +1 04/17/24, 09:55 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56144

  43. Christian Kuhn commented on patch set 20 04/17/24, 11:48

  44. Christian Kuhn uploaded patch set 21 V +1 04/17/24, 13:50

  45. core-ci voted on patch set 21 V +1 04/17/24, 13:55 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56167

  46. Christian Kuhn uploaded patch set 22 V +1 04/18/24, 13:16

  47. core-ci voted on patch set 22 V +1 04/18/24, 13:24 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56264

  48. Christian Kuhn commented on patch set 22 04/19/24, 07:52

    set 22 should extract the given RU tests as pre-patch.
  49. Christian Kuhn uploaded patch set 23 V +1 04/19/24, 07:58

  50. core-ci voted on patch set 23 V -1 04/19/24, 08:05 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56308

  51. Christian Kuhn uploaded patch set 24 V -1 04/19/24, 20:20

  52. core-ci voted on patch set 24 V -1 04/19/24, 20:28 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56360

  53. Christian Kuhn uploaded patch set 25 V -1 04/22/24, 08:24

  54. core-ci voted on patch set 25 V -1 04/22/24, 08:35 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56476

  55. Christian Kuhn uploaded patch set 26 V -1 04/22/24, 23:17

  56. core-ci voted on patch set 26 V -1 04/22/24, 23:22 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56514

  57. Christian Kuhn uploaded patch set 27 V -1 04/23/24, 07:12

  58. core-ci voted on patch set 27 V -1 04/23/24, 07:34 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56548

  59. Christian Kuhn uploaded patch set 28 V -1 04/23/24, 09:54

  60. core-ci voted on patch set 28 V -1 04/23/24, 10:02 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56559

  61. Christian Kuhn uploaded patch set 29 V -1 04/23/24, 14:18

  62. core-ci voted on patch set 29 V -1 04/23/24, 14:26 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56585

  63. Christian Kuhn uploaded patch set 30 V -1 04/24/24, 11:47

  64. core-ci voted on patch set 30 V -1 04/24/24, 11:55 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56658

  65. Christian Kuhn uploaded patch set 31 V -1 04/25/24, 07:43

  66. core-ci voted on patch set 31 V -1 04/25/24, 07:50 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56690

  67. Christian Kuhn uploaded patch set 32 V -1 04/25/24, 10:53

  68. core-ci voted on patch set 32 V -1 04/25/24, 10:59 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56694

  69. Christian Kuhn uploaded patch set 33 V -1 04/25/24, 11:32

  70. core-ci voted on patch set 33 V -1 04/25/24, 11:38 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56697

  71. Christian Kuhn uploaded patch set 34 V -1 04/25/24, 12:12

  72. core-ci voted on patch set 34 V +1 04/25/24, 12:17 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56700

  73. Christian Kuhn uploaded patch set 35 V +1 04/26/24, 08:43

  74. core-ci voted on patch set 35 V +1 04/26/24, 08:48 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56759

  75. Christian Kuhn uploaded patch set 36 V +1 04/26/24, 08:54

  76. core-ci voted on patch set 36 V +1 04/26/24, 08:59 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56761

  77. Christian Kuhn uploaded patch set 37 V +1 04/26/24, 10:52

  78. core-ci voted on patch set 37 V -1 04/26/24, 10:57 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56773

  79. Christian Kuhn uploaded patch set 38 V -1 04/26/24, 10:58

  80. core-ci voted on patch set 38 V +1 04/26/24, 11:03 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56774

  81. Christian Kuhn uploaded patch set 39 V +1 04/26/24, 11:20

  82. core-ci voted on patch set 39 V +1 04/26/24, 11:25 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56776

  83. Christian Kuhn uploaded patch set 40 V +1 04/26/24, 12:29

  84. core-ci voted on patch set 40 V +1 04/26/24, 12:34 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56778

  85. Christian Kuhn uploaded patch set 41 V +1 04/26/24, 22:43

  86. core-ci voted on patch set 41 V +1 04/26/24, 22:48 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56815

  87. Christian Kuhn uploaded patch set 42 V +1 04/27/24, 14:35

  88. core-ci voted on patch set 42 V +1 04/27/24, 14:42 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56856

  89. Christian Kuhn uploaded patch set 43 V +1 04/28/24, 09:40

  90. core-ci voted on patch set 43 V +1 04/28/24, 09:45 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56907

  91. Christian Kuhn uploaded patch set 44 V +1 04/28/24, 10:28

  92. core-ci voted on patch set 44 V +1 04/28/24, 10:32 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56908

  93. Christian Kuhn uploaded patch set 45 V +1 04/28/24, 12:03

  94. core-ci voted on patch set 45 V +1 04/28/24, 12:08 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56913

  95. Christian Kuhn uploaded patch set 46 V +1 04/28/24, 12:10

  96. core-ci voted on patch set 46 V +1 04/28/24, 12:15 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56915

  97. Christian Kuhn updated the commit message in patch set 47 V +1 04/28/24, 14:11

  98. core-ci voted on patch set 47 V +1 04/28/24, 14:16 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56917

  99. Oliver Klee updated the commit message in patch set 48 V +1 04/28/24, 14:27

  1. Christian Kuhn updated the commit message in patch set 49 04/28/24, 14:33

  2. core-ci voted on patch set 48 V +1 04/28/24, 14:34 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56920

  3. Christian Kuhn updated the commit message in patch set 50 04/28/24, 14:35

  4. core-ci voted on patch set 50 V +1 04/28/24, 14:40 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56922

  5. core-ci voted on patch set 49 V +1 04/28/24, 14:42 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56921

  6. Christian Kuhn updated the commit message in patch set 51 V +1 04/28/24, 14:43

  7. core-ci voted on patch set 51 V +1 04/28/24, 14:48 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56923

  8. Christian Kuhn updated the commit message in patch set 52 V +1 04/28/24, 15:14

  9. Stefan Bürk voted on patch set 51 CR +1 V +1 04/28/24, 15:14

  10. core-ci voted on patch set 52 V +1 04/28/24, 15:19 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56924

  11. Stefan Bürk voted on patch set 52 CR +1 V +1 04/28/24, 15:20

  12. Nikita Hovratov voted on patch set 52 CR +1 04/28/24, 17:00

  13. Simon Schaufelberger updated the commit message in patch set 53 CR +1 V +1 04/28/24, 17:01

  14. core-ci voted on patch set 53 V +1 04/28/24, 17:09 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56925

  15. Anja Leichsenring voted on patch set 53 CR +1 V +1 04/28/24, 17:31

  16. Stefan Bürk voted on patch set 53 CR +1 V +1 04/28/24, 17:48

  17. Albrecht Köhnlein commented on patch set 53 04/28/24, 18:39

    post
  18. Stefan Bürk updated the commit message in patch set 54 CR +1 V +1 04/28/24, 18:42

  19. Stefan Bürk commented on patch set 54 CR +1 V +1 04/28/24, 18:42

    Reply

    Done
  20. core-ci voted on patch set 54 V +1 04/28/24, 18:47 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56929

  21. André Buchmann voted on patch set 54 CR +1 V +1 04/28/24, 19:18

  22. Anja Leichsenring voted on patch set 54 CR +1 V +1 04/28/24, 19:40

  23. Benni Mack voted on patch set 54 CR +1 V +1 04/29/24, 07:32

  24. Christian Kuhn voted on patch set 54 CR +2 V +2 04/29/24, 07:38

  25. Christian Kuhn merged patch set 55 04/29/24, 07:42