Calling Developers!
We are reenergizing our code contribution process! Learn More

What are the Slack Archives?

It’s a history of our time together in the Slack Community! There’s a ton of knowledge in here, so feel free to search through the archives for a possible answer to your question.

Because this space is not active, you won’t be able to create a new post or comment here. If you have a question or want to start a discussion about something, head over to our categories and pick one to post in! You can always refer back to a post from Slack Archives if needed; just copy the link to use it as a reference..

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

Options
UP4C7GASJ
UP4C7GASJ Posts: 19 🧑🏻‍🚀 - Cadet

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 🙂

Comments

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet
    Options

    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
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet
    Options

    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
    UP4C7GASJ Posts: 19 🧑🏻‍🚀 - Cadet
    Options

    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
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet
    Options

    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
    UP4C7GASJ Posts: 19 🧑🏻‍🚀 - Cadet
    Options

    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
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet
    Options

    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
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet
    edited March 2022
    Options

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