We have recently added 13K products to our Magento 2 based store. When we tried to run the reindexing process the indexer kept being stuck half way and our store became unreachable. We had to restart mariadb and run the indexer again to complete the reindexing successfully. Since we have a number of jobs running during the night including reindexing we had to find out why this is happening.

After reading up on the problem it became clear that it is some kind of a resource issue. We tried running the reindexing process granting 4G ram for the php process that runs the indexing but the issue was still the same. The command we used was:

php -d memory_limit=4G bin/magento indexer:reindex

After checking magento’s system.log we found the following warnings:

[2020-01-18 09:39:36] 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: 600000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []
[2020-01-18 09:39:37] 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: 600000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []
[2020-01-18 09:39:38] 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: 600000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []
[2020-01-18 09:39:38] 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: 600000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []
[2020-01-18 09:39:38] 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: 600000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []
[2020-01-18 09:39:38] 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: 600000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []
[2020-01-18 09:39:39] 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: 600000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []
[2020-01-18 09:39:44] 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: 600000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []
[2020-01-18 09:39:48] 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: 1000; Allocated memory size: 32500000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []

After reading up on this issue we added the following parameter to the maridb’s 50-server.cnf configration file under the [mysqld] section:

innodb_buffer_pool_size=3G

If you use mysql instead of mariadb the file you need to modify is my.cnf.

UPDATE: This didn’t solve the issue permanently we also had to change settings in vendor/magento/module-catalog/etc/di.xml file. batchRowsCount in Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\BatchSizeCalculator to 1000

<preference for="Magento\Catalog\Pricing\Price\MinimalPriceCalculatorInterface" type="Magento\Catalog\Pricing\Price\MinimalTierPriceCalculator" />
<type name="Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\BatchSizeCalculator">
    <arguments>
        <argument name="batchRowsCount" xsi:type="array">
            <item name="default" xsi:type="number">1000</item>
        </argument>
        <argument name="estimators" xsi:type="array">
            <item name="default" xsi:type="object">Magento\Catalog\Model\Indexer\Price\BatchSizeManagement</item>
        </argument>
    </arguments>
</type>

..and the batchRowsCount in Magento\Catalog\Model\Indexer\Category\Product\Action\Full to 10000

   <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>

These settings reduce the batch size for the indexer making it lighter on server’s resources. Let’s see how it goes now.