Magento Multi Source Inventory, 23 Nov 2018

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Magento MSI Open Demo. November 23, 2018 (BLACK FRIDAY EDITION)

Description

Agenda:
- Fix critical issue on Inventory Import
- Asynchronous Source Migration via RabbitMQ
- Some performance pitfalls identified on MSI during the regression testing and demonstration how this degradation could be mitigated via a workaround.
https://github.com/magento-engcom/msi/issues/1921

A

Hello, everyone, today's today's Black Friday, but despite that fact, we we are still on track and we are still making the msi weekly meeting. So nothing, nothing prevent us to make our regular MSI with meeting and share share the status of what new was implemented and what the works with the updates. We have on a project. So today we will show you so because we have. Today we have pretty somatic demo meeting, because the Black Friday is the day when the high availability and the high performance matters.

A

So we will will tell you a little bit more about the therefore performance pitfalls we faced during this week, because even this week we made history from running the performance acceptance test because, as you know, we are on the final step of 22 distri, that the old reparation and and the msi gonna be with the core bond extension of magenta to the treated and this week we were finalizing all the development of the regression and performance acceptance test.

A

So we actually faced with some degradation, and today we will show you the root cause of this degradation. We found probably how to mitigate mitigate.

A

The issue of this degradation of the workaround we recommend motion to proceed while we fix in the radiation we which would be released.

A

But let's start from from another demonstration that started from the blocker issue, which was found at the beginning of this week. As we found the critical issue on the inventory import process and now Slava will make demonstrations that everything work and describe a little bit more about that issue. So sorrowful free to share your screen and tell us more about it.

B

Ok, kill everybody so that made my screen and I will start from this backhoe digger mentioned it's about the stuff of our 500 during the import least source type of source, and according to this, I will show that this issue is fixed. This fit provided by you know, and you know so we have a symbol or I already created simple product. It's as I need to.

B

Custom test source, one response 3100. What is important, because I will do import of eight. It is this very area, so I go to import.

B

I select stocks sources, we simple, behavior, add and update and I choose previously created the file.

B

To do our import, so I Nick, check data and it's okay file is varied and I do import.

B

Let's just stay here. It's actually done and I go to check my products and product.

B

And the quantity was 131 after import, it became 50 or imports made properly. This is shorty show taken the issue. It's dedicated to my section, to inventory, Junction or from our show or such case do doing a mass transfer from default source to custom source and I previously in configuration. I already said that we will do it as synchronous transfer, with message queue and I implemented performance.

B

Key. It's a small small profile, I use smoke, provide to create fourteen hundreds products in our system and all assign it at the moment to the default source.

B

So I will do sure, I select all fourteen hundreds items and go to transfer mentally to source. Yes,.

B

B

Okay, so all our products assigned need to differ default sorts I, select them all children's elementary sourced and in selection, page with choosing transfer from source default source.

B

It would be a short one and we do enough time from region source of a transfer in a click on the entry transfer.

B

It's a little bit slowly, working computer, at least, and he used to the message that we have created queue for bulk operation and Intelligencer and to start ensure I will run my Kron run.

B

B

Need to do it worst.

B

So I started to come and.

B

Eventually take some time and I, don't think it's necessary to wait till the end. But after.

B

Updating my page I think we already will see the short page will be change. It.

B

A

Can show the status of the variation? So if you click you details and you can go to page, it's.

B

Do or short line is done. We will have fortune it's fortune item, because it split into 1400 is split in 14 lines for 100, bearer ones, so I think I believe after refreshing. Once more, we will see result on first page, it actually I said it's 100 product per page.

B

Yeah hee hee we used to have the full source mentally on the phone source. Now our first hundred products already turned short their inventory to source one and therefore it's a sellable quantity count on stuff, fun and eventually I think some minutes all for tonight and will be done and all 1,400 products will be to ensure, as I already checked today.

B

Well, I think this all for me.

A

Yes, thank you for this demonstration. We will get back to this functionality little bit later, while we will be talking how to mitigate one of the performance degradation, but currently just just mentioned that this functionality of the inventory inventory migration would be handy for merchants as a the quick start at the quicken Borgan process for those merchants who won't want to start using the multi source functionality based on their single stock single stock magenta, which they have after after up and running magenta.

A

So out of the box, magenta still the single stock and you an initially merchants should should add custom sources, custom stocks and then the quick.

A

Quick onboarding process: they can just make the migration from the default source to the custom sources, and actually this was the rated by Slava and we'll talk a little bit later. How this particular operation can boost the performance even for the single stock merchants.

A

So but now, let's talk a little bit more about the performance investigation we've done during this week and what the performance degradation we we investigated. So what the performance degradation was observed and the Yvonne we'll talk and we'll make the demonstration of what what they issue we currently found and will, after that, we'll we'll talk a little bit more about root cause of this issue. And how can we fix it? So it won't feel free to share your screen and make a presentation of what you identified.

C

Hello, let me share my screen.

C

So we have, we found issue 1921, it's about perform performance degradation, category, catalog, page.

C

Yeah I will show you SQL queries with each Agra date on category page: okay, as ah okay, I've loaded pictures from performance tool kits with products and I have enabled rest. You know profile for show you all speech query as Gil query speed.

C

That was the request to which degrees performance and it work 100 times longer and like this, he aversion.

C

On msi, we have query which perform 100 milliseconds, but on C inversion, this query perform one millisecond.

C

Next, where is which I will show with a mess around 10 to 20? Milliseconds, wonder of life, see a version.

C

C

On our side, we can see that where, as performers 16 milliseconds 19 milliseconds 16 milliseconds on the C version, we sees that all these queries perform maximum 5 milliseconds.

C

Ok, I also I want to say that if we transfer transfer all products.

B

C

Stock to custom stock, Rachel Smith approximately equal to Magento without MSI.

C

Speed without msi speed with msi, but all products transferred to custom stock. It's all from me.

A

Think you won so actually the main root cause for the degradation is it so I will. Let me share my screen.

A

Let me share my screen, and here is the issue we created for this particular ticket: the performance degradation of the catalog page.

A

Actually, the performance degradation is not just on the catalog page, but on the search page search, result page as well and on the on the product view page and actually currently we experience so I degredation on all the pages where we have the SQL queries involved involved, join on the inventory stock status page because all the users of the Cadillac inventory stock status index was substituted with hadouken venturi hmm inventory underscore stock underscore one, and especially one because, as you know, we would build custom indexes for for stocks, and we have.

A

This is to be distributed, independent indexes for each particular stock and for custom stocks. This this indexes would be represented, independent tables and for custom stock. They would be like inventory, stock to stock, three stock, 5, etc, and actually the number means the stock ID, but for the fall stock, so for the for the single stock mod and for merchants who used default stock, so the stock.

A

What was created in the time when, at the time of majority installation we use inventory stock one and taken into account that in the new news system in the new inventory mekinese, we always rely on the product squ, but not on the product idea, but in a previous version of system. So in in the legacy index we our our our inventory index, contains not product SQ, but product IDs of the table. Catalog k, local vendor e stock status, does not contain products q. It contains a product ID so to deal with this issue.

A

We created the my sequel view and for inventory stock one we we created. We created my sequel view, which is represented as a as a drawing offer of legacy index of the stock status table with with the catalog product product entity and instead of instead of, for example, old query where we, where we had just the inner, join to the calc inventory stock status with with the filter by the product aging.

A

Instead, instead of this pre-k lightweight query, we are given something: Samsung exists: we're given a beginner drawn to the inventory stock, 1 and inventory stock. 1 is actually our my sequel view, but, along with that, we are also adding joint to the to the catalog product entity.

A

A

This is this introduced additional like to to joins, which introduced this.

A

This performance degradation, and especially it matters for for search, result in this particular query where you can see that we have drawn with a temporary table like search DMV, which contains the search result for particular query table and- and in this particular case we, the performance degradation, is pretty big here here you can see other cases demonstrated by demonstrated by Yvonne and the main difference you can see on a on a on a my sequel. Query explained so this is this is default query.

A

This is default query without Emma side, where we help just a join to the legacy in Catholic inventory index, so the case documentary stock status- and in this case we have just just a join of four different queries and all these join are done using the mostly using the primary primary index key, but as soon as we have the as soon as we have the bicycle wheel involved.

A

Now we have three additional joins because because this is actually my sequel view and my simple view represented by drawing of two tables and to keep the contract, we have to join this Messick review also using the catalog product entity. So this is also additional drug, and these three drones introduced introduced this degradation.

A

So from what we see now, it is better currently like.

A

Current employment Jason you using the my secure view, makes our our application code is really agnostic to whether we work with with the legacy implementation of this a new implementation. So we can still rely on this on this contract provided by the by the stock inventory stock index. But probably this is not acceptable from that from the performance point of view, so there are actually two to workaround for this issue, so the first workaround, it's pretty straightforward and this workaround is, will fit for merchants who don't have bundled products, because current state weather has this.

A

My secret view is actually intermediate state. So we will use this message review up to the time when all the product would be supported by MSI. So my basic values actually also work around because currently, not all the product types support, the multi sourcing and currently the only time type product type is not supported, is a bundled product and as soon as we will get the bundled product support, we can drop the my sequel view and we we can create index for the default stock, actually, which will be represented by the single table.

A

As any other as any other indexes, but if merchants, for example, now don't use bundled product, we really recommend this version to migrate all his products to custom stock, so just just to create a custom stock just to create one custom source, assign it to this custom stock and migrate, all the products from the default source to this custom source.

A

After that, we wouldn't use the bicycle view and we will use a real index which would be created for this particular stock, even even so that merchants will continue use a system in a single stock mode, but here so he will have that custom stock in one and only custom source assigned to the stock.

A

But he will get the performance boost because in this particular setup he wouldn't be using the bicycle view anymore, and instead we will mighty realize that my sequel view in a real in a real table, and this is what actually was demonstrated by Yvonne this. This issues and these this values for this sequel queries will get the time for this queries.

A

Queries processing will decrease again from like from 10 from 10 to 20 milliseconds to 1 or 2 milliseconds, because there will not be three turns involved and the system will do just one run in this case, but for now along this. So this is a workaround. Currently, merchant merchants can use when, when magenta to the street, that all will be released with the first version of a messiah, but starting from from from tomorrow, we will start working on fixing this performance issue and probably we will like.

A

The easiest solution, how how this is you could be solved. We can introduce like conditional logic, which will check whether this is whether this is a single stock mode and if the custom, if the default stock, if our stock resolver returns the default stock, we can build the same. The same SQL queries which were built before so we can make a fall back to the previous implementation and would not overwrite and would not overwrite SQL queries or for the search requests with with the custom logic with the MSI related logic.

A

So we can introduce this fallback logic to preserve the previous behavior for default stock mode. So this supposed to eliminate this degradation and get back to the previous previous performance numbers we had before. So we will start working on.

A

This particular fix will check whether, like for this special case for the default stock, we did not want to introduce it from the beginning, because our idea was because you know, like the last conditional logic you have in your system, the better your architecture and the better code looks like, and this much easier to work with with the customization with the plugins issue this already, because you don't have any any condition you need to track some specifically but looks right from the take taken, the performance aspects and taken the paying attention on the performance degradation we got for the fall stock.

A

We will introduce this fall back for the default stock, just to eliminate this degradation of for default stock.

A

So probably this is all regarding this particular issue and another issue. So this also this also performance issue. The another performance issue was found by bar stick, so bar stick was on fire during during this week because he made the pretty pretty pretty cool vibe Bernardo voted for release of the first project based on magenta to the tree and plus MSI.

A

So Bartok made a great job on toast polska project and what he identified during this project that we is pretty strange. But for some reasons we don't have the we don't have the index cover index in inventory source item table. So the inventory source item table currently contains product on sources, so products on a quantity of products on sources and in this table we have just table.

A

We have just index covering stock code, a source code, sorry source code and s Q. So this is a composite. This is a composite index, but currently we don't have index starting from the SQ, and you can see that Bartek has a pretty on his project, those polska they have pretty big database with more than two million two million records storing in the table and, for example, such query words. They have just filtering by ask you this this.

A

The tape table scan is involved, and actually the database adapter should make a table scan to find this to find a particular record, and this type of queries are executed when not on the front end. But on the back end, when admin goes to the admin page to talk to the product, editing page and at this time we are retrieving all the source items filtering by particular SQ.

A

But my proposition here to Bartok was that we, so he shall Bartok idea was to create dedicated in the squ only but my year here was that we need to introduce covering index and introduce an index which will cover squ, then source code and then quantity.

A

In this case, all these three fields would be covered by index and the database adapter will use this so called covering index and retrieve all of them all the fields and all the records from the from the RAM from the first memory read on the memory and no need to make disk access, because if you will create the index cover in just s, cube field will retrieve all the excuse and to retrieve the data, so the source, codes and and quantity database adapter should proceed with with disk access, and this still could be quite slow if, if the source code allocated on a different areas of the disk and yeah.

A

So this is pretty interesting case and this fix. So this covering index supposed to improve the performance of product editing page in the admin panel.

A

So this we had two issues identified urine urine this week and we will try to provide fixes for this issues in the in the nearest future and I hope they will be demonstrated, maybe on the next demo meeting or maybe maybe in two weeks, because this performance fee issues a pretty crucial for us. We will try to to make our best to fix them as soon as possible and also have a small announcement for you.

A

That recorder will Ricardo finalised the development of distance-based algorithm, so the distant base algorithm is completed and now now this algorithm in this pool requests and recorded a finished it actually just recently like 17 hours ago. You really wanted to make a demonstration today, but we agreed that he will make this final demonstration next week, and this is really cool demonstration because record the introduced support of the Google Jai accordion. Along with that, he introduced a possibility for the fallback to the offline Jericho Dean into introducing the database of zip codes.

A

And now, if the, if the Google is not available, yeah or if the, if the Google API is not specified, the system will will proceed with a fallback to the to the pre specified database of the zip zip code that will make the whole computation is not so accurate as Google does, but but still it will show that the system is mature and even without the connection to them to the Google services can proceed with these results of the distance based algorithm.

A

So this is small announcement for the next week, and probably probably this is all for today. If there are any questions from from you regarding the performance regarding the Mexico view or anything else, you can you can ask this question now or you can you can throw you can you can drop the question into the into the MSI selectional.

A

And we will also will definitely answer them as soon as possible.

A

Thank you very much for your attention today and have a great back Friday and happy sense giving day, and thank you that you spend this time on this really really rush day today with us and hope to see you in the week on the next msi emo medium. Thank you and goodbye thanks.

C