Bug #58357
closedEpic #55070: Workpackages
Epic #55065: WP: Overall System Performance (Backend and Frontend)
Superfluous SQL queries on copying records
100%
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.
Files
Updated by Markus Klein over 10 years ago
Wow, selecting each field separately from pages and so on is really dumb. I'm curious where this comes from.
Updated by Markus Klein over 10 years ago
No not related. This is expected to happen.
Updated by Philipp Gampe over 10 years ago
Wherever this happens, we should always fetch the full row and cache the uid in some runtime cache.
Updated by Alexander Opitz over 10 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
Updated by Alexander Opitz over 10 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;
Updated by Oliver Hader about 10 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.
Updated by Oliver Hader about 10 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.
Updated by Gerrit Code Review about 10 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
Updated by Gerrit Code Review about 10 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
Updated by Gerrit Code Review about 10 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
Updated by Gerrit Code Review about 10 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
Updated by Gerrit Code Review about 10 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
Updated by Gerrit Code Review about 10 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
Updated by Gerrit Code Review about 10 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
Updated by Gerrit Code Review about 10 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
Updated by Gerrit Code Review about 10 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
Updated by Oliver Hader about 10 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 3e1a8f53915c3e7ea871fe2a42aa8048ae540375.
Updated by Benni Mack almost 6 years ago
- Status changed from Resolved to Closed