23

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?

0

9 Answers 9

13

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.

2
  • It says we have to change innodb_buffer_pool_size but it does not explain how!
    – Black
    Dec 29, 2021 at 9:53
  • Explaining the error is detail but maybe provide a way to correct the issue as well, how was this accepted as an answer?
    – Sebastian
    Feb 4 at 17:23
6
  • Go to your server MySQL settings file (my.ini)
  • Search for innodb_buffer_pool_size and increase this value.
2
  • 2
    where do i locate the mySQL setting file (my.ini)? thanks Jun 1, 2019 at 0:50
  • is there a way to config this in project level, and not on mysql?
    – rafaelphp
    Jun 9, 2019 at 20:07
5

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
  1. 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;
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

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

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
1
  • 512MB is 536870912 bytes. 521MB is 546308096 bytes.
    – h q
    Oct 13, 2022 at 5:50
1

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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