How to solve slow performance, slow and long running CRONs in Magento Cloud?

Question

I'm having these two main issues below in my Magento 2.3.2 Enterprise Cloud.

• Heavy SQL load and site performance issues.
• Long-running and stuck CRON.

How could I solve it?

Answer 1

Cause

Flat tables and indexers enabled.

Solution

Starting with Magento 2.1.x and above, the use of a flat catalog is no longer a best practice and is not recommended. Continued use of this feature is known to cause performance degradation and other indexing issues. To disable the flat catalog:

1. In the Magento Admin, navigate to Stores > Settings > Configuration.

2. In the panel on the left under Catalog, choose Catalog.

3. Expand the Storefront section, and do the following:

   • Set Use Flat Catalog Category to No.
   • Set Use Flat Catalog Product to No.

4. When complete, click Save Config. Then when prompted, refresh the cache.

5. Flush cache by running php bin/magento cache:flush

If you can't change the Use Flat Catalog Category and Use Flat Catalog Product to No because the options are greyed out disable flat indexers in app/etc/config.php:

1. Run this command to make sure all indexers are set to Update by schedule: php bin/magento indexer:set-mode schedule
2. Edit app/etc/config.php and locate the lines with flat_catalog_product and flat_catalog_category - change them from 1 to 0 to disable them.
3. Run the command php bin/magento app:config:import
4. Run this command to confirm that the flat indexers are disabled: php bin/magento indexer:status
5. Flush cache by running php bin/magento cache:flush

Reference: https://support.magento.com/hc/en-us/articles/360034631192