I have a single view Magento 2, with about 5000 products on it, running on a cloud hosting with 4GB RAM.
What happens is that every time the reindex process is triggered (by the cron job or by hand) it never ends and the website goes down (503 error).
I have checked the cPanel troubleshooting tool and it shows 2 queries being killed due to timeout, both trying to access the table MG_CATALOG_CATEGORY_PRODUCT_INDEX_STORE1.
So I tried to truncate the table but it's not working, the query fails and the e-commerce goes down during the process.
I have tried to reset index status with
php bin/magento indexer:reset
I have tried to reset index status by changing the status to
invalid directly in the db
but then, when I try the reindex it fails again.