Mysql server memory is full due to request cache table

[sietsevandreven]

A somewhat larger webshop will experience a problem with the amount of data after a while. This is caused by the filters. These filter combinations are stored in the cache.

We received the message below from the data center. The server also crashes after this message.

SELECT * FROM tl_iso_requestcache WHERE store_id='0' AND config='a:3:{s:7:"filters";a:1:{i:59;a:3:{s:10:"kleur=Roze";O:30:"Isotope\RequestCache\CsvFilter":1:{s:12:"\0*\0arrConfig";a:4:{s:9:"attribute";s:5:"kleur";s:8:"operator";s:11:"FIND_IN_SET";s:5:"value";s:4:"Roze";s:5:"group";s:16:"cumulative_kleur";}}s:20:"formaat_filter=15x15";O:30:"Isotope\RequestCache\CsvFilter":1:{s:12:"\0*\0arrConfig";a:4:{s:9:"attribute";s:14:"formaat_filter";s:8:"operator";s:11:"FIND_IN_SET";s:5:"value";s:5:"15x15";s:5:"group";s:25:"cumulative_formaat_filter";}}s:20:"formaat_filter=25x33";O:30:"Isotope\RequestCache\CsvFilter":1:{s:12:"\0*\0arrConfig";a:4:{s:9:"attribute";s:14:"formaat_filter";s:8:"operator";s:11:"FIND_IN_SET";s:5:"value";s:5:"25x33";s:5:"group";s:25:"cumulative_formaat_filter";}}}}s:8:"sortings";N;s:6:"limits";N;}' LIMIT 0,1

[aschempp]

What amount of data are we talking about?

Generally speaking, I'm aware that there are possible issues with this cache system, unfortunately I don't see a way to fix this in the current version. I'm planning to improve on that in the upcoming Isotope for Contao 5 (https://www.isotopeecommerce.org/en/blog/heres-to-the-next-15-years.html)

[sietsevandreven]

Hi Andreas,

The cache table is about 2.8GB. The server has 8GB of RAM, that is too little because the SQL process then stops. Above 10GB of RAM seems to be sufficient. As a temporary fix all cache data older than 6 months are deleted automatically, we made a script for that.

A developer who's expertise we use had an idea about how to fix this, so if you need ideas for the C5 version, maybe I can get you in touch with him. He has developed quite a lot (specific use-case) extensions for Isotope.

[aschempp]

Sure! He's happy to contribute his ideas, maybe to this issue first?

[jaapjansma]

I am the developer @sietsevandreven is talking about. My idea was to add a column to the cache table with the name hash. This column then contains a hash of the contents of the config column. When we want to lookup if a certain item exists we first look it up by its hash. @aschempp what do you think? I can also provide a pull request if needed.

[fritzmg]

But that was already implemented in 2.9.0 (#2525), no?

[aschempp]

Yeah I was looking that up right now. A hash was added to the table, but I don't think we're using that hash in the URL (yet). Not sure if that would be a break for existing shops though 🤔

[jaapjansma]

Ah yes. I did not checkout the latest version yet. So this issue can be closed as it is the same as #2525

[aschempp]

But #2525 is merged and your issue still exists?

[jaapjansma]

Yes but on Isotope 2.8

[aschempp]

aah 👍 then I'm closing this for now, let me know if the issue persists in 2.9