TYPO3 Core

Hi Andreas,

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

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

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

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)

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?

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

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.

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.

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)

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.

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)

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.

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.

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?

Hi TSniper,

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

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.

Please have a look to https://forge.typo3.org/issues/66231

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

@Grigori Prokhorov

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

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 up¹ to² 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')

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                    

    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

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

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