Bug #86952

flushByTags() is using a all in one , slow mysql command

Added by Jörg Velletti about 1 year ago. Updated 3 months ago.

Status:
Under Review
Priority:
Should have
Assignee:
-
Category:
Caching
Target version:
-
Start date:
2018-11-20
Due date:
% Done:

0%

TYPO3 Version:
8
PHP Version:
7.1
Tags:
flushByTags, 8.7. zap, gremlin
Complexity:
Is Regression:
Sprint Focus:

Description

on a big TYPO3 instance with 16 languages, working in the backend is a "pain" :

When I press save button on a simple text media element, it takes up to 20 seconds. Changing the status "Hidden" status to visible of some elements in the list mode often runs into timeouts.

reason is in my eyes this uncahchable MySQL command inside of flushByTags()

DELETE tags2, cache1 FROM cf_cache_hash_tags AS tags1 JOIN cf_cache_hash_tags AS tags2 ON tags1.identifier = tags2.identifier JOIN cf_cache_hash AS cache1 ON tags1.identifier = cache1.identifier WHERE tags1.tag IN ('pageId_5529','tt_content','tt_content_102996','pageId_5531','pageId_5535','pageId_5532','pageId_5541','pageId_5538','pageId_5539','pageId_5540','pageId_5528','pages','pages_5529')

This is genereted for each translated language of that page for each changes content element :

If i do tests directly it takes 2-5 seconds each. But if this text is referenced many times it runs sometimes into a timeout.

Solution (for us):
if i change it from ONE Call to many single Calls in a for Loop ( like it was 10 Years ago in 4.5 ) :

 foreach ($tags as $tag ) {
                $quotedTag = '\'' . $tag . '\'';
                $connection->executeQuery(
                    'DELETE tags2, cache1'
                    . ' FROM ' . $this->tagsTable . ' AS tags1'
                    . ' JOIN ' . $this->tagsTable . ' AS tags2 ON tags1.identifier = tags2.identifier'
                    . ' JOIN ' . $this->cacheTable . ' AS cache1 ON tags1.identifier = cache1.identifier'
                    . ' WHERE tags1.tag = ' . $quotedTag
                );
            }

The total needed time 13 * 0.016 sec = means < than 0.2 seconds in total

See the attached patch, i am using actually with 8.7 LTS.
NOW working in backend is again acceptable. I will try to put the patch also into Github as a pull request

Hiding or activationg a lot of Content elements or database entries in the list mode is now again working FAST as expeted.


Some technical background:

- the MySQL Server is dedicated for this TYPO3 instance (no shared hoster) and attached with 10 GB network ..
- cf_cache_hash table has actually 50.000 rows and 500 MB
- cf_cache_hash_tags table has actually 250.000 rows and 46 MB
- 200.000 registered frontend user with many different usergroups and usergroup combinations.

speed_up_backend_flushByTags.diff View (2.9 KB) Jörg Velletti, 2018-11-20 10:45

History

#1 Updated by Jörg Velletti about 1 year ago

I added this
https://github.com/velletti/TYPO3.CMS/commit/0fcae172daf32b330114d4ce5b84d0dc785a43b3
as a pull request to the master branch.

As i learned afterwards from the links in the automatic emails i got, that my commit message does not fit the TYPO3 Commit Rules, what shall i do?

Additional:
How can this change get also into 8.7 core, as we will stuck for at least one more year in 8.7 LTS?
Actually i use composer extras -> patches

#2 Updated by Wolfgang Klinger about 1 year ago

I tested this with 10000 cache entries on MySQL (https://gist.github.com/wazum/189226c9b7335fe77939aa616619fa7f)

----------------------------------------
Multi flush: 1.0613176822662
----------------------------------------
Single flush: 0.14994651873906
----------------------------------------

and it more or less confirms the report.

For TYPO3 core contribution please read and follow the guide: https://docs.typo3.org/typo3cms/ContributionWorkflowGuide/

#3 Updated by Gerrit Code Review about 1 year ago

  • Status changed from New to Under Review

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/58917

#4 Updated by Jörg Velletti about 1 year ago

I checked the changed Code on gerrit after Patch set 2 (thanks to Wolfgang for testing) and it looks good for me.

#5 Updated by Gerrit Code Review about 1 year 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/58917

#6 Updated by Gerrit Code Review 10 months 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/c/Packages/TYPO3.CMS/+/58917

#7 Updated by Gerrit Code Review 10 months 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/c/Packages/TYPO3.CMS/+/58917

#8 Updated by Oliver Hader 3 months ago

  • Tags changed from flushByTags, 8.7. to flushByTags, 8.7. zap, gremlin

#9 Updated by Gerrit Code Review 3 months 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/c/Packages/TYPO3.CMS/+/58917

#10 Updated by Gerrit Code Review 3 months 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/c/Packages/TYPO3.CMS/+/58917

Also available in: Atom PDF