cron job Re-indexing takes 1 hour and gets stuck mostly

[adityag186]

Preconditions

1. 2CPU Cores, CentOS, 5GB RAM, 60GB SSD on siteground cloud server
2. Magento CE 2.1.3
3. 350000 products,5GB database size

Steps to reproduce

1. Cron job runs re-indexer at night when there is no traffic 99% of the times

Expected result

1. Indexing should happens in 10 minutes at the most I guess

Actual result

1. Indexers get stuck at processing. Catalog search gets stuck.
2. Website does not show products for that time
3. Manual re-indexing needs to be done everytime it happens

[choukalos]

Tracking the issue for FullText indexer as MAGETWO-56356 - we're planning on addressing near term.

[choukalos]

In the short term please adjust your MySQL configuration - tmp_table_size variable so indexer continues.

[adityag186]

Hi,

Can you please tell me steps on how to do that?

[maghamed]

@adityag186
You have to set MySQL global system variable https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tmp_table_size
which default value is 16MB, If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table, so the re-indexation process stucks.

you can read how to do it in this article
https://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html