Bug #61184

Backend - Page Tree in Workspace & Publish content - Performance Issue - BackendUtility::countVersionsOfRecordsOnPage

Added by Andreas Lingott almost 5 years ago. Updated about 4 years ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2014-08-26
Due date:
% Done:

0%

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

Description

When working in a workspace (Typo3 Backend), loading of the page tree is very slow and sometimes resolving in a connection timeout.
Also, when applying / publish changes of a workspace to the live workspace, changes are getting lost caused by the connection timeout.

That's because, there are a lot of SQL queries which took more than 8 seconds to be processed.

The bottle neck is the following method:
\TYPO3\CMS\Backend\Utility\BackendUtility::countVersionsOfRecordsOnPage

In our case, this method generates queries like this:

mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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;
+----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys    | key    | key_len | ref   | rows  | Extra       |
+----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+
|  1 | SIMPLE      | B     | ref  | PRIMARY,parent   | parent | 4       | const |   120 | Using where |
|  1 | SIMPLE      | A     | ref  | t3ver_oid,parent | parent | 4       | const | 12507 | Using where |
+----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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;
Empty set (8.21 sec)

The problem here is the "OR" part of the query.

A.t3ver_oid=B.uid AND A.t3ver_state<>4 OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4

That's only one of round about 2.000 queries to be need to generate the page tree, and at least 15 of them takes more than 8 seconds.

The solution is, to split this kind of queries into two and merge the results together.

mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.uid AND A.t3ver_state<>4 AND A.deleted=0 AND B.deleted=0;
+----+-------------+-------+------+------------------+-----------+---------+------------------------------+------+-------------+
| id | select_type | table | type | possible_keys    | key       | key_len | ref                          | rows | Extra       |
+----+-------------+-------+------+------------------+-----------+---------+------------------------------+------+-------------+
|  1 | SIMPLE      | B     | ref  | PRIMARY,parent   | parent    | 4       | const                        |  120 | Using where |
|  1 | SIMPLE      | A     | ref  | t3ver_oid,parent | t3ver_oid | 8       | typo3_cms_switch.B.uid,const |    4 | Using where |
+----+-------------+-------+------+------------------+-----------+---------+------------------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4 AND A.deleted=0 AND B.deleted=0;
+----+-------------+-------+------+------------------+-----------+---------+----------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys    | key       | key_len | ref                                    | rows | Extra       |
+----+-------------+-------+------+------------------+-----------+---------+----------------------------------------+------+-------------+
|  1 | SIMPLE      | B     | ref  | parent           | parent    | 4       | const                                  |  120 | Using where |
|  1 | SIMPLE      | A     | ref  | t3ver_oid,parent | t3ver_oid | 8       | typo3_cms_switch.B.t3ver_move_id,const |    4 | Using where |
+----+-------------+-------+------+------------------+-----------+---------+----------------------------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.uid AND A.t3ver_state<>4 AND A.deleted=0 AND B.deleted=0;
Empty set (0.00 sec)

mysql> SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4 AND A.deleted=0 AND B.deleted=0;
Empty set (0.70 sec)

So this construct is 7.3 seconds faster for only one query.

Please take a look on the attached patch, which solves the issue on my end.

BackendUtilitycountVersionsOfRecordsOnPage.patch View - Patch File (2.45 KB) Andreas Lingott, 2014-08-26 10:25

BackendUtility.php View - Changed file with fix (178 KB) Andreas Lingott, 2014-08-26 10:25

BackendUtility.php View - Original unchanged file without fix (181 KB) Andreas Lingott, 2014-08-26 10:25


Related issues

Related to TYPO3 Core - Task #50349: Reduce SQL queries of page tree in workspaces Closed 2013-07-24
Related to TYPO3 Core - Bug #66231: Workspace very slow loading page tree Closed 2015-04-02
Related to TYPO3 Core - Task #69439: Enhance SQL query reduction in page tree in workspaces Closed 2015-08-31

Associated revisions

Revision 37cd2d50 (diff)
Added by Nicole Cordes about 4 years ago

[TASK] Reduce SQL queries of page tree in workspaces

Nodes of the page tree are highlighted, if that particular node
has versioned elements in the current workspace. However, this is
determined by iterating over all tables and all records for each
page. If having installed many extensions with many tables and
many records, this will produce a lot of SQL queries.

This patch introduces a new cache for record versions per page which is
fetched once from the database. This cache is located within the
\TYPO3\CMS\Workspaces\Service\WorkspaceService and can be shared between
several other classes as this is implements a SingletonInterface.

Resolves: #50349
Resolves: #66231
Related: #61184
Releases: master, 6.2
Change-Id: Ibaef07bdab87147c6826af8578870c52803dfe03
Reviewed-on: http://review.typo3.org/22528
Reviewed-by: Georg Ringer <>
Tested-by: Georg Ringer <>
Reviewed-by: Nicole Cordes <>
Tested-by: Nicole Cordes <>

Revision d2c4bbbb (diff)
Added by Nicole Cordes about 4 years ago

[TASK] Reduce SQL queries of page tree in workspaces

Nodes of the page tree are highlighted, if that particular node
has versioned elements in the current workspace. However, this is
determined by iterating over all tables and all records for each
page. If having installed many extensions with many tables and
many records, this will produce a lot of SQL queries.

This patch introduces a new cache for record versions per page which is
fetched once from the database. This cache is located within the
\TYPO3\CMS\Workspaces\Service\WorkspaceService and can be shared between
several other classes as this is implements a SingletonInterface.

Resolves: #50349
Resolves: #66231
Related: #61184
Releases: master, 6.2
Change-Id: Ibaef07bdab87147c6826af8578870c52803dfe03
Reviewed-on: http://review.typo3.org/41425
Reviewed-by: Nicole Cordes <>
Tested-by: Nicole Cordes <>

Revision c45de676 (diff)
Added by Nicole Cordes about 4 years ago

[FOLLOWUP][TASK] Reduce SQL queries of page tree in workspaces

The patch adds a check if workspaces is activated before using its
class and function.

Releases: 6.2
Resolves: #50349
Resolves: #66231
Related: #61184
Change-Id: I12bc2d496f2216725ac97a8640bc6c431630cc7a
Reviewed-on: http://review.typo3.org/41553
Reviewed-by: Daniel Goerz <>
Reviewed-by: Susanne Moog <>
Tested-by: Susanne Moog <>
Reviewed-by: Nicole Cordes <>
Tested-by: Nicole Cordes <>

History

#1 Updated by Andreas Lingott almost 5 years ago

  • Assignee set to Alexander Opitz

#2 Updated by Alexander Opitz almost 5 years ago

Hi Andreas,

thanks for the detailed report. Why did you assign it to me?

#3 Updated by Andreas Lingott almost 5 years ago

Because nobody took care of this issue for weeks and my hope is, that you would do :)

#4 Updated by Alexander Opitz almost 5 years ago

Which Version of MySQL Server do you use?
And how many tt_content elements do you have? select count(uid) from tt_content

#5 Updated by Alexander Opitz almost 5 years ago

  • Description updated (diff)

#6 Updated by Andreas Lingott almost 5 years ago

mysql> select count(uid) from tt_content;
+------------+
| count(uid) |
+------------+
|      21282 |
+------------+
1 row in set (0.00 sec)

mysql> show variables like 'version';
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| version       | 5.1.55-enterprise-gpl-advanced-log |
+---------------+------------------------------------+
1 row in set (0.00 sec)

#7 Updated by Alexander Opitz almost 5 years ago

Huh, 5.1 ... very old ;-)

Anyway, as I understand ... you have 21.282 tt_content elements in total and 12.507 on page 42?
Why does so many content elements lye on this "galaxy" page?

By the way, do you run the "lowlevel cleaner" tools sometimes?

#8 Updated by Alexander Opitz almost 5 years ago

Excuse the 12.507 are -1 ... this means so much CEs are changed in Workspace.

#9 Updated by Andreas Lingott almost 5 years ago

Yes, 5.1 is a little bit old :)

After executing the "lowlevel cleaner", the EXPLAIN looks much better and the query is much faster.

mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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;
+----+-------------+-------+------+------------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys    | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+------------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | B     | ref  | PRIMARY,parent   | parent | 4       | const |  102 | Using where |
|  1 | SIMPLE      | A     | ref  | t3ver_oid,parent | parent | 4       | const | 1290 | Using where |
+----+-------------+-------+------+------------------+--------+---------+-------+------+-------------+
2 rows in set (0.02 sec)

mysql> SELECT SQL_NO_CACHE B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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;
Empty set (0.59 sec)

So I think, that my problem occurred, because of the amount of "unused entries", which were removed by the "lowlevel cleaner".
If you agree, we could close this report, although my changes are much more performant.

#10 Updated by Alexander Opitz almost 5 years ago

So fine, but the low level cleaner will also fasten other areas of your TYPO3 installation. :-D

So, we can come now to the next.

sql: _OPTIMIZE TABLE `tt_content` _

Test your sql statement.

Then we add an extra index, only for the WSID

sql: ALTER TABLE `tt_content` ADD INDEX `wsid` (`t3ver_wsid`)

Test your sql statement.

And show me the execution times of both Test sql Statements.

#11 Updated by Andreas Lingott almost 5 years ago

a little bit faster.

mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content_test A,tt_content_test B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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;
+----+-------------+-------+------+-----------------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys         | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | B     | ref  | PRIMARY,parent        | parent | 4       | const |   88 | Using where |
|  1 | SIMPLE      | A     | ref  | t3ver_oid,parent,wsid | parent | 4       | const | 1531 | Using where |
+----+-------------+-------+------+-----------------------+--------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE B.uid as live_uid, A.uid as offline_uid FROM tt_content_test A,tt_content_test B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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;
Empty set (0.58 sec)

#12 Updated by Alexander Opitz almost 5 years ago

I see no "before/after" the change data. So I can see the difference.

The only thing I see is a change of the numbers and that your MySQL isn't using the new index.

#13 Updated by Andreas Lingott almost 5 years ago

You are right.
When I force to use the index, it looks like this.
But in generally, I don't prefer to force using a key.

mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content_test A USE INDEX (wsid) ,tt_content_test B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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;
+----+-------------+-------+------+----------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys  | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+----------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | B     | ref  | PRIMARY,parent | parent | 4       | const |   88 | Using where |
|  1 | SIMPLE      | A     | ref  | wsid           | wsid   | 4       | const | 2974 | Using where |
+----+-------------+-------+------+----------------+--------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE B.uid as live_uid, A.uid as offline_uid FROM tt_content_test A USE INDEX (wsid) ,tt_content_test B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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;
Empty set (0.35 sec)

#14 Updated by Alexander Opitz almost 5 years ago

My system use this key automatically.
Your preferred change have the issue, that you afterwards needs to merge in PHP which costs extra time and later if we change this code using Extbase we came back to such an SQL statement.

So I would like to add the mentioned INDEX to TYPO3 CMS and look for more response from Team.

#15 Updated by Andreas Lingott almost 5 years ago

Ok. Sounds good to me.
My preferred change, with merging the results via PHP, helped me a lot before I have executed the "lowlevel cleaner".
But after the "lowlevel cleaner" removed all the unused entries in the database, my fix is not really needed anymore.

Thank you for your support.

#16 Updated by Alexander Opitz almost 5 years ago

  • Status changed from New to Accepted

#17 Updated by TSniper no-lastname-given over 4 years ago

I have same slow performance on workspace and it is really slow on loading page tree after a content editing in workspace, the loading is starting all over, there are errors thrown by ajax :

http://t3.dev/typo3/ajax.php?ajaxID=ExtDirect::route&namespace=TYPO3.Components.PageTree
29,98s Aborted
How can i optimize the workspace performance?

#18 Updated by Alexander Opitz over 4 years ago

Hi TSniper,

how many pages do you have?
Did you already run the low level cleaner?

#19 Updated by Andreas Lingott over 4 years ago

TSniper no-lastname-given wrote:

How can i optimize the workspace performance?

It's also helpful, to discard or publish your open changes in the workspace.
Too many open changes will slow down your backend.

#21 Updated by Grigori Prokhorov about 4 years ago

Can confirm same issue with same bottleneck in TYPO3 6.2.6 instance with > 6000 pages and > 42000 content records.

#22 Updated by Alexander Opitz about 4 years ago

@Grigori Prokhorov

Did you already run the lowlevel cleaner? Can you update to latest version (6.2.12)?

#23 Updated by Grigori Prokhorov about 4 years ago

Hi Alexander,

executing the lowlever cleaner is not possible in the current setup we have with our customer.
Nonetheless Martin Trepper's patch from issue #66231 does in fact resolve the issue.

The original code is apparently yet unchanged in any TYPO3 CMS 6 version up1 to2 6.2.12, so that I don't quite see how this could solve this issue aside from being a temporary fix.

The only issue I have with the patch is that it drops the second part of the workspace record check, i.e. the WHERE-clause includes

'A.pid=-1' .
' AND A.t3ver_wsid=' . $workspaceId .
' AND A.t3ver_oid=B.uid' .
BackendUtility::deleteClause($tableName, 'A') .
BackendUtility::deleteClause($tableName, 'B')

what for tt_content translates to
SELECT                                   
    B.uid as live_uid,                   
    B.pid as live_pid,                   
    A.uid as offline_uid                 
FROM                                     
    tt_content as A,                     
    tt_content as B
WHERE                                    
    A.pid = -1                           
    AND A.t3ver_wsid = ###WORKSPACE_ID###
    AND A.t3ver_oid = B.uid              
    AND A.deleted = 0                    
    AND B.deleted = 0                    

but drops the part
    A.t3ver_oid = B.uid
AND A.t3ver_state<>4
 OR A.t3ver_oid=B.t3ver_move_id
AND A.t3ver_state=4

This means that the versioning state is not regarded at all, although it's not quite clear to me why it should have been regarded in the first place since an editor can not select the versioning state when looking at the page tree anyway.

I am therefore "+1"-ing the solution proposed in #66231 to be included into the core as a proper fix for this issue.

Best,
Grigori


1 https://forge.typo3.org/projects/typo3cms-core/repository/revisions/master/entry/typo3/sysext/backend/Classes/Tree/View/AbstractTreeView.php#L930

2 https://forge.typo3.org/projects/typo3cms-core/repository/revisions/master/entry/typo3/sysext/workspaces/Classes/ExtDirect/PagetreeCollectionsProcessor.php#L75

#24 Updated by Alexander Opitz about 4 years ago

  • Status changed from Accepted to Closed
  • Assignee deleted (Alexander Opitz)

Hi,

thanks for reporting back. Fine that the patch works for you.

It may be that this patch won't be provided for TYPO3 CMS 6.2 LTS but only for TYPO3 CMS 7 LTS.
I'll close this issue as duplicate of issue #50349

Also available in: Atom PDF