Bug #77618

Workspace PageTree can become super-slow

Added by Robert Vock almost 3 years ago. Updated 10 months ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Workspaces
Target version:
-
Start date:
2016-08-24
Due date:
% Done:

0%

TYPO3 Version:
7
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

The PageTree in workspaces gets a different style for all pages, that contain changes. This is a helpful feature to quickly see, which pages have changes.

But with a larger TYPO3 installation this feature can become unusable and prevent the page tree from loading. This happens because for each page in the page tree and each table in the TCA a MySQL query is executed:
NumberOfPages * NumberOfTablesWithWorkspaceOverlay = NumberOfAdditionalQueries.

We have an installation with 22 Tables with workspace overlay: 10 Core-Tables, 1-Gridelements-Table, 5 Powermail-Tables, 6 Custom-Tables.

A problem might be, that we have ~30.000 content elements and the generated query tries to find which element is changed:
SELECT B.uid as live_uid, B.pid as live_pid, A.uid as offline_uid
FROM tt_content A,tt_content B
WHERE A.pid=-1 AND A.t3ver_wsid=1 AND (A.t3ver_oid=B.uid AND A.t3ver_state<>4 OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4) AND A.deleted=0 AND B.deleted=0

Links to relevant code positions:
https://github.com/TYPO3/TYPO3.CMS/blob/TYPO3_7-6-10/typo3/sysext/backend/Classes/Tree/Pagetree/DataProvider.php#L175
https://github.com/TYPO3/TYPO3.CMS/blob/TYPO3_7-6-10/typo3/sysext/workspaces/Classes/ExtDirect/PagetreeCollectionsProcessor.php#L42
https://github.com/TYPO3/TYPO3.CMS/blob/TYPO3_7-6-10/typo3/sysext/workspaces/Classes/Service/WorkspaceService.php#L756

(Even though these links point to the classes for TYPO3 7 instead of 8, I believe the bug will also be present in TYPO3 8, because the queries stay the same)

Currently the page tree does not load at all. There is a timeout after 30 seconds and editors have to hope that the request finishes before. We just removed the workspace hook from

$GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/tree/pagetree/class.t3lib_tree_pagetree_dataprovider.php']['postProcessCollections']

typo3-core-patch.diff View (1.01 KB) Simon Dawes, 2017-05-09 07:43


Related issues

Related to TYPO3 Core - Task #69439: Enhance SQL query reduction in page tree in workspaces Closed 2015-08-31
Related to TYPO3 Core - Bug #66231: Workspace very slow loading page tree Closed 2015-04-02
Related to TYPO3 Core - Task #54725: Add Indexes to sys_file_reference Closed 2014-01-03

History

#1 Updated by Simon Dawes over 2 years ago

We have the same issue with workspace page tree generation on a large site in 7.6.18 too.

It is about 4500 live pages and 40,000 content records, and 6 active workspaces.

This worked but slowly in 6.2, with the move to 7.6 it almost always times out now.
Additionally this result then seems to be cached so the user can't see the page tree again unless they log out and back in.

Editors can access/see portions of the tree/pages if you use the Page Filter function at top of tree.

Currently our only solution that works consistently is to modify the fetchPagesWithVersionsInTable function that determines changes to display in
/typo3/sysext/workspaces/Classes/Service/WorkspaceService.php (see attached diff file).

Is this improved in 8.7? If so is there a chance that that improvement can be back ported to 7.6?
If not are the option to have a configuration in 7.6 to switch this function to a simpler version (like we have now) so we don't have to constantly patch the core every update?

#2 Updated by Frank Gerards over 1 year ago

\+1 for this - we also got 8000+ pages and 12 workspaces and pageTree loading times out a lot - is this tackled in 8.7 or 9-dev ?

#3 Updated by Benni Mack over 1 year ago

  • Status changed from New to Needs Feedback

Could you recheck in TYPO3 v9.2? We reworked the pagetree and also the fetching of all data in workspaces.

#4 Updated by Tobias Gaertner about 1 year ago

In TYPO3 7.6 the core-patch worked for me.
To come around to manual patch the core, for me worked indexing the tt_content table like this:

ALTER TABLE `tt_content`
 ADD INDEX `pid` (`pid`),
 ADD INDEX `t3ver_wsid` (`t3ver_wsid`),
 ADD INDEX `t3ver_state` (`t3ver_state`),
 ADD INDEX `t3ver_move_id` (`t3ver_move_id`)
;

see: https://forge.typo3.org/issues/69068

#5 Updated by Riccardo De Contardi 10 months ago

  • Status changed from Needs Feedback to Closed

I think it is safe to close this for now; it should be fixed on the latest 9.5.x version.

If you think that this is the wrong decision please reopen it or ping me.

Also available in: Atom PDF