Hey everybody, maybe someone already solved this or has an idea how to better approach it, but we ha

UP4C7GASJ

Hey everybody,
maybe someone already solved this or has an idea how to better approach it, but we have been puzzling for quite some time now. For our customer we are migrating to the most recent version of Spryker 2021.08. In this migration we also moved to MariaDB, after solving some custom queries and some issues that are caused by Propel (yay types are lost on queries), we now headed into the weird part of the update.

Our shop runs on roughly 5k categories with nodes and two languages, hence 10k attributes. These categories are nested together in a closure table with roughly 23k records. When we now publish the tree or a single node, which updates the entire tree, we are invoking the default publisher (CategoryNodeWritePublishPlugin). This publisher collects data and runs into the repository of the Category module (CategoryRepository). The invoked query with multiple joins took on our old Postgres database about 1.5s to collect all joined records. After moving to MariaDB this query takes from 65 to 95 seconds. As this is invoked for each node that is getting published our queue for all 5k nodes is just not being processed.

Question now:
1. is anyone aware of this topic?
a. If yes, how did you solve it in the past?
b. If no, any ideas how to approach this?
2. any ideas on how to optimize it from the infrastructure, as the query is build in the core?
We already tried to optimize the query slightly and also added combined indexes, which either had no impact or reduced the collected data.

Looking forward to any suggestion or insight 🙂

UKEP86J66

I’m aware of this issue from a shop with not so many categories but ~30 stores which multiplied the query complexity and also resulted in very slow pub/sync. We did some optimisations to split the query into a few parts and I think we removed the ordering in some places. This did improve performance but categories is still the slowest part of our pub-sync. Our next ideas were to look at some form of caching but I suspect the issue is the closure table itself, where the db has to create many internal in-memory structures which do not scale well.

UKEP86J66

In the end the performance was ok-ish so we’ve not done anything further but you could always raise this as a bug or performance improvement with Spryker

UP4C7GASJ

Did you already raise this as a bug or performance improvement ? I guess with more than one project/partner mentioning it would be moved more quicker 😉

UKEP86J66

No I didn’t raise anything as I thought our use-case was unusual due to so many stores, but if you raise it you can mention it’s also an issue for less categories but with more stores.

UP4C7GASJ

Alright. Can you maybe also provide me some numbers on your setup?

Maybe record counts on the above mentioned tables. I would provide them as another setup that acts up 🙂

UKEP86J66

Sure, these are the counts:

spy_category -> 110
spy_category_attribute -> 1540
spy_category_store -> 2989
spy_category_closure_table -> 326
spy_category_node -> 110

UKEP86J66

Not as high as your numbers, but due to the number of stores and locales it does make it close.