Magento 2 Please update innodb_buffer_pool_size or decrease batch size value

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?

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.

Answer 2

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

Answer 3

To determine the optimal innodb_buffer_pool_size, run this query written by @RolandoMySQLDBA

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

This will give you the RIBPS, Recommended InnoDB Buffer Pool Size, based on all InnoDB Data and Indexes, with an additional 60%.

In my case, the value is 1 (GB), so you have 2 options:

A) Configure InnoDB Buffer Pool Size using config file

1. Edit my.cnf (MySQL) or mariadb-server.cnf (MariaDB), and add one line under the [mysqld] section, e.g.:

[mysqld]
innodb_buffer_pool_size=1G

2. Restart your MySQL Database

OR

B) Configure InnoDB Buffer Pool Size Online

1GB is 1073741824 bytes. Run the following while logged in as root:

mysqld> SET GLOBAL innodb_buffer_pool_size=1073741824;

Confirm the new value by running:

mysqld> SELECT @@innodb_buffer_pool_size/1024/1024/1024;

Answer 4

To prevent this error message, update innodb_buffer_pool_size or decrease the batch size value (which decreases memory usage for the temporary table).

Here is an example of the configuration in app/etc/env.php

<?php
return [
    'indexer' => [
        'batch_size' => [
            'cataloginventory_stock' => [
                'simple' => 200
            ],
            'catalog_category_product' => 666,
            'catalogsearch_fulltext' => [
                'partial_reindex' => 100,
                'mysql_get' => 500,
                'elastic_save' => 500
            ],
            'catalog_product_price' => [
                'simple' => 200,
                'default' => 500,
                'configurable' => 666
            ],
            'catalogpermissions_category' => 999,
            'inventory' => [
                'simple' => 210,
                'default' => 510,
                'configurable' => 616
            ]
        ]
    ]
];

Reference: Indexer optimization

Answer 5

Try to update the value for batchRowsCount to 10000 in {Your_Module_Name}/etc/di.xml file of catalog_category_product (Category Products) indexer Magento\Catalog\Model\Indexer\Category\Product\Action\Full

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

Answer 6

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

Answer 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%'; 

Answer 8

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

Answer 9

• 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