Magento 2 Please update innodb_buffer_pool_size or decrease batch size value

by

The question:

After installing Magento 2.2.2 when I check my error log I get this 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:
50000000 bytes; InnoDB buffer pool size: 134217728 bytes.

Where the error is saying memory size allocated for the temporary table is more than 20%. Which temporary table is it referencing to? How should I resolve it?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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

Method 2

  • Go to your server MySQL settings file (my.ini)
  • Search for innodb_buffer_pool_size and increase this value.

Method 3

Try to update the value for batchRowsCount to 10000 in {Your_Module_Name}/etc/di.xml file of catalog_category_product (Category Products) indexer MagentoCatalogModelIndexerCategoryProductActionFull

   <type name="MagentoCatalogModelIndexerCategoryProductActionFull">
        <arguments>
            <argument name="batchRowsCount" xsi:type="number">10000</argument>
            <argument name="batchSizeManagement" xsi:type="object">MagentoCatalogModelIndexerCategoryProductBatchSize</argument>
        </arguments>
    </type>

Method 4

The default innodb size is 128MB

[mysqld]
innodb_buffer_pool_chunk_size=134217728

Edit /etc/my.cnf and overwrite it to 512MB

[mysqld]
innodb_buffer_pool_chunk_size=546308096

Then restart MySQL. If you are unsure how, just kill it and wait for it to be restarted by your system:

killall mysqld

Method 5

The file is at /etc/my.cnf in root SSH, since the full path wasn’t noted previously. Upon making any changes to /etc/my.cnf file, please restart MySQL for those changes to take effect

Method 6

  • Search your my.cnf file by executing locate my.cnf (mine is under /etc/mysql/my.cnf)

  • Edit this file

  • Search for innodb_buffer_pool_size. If it does not exist then create it, e.g.

    innodb_buffer_pool_size=512M

  • Restart the mysql service sudo service mysql restart

Method 7

For updating the MySQL configuraiton file in my MAC OS Big Sur is in /usr/local/etc/my.cnf, it was installed using brew.

Add line in the my.cnf

innodb_buffer_pool_size=512M

save file and restart mysql using command

brew services restart mysql

Check if the setting is reflected correctly using this query in MySQL:

SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment