Issue Bug Closed

#98350MySql 8 performance reloading page tree on Workspace

Danilo Caccialanza 09/15/22, 11:51 updated 11/22/24, 16:33

Description

I have a TYPO3 v10 platform with 9 workspaces which contain about ca. 3000 pages for each workspace.

There is a performance problem if I migrate TYPO3 v10 databases from MySQL 5.6 to MySQL 8.0.

Reloading page tree on Workspace it takes ca. 3500 ms with MySql 8.0 (the same operation in mysql5 takes us ca. 600 ms).

Also in general TYPO3 v10 on mysql 8.0 is slower than on MySql 5.6.

We analyzed the problem with DBA experts and powerful monitoring software.

We have found that in some cases using the “groub by” clause is a performance killer, you should use the “ditinct” clause instead.

Here is an example:

Original query:

select `B`.`pid` as `pageId` from `tt_content` `B` 
 where ((`B`.`uid` in (select `A`.`t3ver_oid` from `tt_content` `A`
where (`A`.`t3ver_oid` > 0) and (`A`.`t3ver_wsid` = 2) and (`A`.`t3ver_state` <> 4) and (`A`.`deleted` = '*****'))) 
 or (`B`.`t3ver_move_id` in 
 (select `A`.`t3ver_oid` from `tt_content` `A` where (`A`.`t3ver_oid` > 0) and (`A`.`t3ver_wsid` = 2) and (`A`.`t3ver_state` = 4) and (`A`.`deleted` = '*****')))) 
 and (`B`.`deleted` = '*****') group by `B`.`pid`;


elapsed 0.172 secondi

Query with distinct without group by:

select distinct `B`.`pid` as `pageId` from `tt_content` `B` 
 where ((`B`.`uid` in (select `A`.`t3ver_oid` from `tt_content` `A`
where (`A`.`t3ver_oid` > 0) and (`A`.`t3ver_wsid` = 2) and (`A`.`t3ver_state` <> 4) and (`A`.`deleted` = '*****'))) 
 or (`B`.`t3ver_move_id` in 
 (select `A`.`t3ver_oid` from `tt_content` `A` where (`A`.`t3ver_oid` > 0) and (`A`.`t3ver_wsid` = 2) and (`A`.`t3ver_state` = 4) and (`A`.`deleted` = '*****')))) 
 and (`B`.`deleted` = '*****')

elapsed di 0.079 secondi

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

History 10

  1. Stefan Bürk commented <span data-processor="relativetime" data-value="2022-09-17T16:16:44+00:00" > 09/17/22, 16:16 </span>

    Are you sure that this query is build by core ? Tried to find the place where this query is build in main, 11.5 and 10.4 – no luck, maybe I’m searching wrong.

    However, the query looks kind weired … checking ‘*****’ as equal condition for tt_content.deleted ? Does not make much sense …

    Do you have by any change some custom extensions or 3rd party extension hooking into the system doing that query ?

  2. Danilo Caccialanza commented <span data-processor="relativetime" data-value="2022-09-18T06:15:06+00:00" > 09/18/22, 06:15 </span>

    Thank you for your answer.
    I have tried to uninstall all third party extensions but the performance issue remains.
    The query I wrote in the post I extrapolated from the detabase monitoring software and gave it to me in this format syntax.

  3. Danilo Caccialanza commented <span data-processor="relativetime" data-value="2022-09-27T14:38:36+00:00" > 09/27/22, 14:38 </span>

    The most likely explanation for this problem is the removal of support for the query cache.

    “The query cache can mask performance problems caused by missing indexes”

    [[https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/]]

    I tried to migrate my db to postgres and mariadb as well.

    The result of this test is that a typo3 database with a very large tree using workspaces has significant performance problems (in BE reloading page tree).

  4. Stefan Bürk commented and changed assigned to Stefan Bürk <span data-processor="relativetime" data-value="2022-10-08T01:22:44+00:00" > 10/08/22, 01:22 </span>

    The problem is still to find the place in core where this query is build. Which as already stated seems wrong at all with the deleted and *****
    However will try to find it in another session.

    EDIT Following may be a candidate for it:

    TYPO3\CMS\Workspaces\Service\WorkspaceService->fetchPagesWithVersionsInTable()

    That method has been changed v10 -> v11. I would guess because of Workspace related changes. As v10 is in security bugfix mode, I’m not sure if there will be a v10 only fix for that.

    Do you have somehow the ability to test this with a v11 instance somehow ?

    Simply using the v11 method will not work I think.

  5. Danilo Caccialanza commented <span data-processor="relativetime" data-value="2022-10-12T12:11:17+00:00" > 10/12/22, 12:11 </span>

    Thank you for your answer.

    I did a test with TYPO3 v11 and the performance improves a lot.

    With v10 reloading page tree in the workspace takes 4000 milliseconds while with v11 1200 milliseconds.

    For comparison with mysql 5 reloading page tree takes 500 milliseconds.

    Probably the performance of the workspaces could still be improved in any case :)

  6. Benni Mack commented and changed status New Needs Feedback <span data-processor="relativetime" data-value="2022-11-08T12:28:57+00:00" > 11/08/22, 12:28 </span>

    Do you have tests for v11? Is v11 faster than v10 now? I think this could relate to Doctrine/DBAL which is shipped with TYPO3 v11 by default, which might handle MySQL 8 better.

  7. Danilo Caccialanza commented <span data-processor="relativetime" data-value="2022-11-08T13:27:37+00:00" > 11/08/22, 13:27 </span>

    With TYPO3 v11 it is better but not enough yet.
    We are still debugging and collecting information to share.
    We are available to share the database.

  8. Benni Mack commented <span data-processor="relativetime" data-value="2024-11-22T14:58:39+00:00" > 11/22/24, 14:58 </span>

    Hey Danilo,

    any progress in v12 or v13 (where we rebiult the tree)?

  9. Danilo Caccialanza commented <span data-processor="relativetime" data-value="2024-11-22T16:32:50+00:00" > 11/22/24, 16:32 </span>

    Hey Benny,

    With versions v12 and v13, we no longer encounter any issues with the tree loading. I believe this ticket can be closed.
    Thanks for your interest!

  10. Benni Mack commented and changed status Needs Feedback Closed <span data-processor="relativetime" data-value="2024-11-22T16:33:20+00:00" > 11/22/24, 16:33 </span>

    Thanks, I will close this issue then! Great to hear.