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

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

    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

    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

    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

    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

    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

    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

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