0

I'm in a situation where some types of queries which run always fast when I execute them will (when ran by themeselves) hang for an undefined amount of time.

What particularly puzzles me in this case is that I don't see any type of table lock that could cause this.

Let me explain further.

This is what I see in mytop

enter image description here

The two queries are running for a prolonged period of time

SELECT  `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`,
`price_index`.`final_price`,
IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price,
                price_index.tier_price), price_index.min_price
  ) AS `minimal_price`,
                `price_index`.`min_price`, `price_index`.`max_price`,
`price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`,
`stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`,
        `link_attribute_position_int`.`value` AS `position`, `link_attribute_qty_decimal`.`value` AS `qty`
    FROM  `catalog_product_entity` AS `e`
    INNER JOIN  `inventory_stock_5` AS `inventory_in_stock`  ON e.sku = inventory_in_stock.sku
    INNER JOIN  `catalog_product_index_price` AS `price_index`  ON price_index.entity_id = e.entity_id
      AND  price_index.customer_group_id = 0
      AND  price_index.website_id = '2'
    INNER JOIN  `catalog_category_product_index_store5` AS `cat_index`  ON cat_index.product_id=e.entity_id
      AND  cat_index.store_id=5
      AND  cat_index.visibility IN(2, 4)
      AND  cat_index.category_id=2
    INNER JOIN  `catalog_product_entity` AS `product`  ON product.entity_id = e.entity_id
    INNER JOIN  `inventory_stock_5` AS `stock_status_index`  ON product.sku = stock_status_index.sku
    INNER JOIN  `catalog_product_link` AS `links`  ON links.linked_product_id = e.entity_id
      AND  links.link_type_id = 1
    LEFT JOIN  `catalog_product_link_attribute_int` AS `link_attribute_position_int`  ON link_attribute_position_int.link_id = links.link_id
      AND  link_attribute_position_int.product_link_attribute_id = '2'
    LEFT JOIN  `catalog_product_link_attribute_decimal` AS `link_attribute_qty_decimal`  ON link_attribute_qty_decimal.link_id = links.link_id
      AND  link_attribute_qty_decimal.product_link_attribute_id = '6'
    INNER JOIN  `catalog_product_entity` AS `product_entity_table`  ON links.product_id = product_entity_table.entity_id
    WHERE  (inventory_in_stock.is_salable = 1)
      AND  (stock_status_index.is_salable = 1)
      AND  (links.product_id in ('158898'))
      AND  (`e`.`entity_id` != '158898')
    ORDER BY  `position` ASC 

and...

SELECT  `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`,
`price_index`.`final_price`,
IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price,
                price_index.tier_price), price_index.min_price
  ) AS `minimal_price`,
                `price_index`.`min_price`, `price_index`.`max_price`,
`price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`,
`stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`,
        `link_attribute_position_int`.`value` AS `position`, `link_attribute_qty_decimal`.`value` AS `qty`
    FROM  `catalog_product_entity` AS `e`
    INNER JOIN  `inventory_stock_5` AS `inventory_in_stock`  ON e.sku = inventory_in_stock.sku
    INNER JOIN  `catalog_product_index_price` AS `price_index`  ON price_index.entity_id = e.entity_id
      AND  price_index.customer_group_id = 0
      AND  price_index.website_id = '2'
    INNER JOIN  `catalog_category_product_index_store5` AS `cat_index`  ON cat_index.product_id=e.entity_id
      AND  cat_index.store_id=5
      AND  cat_index.visibility IN(2, 4)
      AND  cat_index.category_id=2
    INNER JOIN  `catalog_product_entity` AS `product`  ON product.entity_id = e.entity_id
    INNER JOIN  `inventory_stock_5` AS `stock_status_index`  ON product.sku = stock_status_index.sku
    INNER JOIN  `catalog_product_link` AS `links`  ON links.linked_product_id = e.entity_id
      AND  links.link_type_id = 1
    LEFT JOIN  `catalog_product_link_attribute_int` AS `link_attribute_position_int`  ON link_attribute_position_int.link_id = links.link_id
      AND  link_attribute_position_int.product_link_attribute_id = '2'
    LEFT JOIN  `catalog_product_link_attribute_decimal` AS `link_attribute_qty_decimal`  ON link_attribute_qty_decimal.link_id = links.link_id
      AND  link_attribute_qty_decimal.product_link_attribute_id = '6'
    INNER JOIN  `catalog_product_entity` AS `product_entity_table`  ON links.product_id = product_entity_table.entity_id
    WHERE  (inventory_in_stock.is_salable = 1)
      AND  (stock_status_index.is_salable = 1)
      AND  (links.product_id in ('154958'))
      AND  (`e`.`entity_id` != '154958')
    ORDER BY  `position` ASC  

As far as I understand there's no database lock on the tables

| InnoDB |      |
=====================================
2023-07-06 11:02:41 0x7fef4d074700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 24 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 24347 srv_active, 0 srv_shutdown, 8847 srv_idle
srv_master_thread log flush and writes: 33193
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 16886491
OS WAIT ARRAY INFO: signal count 40586870
RW-shared spins 92106908, rounds 281998222, OS waits 5213540
RW-excl spins 45936899, rounds 205260401, OS waits 3412521
RW-sx spins 9359123, rounds 275438314, OS waits 8071513
Spin rounds per wait: 3.06 RW-shared, 4.47 RW-excl, 29.43 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1772230899
Purge done for trx's n:o < 1772180308 undo n:o < 0 state: running but idle
History list length 20289
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422176365707528, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422176365711744, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422176365703312, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 74906, OS thread handle 140665393481472, query id 134164457 localhost 127.0.0.1 db_live Sending data
SELECT `cpe`.`entity_id`, inventory_stock.quantity+IF(ISNULL(SUM(ir.quantity)),0,SUM(ir.quantity)) AS quantity, MAX(is_salable) as is_salable FROM `inventory_stock_7` AS `inventory_stock`
 LEFT JOIN `inventory_reservation` AS `ir` ON ir.sku = inventory_stock.sku
 LEFT JOIN `catalog_product_entity` AS `cpe` ON inventory_stock.sku = cpe.sku GROUP BY `cpe`.`entity_id`
Trx read view will not see trx with id >= 1772230895, sees < 1772230895
---TRANSACTION 422176365774984, ACTIVE 0 sec fetching rows
mysql tables in use 17, locked 17
264 lock struct(s), heap size 57464, 6041 row lock(s)
MySQL thread id 74251, OS thread handle 140666153232128, query id 134164580 localhost 127.0.0.1 db_live Sending data
INSERT INTO `catalog_category_product_index_store7_tmp` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, IFNULL(ccp2.position, ccp.position + 10000) AS `position`, 0 AS `is_parent`, 7 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
 INNER JOIN `temp_catalog_category_tree_index_c1453bad` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN (1)
 INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_id
 LEFT JOIN `ca
---TRANSACTION 422176365745472, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422176365749688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422176365715960, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422176366040592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422176365990000, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422176365732824, ACTIVE 1338 sec starting index read
mysql tables in use 10, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 69614, OS thread handle 140665391331072, query id 128136000 localhost 127.0.0.1 db_live Sending data
SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`, `link_attribute_position_int`.`value` AS `position`, `link_attribute_qty_decimal`.`value` AS `qty` FROM `catalog_product_entity` AS `e`
Trx read view will not see trx with id >= 1772180307, sees < 1772180307
---TRANSACTION 422176365724392, ACTIVE 1338 sec
mysql tables in use 10, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 69607, OS thread handle 140665752717056, query id 128135089 localhost 127.0.0.1 db_live Sending data
SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`, `link_attribute_position_int`.`value` AS `position`, `link_attribute_qty_decimal`.`value` AS `qty` FROM `catalog_product_entity` AS `e`
Trx read view will not see trx with id >= 1772180307, sees < 1772180307
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1667776 OS file reads, 9939666 OS file writes, 1470789 OS fsyncs
0.08 reads/s, 16384 avg bytes/read, 126.08 writes/s, 32.42 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 609294, seg size 609296, 7555 merges
merged operations:
 insert 56518, delete mark 104706, delete 28855
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 8850487, node heap has 30509 buffer(s)
Hash table size 8850487, node heap has 52908 buffer(s)
Hash table size 8850487, node heap has 33460 buffer(s)
Hash table size 8850487, node heap has 16647 buffer(s)
Hash table size 8850487, node heap has 42176 buffer(s)
Hash table size 8850487, node heap has 49675 buffer(s)
Hash table size 8850487, node heap has 28265 buffer(s)
Hash table size 8850487, node heap has 34932 buffer(s)
2132837.05 hash searches/s, 363533.85 non-hash searches/s
---
LOG
---
Log sequence number 29949846571266
Log flushed up to   29949846571266
Pages flushed up to 29949832920413
Last checkpoint at  29949829760229
0 pending log flushes, 0 pending chkp writes
1204214 log i/o's done, 23.67 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 34368126976
Dictionary memory allocated 5858440
Buffer pool size   2054912
Free buffers       1000
Database pages     1765337
Old database pages 651637
Modified db pages  1691
Percent of dirty pages(LRU & free pages): 0.096
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 212082, not young 21165531
0.21 youngs/s, 2.83 non-youngs/s
Pages read 1665282, created 1089907, written 8589572
0.08 reads/s, 28.58 creates/s, 100.54 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1765337, unzip_LRU len: 0
I/O sum[5194]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
3 read views open inside InnoDB
Process ID=2893801, Main thread ID=140666312111872, state: sleeping
Number of rows inserted 164363494, updated 15842852, deleted 69133279, read 72104189322
7347.82 inserts/s, 375.28 updates/s, 3796.01 deletes/s, 3865928.96 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Moreover if I execute the same queries at the same period the same queries they will take around 90ms to finish while at the same time those are still hanging there.

I'm confused as a lock would explain this but I would expect my own query executions to be slow as well and not finish in 90ms but unless I'm reading this wrong

mysql tables in use 10, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 69614, OS thread handle 140665391331072, query id 128136000 localhost 127.0.0.1 db_live Sending data
SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`, `link_attribute_position_int`.`value` AS `position`, `link_attribute_qty_decimal`.`value` AS `qty` FROM `catalog_product_entity` AS `e`
Trx read view will not see trx with id >= 1772180307, sees < 1772180307
---TRANSACTION 422176365724392, ACTIVE 1338 sec
mysql tables in use 10, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 69607, OS thread handle 140665752717056, query id 128135089 localhost 127.0.0.1 db_live Sending data
SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`, `link_attribute_position_int`.`value` AS `position`, `link_attribute_qty_decimal`.`value` AS `qty` FROM `catalog_product_entity` AS `e`
Trx read view will not see trx with id >= 1772180307, sees < 1772180307

Those queries are not trying to access locked tables as it says 0 locks

How come the queries that were issued minutes ago are not finishing, and I can execute the same queries in 90ms? What could be causing this stallness?

What can I do to understand why they are slow next time it happens?

My version of MariaDB is

Server version: 10.4.28-MariaDB-1:10.4.28+maria~ubu2004-log mariadb.org binary distribution

My server has around 35GB of RAM free out of 128GB.

The disk is an Nvme Gen4 with ample resources.

%iowait% is always near 0 or 0.1

Load is around 6 on my 32 core machine.

Any help is appreciated.

+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+------+--------+----------+--------------------------------------------+
|id|select_type|table                      |type  |possible_keys                                                                                                                        |key                                                           |key_len|ref                                                |rows|r_rows|filtered|r_filtered|Extra                                       |
+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+------+--------+----------+--------------------------------------------+
|1 |SIMPLE     |links                      |ref   |CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID|CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID|6      |const,const                                        |8   |8.00  |100     |100       |Using index; Using temporary; Using filesort|
|1 |SIMPLE     |cat_index                  |eq_ref|PRIMARY,CAT_CTGR_PRD_IDX_STORE5_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,IDX_D66B6557126C2DE627A049511C72BE10                              |PRIMARY                                                       |10     |const,db_live.links.linked_product_id,const|1   |1.00  |100     |100       |Using where                                 |
|1 |SIMPLE     |price_index                |eq_ref|PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE                                |PRIMARY                                                       |10     |db_live.links.linked_product_id,const,const|1   |0.62  |100     |100       |                                            |
|1 |SIMPLE     |e                          |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU                                                                                                   |PRIMARY                                                       |4      |db_live.links.linked_product_id            |1   |1.00  |100     |100       |Using where                                 |
|1 |SIMPLE     |product                    |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU                                                                                                   |PRIMARY                                                       |4      |db_live.links.linked_product_id            |1   |1.00  |100     |100       |Using where                                 |
|1 |SIMPLE     |product_entity_table       |eq_ref|PRIMARY                                                                                                                              |PRIMARY                                                       |4      |db_live.links.product_id                   |1   |1.00  |100     |100       |Using index                                 |
|1 |SIMPLE     |inventory_in_stock         |eq_ref|PRIMARY,index_sku_qty                                                                                                                |PRIMARY                                                       |194    |db_live.e.sku                              |1   |1.00  |100     |100       |Using where                                 |
|1 |SIMPLE     |stock_status_index         |eq_ref|PRIMARY,index_sku_qty                                                                                                                |PRIMARY                                                       |194    |db_live.product.sku                        |1   |1.00  |100     |100       |Using where                                 |
|1 |SIMPLE     |link_attribute_position_int|eq_ref|CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID                                               |CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID                   |7      |const,db_live.links.link_id                |1   |1.00  |100     |100       |                                            |
|1 |SIMPLE     |link_attribute_qty_decimal |eq_ref|CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID                                           |CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID                   |7      |const,db_live.links.link_id                |1   |0.00  |100     |100       |                                            |
+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+------+--------+----------+--------------------------------------------+

EXPLAIN on the active query that's stuck

EXPLAIN SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`, `link_attribute_position_int`.`value` AS `position`, `link_attribute_qty_decimal`.`value` AS `qty` FROM `catalog_product_entity` AS `e` INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '2' INNER JOIN `catalog_category_product_index_store5` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=5 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=2 INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = e.entity_id INNER JOIN `inventory_stock_5` AS `stock_status_index` ON product.sku = stock_status_index.sku INNER JOIN `catalog_product_link` AS `links` ON links.linked_product_id = e.entity_id AND links.link_type_id = 1 LEFT JOIN `catalog_product_link_attribute_int` AS `link_attribute_position_int` ON link_attribute_position_int.link_id = links.link_id AND link_attribute_position_int.product_link_attribute_id = '2' LEFT JOIN `catalog_product_link_attribute_decimal` AS `link_attribute_qty_decimal` ON link_attribute_qty_decimal.link_id = links.link_id AND link_attribute_qty_decimal.product_link_attribute_id = '6' INNER JOIN `catalog_product_entity` AS `product_entity_table` ON links.product_id = product_entity_table.entity_id WHERE (inventory_in_stock.is_salable = 1) AND (stock_status_index.is_salable = 1) AND (links.product_id in ('177309')) AND (`e`.`entity_id` != '177309') ORDER BY `position` ASC:

*** row 1 ***
          table:  links
           type:  ref
  possible_keys:  CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID
            key:  CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID
        key_len:  6
            ref:  const,const
           rows:  20
          Extra:  Using index; Using temporary; Using filesort
*** row 2 ***
          table:  cat_index
           type:  eq_ref
  possible_keys:  PRIMARY,IDX_78288F76D618C4C378D1DE1DB362C05F,IDX_04B1890D19F381013371F9182F62C602
            key:  PRIMARY
        key_len:  10
            ref:  const,db_live.links.linked_product_id,const
           rows:  1
          Extra:  Using where
*** row 3 ***
          table:  price_index
           type:  eq_ref
  possible_keys:  PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE
            key:  PRIMARY
        key_len:  10
            ref:  db_live.links.linked_product_id,const,const
           rows:  1
          Extra:  NULL
*** row 4 ***
          table:  e
           type:  eq_ref
  possible_keys:  PRIMARY,CATALOG_PRODUCT_ENTITY_SKU
            key:  PRIMARY
        key_len:  4
            ref:  db_live.links.linked_product_id
           rows:  1
          Extra:  Using where
*** row 5 ***
          table:  inventory_in_stock
           type:  eq_ref
  possible_keys:  PRIMARY,index_sku_qty
            key:  PRIMARY
        key_len:  194
            ref:  db_live.e.sku
           rows:  1
          Extra:  Using where
*** row 6 ***
          table:  product
           type:  eq_ref
  possible_keys:  PRIMARY,CATALOG_PRODUCT_ENTITY_SKU
            key:  PRIMARY
        key_len:  4
            ref:  db_live.links.linked_product_id
           rows:  1
          Extra:  Using where
*** row 7 ***
          table:  stock_status_index
           type:  eq_ref
  possible_keys:  PRIMARY,index_sku_qty
            key:  PRIMARY
        key_len:  194
            ref:  db_live.product.sku
           rows:  1
          Extra:  Using where
*** row 8 ***
          table:  product_entity_table
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  db_live.links.product_id
           rows:  1
          Extra:  Using index
*** row 9 ***
          table:  link_attribute_position_int
           type:  eq_ref
  possible_keys:  CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID
            key:  CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID
        key_len:  7
            ref:  const,db_live.links.link_id
           rows:  1
          Extra:  NULL
*** row 10 ***
          table:  link_attribute_qty_decimal
           type:  eq_ref
  possible_keys:  CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID
            key:  CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID
        key_len:  7
            ref:  const,db_live.links.link_id
           rows:  1
          Extra:  NULL
4
  • What does the EXPLAIN ANALYZE show for both the slow and fast instances of the queries? I'd bet you're getting different query plans in both cases.
    – J.D.
    Jul 6, 2023 at 11:54
  • I've added the analyze for when I run it but how can I do this when the query is already in process for that instance?
    – gabtzi
    Jul 6, 2023 at 12:10
  • Please point out the differences between the two queries.
    – Rick James
    Jul 7, 2023 at 4:27
  • I'm not sure how to explain this correctly. I won't be able to provide the same result for the same query every single time because this is a dynamic query and the same query doesn't fail every time. The same query may or may not run fast. All of the queries I listed, are actually exhibiting the same behavior. When I run them manually they run really fast. In certain occassions they hang for an indefinite amount of time. I'm trying to detect the cause of it because generally they are 99.9% of the time fast except specific occassions.
    – gabtzi
    Jul 7, 2023 at 7:34

1 Answer 1

1

Indexes that may help:

price_index:  INDEX(customer_group_id, website_id)
cat_index:  INDEX(category_id, store_id, product_id,  position, visibility)
inventory_stock_5:  INDEX(is_salable, sku)
links:  INDEX(product_id, linked_product_id,  link_id, link_type_id)
link_attribute_position_int:  INDEX(link_id,  value, product_link_attribute_id)
link_attribute_qty_decimal:  INDEX(link_id,  value, product_link_attribute_id)
catalog_product_entity:  INDEX(entity_id, sku)

Please follow the convention where

  • ON indicates how the JOINing should be done;
  • WHERE provides filtering

This seems to be causing some of the trouble:

INSERT
     INTO  `catalog_category_product_index_store7_tmp`
      ( `category_id`, `product_id`, `position`,
       `is_parent`, `store_id`, `visibility` ) 
    SELECT  `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`,
        IFNULL(ccp2.position, ccp.position + 10000) AS `position`,
        0 AS `is_parent`, 7 AS `store_id`,
        IFNULL(cpvs.value, cpvd.value) AS `visibility`
    FROM  `catalog_category_entity` AS `cc`
    INNER JOIN  `temp_catalog_category_tree_index_c1453bad` AS `cc2`
           ON  cc2.parent_id = cc.entity_id
          AND  cc.entity_id NOT IN (1)
    INNER JOIN  `catalog_category_product` AS `ccp`
           ON ccp.category_id = cc2.child_id
    LEFT JOIN  `ca 

(need to see the rest of it!)

These indexes may help the INSERT..SELECT to run faster, hence get out of the way for the slow selects:

cc:  INDEX(entity_id)
ccp:  INDEX(category_id,  product_id, position)
cc2:  INDEX(parent_id,  child_id)

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.