Magento – magento2 decrease batch size of catalog_category_product

magento2

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. [] []

Best Answer

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.

Related Topic