0

We use the following configuration:

Engine: Aurora MySQL v8.0

Instance Type: db.r6g.4xlarge

vCPU: 16

Memory: 128.0 GiB

Role: Writer Instance

I'm encountering a critical issue during the reindexing process of the catalog_product_flat index in my OpenMage (old Magento) project.

The problem leads to a spike in CPU usage to 100%, causing the MySQL server to become inaccessible. I have also noticed significant waits in SYNCH context, which may be related to the issue.

About Magento reindex process - something like normalizing different product attributes into one (flat) table. I assume, that this process affects some internal resources (sorry, I'm not DBA, but I think it's some Mysql internals like innodb pool, hash tables) that leads for using IO (you can see it on a pictures) and locking access to target table (catalog_product_flat). As a result, even SELECT queries stucks, session count number is growing like a snowball. I have to manually stop the process, because server just always has 100% and then doesn't response.

Here are some statistics regarding the problem. These are waits generated by Performance Insights of Aurora SQL:

 7.62 wait/io/table/sql/handler

 1.02 wait/synch/mutex/innodb/fil_system_mutex

 0.96 CPU

 0.56 wait/synch/sxlock/innodb/trx_purge_latch

 0.49 wait/synch/cond/innodb/row_lock_wait

 0.09 wait/synch/sxlock/innodb/hash_table_locks

Waits:

Other stats like IO/CPU

Reindex process is typical to Magento projects, so there should be something in DB. Maybe amount of products we try to reindex became too high and it overflows some buffers, or some internal struct became bottleneck? We use pretty expensive server, but im sure the problem is not in the hardware potential. I want to ask for an advice, how to debug what exactly leads to this affect. Maybe somebody will point me. Thank you Drop FK here:

ALTER TABLE `catalog_product_flat_1` DROP FOREIGN KEY `FK_TMP_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID`
CREATE TABLE `catalog_product_flat_1` (
  `entity_id` int unsigned NOT NULL COMMENT 'entity_id',
  `attribute_set_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT 'attribute_set_id',
  `type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'type_id',
  `name` varchar(255) DEFAULT NULL COMMENT 'name',
  `short_description` text COMMENT 'short_description',
  `sku` varchar(64) DEFAULT NULL COMMENT 'sku',
  `price` decimal(12,4) DEFAULT NULL COMMENT 'price',
  `special_price` decimal(12,4) DEFAULT NULL COMMENT 'special_price',
  `special_from_date` datetime DEFAULT NULL COMMENT 'special_from_date',
  `special_to_date` datetime DEFAULT NULL COMMENT 'special_to_date',
  // AROUND 100 LINES WITH DYNAMIC ATTRIBUTES like color, frame, etc
  PRIMARY KEY (`entity_id`),
  KEY `IDX_TMP_CATALOG_PRODUCT_FLAT_1_TYPE_ID` (`type_id`),
  KEY `IDX_TMP_CATALOG_PRODUCT_FLAT_1_ATTRIBUTE_SET_ID` (`attribute_set_id`),
  KEY `IDX_TMP_CATALOG_PRODUCT_FLAT_1_NAME` (`name`),
  KEY `IDX_TMP_CATALOG_PRODUCT_FLAT_1_SKU` (`sku`),
  KEY `IDX_TMP_CATALOG_PRODUCT_FLAT_1_PRICE` (`price`),
  KEY `IDX_TMP_CATALOG_PRODUCT_FLAT_1_STATUS` (`status`),
  CONSTRAINT `FK_TMP_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='Catalog Product Flat (Store 1)'
catalog_product_entity | CREATE TABLE `catalog_product_entity` (
  `entity_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  `entity_type_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type ID',
  `attribute_set_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set ID',
  `type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
  `sku` varchar(64) DEFAULT NULL COMMENT 'SKU',
  `has_options` smallint NOT NULL DEFAULT '0' COMMENT 'Has Options',
  `required_options` smallint unsigned NOT NULL DEFAULT '0' COMMENT 'Required Options',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation Time',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update Time',
  PRIMARY KEY (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE_ID` (`entity_type_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_SKU` (`sku`),
  CONSTRAINT `FK_CAT_PRD_ENTT_ATTR_SET_ID_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_ENTT_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=427582 DEFAULT CHARSET=utf8mb3 COMMENT='Catalog Product Table'

Also, found this in ENGINE INNODB STATUS:

*** (2) TRANSACTION:
TRANSACTION 134987570, ACTIVE 82 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 235660 lock struct(s), heap size 41164920, 6362698 row lock(s), undo log entries 1437450
MySQL thread id 14450819, OS thread handle 70462394118000, query id 1011643779 192.168.10.10 admin executing
UPDATE `catalog_product_flat_1` AS `e`
 LEFT JOIN `catalog_product_entity_decimal` AS `t1` ON e.entity_id = t1.entity_id AND t1.entity_type_id = 4 AND t1.attribute_id = 75 AND t1.store_id = 0
 LEFT JOIN `catalog_product_entity_decimal` AS `t2` ON e.entity_id = t2.entity_id AND t2.entity_type_id = 4 AND t2.attribute_id = 75 AND t2.store_id = 1
SET `e`.`price` = IF(t2.value_id > 0, t2.value, t1.value)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3053 page no 3115 n bits 320 index PRIMARY of table `DB`.`catalog_product_entity` trx id 134987570 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 4; hex 00068399; asc     ;;
 1: len 6; hex 000006def125; asc      %;;
 2: len 7; hex 020000009b0c29; asc       );;
 3: len 2; hex 0004; asc   ;;
 4: len 2; hex 0027; asc  ';;
 5: len 12; hex 636f6e666967757261626c65; asc configurable;;
 6: len 15; hex 472d37363135353233363130363030; asc G-7615523610600;;
 7: len 2; hex 8001; asc   ;;
 8: len 2; hex 0001; asc   ;;
 9: len 4; hex 64832587; asc d % ;;
 10: len 4; hex 648325b8; asc d % ;;

As I understand, "reindex" process gets value from catalog_product_entity_decimal (scoped by store (0and wants to update on select into catalog_product_flat_1 table

6
  • MySQL does not need its table "reindexed". If Magento is invoking such, see if you can turn it off.
    – Rick James
    Commented Jun 16, 2023 at 3:06
  • Hello, @RickJames. Thanks for the input. By "reindexing" magento means its own process of normalizing data, happening on application level. It has nothing to InnoDB or lower levels processes. It just recollecting data from different sources into specific normalized table
    – Winfle
    Commented Jun 16, 2023 at 16:06
  • What queries does Magento use for that "reindexing"?
    – Rick James
    Commented Jun 16, 2023 at 20:41
  • @RickJames I found, that we have around 130 columns with 368k if rows having foreign key on main table. I think. the problem appears, when it drops foreign key from "index" table to main. It creates "waiting metadata" lock events. Appritiate your input, man!
    – Winfle
    Commented Jun 20, 2023 at 14:53
  • Please provide the generated SQL for the query, plus SHOW CREATE TABLE for the two tables.
    – Rick James
    Commented Jun 20, 2023 at 16:18

1 Answer 1

0

This "composite" index is likely to speed up the subqueries, perhaps significantly:

INDEX(entity_id,  entity_type_id, attribute_id, store_id)

Once that index is in place, there should be no need to "reindex".

Please provide SHOW CREATE TABLE catalog_product_entity_decimal.

The two tables you provided both have the same PRIMARY KEY; is there some reason not go combine the two tables into one? Also, 3 other columns seem to be redundantly provided in the two tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.