Category Products Index Reindexing in CE 2.1.8 takes 1000x longer to run. #10531
Comments
|
Next reindexing with 2.1.8 code will not complete within minutes as well? |
|
As there seem again to be more update problems when updating to magento 2.1.8 i ask myself if the magento core team really tested updates from former versions as well? |
|
@orlangur Unfortunately I rolled my test environment back last night, so I can no longer test. However, it shouldn't have taken 4 hours in the first place. |
|
@spyrule no doubt it should, just wondering whether it's some one-time issue or it works like that always. |
|
How many store views & customer groups do you have in the system? |
|
@choukalos 2 websites each with 1 store/store_view, The stock 4 groups. |
|
Unfortuantely, I can confirm that I have been experiencing this issue after upgrading to Magento 2.1.8 as well. Re-indexing those two categories never completes for me, whereas before they completed in 6 minutes. You can find the details of my outlined problem here: https://magento.stackexchange.com/questions/189180/magento-2-1-8-reindex-timeout |
|
Same here - this leaves us speechless. We can not stay with 2.1.7 because this version does not work right with PayPal express and also indexing is false in 2.1.7 - and upgrading to 2.1.8 is not possible because of this issue. This really leaves us speechless. |
|
This should almost be an emergency patch for 2.1.8. |
|
The @magento-team promised to built a more stable system over the last 6-9 month. We really do not know what to say about the massive update problems reported here and experienced in our systems. |
|
I second the urgency of a patch to fix this VERY important issue. Our stores won't work properly or operate at all if indexing isn't working. |
|
@vherasymenko @orlangur can you pls adress this a a priority one major bug to the development team now - the system is not usable without indexing. I think this should be investigated and a quick fix should be delivered now - not in days, not in weeks. |
|
We are experiencing the same issue with a catalog containing over 100k products and around 1000 categories with multiple store views. In our case it seems that 8 hours is still not sufficient to complete the task. We unfortunately killed the process before it reaches completion as we thought it was stuck somewhere in the process. When we tried again, we used strace and we realized that it is in fact iterating very slowly, looping over a query batch. There is an extract of one of these queries:It is unfortunate as we just upgraded from our 2.1.6 version that was also not reindexing as it should. We had a "functional" way to reindex the The fix applied by Magento to resolve the 500 limit issue in 2.1.8 seems to be the culprit of this new bug. Comments:It might be a good idea to add a configuration allowing to set the batch size from the backend advanced configurations. This way every store owner would be able to adjust the performance/recource consomption ratio for the reindexing process. The relation between the server capacity (memory/CPU) and catalog size vary greatly among Magento users and I think that there is no "one size fits all". |
|
I have notice that using the fix in #8018 The reindex completes with no errors. But the problem it fixed in Magento 2.1.7 became unfixed. So It really doesn't help that much :/ |
|
We are having the same issue with a 300k+ sku catalog. |
|
It seems i stumble upon some kind of solution. I am not sure what I did but I will tell you what I remember doing.
Now the only thin missing is some of the product images on the category page. The weird thing is that if i go to the product page I can see the product image. There are also a small version in the cache folder. |
|
@alankent are you aware what is going on here with the development? I thought you promised over a year ago to improve the bug tracking process and aim for a much more stable system? Could you pls explain what is going on with the magento2 development team since now over 2 years? Every release and update contains hundreds of new and old bugs. At the moment you can not even upgrade to 2.1.8 - also you can not stay at 2.1.7 because of the known major bugs (for example PayPal bugs). It seems that this does not go the right direction. |
|
Same issue with us too. We had store running very well on 2.1.2 Enterprise Edition. Product flat reindexing used to take hardly 30 seconds for our catalog size of 25000 + products. We upgraded to 2.1.7 last week, and reindex now takes around 30+ minutes. Also category product count issue which was not there earlier got introduced. Magento Enterprise support is worst in this case. Seems we are wasting $20000 every year for the same. As most of the time they dont even know the issues and 90% of issues never got solved from them. They just keep on asking for data access, once given, they keep on asking for mysql / php global variables. We had store running very well since a year, and all of the sudden on upgrading site stops functioning well. Any idea / solution for the same would be appreciated. Thanks |
|
This problem is serious and still on-going. Can we please get a response about the possibility of a fix, and how soon it will be available? |
|
we are having the same problem on a store with 330k+ products and 500+ categories, last week the reindex took roughly 24 hours, this week after the addition of 1,000,000 category filters the reindex is still running after 24 hours... worst part is that the products drop out during reindex and it halts dev... need a fix quick!!! |
|
Hi @vherasymenko, Hi @choukalos, Should we expect a fast fix in the next few days? Or is this rather a bigger complex issue with no fix expected in the foreseeable future? In such case kind of patch or a workaround would be quite useful. Would you recommend to stay on\roll back to 2.1.7 for the time being instead? |
|
@korostii i think you can not expect a quick fix, workaround or patch from the magento team. This never happened on magento version 2 as far as i know. The "normal" process of tickets here is the magento team creates an internal ticket and then it takes in the best case 3-6 month - in the normal case 6-18 month until a fix is provided with a new release. So i would not suggest to hope that the magento2 team is providing help here at all. |
I would have to respectfully disagree. Similar approach might be appropriate here, especially considering that the |
|
@korostii thanks for your input - this is one bug solved fast. Pls have a look at about 200-300 bugs reported here which are not fixed since 12-18 month - maybe longer. In my opinion you can not say that you can expect to fix this fast because another bug was also fixed fast. You have to take the average time here a bug is fixed and this is in my opinion not 1-3 month but 6-18 month. You can find a lot of examples here on GitHub of reported bugs not solved for over 1 year. |
|
It looks like there will be no fix in v2.1.* officially. The best case it will suggest you upgrade to v2.2.
|
|
@andidhouse, Yes, I do know at which most of the average-priority bugs move around here. What I am saying is that this is no ordinary bug and the Magento guys could for once show they are able to move fast when it's deemed necessary. If such a massive issue introduced in a patch release is not the time time to do that, I do not know what is (a security hole? broken checkout?). I think this issue deserves attention and should've been fixed in under a week in 2.1.8.1 or whatever like a week ago unless there're any complications to it. I know @orlangur might argue that Magento doesn't need short development/bugfixing cycles and it's okay and fine and bug-free as long as it's covered by all those automatic tests... Well, how come that testing didn't catch this bug prior to release? =) |
|
@korostii - totally agree with you that this needs priority one to fix by the magento team. I also totally agree with the priority of this one with you - if not now when? But to be honest we learnt the last 1.5 years that no quick help is supported by the magento2 team at all - no matter which bugs they produce or which version they release. And this is why i think nothing will happen here for the next weeks - which is a shame. |
|
@orlangur Yes, I was wondering if 2.2 fixed this indexing bug. I am worried about upgrading and breaking the indexing again. At least the other fix works for now.. |
|
@orlangur Влад, если есть желание, можем обсудить. Нужен контакт. |
|
i have installed 2.2 but am seeing a few errors i believe are related to some extensions that i have installed... other than that all seems to be working well |
|
Before 2.2 release price indexing was "invisible". In 2.2 it takes very long time. |
|
@spyrule, thank you for your report. |
|
Will this be ported to 2.1.10? |
|
I can confirm the workaround |
|
the fix for (2.1.8, 2.1.9, 2.1.10): $object->limit($this->currentBatch, $this->batchSize);correct: $object->limit($this->batchSize, $this->currentBatch);wrong code produces, which is exponentially slower than non-range indexing: SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 500 OFFSET 500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 1000 OFFSET 500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 1500 OFFSET 500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 2000 OFFSET 500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 2500 OFFSET 500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 3000 OFFSET 500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 3500 OFFSET 500correct code produces: SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 500 OFFSET 500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 500 OFFSET 1000
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 500 OFFSET 1500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 500 OFFSET 2000
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 500 OFFSET 2500
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 500 OFFSET 3000
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 84
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 91
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1 WHERE (cc.path LIKE '1/381/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) ORDER BY `cc`.`entity_id` ASC
LIMIT 500 OFFSET 3500But this still misses the last batch in the range so if you want ranging enabled you need to change: $this->isValid = ($this->batchSize + $this->currentBatch) < $this->totalItemCount;To: $this->isValid = $this->currentBatch < $this->totalItemCount;To check, apply fixes and compare number of products in "catalog_category_product_index" with isRangingNeeded() returning false and true, both cases should result same number of products in table |
|
@ashuston your method works for my server. you save my whole day. Thanks a bunch |
|
The issue (and the fix) is similar to #8018. |
|
Why was the ticket closed? Where's the official fix? |
|
I really don't see why this issue was closed. As mentioned by @am2008 the price indexer takes FOREVER in 2.2.1. It's been running for over 24 hours and still going. (350K+ products) |
|
reported that it is reproduced on 2.2.1 |
|
I am running M 2.2.1. i7-7700HQ / 16GB RAM /m.2 SSD It used to run 58 minutes top on 2.1.9 I don't know what to say :( EDIT: My laptop is so loud atm that I'm not sure how will I sleep tonight :D |
|
Price reindex time = X * customer_groups. An almost direct dependence is observed. For example: if you have 10 customer groups and you using only 3, try delete unused groups and get x7 boost. Here 1M+ skus reindex log: This is a blank database(without the use of reindex). If suddenly the reindex process terminated(electricity or the internet was gone), subsequent reindexing turns into a nightmare, as @tomasinchoo described. |
|
why are you guys talking about price index in this issue? This originally was about category_product index.
if there is issue with price index another issue should be created imo. |
|
Problem with price indexer is hard to detect on new installations, as core of the problem is with batch calculator and estimating page sizes. Instead of counting how many products there are and using that to calculate pages, batch calculator relies on the highest product id. Meaning that if you have single product with id 1000001 you will have a lot of indexing operations for nothing. Further to that, price values are calculated on (almost) every iteration so the problem is quantity of operations. Sugar on top, batch size is terribly low. Ugly hack around would be to adjust batch size here so that you are sure it will take all products at once. That means that it will do only 8 calculations (8 indexer types), which is much faster. And by much faster I mean finishing within 1-2min compared to not being able to complete the job even after 2 days of working. |
magento-engcom-team
added
Fixed in 2.2.x
|
Working fine reindexing with 4,00,000+ products in magento 2.1.9 after change @ashuston comment. thanks guys |
|
Anyone have a fix working for 2.0.6? |
|
The batch calculation is not fixed in 2.2.x. The batches still walk through all the ids even if there are no entities in that batch.i watched the price indexer try to reindex bundles, batch at a time when there are no batches on the site. There are queries that select * from catalog product entity where entity_id between 100 and 200 (depending on the batch size) I briefly looked at 2.3.x and it appear the price index is changed. Has this been fixed properly? |
ghost
mentioned this issue
|
Hi, I am having this issue on 2.2.6, Im new to GitHub so not sure what I need to do make you guys look at this? Mike Preconditions (*)
Steps to reproduce (*)
Expected result (*)
Actual result (*)
I could really do with some help to diagnose what is going on here. Where should I look for more information? All help greatly appreciated. Mike |
|
Magento 2.2.7 has feature to disable product eav attribute indexing when not using MySql as search engine. Most of the vendor's have large catalog and they prefer fast searching with external search engine. |
and others
Re-indexing the Category Products Index takes literally 1000x longer to complete.
In my production environment:
(This is run from 2.1.3)
In our Development enivornment (exact copy of Production, upgraded to 2.1.8 only):
Preconditions
Steps to reproduce
Expected result
Actual result
The text was updated successfully, but these errors were encountered: