I am stuck in an issue with Magento 2 has more than 13 million products. We have managed to upload 13 million products although is slow and it works like indexing, product page, and search page. enter image description here

My frontend category page isn't opening now after waiting almost 2 minutes getting memory exhausted we have tried to allocate 18GB memory but it gets stuck on this query and through error memory exhausted.

SELECT 1 AS status, e.entity_id, e.attribute_set_id, e.type_id, e.created_at, e.updated_at, e.sku, cat_index.position AS cat_index_position, stock_status_index.stock_status AS is_salable FROM catalog_product_flat_1 AS e INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id='4' INNER JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 WHERE (stock_status_index.stock_status = 1)

Can anyone please suggest me how can I overcome this issue or speed up the processes and MySQL queries what architecture level changes should I do to make it work the category list page on frontend. I know this is huge data but it's our requirement to have this much products. We have a pretty good server

  • Intel Xeon E5-2689v4
  • 128 GB RAM
  • 1200 GB Datastores
  • Try to explain the query in MySQL to see what exactly it is trying to fetch. Strange for me is that it tries to load all products from category ID = 4. Probably there is huge number of products and php is not able to load all of them because of the memory limit. Try to debug it and see where and why it is called without any criteria. – rafal-kos Apr 26 '19 at 8:44
  • Additional information request. Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server tuning analysis. – Wilson Hauck Apr 26 '19 at 17:19
  • Please post TEXT RESULTS of EXPLAIN SELECT SQL_NO_CACHE ....... (your query); Thanks – Wilson Hauck Apr 26 '19 at 17:25
  • No posting of requested data=No analysis to improve your instance. – Wilson Hauck May 4 '19 at 17:32
  • No information=no suggestions. – Wilson Hauck May 10 '19 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.