I noticed my DB CPU creeping up and hitting 100% this week. Looking into the MySQL process list I saw 4 instances of the same query running. These queries never finish though, one has been running for 50+ hours, and the others nearly 20. If I kill any of these processes, they immediately get replaced with a new process running the same query. No matter how many times I kill them, they immediately come back.
All of the indexers are set to update on save and all are “ready” and not “processing”. The site is not public at the moment, so there’s no web traffic — I’ve turned off the cron as well. Hoping someone can point me to where this query might be coming from. It seems to be querying at_has_map in a selection of category IDs. has_map is a custom product attribute that we have. These queries are all stuck in the “statistics” state. The only thing that has stopped them from re-populating is turning on maintenance mode.
Any ideas?
SELECT `e`.*,
`at_has_map`.`value` AS `has_map`
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_website` AS `product_website`
ON product_website.product_id = e.entity_id
AND product_website.website_id IN( 1 )
LEFT JOIN `catalog_product_entity_int` AS `at_has_map`
ON ( `at_has_map`.`entity_id` = `e`.`entity_id` )
AND ( `at_has_map`.`attribute_id` = '425' )
AND ( `at_has_map`.`store_id` = 0 )
WHERE (( e.entity_id IN((SELECT `ca_has_map`.`entity_id`
FROM `catalog_product_entity_int` AS `ca_has_map`
WHERE ( ca_has_map.attribute_id = '425' )
AND ( ca_has_map.value IN( '0' ) )))
AND ( e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
647, 1278, 2698, 2699,
2700, 1446, 2662, 2663,
2664, 1453, 2659, 2660,
2661, 1454, 2656, 2657,
2658, 1455, 2652, 2653,
2654, 1456, 1457, 1458,
2649, 2650, 2651, 1539,
2701, 2702, 2703, 314,
2672, 2673, 2675, 320,
2680, 2681, 2682, 321,
2689, 2690, 2691, 322,
2692, 2693, 2694, 668,
2665, 2666, 2667, 669,
2668, 2669, 2670, 670,
2671, 2674, 2676, 671,
2677, 2678, 2679, 672,
2683, 2684, 2685, 673,
2686, 2687, 2688, 674,
2704, 2705, 2706 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
315, 1079, 1080, 948,
950, 951, 952, 953,
954, 955, 957, 958 ) )
)
)
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN( 317 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
900, 1293, 1294, 651,
655, 659, 901, 1432,
1434, 1435, 1436, 1444
,
902, 1437, 904, 905 )
)
))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
319, 1000, 1953, 999 )
))
)
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
345, 1255, 1315, 1465,
1466, 1316 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
334, 1001, 1298, 1297,
652, 657, 1247, 662,
1022
) )
))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
343, 1105, 1493, 1494,
1430, 1495, 1496, 1497
,
1451, 654, 1317, 1318
)
)
))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
346, 1075, 1076, 1077,
1078, 964, 1189, 1190,
967, 1181, 1182, 1183,
972, 975, 977 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
304, 1032, 1033, 1034,
1037, 1039, 1040, 1041
,
1042, 1043, 1053, 980
)
)
))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN( 1940 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
300, 1148, 1275, 1276,
1299, 912, 1253, 1254,
1413, 1414, 1415, 1416
,
1417, 913, 914, 915,
1086
) )
))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
294, 3130, 916, 1090,
1096, 1097, 1100, 1101
,
1300, 917, 1301, 3121
)
)
))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
306, 1012, 1094, 1095,
1108, 1109, 1102, 1104
,
1106, 1107, 1110, 1111
)
)))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN( 287, 1084, 1112
) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN( 1085 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
1016, 1687, 1688, 1689
,
1690, 1691, 1692, 1693
,
1694, 1695, 1696 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
1543, 1566, 1567, 1568
,
1569, 1570 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
1612, 1613, 1614, 1615
,
3131
)
)))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN( 1942 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN( 1949 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN( 1951, 3120 ) ))
)
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN( 1963 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
2610, 3025, 3026 ) )))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
2632, 1944, 1945, 2608
)
)))
AND e.entity_id IN((SELECT `cat`.`product_id`
FROM `catalog_category_product` AS `cat`
WHERE ( cat.category_id IN(
2648, 1946, 3122, 3123
,
1947, 1948 ) ))) ) ))