0

MariaDB 10.4.21 Magento 2.4.2p1

Database keeps hanging when trying to reindex - Catalog Product / Category Association, when trying to insert into catalog_category_product_index_store1_replica.

36,000 products

Updated batchRowsCount to be 100 for Magento\Catalog\Model\Indexer\Category\Product\Action\Full

Has anyone else managed to troubleshoot this issue?

innodb_file_per_table     = 1
max_allowed_packet        = 64M
table_open_cache          = 8192
table_definition_cache    = 8192
sort_buffer_size          = 1M
net_buffer_length         = 16K
read_buffer_size          = 512K
read_rnd_buffer_size      = 1M
myisam_sort_buffer_size   = 16M
concurrent_insert         = 2
low_priority_updates      = 1
query_cache_size          = 128M
query_cache_limit         = 64M
key_buffer_size           = 128M
thread_cache_size         = 6
innodb_buffer_pool_size   = 512M
join_buffer_size          = 1M
tmp_table_size            = 256M
max_heap_table_size       = 256M

The following query during reindex hangs the database for 10 - 20 minutes

INSERT INTO `catalog_category_product_index_store1_replica` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
     INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
     INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
     INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
     INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cpe.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 96
     LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = cpe.entity_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
     INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cpe.entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 102
     LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = cpe.entity_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1
     LEFT JOIN `catalog_product_relation` AS `relation` ON cpe.entity_id = relation.parent_id
     LEFT JOIN `catalog_product_entity` AS `relation_product_entity` ON relation.child_id = relation_product_entity.entity_id
     LEFT JOIN `catalog_product_entity_int` AS `child_cpsd` ON child_cpsd.entity_id = relation_product_entity.entity_id AND child_cpsd.store_id = 0 AND child_cpsd.attribute_id = 96
     LEFT JOIN `catalog_product_entity_int` AS `child_cpss` ON child_cpss.entity_id = relation_product_entity.entity_id AND child_cpss.attribute_id = child_cpsd.attribute_id AND child_cpss.store_id = 1 WHERE (cc.path LIKE '1/2/%') AND (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (relation.child_id IS NULL OR IFNULL(child_cpss.value, child_cpsd.value) = 1) AND (ccp.product_id IN (44387, 44388, 44389, 44390, 44391, 44392, 44393, 44394, 44395, 44396, 44401, 44404, 44405, 44407, 44411, 44413, 44417, 44421, 44425, 44434, 44435, 44436, 44438, 44444, 44446, 44447, 44448, 44454, 44464, 44466, 44517, 44519, 44526, 44528, 44530, 44534, 44536, 44538, 44539, 44540, 44543, 44548, 44553, 44555, 44563, 44564, 44565, 44566, 44567, 44569, 44570, 44571, 44572, 44573, 44576, 44577, 44583, 44585, 44586, 44587, 44594, 44596, 44597, 44600, 44601, 44603, 44606, 44607, 44609, 44610, 44615, 44616, 44618, 44623, 44624, 44625, 44626, 44627, 44628, 44629, 44631, 44633, 44634, 44635, 44636, 44637, 44638, 44640, 44641, 44643, 44644, 44645, 44646, 44648, 44651, 44653, 44654, 44655, 44656, 44657, 44658, 44659, 44660, 44662, 44663, 44664, 44666, 44668, 44669, 44670, 44673, 44674, 44675, 44676, 44677, 44687, 44689, 44690, 44694, 44698, 44699, 44700, 44702, 44704, 44706, 44707, 44708, 44709, 44710, 44711, 44715, 44716, 44717, 44719, 44720, 44721, 44722, 44723, 44724, 44725, 44727, 44728, 44729, 44731, 44734, 44735, 44736, 44737, 44738, 44739, 44740, 44741, 44742, 44766, 44767, 44768, 44769, 44771, 44772, 44774, 44777, 44788, 44802, 44825, 44826, 44827, 44828, 44891, 44893, 44894, 44895, 44896, 44898, 44899, 44900, 44901, 44904, 44905, 44907, 44910, 44911, 44914, 44915, 44916, 44917, 44920, 44921, 44922, 44926, 44928, 44929, 44931, 44932, 44933, 44934, 44935, 44937, 44938, 44939, 44940, 44941, 44943, 44944, 44945, 44946, 44947, 44948, 44949, 44950, 44952, 44953, 44957, 44965, 44967, 44969, 44970, 44971, 44972, 44974, 44975, 44989, 44990, 44997, 45000, 45001, 45003, 45009, 45010, 45011, 45012, 45013, 45014, 45020, 45021, 45026, 45032, 45033, 45035, 45036, 45037, 45038, 45039, 45040, 45041, 45042, 45043, 45044, 45045, 45046, 45047, 45048, 45049, 45050, 45051, 45052, 45053, 45054, 45055, 45056, 45057, 45058, 45059, 45060, 45061, 45062, 45064, 45065, 45066, 45067, 45068, 45069, 45070, 45071, 45072, 45073, 45074, 45075, 45076, 45077, 45078, 45079, 45080, 45081, 45082, 45083, 45084, 45085, 45086, 45087, 45088, 45089, 45090, 45091, 45092, 45093, 45094, 45095, 45096, 45097, 45098, 45099, 45100, 45101, 45102, 45103, 45104, 45105, 45106, 45107, 45108, 45109, 45110, 45111, 45112, 45113, 45114, 45115, 45116, 45117, 45119, 45120, 45121, 45122, 45123, 45124, 45125, 45126, 45127, 45128, 45129, 45130, 45131, 45132, 45133, 45134, 45135, 45136, 45137, 45138, 45139, 45140, 45141, 45142, 45143, 45144, 45145, 45146, 45147, 45148, 45149, 45150, 45151, 45152, 45153, 45154, 45155, 45156, 45157, 45158, 45159, 45160, 45161, 45162, 45163, 45164, 45165, 45166, 45167, 45168, 45169, 45170, 45171, 45172, 45173, 45174, 45175, 45176, 45177, 45178, 45179, 45180, 45181, 45182, 45183, 45184, 45185, 45186, 45187, 45188, 45189, 45190, 45191, 45192, 45193, 45194, 45195, 45196, 45197, 45198, 45199, 45200, 45201, 45202, 45203, 45204, 45205, 45206, 45207, 45208, 45209, 45210, 45211, 45212, 45213, 45214, 45215, 45216, 45217, 45218, 45219, 45220, 45221, 45222, 45223, 45224, 45225, 45226, 45227, 45228, 45229, 45230, 45231, 45232, 45233, 45234, 45235, 45236, 45237, 45238, 45239, 45240, 45241, 45242, 45243, 45244, 45247, 45248, 45249, 45250, 45251, 45252, 45253, 45254, 45255, 45256, 45257, 45258, 45259, 45260, 45261, 45262, 45263, 45264, 45265, 45266, 45267, 45268, 45269, 45270, 45271, 45272, 45273, 45274, 45275, 45276, 45277, 45278, 45279, 45280, 45281, 45282, 45283, 45284, 45285, 45286, 45287, 45288, 45289, 45291, 45292, 45293, 45294, 45295, 45296, 45297, 45298, 45299, 45300, 45301, 45302, 45303, 45305, 45306, 45325, 45326, 45327, 45328, 45329, 45331, 45332, 45333, 45334, 45335, 45336, 45337, 45338, 45339, 45340, 45341, 45342, 45343, 45344, 45345, 45346, 45347, 45348, 45349, 45350, 45351, 45352, 45353, 45354, 45355, 45363, 45379, 45383, 45384, 45385, 45387, 45388, 45389, 45390, 45391, 45392, 45395, 45396, 45398, 45400, 45401, 45402, 45403, 45406, 45407, 45408, 45409, 45413, 45414, 45416, 45419, 45423, 45424, 45425, 45426, 45427, 45429, 45431, 45432, 45434, 45435, 45436, 45437, 45438, 45439, 45440, 45441, 45442, 45443, 45444, 45445, 45447, 45448, 45449, 45451, 45469, 45470, 45540, 45541, 45558, 45560, 45561, 45563, 45564, 45566, 45569, 45609, 45610, 45611, 45612, 45613, 45614, 45615, 45618, 45619, 45620, 45621, 45622, 45624, 45625, 45626, 45627, 45628, 45629, 45630, 45631, 45632, 45633, 45634, 45635, 45636, 45637, 45638, 45640, 45642, 45651, 45652, 45653, 45654, 45655, 45657, 45658, 45659, 45661, 45662, 45663, 45664, 46130, 46132, 46271, 46272, 46276, 46283, 46287, 46288, 46292, 46293, 46294, 46295, 46296, 46297, 46298, 46300, 46304, 46305, 46328, 46330, 46331, 46332, 46333, 46335, 46336, 46338, 46339, 46340, 46341, 46342, 46343, 46345, 46346, 46347, 46348, 46356, 46362, 46363, 46374, 46376, 46377, 46378, 46379, 46380, 46398, 46407, 46408, 46412, 46413, 46414, 46415, 46416, 46417, 46419, 46420, 46421, 46422, 46423, 46424, 46425, 46426, 46427, 46428, 46429, 46430, 46432, 46433, 46434, 46435, 46437, 46438, 46439, 46440, 46441, 46442, 46443, 46467, 46501, 46502, 46503, 46504, 46505, 46506, 46507, 46508, 46509, 46510, 46528, 46542, 46543, 46544, 46551, 46596, 46597, 46598, 46599, 46600, 46601, 46602, 46604, 46606, 46607, 46608, 46609, 46610, 46612, 46613, 46614, 46615, 46616, 46617, 46620, 46621, 46622, 46624, 46625, 46626, 46629, 46631, 46632, 46633, 46634, 46645, 46649, 46651, 46754, 46757, 46765, 46766, 46769, 46770, 46774, 46775, 46777, 46779, 46781, 46782, 46783, 46789, 46791, 46792, 46793, 46805, 46809, 46811, 46812, 46813, 46814, 46815, 46816, 46819, 46820, 46821, 46826, 46828, 46829, 46830, 46831, 46833, 46851, 46855, 46867, 46868, 46872, 46873, 46874, 46875, 46885, 46887, 46890, 46892, 46907, 46910, 46990, 46991, 46993, 46994, 46995, 46996, 46998, 47001, 47002, 47005, 47007, 47008, 47009, 47013, 47071, 47088, 47091, 47092, 47093, 47094, 47145, 47147, 47149, 47150, 47156, 47157, 47158, 47159, 47160, 47161, 47162, 47163, 47164, 47165, 47166, 47167, 47168, 47169, 47170, 47171, 47172, 47173, 47174, 47175, 47176, 47177, 47178, 47179, 47180, 47181, 47182, 47183, 47184, 47185, 47186, 47187, 47188, 47189, 47190, 47191, 47193, 47194, 47196, 47197, 47198, 47199, 47200, 47201, 47202, 47234, 47235, 47236, 47238, 47239, 47240, 47241, 47245, 47246, 47247, 47248, 47249, 47250, 47252, 47253, 47254, 47255, 47256, 47262, 47263, 47266, 47267, 47268, 47269, 47270, 47271, 47272, 47273, 47274, 47275, 47276, 47277, 47278, 47279, 47280, 47281, 47282, 47283, 47284, 47285, 47286, 47287, 47288, 47289, 47290, 47291, 47292, 47293, 47294, 47295, 47296, 47297, 47298, 47299, 47300, 47301, 47303, 47304, 47305, 47306, 47307, 47308, 47309, 47310, 47311, 47312, 47313, 47315, 47316, 47317, 47318, 47319, 47320, 47321, 47322, 47323, 47324, 47325, 47326, 47327, 47328, 47329, 47330, 47331, 47332, 47333, 47334, 47335, 47336, 47337, 47338, 47339, 47340, 47341, 47343, 47344, 47345, 47347, 47348, 47349, 47350, 47351, 47352, 47355, 47356, 47357, 47358, 47359, 47360, 47361, 47362, 47363, 47364, 47365, 47366, 47367, 47370, 47372, 47373, 47374, 47375, 47376, 47377)) GROUP BY `cc`.`entity_id`,
            `ccp`.`product_id`,
            `visibility` ON DUPLICATE KEY UPDATE `category_id` = VALUES(`category_id`), `product_id` = VALUES(`product_id`), `position` = VALUES(`position`), `is_parent` = VALUES(`is_parent`), `store_id` = VALUES(`store_id`), `visibility` = VALUES(`visibility`)
2
  • Interestingly, this issue occurs when dealing with the backlog of reindexes on schedule. Indexing manually reindexes without issue
    – elfling
    Aug 18 at 11:40
  • The only way to overcome this, was to set product / category association to update on save when doing 1000s of product updates via the API.
    – elfling
    Aug 19 at 10:24
0

I recently made something similar with a plugin observer. After that I had no more issue. If the problem persist, may be it's more an infra issue with your database configuration ?

class MaxBatchSize
{
    public function afterEstimateBatchSize($subject, $batchRowsCount)
    {
        return min(1000, $batchRowsCount);
    }
}
4
  • Hi @Claims, Hate to ask, but could you elaborate a bit more? Which observer did you end up using to help with this?
    – elfling
    Aug 18 at 9:04
  • I don't have it in mind exactly but if you just look in core for the method estimateBatchSize you should get it. It's just a plugin on this specific method.
    – Claims
    Aug 18 at 9:10
  • Yeah, just found what I was looking for as you replied. Much appreciated
    – elfling
    Aug 18 at 9:16
  • not sure it will work for you but it worth the try I think. Good luck.
    – Claims
    Aug 18 at 9:23

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.