On busy shops, we often see an issue appear that Magento for some reason still hasn’t fixed in the Magento core.
This issue causes massive performance issues and deadlocks on shops that receive lots of requests and have a steady database use.
TABLE OF CONTENTS
Indexers that extend
Mage_Index_Model_Resource_Abstract run an
ALTER TABLE ... DISABLE|ENABLE KEYS statement before and after inserting data.
The statement is only supported by
MyISAM tables so it is useless for modern Magento installations, but it still requires a table metadata lock even if
MyISAM is not used.
catalogsearch index does still use
MyISAM, but it doesn’t extend
Mage_Index_Model_Resource_Abstract so it isn’t affected.
As the statement doesn’t actually do anything it should be removed and the lock would be avoided.
We’ve seen many database deadlocks caused by this legacy code and recommend developers to remove it from the code.
It may seem like it wouldn’t have that much impact as the lock should only be for a very short time, but in production we’ve seen large queues of queries ‘Waiting for table metadata lock’ on tables like
catalog_product_index_price when the
ALTER TABLE statement is blocked by some other very slow transaction.
In OpenMage, an LTS fork of the Magento 1 codebase, a pull request is made to resolve this issue by removing the
DISABLE/ENABLE KEYS statement. You can find this Pull request in their github repository.
A full patch can be found here.
We do not recommend hacking the Magento core, but instead copying the changed files to