Magento 2.3 - After migration my multi-store website Reindex Issue - No such entity

[Aveeva]

From Magento 1.9.2.3 multi-store to Magento 2.3.4 after successfully migrated settings and data to Magento 2.3.4 reindex error,

Error Screenshot : https://snipboard.io/cGWypo.jpg

System.log Error:

[2020-06-08 09:17:23] 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: 1000; Allocated memory size: 202500000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []

[Manish Mittal]

Hello @Aveeva 

Please reset first all indexer then run again index command:

bin/magento indexer:reset
bin/magento indexer:reindex

and for no such entity, you can try below shared link:

https://magento.stackexchange.com/questions/106793/magento-2-reindex-returns-no-such-entity

https://mage2-blog.com/magento-2-indexer-returns-no-such-entity/

[Aveeva]

@Manish Mittal  

System.log Error:

[2020-06-08 09:17:23] 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: 1000; Allocated memory size: 202500000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []

[Manish Mittal]

@Aveeva 

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

More: https://devdocs.magento.com/guides/v2.4/extension-dev-guide/indexer-batch.html#:~:text=Memory%20size...

[Aveeva]

@Manish Mittal  How to update Batching configuration my current batch 1000.

[Manish Mittal]

@Aveeva 

The batch size for each product type is defined in the di.xml file, with the default batch size of 5000 items pr batch.

You can follow same official document I have shared in that they already mentioned how to configure.

https://devdocs.magento.com/guides/v2.2/extension-dev-guide/indexer-batch.html

Or 2nd option you can do this too:

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

[Aveeva]

@Manish Mittal  After the update innodb_buffer_pool_size = 8048M still getting issue,

error :

 [2020-06-08 10:17:33] 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: 1000; Allocated memory size: 202500000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []

may i know the recommend size?

[Manish Mittal]

@Aveeva 

As such I didnt find any recommendation, what I see in few articles they are recommending to make it 80% fo your ram, innodb_buffer_pool_size. 

Found this in official doc:

Reducing the batch size for catalog_product_price indexer from 5000 to 1000 decreases the execution time from about 4 hours to less than 2 hours. You can experiment to determine the ideal batch size. In general, halving the batch size can decrease the indexer execution time.

Got this article too:

http://www.solutioning.eu/2020/01/18/magento-2-3-3-indexer-stuck-at-reindexing/

Please debug based on these if you can fix. Thanks