#90518DB problems with rootline cache: DELETE cf_cache_rootline double-JOIN with cf_cache_rootline_tags take excessively long
Description
Recently we are experiencing increasing number of exceptions and “too many connections” on TYPO3 9.5.13 in combination with DELETE queries on cf_cache_rootline and cf_cache_rootline_tags that take excessively long.
Obviously, this is causing massive problems.
Findings:
- Increasing number of exceptions: An exception occurred while executing ‘INSERT INTO `cf_cache_rootline … Lock wait timeout exceeded; try restarting transaction | Doctrine\DBAL\Exception
This seems to be correlated with excessively long DELETE queries on cf_cache_rootline and cf_cache_rootline_tags with 2 JOINs.
# Time: 200221 11:44:21
# User@Host: ------ @ [------]
# Thread_id: 13694456 Schema: ----- QC_hit: No
# Query_time: *2212.109482* Lock_time: 0.000340 Rows_sent: 0 Rows_examined: *1007307838*
# Rows_affected: *94732*
SET timestamp=1582281861;
DELETE tags2, cache1 FROM cf_cache_rootline_tags AS tags1 JOIN cf_cache_rootline_tags AS tags2 ON tags1.identifier = tags2.identifier JOIN cf_cache_rootline AS cache1 ON tags1.identifier = cache1.identifier WHERE tags1.tag IN ('pageId_3');
(I blanked out some IP addresses etc.)
in Typo3DatabaseBackend::flushByTags()