[BUG] possibly causing MySQL Dead-Locks and/or unnecessary load on database-server

[typoworx-de]

Bug Report

Current Behavior
I noticed some issues with MySQL (MariaDB) dead-locks and found that StaticFileCache is possiby causing them in this line:

staticfilecache/Classes/Middleware/GenerateMiddleware.php

Line 81 in 21697e6

$this->cache->set($uri, $response, (array) $response->getHeader('X-SFC-Tags'), $lifetime);

StaticFileCache will even fire cache-entries with cache-lifetime 0 to database, which (as far as I see) is not required and may cause load on MySQL server for pages that have cache-restrictions preventing caching of the page.

Expected behavior/output
StaticFileCache should only cache / generate cache database entries

Environment

• TYPO3 version(s): 11.5
• staticfilecache version: 13.1
• Is your TYPO3 installation set up with Composer (Composer Mode): yes
• OS: Ubuntu Linux

Possible Solution
I made an extension extending staticfilecache GenerateMiddleware.php to test my own approach to solve this.
see: https://gitlab.com/typoworx-gmbh/typo3/extensions/typo3-staticfilecache-extender/-/blob/main/Classes/Http/Middleware/GenerateMiddleware.php?ref_type=heads

Additional context
--/--

[lochmueller]

Hey @typoworx-de

EXT:staticfilecache also log the reason why a page is not cachable directly into the cache. If there is an explanation, the storage backend does not create a file and store the explanation as serialized content (instead of the original content) in the cache: https://github.com/lochmueller/staticfilecache/blob/main/Classes/Cache/StaticFileBackend.php#L73

PS:

• Check for status code https://gitlab.com/typoworx-gmbh/typo3/extensions/typo3-staticfilecache-extender/-/blob/main/Classes/Http/Middleware/GenerateMiddleware.php?ref_type=heads#L50 could be moved in separated event listener
• This check is double https://gitlab.com/typoworx-gmbh/typo3/extensions/typo3-staticfilecache-extender/-/blob/main/Classes/Http/Middleware/GenerateMiddleware.php?ref_type=heads#L46 because the prepare middleware also check this via https://gitlab.com/typoworx-gmbh/typo3/extensions/typo3-staticfilecache-extender/-/blob/main/Classes/Http/Middleware/GenerateMiddleware.php?ref_type=heads#L54

I suggest to not remove the cache, if the $lifetime is 0: https://gitlab.com/typoworx-gmbh/typo3/extensions/typo3-staticfilecache-extender/-/blob/main/Classes/Http/Middleware/GenerateMiddleware.php?ref_type=heads#L82 Cache invalidation is not part of the GenerationMiddleware

Regards,
TIm

[typoworx-de]

Hey @lochmueller thanks for your feedback and pointing out the reasons for your code. I already thought something like that. But never the less in my TYPO3 Lifetime I already had two Websites that ran into some kind of MySQL Dead-Locks obviously caused by static-file-cache.

Also thank you very much for your feedback and improvements for my extending-code :)

It might be some kind of edge-case, but at least there seems to be the case. The first project I had this problem was hosted on Google-Cloud running multiple frontend-instances (against one database) - which may have caused some kind of this edge-condition. In this project I refactored static-file-cache to be configureable to any kind of cache-backend being able to configure it with Redis.

In the current project there is no kubernetes nor docker nor multiple frontends. We noticed recurring issues with MySQL Server over the last weeks. Since I used my approach with my extension to disable sfc negative caching - the dead-lock crash problems with the MySQL Server have been gone.

So I understand that the negative cache has its reason - but at least in some (of projects I supported) there already were two projects that had some kind of edge-conditions causing massive problems.

[typoworx-de]

@lochmueller

I suggest to not remove the cache, if the $lifetime is 0: https://gitlab.com/typoworx-gmbh/typo3/extensions/typo3-staticfilecache-extender/-/blob/main/Classes/Http/Middleware/GenerateMiddleware.php?ref_type=heads#L82 Cache invalidation is not part of the GenerationMiddleware

I understand that its not part of the concept. It also didn't feel right to abuse the GenerateMiddleware like that - but I decided to check for Response http status-code to also decide to cache it or not. As far as I see there are only Pre-Conditions running against Request - but no Post-Conditions being able to post-negate the cache decision basing on Response-Result to avoid caching results that should not be cached better.

I think at least in some cases this could make sense to make sure only valid Responses (2xx, 3xx, eventually 404) are cacheable. It would also be greate to leave it up to site-admin making this configureable.

[lochmueller]

I understand that its not part of the concept. It also didn't feel right to abuse the GenerateMiddleware like that - but I decided to check for Response http status-code to also decide to cache it or not. As far as I see there are only Pre-Conditions running against Request - but no Post-Conditions being able to post-negate the cache decision basing on Response-Result to avoid caching results that should not be cached better.

Good point. The response is already generated in the check process. So I added the response to the event and add a first check, related to the valid status code 200: 0bc6c87

It would also be greate to leave it up to site-admin making this configureable

You can disable every event and create your own event to customize the generation and evaluation process.

Regard,
Tim

[typoworx-de]

Thanks for your response to my modifications @lochmueller I appreciate your help.

By the way I think I will re-implement my modifications from earlier project integrating flexible Cache-Backend (configureable between Typo3Database, Redis, ...) again. Any intererest to give that a review and think about integrating it in the official project? I think I will refactor the current "backend" which seems to work like hybrid between File-Backend + Cache-Backend to kind of Proxy-Class targeting these both backend - but being able to switch the Cache-Backend then.

[lochmueller]

Hey @typoworx-de ,
would be cool. Currently, there is a separation of the "file" and the "database" logic. But StaticFileBackend directly extends StaticDatabaseBackend :-/ Not a wonderful design approach, if you/we want to change the storage of the meta information from db to another store. A solution for this problem would be cool :)
Regards,
Tim

[typoworx-de]

Hello @lochmueller,

I had a solution some month ago doing some refactoring on this. For my sake after finishing it urgently for a project I didn't have the time to publish it back to you. I'm looking for it and check how I can adopt it for current version of static-file-cache and I'll drop you a link or MR for it if I'm ready.

I would be glad to give back some improvements to your project.

[chrismoeller]

Hi @typoworx-de and Tim,

just stumbled upon this issue since we're also hit big-time by slow MySQL performance whenever saving records in the BE. We're using the feature frontend.cache.autoTagging introduced in TYPO3 13.3 (see changelog).

This leads to perfect consistency in the frontend after editorial changes, including matching deletion of affected DB/Redis cache entries and static files (without needing to use TCEMAIN.clearCacheCmd). But due to the complexity of our extensions' models, the big amount of extension records (500 persons having relations to 300 expertises, 15.000 articles, ...) and many plugins displaying all kinds of data all over the place, auto-tagging also leads to millions of rows in the sfc_staticfilecache_tags table and even more millions of rows in cache_pages(_tags). We solved the cache_pages(_tags) performance issue by using RedisCacheBackend for pages/rootline/hash caches.

When SFC deletes from sfc_staticfilecache(_tags) using DELETE FROM sfc_staticfilecache_tags WHERE identifier IN ('https://url1...','https://url2...','....') statements, it takes minutes to delete several hundred thousand rows from the million rows total. Usually, the BE then throws 50x errors due to ipc or lock timeouts.

Of course, this makes the editorial BE work nearly impossible.

@typoworx-de I have a feeling that your refactoring approach to be able to use the RedisCacheBackend for staticfilecache would be a possible improvement in our case, as well. Would you mind sharing your work so we can figure this out and test together?

[typoworx-de]

Hi @chrismoeller my refactoring approach was some time ago for a project. I'll try to find some time to investigate in this and trying to provide a MR which should work for current TYPO3 version. Give me some time - I'm kinda busy in projects actually. If I find the codebase I may provide it here (unchanged) for working together on a merge with current SFC Codebase.