Magento2: [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

• PHP 7.1, PHP 7.2
• 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 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)

[nsiebler-dm]

Another possible hint also regarding the performance:

We had to roll back an update from 2.2 to 2.3.
MySQL was blocking due to too many connections that were stuck with a lot of those queries that would not finish:
SHOW TABLE STATUS LIKE 'inventory_stock_1'

Could be just a symptom but that does not look right to me.

[m2-assistant[bot]]

Hi @engcom-backlog-nazar. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and _stop verification process here_!
  

• [ ] 5. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• [ ] 6. Add label Issue: Confirmed once verification is complete.

• [ ] 7. Make sure that automatic system confirms that report has been added to the backlog.

[nsiebler-dm]

@engcom-backlog-nazar Any updates on this issue? Can we deliver any more information to accelerate a solution?

[pmathbliss]

This is also happening on sites without bundles and 24k products.
Is there a join to the catalog_product_super_link or catalog_product_relation that could be done to reduce the number of queries here?

[groomershop]

We confirm this on on fresh install:

• Magento 2.3.2 (from composer): composer create-project --repository=https://repo.magento.com/ magento/project-community-edition public_html
• Magento sample data (from composer): php bin/magento sampledata:deploy
• PHP 7.2.20 with Opcache and MariaDB (MySQL) 10.3.10

Result:
1 product in cart: 446 queries in checkout/cart/ and 342 queries in checkout/
10 product in cart: 3247 queries in checkout/cart/ and 2187 queries in checkout/
20 product in cart: 5945 queries in checkout/cart/ and 4247 queries in checkout/
With 20 products in cart the SQL queries takes over 0.5s on empty server (6 core i7-8600, 64GB RAM DDR4, NVMe disks).

This is a huge problem especially for B2B shops.

Any ideas how to fix this?

[groomershop]

Additionally we can confirm that on Magento 2.2.x there is not such issue - we have added 100 products to cart in Magento 2.2.7 and in checkout there was then about 2000 queries to MySQL (query time 0,2-0,3 -> generally acceptable).

[gaurangpadhiyar007]

Any update on this?

[Akki-8388]

Hi getting same issue even 2.3 is working slow rather 2.2.x version

is there any update?

[nsiebler-dm]

@Akki-8388 @gaurangpadhiyar007 and others
Here is what solved some problems for us so far, hope this will help you too or hint to a solution:

1. Check if MSI extensions are enabled (Any module starting with "Magento_Inventory")
2. Disable them and run setup:upgrade etc.

This prevented the stacking of most SQL queries for us, so queries are reduced to a reasonable amount and the shop is much faster. Checked for Magento OpenSource 2.3.0 and 2.3.2 on a project.
As the MySQL view for the inventory table join was removed as well with the disabling of the extensions, the "show table status ..." queries will vanish too.

This was not tested on a vanilla version, so feedback would be very welcome if this is helpful.

Of course this would not apply if you actually need the MSI extensions. In this case there are ways to cache or bundle the most MSI SQL queries with a custom developed module.

[Akki-8388]

Hello @nsiebler-dm

Yes its work thank you for suggestion.

Thank you,
Akshay Thakkar

[gaurangpadhiyar007]

@nsiebler-dm @Akki-8388
I have disabled all modules still it takes around 6 second to load checkout/cart page.
No third part module are on that page.

Magento_Inventory
Magento_InventoryAdminUi
Magento_InventoryApi
Magento_InventoryBundleProduct
Magento_InventoryBundleProductAdminUi
Magento_InventoryCatalog
Magento_InventorySales
Magento_InventoryCatalogAdminUi
Magento_InventoryCatalogApi
Magento_InventoryCatalogSearch
Magento_InventoryConfigurableProduct
Magento_InventoryConfigurableProductAdminUi
Magento_InventoryConfigurableProductIndexer
Magento_InventoryConfiguration
Magento_InventoryConfigurationApi
Magento_InventoryDistanceBasedSourceSelection
Magento_InventoryDistanceBasedSourceSelectionAdminUi
Magento_InventoryDistanceBasedSourceSelectionApi
Magento_InventoryElasticsearch
Magento_InventoryExportStockApi
Magento_InventoryIndexer
Magento_InventorySalesApi
Magento_InventoryGroupedProduct
Magento_InventoryGroupedProductAdminUi
Magento_InventoryGroupedProductIndexer
Magento_InventoryImportExport
Magento_InventoryCache
Magento_InventoryLowQuantityNotification
Magento_InventoryLowQuantityNotificationAdminUi
Magento_InventoryLowQuantityNotificationApi
Magento_InventoryMultiDimensionalIndexerApi
Magento_InventoryProductAlert
Magento_InventoryReservations
Magento_InventoryReservationCli
Magento_InventoryReservationsApi
Magento_InventoryExportStock
Magento_InventorySalesAdminUi
Magento_InventorySalesFrontendUi
Magento_InventorySetupFixtureGenerator
Magento_InventoryShipping
Magento_InventorySourceDeductionApi
Magento_InventorySourceSelection
Magento_InventorySourceSelectionApi
Magento_InventoryShippingAdminUi
Magento_InventoryGraphQl

Any other solution?

[m2-assistant[bot]]

Hi @engcom-Charlie. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [x] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [x] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [x] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and _stop verification process here_!

• [ ] 5. Add label Issue: Confirmed once verification is complete.

• [ ] 6. Make sure that automatic system confirms that report has been added to the backlog.

[engcom-Charlie]

Hello @annakoehler
I can't reproduce this issue on fresh Magento 2.3-develop.

Manual testing scenario:

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 and compare number of mysql requests in developer toolbar

Actual result:
The qty of mysql requests is similar:

1. With 1 product in cart
   
2. With 5 products in cart
   
   

So i close this issue.
Thank for your report!

[annakoehler]

HI @engcom-Charlie
Thank you for working on issue. Check please the number of queries on checkout/cart page. (if you have Amazon Enabled, you will see the problem on other pages also). We have still this issue if Magento_Inventory modules are enabled..

[engcom-Charlie]

@annakoehler thank you for contributing!
It is really works on composer version of Magento.
So i have to change a description of this issue and i will confirm it.
Thanks for your report!

[m2-assistant[bot]]

Hi @engcom-Charlie. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [x] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [x] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [x] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and _stop verification process here_!

• [ ] 5. Add label Issue: Confirmed once verification is complete.

• [ ] 6. Make sure that automatic system confirms that report has been added to the backlog.

[engcom-Charlie]

@annakoehler i have to close this issue because its not for own repository. Please duplicate this issue in Magento MSI repository and they will confirm it.

[annakoehler]

@engcom-Charlie Thank you!
I dublicated this issue, see here https://github.com/magento/inventory/issues/2496