1

We are running Magento 2.3.1 which has been running properly for a while.

Recently the site began to experience extremely long wait times whenever hitting pages not cached by Varnish such as searches and checkout pages.

Looking at the CPU usage on our box it seemed that MySql was consuming nearly all of the it. Next, I took a look at what queries were being processed using the show full processlist command. This returned a bunch of the same queries stuck in the Sending Datastatus. The Time column showed values ranging from 10 to 10,000 with the amount of process growing with each added iteration of the query.

Killing all of these processes fixed the usage for a short time until these queries piled back up causing the site to slow to a crawl once again.

Here's the query:

SELECT `product_table`.*, 
   `product_table`.`entity_id` 
FROM   `catalog_product_entity` AS `product_table` 
   LEFT JOIN `catalog_product_entity_int` AS `status_global_attr` 
          ON status_global_attr.attribute_id = 97 
             AND status_global_attr.store_id = 0 
   LEFT JOIN `catalog_product_entity_int` AS `status_attr` 
          ON status_attr.attribute_id = 97 
             AND status_attr.store_id = 1 
WHERE  ( product_table.entity_id IN( '132617' ) ) 
   AND ( Ifnull(status_attr.value, status_global_attr.value) = 2 )

The queries are all the same as this one with the exception of the attribute_id being different for each.

I spent the past couple of days trying to track down what is issuing the query and why it is taking so long to complete but haven't been able to figure it out.

If anyone knows why this is happening, or how to diagnose further it would be greatly appreciated.

EDIT:

After further investigation these queries seem to be created when a product is saved.

In response to comments:

A) Explain

1   SIMPLE  product_table   <null>  const   PRIMARY PRIMARY 4   const   1   100 <null>
1   SIMPLE  status_global_attr  <null>  ref CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID 2   const   219490  50  Using where
1   SIMPLE  status_attr <null>  ALL CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID <null>  <null>  <null>  438980  25  Using where; Using join buffer (Block Nested Loop)

B) Create Table

CREATE TABLE `catalog_product_entity` (
  `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  `attribute_set_id` smallint(5) 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(6) NOT NULL DEFAULT '0' COMMENT 'Has Options',
  `required_options` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Required Options',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
  PRIMARY KEY (`entity_id`),
  KEY `CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
  KEY `CATALOG_PRODUCT_ENTITY_SKU` (`sku`)
) ENGINE=InnoDB AUTO_INCREMENT=308337 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table'

C/D) Create Table (combined since they reference the same table)

CREATE TABLE `catalog_product_entity_int` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `value` int(11) DEFAULT NULL COMMENT 'Value',
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
  KEY `CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID` (`attribute_id`),
  KEY `CATALOG_PRODUCT_ENTITY_INT_STORE_ID` (`store_id`),
  CONSTRAINT `CATALOG_PRODUCT_ENTITY_INT_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,
  CONSTRAINT `CAT_PRD_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
  CONSTRAINT `CAT_PRD_ENTT_INT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8166684 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Integer Attribute Backend Table'
3
  • Please post A) EXPLAIN (your query); and text results of B) SHOW CREATE TABLE product_table; and C) SHOW CREATE TABLE status_attr; and D) SHOW CREATE TABLE status_global_attr; for analysis. – Wilson Hauck Mar 4 '20 at 1:51
  • 1
    @WilsonHauck Thanks for taking to time to respond, I've updated my question with the details you requested. Just want to point out the that status_attr and status_global_attr reference the same tables, so I only included the SHOW CREATE TABLE command for one. – Daniel Black Mar 4 '20 at 18:45
  • Please see my Answer posted Mar 4, 2020. Did it help in any way? Please upvote or accept if the answer was helpful. Thanks – Wilson Hauck Mar 10 '20 at 18:20
0

A quick look at available information indicates this line

WHERE  ( product_table.entity_id IN( '132617' ) ) 

could be

WHERE  ( product_table.entity_id = 132617 ) 

to avoid DATATYPE conversion slowdown because of the single quotes around the value and for this query, IN is not really needed and can be a direct equal compare.

Please view my profile, Network profile for contact info and free downloadable Utility Scripts to assist with performance improvements.

1
  • Another way to conserve resources required is to avoid A) SELECT product_table.*, and only gather the columns of data required for your task. – Wilson Hauck Mar 6 '20 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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