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
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
-
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.
0 -
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
0 -
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 π0 -
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.
0 -
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 π
0 -
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
0 -
Not as high as your numbers, but due to the number of stores and locales it does make it close.
0
Categories
- All Categories
- 42 Getting Started & Guidelines
- 7 Getting Started in the Community
- 8 Additional Resources
- 7 Community Ideas and Feedback
- 69 Spryker News
- 899 Developer Corner
- 760 Spryker Development
- 83 Spryker Dev Environment
- 361 Spryker Releases
- 3 Oryx frontend framework
- 34 Propel ORM
- 68 Community Projects
- 3 Community Ideation Board
- 30 Hackathon
- 3 PHP Bridge
- 6 Gacela Project
- 24 Job Opportunities
- 3.2K π Slack Archives
- 116 Academy
- 5 Business Users
- 370 Docker
- 551 Slack General
- 2K Help
- 75 Knowledge Sharing
- 6 Random Stuff
- 4 Code Testing
- 32 Product & Business Questions
- 68 Spryker Safari Questions
- 50 Random