fast SELECT, slow INSERT

[fballiano]

Hi everybody, in one of my OpenMage projects (140k SKUs, 4 stores, 8 store views) I'm having problems with reindexing since a while, the reindex queries (most of them actually) are really slow (full reindex takes 20-25 minutes) but I've notices a weird thing.

This is a price reindex query:

INSERT INTO `catalog_product_index_price_bundle_sel_idx` SELECT `i`.`entity_id`, `i`.`customer_group_id`, `i`.`website_id`, `bo`.`option_id`, `bs`.`selection_id`, IF(bo.type = 'select' OR bo.type = 'radio', 0, 1) AS `group_type`, `bo`.`required` AS `is_required`, IF(IF(i.base_group_price IS NOT NULL, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty, IF(i.group_price_percent > 0, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) - (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.group_price_percent / 100)),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value))) * bs.selection_qty, NULL) < IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty, IF(i.base_group_price IS NOT NULL, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty, IF(i.group_price_percent > 0, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) - (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.group_price_percent / 100)),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value))) * bs.selection_qty, NULL), IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty) AS `price`, IF(i.base_tier IS NOT NULL, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.base_tier - (i.base_tier * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100)),2), IF(i.tier_percent > 0, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) - (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.tier_percent / 100)),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value))) * bs.selection_qty, NULL) AS `tier_price`, IF(i.base_group_price IS NOT NULL, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty, IF(i.group_price_percent > 0, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) - (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.group_price_percent / 100)),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value))) * bs.selection_qty, NULL) AS `group_price` FROM `catalog_product_index_price_bundle_idx` AS `i`
 INNER JOIN `catalog_product_bundle_option` AS `bo` ON bo.parent_id = i.entity_id
 INNER JOIN `catalog_product_bundle_selection` AS `bs` ON bs.option_id = bo.option_id
 LEFT JOIN `catalog_product_bundle_selection_price` AS `bsp` ON bs.selection_id = bsp.selection_id AND bsp.website_id = i.website_id
 INNER JOIN `catalog_product_index_price_idx` AS `idx` ON bs.product_id = idx.entity_id AND i.customer_group_id = idx.customer_group_id AND i.website_id = idx.website_id
 INNER JOIN `catalog_product_entity` AS `e` ON bs.product_id = e.entity_id AND e.required_options=0 WHERE (i.price_type=1) ON DUPLICATE KEY UPDATE `group_type` = VALUES(`group_type`), `is_required` = VALUES(`is_required`), `price` = VALUES(`price`), `tier_price` = VALUES(`tier_price`), `group_price` = VALUES(`group_price`)

which takes minutes to run, but if I remove the "INSERT" part and extract only the "SELECT":

SELECT `i`.`entity_id`, `i`.`customer_group_id`, `i`.`website_id`, `bo`.`option_id`, `bs`.`selection_id`, IF(bo.type = 'select' OR bo.type = 'radio', 0, 1) AS `group_type`, `bo`.`required` AS `is_required`, IF(IF(i.base_group_price IS NOT NULL, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty, IF(i.group_price_percent > 0, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) - (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.group_price_percent / 100)),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value))) * bs.selection_qty, NULL) < IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty, IF(i.base_group_price IS NOT NULL, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty, IF(i.group_price_percent > 0, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) - (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.group_price_percent / 100)),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value))) * bs.selection_qty, NULL), IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty) AS `price`, IF(i.base_tier IS NOT NULL, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.base_tier - (i.base_tier * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100)),2), IF(i.tier_percent > 0, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) - (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.tier_percent / 100)),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value))) * bs.selection_qty, NULL) AS `tier_price`, IF(i.base_group_price IS NOT NULL, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, IF(IF(bsp.selection_price_type IS NULL, bs.selection_price_type, bsp.selection_price_type) = 1, ROUND(i.price * (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) / 100),2), IF(i.special_price > 0 AND i.special_price < 100, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.special_price / 100),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value)))* bs.selection_qty, IF(i.group_price_percent > 0, ROUND(IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) - (IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value) * (i.group_price_percent / 100)),2), IF(bsp.selection_price_value IS NULL, bs.selection_price_value, bsp.selection_price_value))) * bs.selection_qty, NULL) AS `group_price` FROM `catalog_product_index_price_bundle_idx` AS `i`
 INNER JOIN `catalog_product_bundle_option` AS `bo` ON bo.parent_id = i.entity_id
 INNER JOIN `catalog_product_bundle_selection` AS `bs` ON bs.option_id = bo.option_id
 LEFT JOIN `catalog_product_bundle_selection_price` AS `bsp` ON bs.selection_id = bsp.selection_id AND bsp.website_id = i.website_id
 INNER JOIN `catalog_product_index_price_idx` AS `idx` ON bs.product_id = idx.entity_id AND i.customer_group_id = idx.customer_group_id AND i.website_id = idx.website_id
 INNER JOIN `catalog_product_entity` AS `e` ON bs.product_id = e.entity_id AND e.required_options=0 WHERE (i.price_type=1)

it takes less than a second to run!

I know writing is slower than reading and that indexes may have a role in this, but do you have any ideas how why this happens and if there's something that could help me debug where the heck is the problem (cause I don't think that's a normal behaviour).

Thanks everybody!

[tmotyl]

Hi
@fballiano please give more context.
Mysql version, is it single node or cluster,
Isolation mode. Do you have any mysql monitoring? If not see if you can set up PMM.

there can be different things causing it. Can you check a similar environment but without a load where you can check whether sth is load related or not?

What is worth mentioning is that due too magento architecture (eg usage of the foreign keys in index tables) that you have to analyze more then just this query and this table - as there are side effects like mysql locking rows when related table is modified.

[fballiano]

hi @tmotyl and thanks for your answer,
I tried both on mariadb 10.2 and mysql 8.0 (this one was tested with no-load cause it's a test server) but no sensible difference.
Both are single instance databases, with plenty or RAM (24GB or more), SSD disks etc...

I'd like to analyse the query but AFAIK there's no way to analyze an INSERT, while the SELECT is extremely fast.

I'll try to install PMM and see if that can help debug something.

[tmotyl]

How long does the query itself take? Have you measured how much time is on mysql and how much on php side (using eg new relic or blackfire)?

i assume mysql is configured to use the amount of resources you have (eg buffers)?

which openmage version you use?
Have you tried how long will the reindexing take if you change isolation mode to read committed on the test instance?

[fballiano]

I'm testing the queries directly from the DB console, so no PHP involved (it's the latest openmage 20).

I'll test different isolation modes (now it's the default: REPEATABLE-READ) although they scare me a little bit (I'm not a DBA).

Thanks again!

[Flyingmana]

have you tried out MySqls built in performance tooling for analyses?

https://vladmihalcea.com/mysql-query-profiling-performance-schema/
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html

can you share the performance_schema.events_stages_history_long for this query?

[fballiano]

sorry for the late reply guys, I've tried multiple things but i'm not that much of a DBA so I've called Oli at FromDual (which I couldn't recommend more) and we've worked together to fix it.

He changed some mariadb configurations, not exactly vitals to the specific problem, but that helped to have a more stable infrastructure.

Then we debugged the reindex system a lot and turns out mariadb query optimizer doesn't always generate the right "path" to execute complex queries like the ones needed for the reindexing process.

The optimizer usually generates the execution path based on table statistics so we tried to update the statistics with "persistant statistics" and "analyze table" but the are multiple problems with this approach so we ended up trashing it.

Turns out that adding a "STRAIGHT JOIN" hint to some of the reindexing queries made the optimizer work correctly and a reindex process that was more than 1 hour long when to less than 20 minutes.

Point is... modify the reindexing queries is not that easy... I didn't want to patch something like 10-20 core files so... long story short I ended up patching lib/Zend/Db/Adapter/Abstract.php in an ugly (but works perfectly, compared to before) way:

diff --git a/lib/Zend/Db/Adapter/Abstract.php b/lib/Zend/Db/Adapter/Abstract.php
index 9df910f75..7c4207533 100644
--- a/lib/Zend/Db/Adapter/Abstract.php
+++ b/lib/Zend/Db/Adapter/Abstract.php
@@ -468,6 +468,23 @@ abstract class Zend_Db_Adapter_Abstract
             $sql = $sql->assemble();
         }

+       if (strpos($sql, "catalog_product_index_eav_idx") === false and (strpos($sql, "_index") !== false or strpos($sql, "_idx") !== false)) {
+            $sql = str_replace(" SELECT ", " SELECT STRAIGHT_JOIN ", $sql);
+        }

so that whatever query involving _index or _idx tables gets modified (I know it's damn ugly, suggestions are welcome). We've to disable the catalog_product_index_eav_idx table from this patch cause performance was actually worse than before (only for that query).

[addison74]

@fballiano - Let's not forget about this issue. You may create a PR for this enhancement and all interested can give it a run.

[fballiano]

mmm that code it's not something that can be in a PR, it should be totally rewritten in the single models but I wouldn't trust it anyway as a "general purpose" code :-)