Issue Bug Closed

#90518DB problems with rootline cache: DELETE cf_cache_rootline double-JOIN with cf_cache_rootline_tags take excessively long

Sybille Peters 02/24/20, 13:27 updated 07/09/24, 12:00

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()

Reviews 4