[MDEV-30823] Long running SELECT in MariaDB version > 10.4 Created: 2023-03-09  Updated: 2024-01-26

Status: Needs Feedback
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.6
Fix Version/s: 10.6

Type: Bug Priority: Critical
Reporter: Matej Assignee: Dave Gosselin
Resolution: Unresolved Votes: 1
Labels: None

Attachments: PNG File 334924939_611458853843888_8138429930301132971_n.png     File Maria-10-4.json     File Maria-latest.json     PNG File MariaDB-10.4.png     PNG File MariaDB-latest.png     File MySQL-latest.json     PNG File MySQL-latest.png     PNG File Percona-latest.png     File s08_a_a-2024-01-19-12-02-01.sql    

 Description   

Hello,

we are using this SQL: 

SELECT FLOOR(MIN(IF (sp.id_specific_price IS NULL, (p_shop.price + IFNULL(pas.price, 0))*((IFNULL(t.rate,0)/100) + 1),IF (sp.reduction_type = 'percentage',IF(sp.price = -1, (p_shop.price + IFNULL(pas.price, 0)), (sp.price + IFNULL(pas.price, 0))) * ((IFNULL(t.rate,0)/100) + 1) * (1 - sp.reduction),(IF(sp.price = -1, (p_shop.price + IFNULL(pas.price, 0)), (sp.price + IFNULL(pas.price, 0))) * ((IFNULL(t.rate,0)/100) + 1)) - sp.reduction)))) AS min_price, CEIL(MAX(IF (sp.id_specific_price IS NULL, (p_shop.price + IFNULL(pas.price, 0))*((IFNULL(t.rate,0)/100) + 1),IF (sp.reduction_type = 'percentage',IF(sp.price = -1, (p_shop.price + IFNULL(pas.price, 0)), (sp.price + IFNULL(pas.price, 0))) * ((IFNULL(t.rate,0)/100) + 1) * (1 - sp.reduction),(IF(sp.price = -1, (p_shop.price + IFNULL(pas.price, 0)), (sp.price + IFNULL(pas.price, 0))) * ((IFNULL(t.rate,0)/100) + 1)) - sp.reduction)))) AS max_price
 
FROM `ps_product` p
 
INNER JOIN `ps_product_shop` p_shop ON p_shop.id_product = p.id_product AND p_shop.id_shop = 1
 
INNER JOIN `ps_category_product` cpro_p ON cpro_p.id_product = p_shop.id_product
 
INNER JOIN `ps_category_shop` cshop_p ON cshop_p.id_category = cpro_p.id_category AND cshop_p.id_shop = 1
 
LEFT JOIN `ps_product_attribute_shop` `pas` ON pas.id_product = p_shop.id_product AND pas.id_shop = 1
 
LEFT JOIN `ps_tax_rules_group` `trg` ON trg.id_tax_rules_group = p_shop.id_tax_rules_group
 
LEFT JOIN `ps_tax_rules_group_shop` `trgs` ON trgs.id_tax_rules_group = trg.id_tax_rules_group AND trgs.id_shop = 1
 
LEFT JOIN `ps_tax_rule` `tr` ON trg.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = 37 AND tr.`id_state` = 0 AND tr.`zipcode_from` = 0
 
LEFT JOIN `ps_tax` `t` ON t.id_tax = tr.id_tax
 
LEFT JOIN `ps_specific_price` `sp` ON p_shop.id_product = sp.id_product AND sp.id_specific_price = (SELECT spc.id_specific_price
 
FROM `ps_specific_price` spc
 
WHERE (spc.id_product = p.id_product) AND (spc.id_shop IN (0, 1)) AND (spc.id_currency IN (0, 1)) AND (spc.id_country IN (0, 37)) AND (spc.id_group IN (0, 3)) AND (spc.from = '0000-00-00 00:00:00' OR '2022-05-31 23:12:00' >= spc.from) AND (spc.to = '0000-00-00 00:00:00' OR '2022-05-31 23:12:00' <= spc.to) AND (spc.id_product_attribute = 0)
 
ORDER BY spc.id_product_attribute DESC, spc.from_quantity DESC, (IF(spc.id_group = 3, 2, 0) + IF(spc.id_country = 37, 4, 0) + IF(spc.id_currency = 1, 8, 0) + IF(spc.id_shop = 1, 16, 0) + IF(spc.id_customer = 0, 32, 0)) DESC, spc.to DESC, spc.from DESC
 
LIMIT 1) 
INNER JOIN `ps_stock_available` stock_0 ON stock_0.id_product = p.id_product AND stock_0.id_product_attribute = IFNULL(pas.id_product_attribute, 0)
 
WHERE (p_shop.active = 1) AND (p_shop.visibility IN ("both", "catalog")) AND (cshop_p.id_category = 12 OR cshop_p.id_category IN (SELECT c.id_category
 
FROM `ps_category` c
 
WHERE (c.nleft >= 3) AND (c.nright <= 100)
 
)) AND (stock_0.quantity > 0) LIMIT 1;

I performed several testing cases with this result:
MariaDB 10.4. - 0.98s
MariaDB 10.6. - 13.49s
MariaDB 10.11. - 11.5s
MySQL 8.0 - 1.15
Percona 5.7 - 0.8s

Looking that version of MariaDB higher than 10.4 spend a lot of time on Creating sort index (96%). Can you advise what is wrong if there is any parameter that can be changed? We are using default configuration

 Comments   
Comment by Daniel Black [ 2023-03-09 ]

What is the ANALYZE FORMAT=json query output (as {noformat} enclosed text)? Did running ANALYZE TABLE on the affected table change the query plan and/or response time?

Can you include EXPLAIN plans from MySQL as comparison?

Comment by Matej [ 2023-03-10 ]

Hello, sorry I don't know what you mean by ANALYZE FORMAT. Data are stored in regular rows in Database, the output format is also regular format row format. No JSON is involved. Yes, I tried ANALYZE tables and also fix, but I performed tests on fresh DB which was created only for testing purposes.

I made mistake in the description. To be exact SQL that we are executing does not begin by EXPLAIN (wrong copy-paste), I fixed the description.

I executed EXPLAIN for several DB engines. The output is in attachments.

Comment by Sergei Golubchik [ 2023-03-30 ]

It meant to run ANALYZE FORMAT=JSON SELECT FLOOR(MIN(IF (... etc.
So, like EXPLAIN, you put ANALYZE FORMAT=JSON in front of your SELECT.
See https://mariadb.com/kb/en/analyze-format-json/

Comment by Matej [ 2023-03-30 ]

Hello, got it. Here is the output: MySQL-latest.json Maria-10-4.json Maria-latest.json

Comment by Dominik Shaim [ 2023-10-28 ]

Any progress about this? We are still facing this problem.

Comment by Dave Gosselin [ 2024-01-18 ]

Unless I'm missing something, Baloga can you please provide schemas for the tables referenced by this query? Either the output of SHOW CREATE TABLE for each table, the CREATE TABLE statements themselves, or a SQL file that I can load to recreate the tables locally.

Comment by Matej [ 2024-01-19 ]

Hello, here it is. Unfortunately I am not able to provide data as there are sensitive customer ifnormation. s08_a_a-2024-01-19-12-02-01.sql

Comment by Dave Gosselin [ 2024-01-23 ]

Hi Baloga, thank you for providing the SQL script to create the tables. After looking a bit further, we note that within the ANALYZE FORMAT=JSON output, the stock_0 table finds itself in a different position in the table join order when compared against 10.4. Additionally, the 'rows' field, which reflects the number of rows that the optimizer expects to find in the table, is 1 while the observed number of rows ('r_rows') is about 150. This is a surprising finding as we expect these numbers to be very similar to one another.
Would you be able to send to us the result of the following query from 10.4, 10.6, and 10.11: SHOW KEYS FROM ps_stock_available;
Thank you.

Generated at Thu Feb 08 10:19:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.