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

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.