Uncached plugin floods sfc_staticfilecache table

[MK-42]

Bug Report

Current Behavior
Today our production system ran into an outOfMemory condition every time an extbase validation error occured on one page (with high traffic and an uncached plugin on it).

Tracing it, the root cause is the Typo3DatabaseBackend fetching all identifiers from the sfc_ tables.
On our system that table contains nearly 3 million entries for the executed query (which are all redundant - telling us that that page is not cacheable - which we are aware of and totally okay with).

the blobs all contain

a:4:{s:7:"created";i:1576742649;s:7:"expires";i:2145909600;s:8:"priority";i:951;s:11:"explanation";a:3:{i:0;s:172:"The page is not static cacheable via TypoScriptFrontend. Check the first Question on: https://github.com/lochmueller/staticfilecache/blob/master/Documentation/Faq/Index.rst";i:1;s:135:"The page has a INTincScript: type: FUNC, userFunc: TYPO3\CMS\Extbase\Core\Bootstrap->run, extensionName: Redacted, pluginName: Cart";i:2;s:29:"static cache disabled on page";}}

Expected behavior/output
SFC should not flood that table with all these entries - one is totally enough.

Environment

• TYPO3 version(s): [e.g. 8.7.12, 9.2.0]
• staticfilecache version: [e.g. 4.0.0]
• Is your TYPO3 installation set up with Composer (Composer Mode): [yes, no]
• OS: [e.g. OSX 10.13.4, Windows 10]

Possible Solution
Bandaid:
adding a ->groupBy clause to the findIdentifiersByTag of typo3DatabaseBackend fixes the symtom, as the duplicate entries will not be fetched into php memory anymore.
I will be proposing a core patch for that - It's probably a good thing anyway.

Solution:
https://github.com/lochmueller/staticfilecache/blob/master/Classes/Middleware/GenerateMiddleware.php#L76
is where the entry is set. Note, that the X-SFC-Explanation header is set here, so no hasValidCacheEntry checks are done.
The StaticFileBackend also calles its parents set method unconditionally, which in turn writes the db row unconditionally.
I don't have strong feelings where it would be appropriate to catch these duplicates, but the Middleware probably is the right place, as the other duplication check is also done there.

Additional context
Boost Mode is active. But that shouldn't bother here I think

[MK-42]

Corresponding Core patch:
https://forge.typo3.org/issues/89997

And Review for fixing that:
https://review.typo3.org/c/Packages/TYPO3.CMS/+/62749

[MK-42]

Okay - in fact. It is somehow connected to boost mode:
https://github.com/lochmueller/staticfilecache/blob/master/Classes/Cache/StaticFileBackend.php#L218

StaticFileBackends flushByTags will actually never delete these entries, when in boost mode. Otherwise these would at least not aggregate over time.

But this is only a side-note for reproducibility. These duplicated records should never be written imho.

[lochmueller]

hey @MK-42
thanks for the issue. I will check this next year...
Have a nice christmas,
Tim

[MK-42]

Thanks a bunch, enjoy the time!

[MK-42]

Hey Tim,
today I did another cleanup because performance of our systems where creeping to death...
over 100k entries in sfc_staticfilecache, and about 4 times as much in _tags. Most of them duplicates.
90k Entries in the boost-queue (from which about 300 where used/processed on boost-queue runs.

What can we do here?

[jackd248]

Got the same problems here.

TYPO3 v9.5.14
SFC v10.2.1

Over 100k entries in sfc_staticfilecache, over 250k entries in sfc_staticfilecache_tags and tx_staticfilecache_queue. Running the extension in boost mode. Flushing the whole cache within the deployment every 2 weeks and using the "removeExpiredPages" command every night.

How can I keep the tables clean?