Project

General

Profile

Actions

Bug #98350

open

MySql 8 performance reloading page tree on Workspace

Added by Danilo Caccialanza almost 2 years ago. Updated over 1 year ago.

Status:
Needs Feedback
Priority:
Should have
Assignee:
Category:
Workspaces
Target version:
-
Start date:
2022-09-15
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
10
PHP Version:
7.4
Tags:
mysql mysql8 8 performance database workpsaces
Complexity:
Is Regression:
Sprint Focus:

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

Actions #1

Updated by Stefan Bürk almost 2 years ago

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 ?

Actions #2

Updated by Danilo Caccialanza almost 2 years ago

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.

Actions #3

Updated by Danilo Caccialanza over 1 year ago

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).

Actions #4

Updated by Stefan Bürk over 1 year ago

  • Assignee set to Stefan Bürk

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.

Actions #5

Updated by Danilo Caccialanza over 1 year ago

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 :)

Actions #6

Updated by Benni Mack over 1 year ago

  • Status changed from New to Needs Feedback

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.

Actions #7

Updated by Danilo Caccialanza over 1 year ago

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.

Actions

Also available in: Atom PDF