magento2 decrease batch size of catalog_category_product

Question

My site contains 10k+ products and 31 stores. I want to decrease the batch size of catalog_category_product

How can I do that?

When I try to do that, it gives me below error:

main.WARNING: Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size. Please update innodb_buffer_pool_size or decrease batch size value (which decreases memory usages for the temporary table). Current batch size: 100000; Allocated memory size: 9240000000 bytes; InnoDB buffer pool size: 2147483648 bytes. [] []

Answer 1

Magento can increase the memory for processing a large amount of data by using memory engines instead of InnoDB. The algorithm increases the memory value for the max_heap_table_size and tmp_table_size MySQL parameters.

When the allocated memory size for a temporary table will be greater than 20% of innodb_buffer_pool_size, the error message is written to the Magento log.

To prevent this error message, you need to update default Batching configuration of catalog_category_product (Category Products) indexer because "Current batch size: 100000".

For more details, take a look here.

Try following code:

app/code/[VendorName]/[ModuleName]/etc/adminhtml/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Catalog\Model\Indexer\Category\Product\Action\Full">
        <arguments>
            <argument name="batchRowsCount" xsi:type="array">
                <item name="configurable" xsi:type="number">100000</item>
            </argument>
        </arguments>
    </type>
</config>

Note: Decrease the batchRowsCount to resolve the error.

Answer 2

This worked for me. Put it in app/code/Malpani/Harsh/etc/adminhtml/di.xml Replace Malpani/Harsh with your module name (can be any registered module)

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Catalog\Model\Indexer\Category\Product\Action\Full">
        <arguments>
            <argument name="batchRowsCount" xsi:type="number">1000</argument>
        </arguments>
    </type>
    <type name="Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\BatchSizeCalculator">
        <arguments>
            <argument name="batchRowsCount" xsi:type="array">
                <item name="configurable" xsi:type="number">500</item>
            </argument>
        </arguments>
    </type>
</config>

https://gist.github.com/harshvardhanmalpani/6528f80c117174a0edb615c93903842f

Answer 3

If you look over this file.

vendor/magento/framework/Indexer/BatchSizeManagement.php

Here details are given, why the issue happened.

Now the logic is

if ($size > $bufferPoolSize * .2) {
   //-- Error will show
}     

Log warning if allocated memory for temp table greater than 20% of innodb_buffer_pool_size

Point a debugger as like below picture in xdebug CLI.

and run

bin/magento indexer:reindex catalog_product_price

You will found your possible problem, then you can make the solution.

Possible Solution 1:

For Price, the Batch Size is taken as default argument,

vendor/magento/module-catalog/Model/ResourceModel/Product/Indexer/Price/BatchSizeCalculator.php

Look at estimateBatchSize method

Now on your module add a file.

{Your_Module_Name}/etc/di.xml.

    <type name="Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\BatchSizeCalculator">
    <arguments>
        <argument name="batchRowsCount" xsi:type="array">
            <item name="configurable" xsi:type="number">500</item>
            <item name="default" xsi:type="number">500</item>
        </argument>
    </arguments>
</type>

Note: Add both configurable & default argument and the value is as per you. By default, it is 5000.

In the same way, you can change the value for others also.

Solution 2 :

Simply increase the innodb_buffer_pool_size size,

But consider the following things.

MySQL configuration for Magento 2

Answer 4

From Magento 2.4.3, we can set the batch size with environment variables app/etc/env.php

For example,

<?php
return [
    'indexer' => [
        'batch_size' => [
            'cataloginventory_stock' => [
                'simple' => 200
            ],
            'catalog_category_product' => 666,
            'catalogsearch_fulltext' => [
                'partial_reindex' => 100,
                'mysql_get' => 500,
                'elastic_save' => 500
            ],
            'catalog_product_price' => [
                'simple' => 200,
                'default' => 500,
                'configurable' => 666
            ],
            'catalogpermissions_category' => 999,
            'inventory' => [
                'simple' => 210,
                'default' => 510,
                'configurable' => 616
            ]
        ]
    ]
];

Magento docs: https://devdocs.magento.com/guides/v2.4/extension-dev-guide/indexer-optimization.html

Answer 5

On Magento 2.3.2, what worked for me was changing the file app/code/[VendorName]/[ModuleName]/etc/adminhtml/di.xml with the following:

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Catalog\Model\Indexer\Category\Product\Action\Full">
        <arguments>
            <argument name="batchRowsCount" xsi:type="number">10000</argument>
            <argument name="batchSizeManagement" xsi:type="object">Magento\Catalog\Model\Indexer\CategoryProductBatchSize</argument>
        </arguments>
    </type>
</config>

Flush the cache and reindex everything.