Magento 2.1.8 Reindex Timeout

Question

We have a Magento store that we upgraded to 2.1.8 last week. Everything seemed to be working well till then. However, after the update, I am no longer able to re-index Category Products and Product Categories. The others indexes run fine. We have added more products since then too, however, it seems like too much of a coincidence that this would stop working right after the update.

I have searched for an answer since last week and applied many different solutions, however nothing seems to have fixed this issue. We did not install any new extensions.

The server specs are as follows:

• Managed VPS
• Dedicated CPU: 2 cores, Xeon E5-2630 @ 2.30GHz
• Dedicated RAM: 6 GB
• Dedicated Storage: 100 GB on SSD
• Monthly Data Transfer: 5000 GB (We launched this store 3 months ago, and right now do not have a lot of traffic.)

Things I have done: (I run reindex from SSH).

• 1) Increased php memory limit to 1024 MB.

• 2) Increased the Apache and FastCGI timeout limits to 900 seconds.

• 3) Increased the memory and CPU limits for SSH:

  • USER_MAX_CPU_SECS=900
  • USER_MAX_VIRT_MEM=800000
  • USER_MAX_RES_MEM=800000

• 4) I reset all indexes and ran reindex again. It did not work:

  • php bin/magento indexer:reset
  • php bin/magento indexer:reindex

• 5) I tried running them one by one:

• php bin/magento indexer:reset catalog_category_product

• php bin/magento indexer:reset catalog_product_category

• 6) Increased [mysqld] innodb_lock_wait_timeout=3600

I have run out of ideas and would greatly appreciate any advice and help. Thank you very much in advance.

Answer 1

It seems like a native issue in 2.1.8: https://github.com/magento/magento2/issues/10531

It seems I stumble upon some kind of solution. I'm not sure what I did but I will tell you what I remember doing.

1. Enable maintenance mode: bin/magento maintenance:enable
2. Reset indexes: bin/magento indexer:reset
3. Make sure that no database processes regarding indexing is running
4. Open your database administration client like PHPMyAdmin
5. Run query: SHOW PROCESSLIST;
6. Run query: KILL QUERY xxxxx (where xxxxx is the ID of processes you want to kill)
7. bin/magento setup:upgrade
8. Clear cache
9. bin/magento setup:static-content:deploy -j 1
10. bin/magento indexer:reindex
11. Disable maintenance mode bin/magento maintenance:disable

Now the only thin missing is some of the product images on the category page. The weird thing is that if I go to the product page I can see the product image. There is also a small version in the cache folder.