I have just installed Magento 2 a few days ago.

And suddenly when I try to reindex manually I noticed that the Category Products indexer takes much time. more than 8 mins

And I've got this exception in Sentry about 2k time

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: 
2160000000 bytes; InnoDB buffer pool size: 2281701376 bytes.  

from the exception above: I've updated the buffer pool size to max value allowed but the problem didn't solve.


  • I don't have so many products, It's just dummy data.
  • I've updated to Magento 2.3.2 today but the problem still exists.
  • I don't have this issue on my localhost domain.
  • try with php -d memory_limit=4096M indexer:reindex – Anas Mansuri Jun 26 '19 at 12:31
  • Thanks but It doesn't solve the problem. – AboElnouR Jun 26 '19 at 12:38
  • Can you check if there is anything logged into log files in indexing process. – nishu Jun 26 '19 at 12:39
  • @nishu Yes, I've included the exception above – AboElnouR Jun 26 '19 at 12:41
  • this is just a warning any error logged in log files. – nishu Jun 26 '19 at 12:48

This seems to be a known bug of 2.3.2 specifically.

Happens if you have a lot of product attributes with used_in_product_listing = 1. Reducing the number of such attributes (by changing this corresponding value to 0 on some of them) seems to help somewhat:

We have 97 attributes used in product listing. By removing just 6 of them, we reduce time to 6 minutes.

For reference: https://github.com/magento/magento2/issues/23462

| improve this answer | |
  • I have only 55 attributes and about 20 used in product listing. – AboElnouR Jul 15 '19 at 12:05

After some experience with Magento, I've noted that disabled items cause this issue.

I've deleted all disabled items like products, categories, store views and so on

After that, this issue fully disappeared.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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