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. [] []
Best Answer
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:
Note: Decrease the batchRowsCount to resolve the error.