[MDEV-27487] After upgrade from MySQL 5.6 to MariaDB 10.5 queries taking more time to execute Created: 2022-01-13  Updated: 2022-03-23  Resolved: 2022-03-23

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Pon Suresh Pandian (Inactive) Assignee: Sergei Petrunia
Resolution: Incomplete Votes: 0
Labels: None
Environment:

Debian

Attachments: File Actual_running_query.sql     File MariaDB-10.5-analyse-json-output.sql     File Mariadb-10.5.12-global-variables.sql     File explain_long_running_querys.md     File mysql-5.6-global-variables     HTML File perf_output    

 Description   

Hi Team,

In mysql 5.6 calculation queries finished quickly within 30 sec. After upgrade MariaDB 10.5.12
same query it's taking more time even it's not completing.

Time of query on old DB: (MySQL 5.6)
 
 
 
bash
 
real 0m30.015s
 
user 0m0.420s
 
sys 0m0.580s
 
 
 
New DB (MariaDB 10.5.12)
 
 
 
ERROR 1969 (70100): Query execution was interrupted (max_statement_time exceeded)
 
 
 
MariaDB [(none)]> show variables like '%max_statement_time%';
 
+--------------------+------------+
 
| Variable_name      | Value      |
 
+--------------------+------------+
 
| max_statement_time | 600.000000 |
 
+--------------------+------------+
 
1 row in set (0.001 sec)

Here I have attached the explain plan, analyze json outputs and global variables output. Please check it. Let me know if you need any other details.

 Comments   
Comment by Sergei Petrunia [ 2022-01-24 ]

Looking at explain_long_running_querys.md and noting differences of query plans:

  • First table, anfragen, uses different indexes.
    • in MariaDB it uses range access, but the query plan is using "Using temporary; Using filesort".
    • In MySQL it uses PRIMARY key which allows not avoid using filesort
  • table 'vario'
    • in MariaDB, it uses full table scan with rows=928K and "Using Join Buffer"
    • in MySQL, it is using full table scan with 906K rows and "derived-with-keys" optimization. (Can this work as poor-mans-BNLH or hash join?)
  • table "vario_condition' is present in MySQL but apparently not present in MariaDB (This is likely Table Elimination).
Comment by Sergei Petrunia [ 2022-01-24 ]

MariaDB-10.5-analyse-json-output.sql actually has EXPLAIN FORMAT=JSON output, not ANALYZE output.

Comment by Sergei Petrunia [ 2022-01-24 ]

Perf output shows that most of the time is spent reading from the join_cache.
EXPLAIN shows that there's only one table reading from the join cache:

|    1 | PRIMARY      | vario                 | ALL    | NULL                                                    | NULL        | NULL    | NULL                                         | 928111 | Using where; Using join buffer (flat, BNL join)                     |

In the query, it is:

 ... LEFT JOIN (SELECT condition_type,inquiry_id,condition_id 
                FROM meinauto_local.vario) var ON var.inquiry_id=anfragen.anID

Generated at Thu Feb 08 09:53:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.