TYPO3 Core

Bug #90518

Updated by Sybille Peters over 4 years ago

*Update*: My initial idea did not work out. I will add some more information.  

 ---- 

 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. (I realize this is not the latest TYPO3 versions. The root of the problem seems to still exist in TYPO3 10). 

 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. 

 <pre><code class="text"> 
 # 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'); 

 </code></pre> 


 (I blanked out some IP addresses etc.) 


 I can add some more information, but it seems to me now the 2 JOINs are excessive and the DB statements can be optimized: 

 So, instead of this: 

 <pre><code class="sql"> 
 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'); 

 </code></pre> 

 We could have this: 

 <pre><code class="sql"> 
 DELETE tags1, cache1  
     FROM cf_cache_rootline_tags AS tags1  
     JOIN cf_cache_rootline AS cache1 ON tags1.identifier = cache1.identifier  
     WHERE tags1.tag IN ('pageId_3'); 

 </code></pre> 

 in Typo3DatabaseBackend::flushByTags() 










 

 Submitting patch ... 

 ---- 

Back