3

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

5

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.

8
  • i follow this but it not work for me – Jigs Parmar Jan 30 '18 at 9:28
  • you descirbe how i sove this issue – Jigs Parmar Jan 30 '18 at 9:28
  • @jigsparmar - Please review updated answer. – Pratik Oza Feb 1 '18 at 17:49
  • also try the below code but its not work for me – Jigs Parmar Feb 2 '18 at 5:37
  • 1
    @PratikOza I got same log while reindexing. When i applied your solution i got following error, PHP Fatal error: Uncaught Error: Unsupported operand types in /var/www/html/vendor/magento/framework/Indexer/BatchSizeManagement.php:49 – Knight017 Jul 17 '18 at 11:44
4

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

3
0

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.

0

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.

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.