[Performance]Magento 2.3 Performance issue because of huge amount of mysql queries per products in cart

[annakoehler]

We found a server performance problem related to the number of products in cart: the more products in cart, the more mysql requests to the server (see in actual results below). A lot of mysql queries are related to inventory_stock information.We have this issue if Magento_Inventory modules are enabled..
We tested it on both magento 2.3 versions: 2.3.0, 2.3.1 and 2.3.2.
Magento 2.2.x versions don’t have this problem.

Preconditions (*)

1. Vanila magento CE 2.3.x(composer install) with manually added 10 products via admin
2. To see mysql requests you need to:

• install Mgt_Developertoolbar https://github.com/mgtcommerce/Mgt_Developertoolbar to see the number of requests and enable it Mgt_Developertoolbar Stores --> Configuration --> MGT-COMMERCE.COM --> Developer Toolbar

• OR Enable Profiler

3. PHP 7.1, PHP 7.2
4. MYSQL 5.6.38

Steps to reproduce (*)

1. Add one product to cart
2. Navigate to checkout and check number of mysql requests in developer toolbar
3. Add another product to cart
4. Navigate to checkout/cart and compare number of mysql requests in developer toolbar

Expected result (*)

The number of requests should not change much, in magento 2.2 it was ok, about + 10 mysql requests per product.

Actual result (*)

With one product in cart:

With two products in cart:

With 10 products in cart:

Here are our results:

Qty	Mysql queries
1	176
2	253
3	339
4	416
5	493
6	570
7	647
8	724
9	801
10	869

If the Amazon module is activated, an even greater number of requests occur and not only on the checkout page, but on all pages, since it loads the configuration using quote in the isQuoteDirty() function, which generates many mysql requests (Tested on magento 2.3.0)

I duplicated this issue from magento/magento2#22027

[ishakhsuvarov]

Hi @annakoehler

Thank you for the detailed description.

Am I correct in understanding that you don't have this occurring with Inventory modules disabled? We have a couple of performance patches which would be included in 2.3.3 and we'll check if your specific case is covered

[annakoehler]

Hi @ishakhsuvarov
Yes, with disabled Inventory modules we don't have this issue.

[budoland-ab]

Hi @ishakhsuvarov

we have a lot of trouble with our international b2b customers regarding to that issue. So can you confirm your performance patches cover this issue? Otherwise we need to disable MSI until it's resolved.

Unfortunately there I found no release date of Magento 2.3.3 so is it possible to get a patch for MSI at this moment?

[perfpcs]

We have the exact same problem and it is even worse than this example! With Amazon Module enabled we get not hundreds but thousands of unnecessary sql queries that kill the performance of our site. Page loads of 15+ seconds and 8-10K sql queries. We have had to disable the entire msi portion of Magento to have a proper working site. With msi disabled we are now back to 2-3 page loads and less than 100 queries. This really needs a proper fix! The sooner the better!

[ishakhsuvarov]

Guys, thank you for all of the reports, information was very helpful.

We are currently working on the problem and evaluating a possible patch. Hopefully it would be available shortly.

[perfpcs]

@ishakhsuvarov Nice to hear!

[vdevx86]

@ishakhsuvarov Finally, good news!
Действительно хорошие новости, Евгений!

[ishakhsuvarov]

@annakoehler @perfpcs @budoland-ab @vdevx86

At this point we have a couple of patches which significantly decrease number of queries, would be great if you could try them out and share feedback on the effects:

• https://github.com/magento/inventory/commit/ad57569792551b58d22e169ed536da4ac22a0f2d.patch
• https://github.com/magento/inventory/pull/2561.patch

We are still working on couple more optimizations and will be wrapping it up in a final solution down the road.

[perfpcs]

I would be happy to test this but I am curious what this all fixes because the descriptions mention only "shopping cart". Our issues were not specific to the shopping cart whether it was empty or had something in it but more general use of our entire site just browsing pages and msi doing thousands of stock queries. Do these patches address general site performance or specific queries only related to the shopping cart? I want to make sure this will be worth to test and will address the problem we are having before installing. Thank you.

[ishakhsuvarov]

@perfpcs These patches are not targeting cart specifically, however we have used cart page for benchmarks. I suspect they would decrease number of queries on multiple frontend scenarios .

[perfpcs]

@ishakhsuvarov did this patch make the M 2.3.3 release? I would like to test it with this new release. So hopefully the bug fixes were added. Let me know please.

[budoland-ab]

@perfpcs Could you test the 2.3.3 release already? @ishakhsuvarov You owe an answer.