Bug #58357

Epic #55070: Workpackages

Epic #55065: WP: Overall System Performance (Backend and Frontend)

Superfluous SQL queries on copying records

Added by GAYA La Nouvelle Agence over 5 years ago. Updated about 1 year ago.

Status:
Closed
Priority:
Must have
Assignee:
Category:
-
Target version:
-
Start date:
2014-04-30
Due date:
% Done:

100%

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

Description

Hello,

We encounter performance problems with TYPO3 6.1, especially visible during page copy. To understand the origin of the problem, we've installed a clean 6.1 (6.1.7) and 6.2 (6.2.1) TYPO3 version and check what's appended on MySQL requests.

For our check :
- 7 pages created on the root directory
- 10 tt_contents created in the 7th page (6 textpic + 4 html)
- copy the 7th page
- paste it after the 7th page

We've activated MySQL query log and looked for queries executed during paste :

- TYPO3 6.1 : 2567 SQL queries
- TYPO3 6.2 : 1827 SQL queries

The difference between 6.1 and 6.2 seems to be an optimization with backend caching (cf_* tables) where all the treatment is done once at the end with 6.2, sounds good !

However, there's still a lot of queries and we can see many queries which seems to be useless like "select ... where uid = 0". In our case, these queries concern page, sys_file_reference and tt_content tables. Ex :

SELECT editlock FROM pages WHERE uid=0
SELECT hidden FROM pages WHERE uid=0
SELECT title FROM pages WHERE uid=0
SELECT doktype FROM pages WHERE uid=0
SELECT TSconfig FROM pages WHERE uid=0
SELECT storage_pid FROM pages WHERE uid=0
SELECT is_siteroot FROM pages WHERE uid=0
SELECT php_tree_stop FROM pages WHERE uid=0
SELECT tx_impexp_origuid FROM pages WHERE uid=0
SELECT url FROM pages WHERE uid=0
SELECT starttime FROM pages WHERE uid=0
SELECT endtime FROM pages WHERE uid=0
SELECT urltype FROM pages WHERE uid=0
SELECT shortcut FROM pages WHERE uid=0
SELECT shortcut_mode FROM pages WHERE uid=0
SELECT no_cache FROM pages WHERE uid=0
SELECT fe_group FROM pages WHERE uid=0
SELECT subtitle FROM pages WHERE uid=0
SELECT layout FROM pages WHERE uid=0
SELECT url_scheme FROM pages WHERE uid=0
SELECT target FROM pages WHERE uid=0
SELECT media FROM pages WHERE uid=0
SELECT lastUpdated FROM pages WHERE uid=0
SELECT keywords FROM pages WHERE uid=0
SELECT cache_timeout FROM pages WHERE uid=0
SELECT cache_tags FROM pages WHERE uid=0
SELECT newUntil FROM pages WHERE uid=0
SELECT description FROM pages WHERE uid=0
SELECT no_search FROM pages WHERE uid=0
SELECT abstract FROM pages WHERE uid=0
SELECT module FROM pages WHERE uid=0
SELECT extendToSubpages FROM pages WHERE uid=0
SELECT author FROM pages WHERE uid=0
SELECT author_email FROM pages WHERE uid=0
SELECT nav_title FROM pages WHERE uid=0
SELECT nav_hide FROM pages WHERE uid=0
SELECT content_from_pid FROM pages WHERE uid=0
SELECT mount_pid FROM pages WHERE uid=0
SELECT mount_pid_ol FROM pages WHERE uid=0
SELECT alias FROM pages WHERE uid=0
SELECT l18n_cfg FROM pages WHERE uid=0
SELECT fe_login_mode FROM pages WHERE uid=0
SELECT backend_layout FROM pages WHERE uid=0
SELECT backend_layout_next_level FROM pages WHERE uid=0
SELECT categories FROM pages WHERE uid=0

We can count 801 queries with 6.1 and 820 with 6.2, so this problem (cause I suppose this is not "normal") is still not solved.

I join the both SQL log files for TYPO3 6.1 and 6.2 of all queries executed during "paste" action.

query-6.1.log View - TYPO3 6.1 query log (285 KB) GAYA La Nouvelle Agence, 2014-04-30 12:17

query-6.2.log View - TYPO3 6.2 query log (193 KB) GAYA La Nouvelle Agence, 2014-04-30 12:17

Associated revisions

Revision 0fa74c59 (diff)
Added by Oliver Hader over 5 years ago

[BUGFIX] Superfluous SQL queries on copying records

On copying a page in the backend there are lots of superfluous
SQL queries containing "... WHERE uid=0". The reason for that
is, that DataHandler::recordInfo() is called for each field
which then tries to fetch the database record for the given
uid "NEW...". Casting the new record placeholder to an integer
value ends up with the "uid=0" condition.

In a test with copying a page with some sub-pages, there was a
total of about 1300 SQL queries, 300 of them have been those
superfluous "uid=0" SELECT statements.

Change-Id: Ib7006a7ceb52376032df8eaefaa918407718a482
Resolves: #58357
Releases: 6.2, 6.3
Reviewed-on: https://review.typo3.org/31310
Reviewed-by: Markus Klein
Tested-by: Markus Klein
Reviewed-by: Sascha Egerer
Tested-by: Sascha Egerer
Reviewed-by: Oliver Hader
Tested-by: Oliver Hader

Revision 3e1a8f53 (diff)
Added by Oliver Hader over 5 years ago

[BUGFIX] Superfluous SQL queries on copying records

On copying a page in the backend there are lots of superfluous
SQL queries containing "... WHERE uid=0". The reason for that
is, that DataHandler::recordInfo() is called for each field
which then tries to fetch the database record for the given
uid "NEW...". Casting the new record placeholder to an integer
value ends up with the "uid=0" condition.

In a test with copying a page with some sub-pages, there was a
total of about 1300 SQL queries, 300 of them have been those
superfluous "uid=0" SELECT statements.

Resolves: #58357
Releases: 6.2, 6.3
Change-Id: Ib7006a7ceb52376032df8eaefaa918407718a482
Reviewed-on: https://review.typo3.org/31502
Reviewed-by: Oliver Hader
Tested-by: Oliver Hader

History

#1 Updated by Markus Klein over 5 years ago

Wow, selecting each field separately from pages and so on is really dumb. I'm curious where this comes from.

#2 Updated by DANIEL Rémy over 5 years ago

Maybe this is related with #47692?

#3 Updated by Markus Klein over 5 years ago

No not related. This is expected to happen.

#4 Updated by Philipp Gampe over 5 years ago

Wherever this happens, we should always fetch the full row and cache the uid in some runtime cache.

#5 Updated by Alexander Opitz over 5 years ago

Backtrace for this issue:


TYPO3\CMS\Core\Utility\GeneralUtility::callUserFunction#93 
call_user_func_array#4170
TYPO3\CMS\Core\ExtDirect\ExtDirectRouter->route# 
TYPO3\CMS\Core\ExtDirect\ExtDirectRouter->processRpc#98 
call_user_func_array#149
TYPO3\CMS\Backend\Tree\Pagetree\ExtdirectTreeCommands->copyNodeAfterDestination#
TYPO3\CMS\Backend\Tree\Pagetree\Commands::copyNode#271 
TYPO3\CMS\Backend\Tree\Pagetree\Commands::processTceCmdAndDataMap#143
TYPO3\CMS\Core\DataHandling\DataHandler->process_cmdmap#217
TYPO3\CMS\Core\DataHandling\DataHandler->copyPages#2942
TYPO3\CMS\Core\DataHandling\DataHandler->copySpecificPage#3150
TYPO3\CMS\Core\DataHandling\DataHandler->copyRecord#3183
TYPO3\CMS\Core\DataHandling\DataHandler->process_datamap#3088
TYPO3\CMS\Core\DataHandling\DataHandler->fillInFieldArray#1150
TYPO3\CMS\Core\DataHandling\DataHandler->checkValue#1404
TYPO3\CMS\Core\DataHandling\DataHandler->recordInfo#1540
TYPO3\CMS\Core\Database\DatabaseConnection->exec_SELECTquery#5838
TYPO3\CMS\Core\Database\DatabaseConnection->debug#303

#6 Updated by Alexander Opitz over 5 years ago

So, as I understand, this issue happens for every "new" Element (not only pages), as checkValue loads the previous values on uid 0;

#7 Updated by Alexander Opitz over 5 years ago

  • Parent task set to #55065

#8 Updated by Oliver Hader over 5 years ago

  • Assignee set to Oliver Hader

Exactly, this is caused by trying to fetch record data for non existing records. The virtual uid in this case looks like "NEW....", after casting to integer the "uid=0" remains.

#9 Updated by Oliver Hader over 5 years ago

  • Subject changed from Backend performances / Hundreds useless sql queries to Superfluous SQL queries on copying records

Exactly, this is caused by trying to fetch record data for non existing records. The virtual uid in this case looks like "NEW....", after casting to integer the "uid=0" remains.

#10 Updated by Gerrit Code Review over 5 years ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/31310

#11 Updated by Gerrit Code Review over 5 years ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/31310

#12 Updated by Gerrit Code Review over 5 years ago

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/31310

#13 Updated by Gerrit Code Review over 5 years ago

Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/31310

#14 Updated by Gerrit Code Review over 5 years ago

Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/31310

#15 Updated by Gerrit Code Review over 5 years ago

Patch set 6 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/31310

#16 Updated by Gerrit Code Review over 5 years ago

Patch set 7 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/31310

#17 Updated by Gerrit Code Review over 5 years ago

Patch set 8 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/31310

#18 Updated by Gerrit Code Review over 5 years ago

Patch set 1 for branch TYPO3_6-2 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/31502

#19 Updated by Oliver Hader over 5 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#20 Updated by Benni Mack about 1 year ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF