Magento 2 and Adobe Commerce Indexers architecture is broken

Magento 2 an Adobe Commerce has random downtime because of the broken core indexer architecture. The indexes mechanic of the Magento is just terrible software development practice.

This issue is connected to MySQL MDL lock context: synch/cond/SQL/MDL_context::COND_wait_status and indicates a high number of SQLs (including selects) trying to access a table that a DCL or DDL is modifying. Avoid running DDL statements to high-traffic tables during regular application activity.

The Magento 2 Catalog SQL query:

SELECT `catalog_category_product_index_store1` . `product_id` FROM `catalog_category_product_index_store1` WHERE ( `product_id` = ? ) AND ( `category_id` = ? )

is locked by this query when indeer is running :

RENAME TABLE `catalog_category_product_index_store1` TO `catalog_category_product_index_store1_outdated` , `catalog_category_product_index_store1_replica` TO `catalog_category_product_index_store1` , `catalog_category_product_index_store1_outdated` TO `catalog_category_product_index_store1_replica`

Indexation is putting your website down. Depends on your luck it can be 1–15 minutes of downtime or even more.

The Magento error report is :

main.CRITICAL: SQLSTATE[08S01]: Communication link failure: 1053 Server shutdown in progress, query was: SELECT catalog_category_product_index_store1.product_id FROM catalog_category_product_index_store1 WHERE (product_id = 22890) AND (category_id = 0) {“exception”:”[object] (Zend_Db_Statement_Exception(code: 8): SQLSTATE[08S01]: Communication link failure: 1053 Server shutdown in progress, query was: SELECT catalog_category_product_index_store1.product_id FROM catalog_category_product_index_store1 WHERE (product_id = 22890) AND (category_id = 0) at
SQLSTATE[08S01]: Communication link failure: 1053 Server shutdown in progress at vendor/magento/framework/DB/Statement/Pdo/Mysql.php:91)”} []

Magento 2 indexes have more disadvantages than benefits. Check what Magento documentation writes about the indexes :

Magento no longer recommends the use of a flat catalog as a best practice. Continued use of this feature is known to cause performance degradation and other indexing issues. A detailed description and solution is available in the Help Center.

Affected versions include:
- Magento Commerce Cloud 2.1.x and above
- Magento Commerce (On-Premise) 2.1.x and above
- Magento Open Source 2.1.x and above

On any Magento version, some extensions only work with flat tables, thus creating a risk if you disable flat tables. If you know that you have some extensions that use Flat Catalog indexers, you need to be aware of this risk when setting those values to No.

Indexer/index is usually associated and advertised as a better performance solution(denormalization). But you must be fixing performance and architecture issues of something that should improve performance…