Magento 2 while reindex INSERT INTO 'catalog_category_product_index_store1' stuck the query

Question

I am working on Magento 2.3.2. When I run re-index command php bin/magento i:rei Design Config Grid and Customer Config Grid is completed. But it is taking too much time to re-index Category Products and Product Categories

I checked the SHOW PROCESSLIT; in my database and I am getting the following query which is running from a while.

INSERT INTO `catalog_category_product_index_store1

After a long time, it is displaying

INSERT INTO `catalog_category_product_index_store2

I tried to reset the indexer and again re-index only those two indexers. But the same thing happening.

I have 60k Products in my Magento Store.

It has suddenly happened from a few days in live only. In local, it is re-indexing everything. The issue is only in live. I have the same number of products in local.

Is it any permission issue to any folder like var or generated?

Please guide me on that. Thanks in advance.

Answer 1

Most probably the difference is that on live you have way more traffic than on your local env and this is interfering with the queries.
I recommend reducing the number of rows processed at once by the indexer.
You can do that by adding this in the di.xml from one of your custom modules.

<!-- this is for the category product indexer -->
<type name="Magento\Catalog\Model\Indexer\Category\Product\Action\Full">
    <arguments>
        <argument name="batchRowsCount" xsi:type="number">500</argument>
    </arguments>
</type>
<!-- this is for the eav indexer -->
<type name="Magento\Catalog\Model\ResourceModel\Product\Indexer\Eav\BatchSizeCalculator">
    <arguments>
        <argument name="batchSizes" xsi:type="array">
            <item name="decimal" xsi:type="number">100</item>
            <item name="source" xsi:type="number">100</item>
        </argument>
    </arguments>
</type>

I have no procedure to determine the numbers (500 and 100) in the example above. I just used "rule of thumb" for them in the project I'm currently on that has a catalog size of around 50k products, around 170 attributes and around 20 categories.

More information about indexer optimization: https://devdocs.magento.com/guides/v2.3/extension-dev-guide/indexer-batch.html

Answer 2

In addition to @Marius♦ answer, Please optimized Category & product relation means do not unnecessarily assign products to the category.

Suppose, If you will feel a product does not need to be assigned to a category and then you should remove that product that category.That why catalog_category_product_index and catalog_category_product table.

It will also help to indexing optimization.

That wrong assignment between product & category is a common mistake the people do.

Answer 3

if you use Maria db 10.4 and face long time when reindexing you need to modify default MariaDB configuration and set the following parameters:

optimizer_switch='rowid_filter=off'

optimizer_use_condition_selectivity = 1

Ref: https://experienceleague.adobe.com/docs/commerce-operations/performance-best-practices/configuration.html

Answer 4

@david-coder - I am just thinking the only difference between local & prod may be cron settings. You can verify the cron too which may try to run some process and may be delayed. It may impact the reindex process.

Answer 5

I have this issue many times in my cases this issue can resolved by running

php bin/magento indexer:reset

then run

php bin/magento indexer:reindex

And wait it will take time do not exist from command and do not run any other command.Let it take as much as time just wait. It will take time because you have lots of products so wait. Your indexer Process will get completed. Hope this will work for you.Mark answer accepted if this solved your problem Thank you :)