I have a MySQL 5.7.30 as storage for a Magento 2 shop. During the magento reindex process (a PHP process that transforms data into various tables for faster retrieval), some temporary tables are created and filled. These queries should be very fast, however, some keep running indefinitely. This causes other queries to hang, because they are waiting for table locks for tables the insert is reading. This brings the whole site down, as no query will ever finish, causing timeout errors on the web frontend.
Even after killing the reindexing process, the situation is not changed. The insert query is still running and other queries wating for table locks - until I kill the query in MySQL.
Here is a list of the processes in MySQL during such a deadlock situation (occurs while running the reindex process):
mysql> select id, db,command, state, left(info,100) from information_schema.processlist;
+---------+----------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+
| id | db | command | state | left(info,100) |
+---------+----------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 8165312 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_bb745c5e7f2610352c060b36709e54032aaf8c15` SELECT `e`.`entity_id` FROM `cata |
| 8165311 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_f91821f67870f011d3f8a38e9908c3235747584b` SELECT `e`.`entity_id` FROM `cata |
| 8165289 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_2d834f92e9b71a91a5c2520192539cd38560d22c` SELECT `e`.`entity_id` FROM `cata |
| 8165288 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_abca7e583ce39ed454be82b78bdc29b389a4ee74` SELECT `e`.`entity_id` FROM `cata |
| 8165260 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_18b43873915bfb0015033be4ee261cae3e897dee` SELECT `e`.`entity_id` FROM `cata |
| 8165244 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_d661c3a49566eb6a35f5043718ae99047b176662` SELECT `e`.`entity_id` FROM `cata |
| 8165217 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_da8bbd709684db07102be86caeae8712790bb37e` SELECT `e`.`entity_id` FROM `cata |
| 8165261 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_dd977f7939670fc3eb19c6b5c89be77b8bba52b1` SELECT `e`.`entity_id` FROM `cata |
| 8165259 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_28d513e50adac3dd498f9d4087f648cf73ad8e9d` SELECT `e`.`entity_id` FROM `cata |
| 8165199 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_438c79baa1470f9f4e435b50046e5f65c06c58dc` SELECT `e`.`entity_id` FROM `cata |
| 8165153 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_a12ea76415ddb9a0179ed2dbdf48b88b9b3132e6` SELECT `e`.`entity_id` FROM `cata |
| 8165020 | magento2 | Query | Waiting for table metadata lock | RENAME TABLE catalog_product_index_price TO catalog_product_index_price_outdated,catalog_product_ind |
| 8165251 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_32a2d91876b5b4fa38eb6f3773074ad9d4854390` SELECT `e`.`entity_id` FROM `cata |
| 8165247 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_be2127ec0bdd7a56beb8e59fdc5a5a1164ef3bbb` SELECT `e`.`entity_id` FROM `cata |
| 8165017 | magento2 | Sleep | | NULL |
| 8164957 | magento2 | Query | Filling schema table | select id, db,command, state, left(info,100) from information_schema.processlist |
| 8165240 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_1ca9188823f3af39434548117fde49aacd9cf7c1` SELECT `e`.`entity_id` FROM `cata |
| 8165226 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_94f703629874d7a4b20c58a4d863266d1f7f6ba4` SELECT `e`.`entity_id` FROM `cata |
| 8165219 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_0e88fc675d33b150e9cc7718cb70820ca88bc80c` SELECT `e`.`entity_id` FROM `cata |
| 8165210 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_b6f322baae91918f8b91001156a149072e904f41` SELECT `e`.`entity_id` FROM `cata |
| 8165206 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_e62016ea2ea8631932f1dd5643c93d81922c4949` SELECT `e`.`entity_id` FROM `cata |
| 8165194 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_e69208892fe0386e6baff43b2ff244d49fa6eb4c` SELECT `e`.`entity_id` FROM `cata |
| 8165202 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_3a805f9691560f0b892b4da18c0041277ce1b99e` SELECT `e`.`entity_id` FROM `cata |
| 8165188 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_c97916867bdb1e1427abeac76cee57a164038c50` SELECT `e`.`entity_id` FROM `cata |
| 8165179 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_2b4ee30f3f92d09e3843e9b6a65f31751d180770` SELECT `e`.`entity_id` FROM `cata |
| 8165173 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_860d16fe274ea842d23c9bd73c74fef4abf5ebc4` SELECT `e`.`entity_id` FROM `cata |
| 8165167 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_a963adc29cdca3c797b041e777010031d3eb5fc6` SELECT `e`.`entity_id` FROM `cata |
| 8165155 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_895b1ef14a24275b53c171d0d2bb5cc94c1ae4b1` SELECT `e`.`entity_id` FROM `cata |
| 8165147 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_cb95277cd601e038246f880aca3c4f1113f61a70` SELECT `e`.`entity_id` FROM `cata |
| 8165142 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_dee736e9ad4f2d069f5750cd5411f566775e42cb` SELECT `e`.`entity_id` FROM `cata |
+---------+----------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+
As you can see, 2 queries are currently inserting data into a temp_XXX table each. Other queries wait for locks. In this scenario, only the indexer was running, as the site was put on maintanence. In situations where the site is accessible, there would also be other queries like selecting products waiting for a table lock.
After killing the reindex process (by Ctrl+C on the terminal running it), the situation looks like this:
mysql> select id, db,command, state, left(info,100) from information_schema.processlist;
+---------+----------+---------+----------------------+------------------------------------------------------------------------------------------------------+
| id | db | command | state | left(info,100) |
+---------+----------+---------+----------------------+------------------------------------------------------------------------------------------------------+
| 8165147 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_cb95277cd601e038246f880aca3c4f1113f61a70` SELECT `e`.`entity_id` FROM `cata |
| 8165142 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_dee736e9ad4f2d069f5750cd5411f566775e42cb` SELECT `e`.`entity_id` FROM `cata |
| 8164957 | magento2 | Query | Filling schema table | select id, db,command, state, left(info,100) from information_schema.processlist |
+---------+----------+---------+----------------------+------------------------------------------------------------------------------------------------------+
As you can see, the two statements from before are still running.
The concrete statements are in the course of this:
INSERT IGNORE INTO `temp_cb95277cd601e038246f880aca3c4f1113f61a70` SELECT `e`.`entity_id` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index_store3` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=3 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=17036
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
INNER JOIN `catalog_product_index_eav` AS `layered_nav_f_filter_typ_1` ON `layered_nav_f_filter_typ_1`.`entity_id` = `e`.`entity_id` AND `layered_nav_f_filter_typ_1`.`attribute_id` = '18249' AND `layered_nav_f_filter_typ_1`.`store_id` = '3' AND `layered_nav_f_filter_typ_1`.`value` IN (2894)
INNER JOIN `cataloginventory_stock_status` AS `layered_nav_f_filter_typ_1__stock` ON `layered_nav_f_filter_typ_1`.`source_id` = `layered_nav_f_filter_typ_1__stock`.`product_id` AND `layered_nav_f_filter_typ_1__stock`.`website_id` = 0 AND `layered_nav_f_filter_typ_1__stock`.`stock_id` = 1 AND `layered_nav_f_filter_typ_1__stock`.`stock_status` = 1
INNER JOIN `catalog_product_index_eav` AS `layered_nav_f_filter_kabelmantel` ON `layered_nav_f_filter_kabelmantel`.`entity_id` = `e`.`entity_id` AND `layered_nav_f_filter_kabelmantel`.`attribute_id` = '18253' AND `layered_nav_f_filter_kabelmantel`.`store_id` = '3' AND `layered_nav_f_filter_kabelmantel`.`value` IN (3044)
INNER JOIN `cataloginventory_stock_status` AS `layered_nav_f_filter_kabelmantel__stock` ON `layered_nav_f_filter_kabelmantel`.`source_id` = `layered_nav_f_filter_kabelmantel__stock`.`product_id` AND `layered_nav_f_filter_kabelmantel__stock`.`website_id` = 0 AND `layered_nav_f_filter_kabelmantel__stock`.`stock_id` = 1 AND `layered_nav_f_filter_kabelmantel__stock`.`stock_status` = 1
INNER JOIN `catalog_product_index_eav` AS `layered_nav_f_filter_paarzahl_1` ON `layered_nav_f_filter_paarzahl_1`.`entity_id` = `e`.`entity_id` AND `layered_nav_f_filter_paarzahl_1`.`attribute_id` = '18251' AND `layered_nav_f_filter_paarzahl_1`.`store_id` = '3' AND `layered_nav_f_filter_paarzahl_1`.`value` IN (3009,3004)
INNER JOIN `cataloginventory_stock_status` AS `layered_nav_f_filter_paarzahl_1__stock` ON `layered_nav_f_filter_paarzahl_1`.`source_id` = `layered_nav_f_filter_paarzahl_1__stock`.`product_id` AND `layered_nav_f_filter_paarzahl_1__stock`.`website_id` = 0 AND `layered_nav_f_filter_paarzahl_1__stock`.`stock_id` = 1 AND `layered_nav_f_filter_paarzahl_1__stock`.`stock_status` = 1 WHERE (stock_status_index.stock_status = 1)
The base select of this insert takes 0.0ms and returns ~1000 records.
While these queries are still running, the other queries keep on hanging waiting for table locks. Only after I issue KILL statements for those inserts, the website returns back to normal operation.
Also note that this does not happen every time. Some times (minority of attempts), the statements finish without problems.
NB: This seems to only happen for reindexes for stock and price tables. Table engine for all tables is InnoDB.
Structure of price table:
CREATE TABLE `catalog_product_index_price` (
`entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
`customer_group_id` int(10) unsigned NOT NULL COMMENT 'Customer Group ID',
`website_id` smallint(5) unsigned NOT NULL COMMENT 'Website ID',
`tax_class_id` smallint(5) unsigned DEFAULT 0 COMMENT 'Tax Class ID',
`price` decimal(20,6) DEFAULT NULL COMMENT 'Price',
`final_price` decimal(20,6) DEFAULT NULL COMMENT 'Final Price',
`min_price` decimal(20,6) DEFAULT NULL COMMENT 'Min Price',
`max_price` decimal(20,6) DEFAULT NULL COMMENT 'Max Price',
`tier_price` decimal(20,6) DEFAULT NULL COMMENT 'Tier Price',
PRIMARY KEY (`entity_id`,`customer_group_id`,`website_id`),
KEY `CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID` (`customer_group_id`),
KEY `CATALOG_PRODUCT_INDEX_PRICE_MIN_PRICE` (`min_price`),
KEY `CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE` (`website_id`,`customer_group_id`,`min_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product Price Index Table'
The table variables are:
mysql> show variables like '%table%';
+----------------------------------------+----------------------+
| Variable_name | Value |
+----------------------------------------+----------------------+
| aria_encrypt_tables | OFF |
| aria_used_for_temp_tables | ON |
| big_tables | OFF |
| encrypt_tmp_disk_tables | OFF |
| innodb_encrypt_tables | OFF |
| innodb_encrypt_temporary_tables | OFF |
| innodb_file_per_table | ON |
| innodb_ft_aux_table | |
| innodb_ft_server_stopword_table | |
| innodb_ft_user_stopword_table | |
| innodb_table_locks | ON |
| innodb_undo_tablespaces | 0 |
| lower_case_table_names | 0 |
| max_heap_table_size | 268435456 |
| max_tmp_tables | 32 |
| old_alter_table | DEFAULT |
| performance_schema_max_table_handles | -1 |
| performance_schema_max_table_instances | -1 |
| replicate_do_table | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
| table_definition_cache | 400 |
| table_open_cache | 1024 |
| table_open_cache_instances | 7 |
| tmp_disk_table_size | 18446744073709551615 |
| tmp_memory_table_size | 268435456 |
| tmp_table_size | 268435456 |
| updatable_views_with_limit | YES |
| use_stat_tables | NEVER |
+----------------------------------------+----------------------+
30 rows in set (0.00 sec)
The question(s): How can queries reading from tables and inserting into an (exclusively for this process created temporary table) cause other reads to hang indefinitely? And how can this query keep running when the process starting it is killed?
And most imporantly: how can I solve this situation?
