[MDEV-25205] Query regression after running analyze on 10.4.18 Created: 2021-03-19  Updated: 2021-03-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Server
Affects Version/s: 10.4.18, 10.5.9
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Anton Avramov Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: regression
Environment:

debian 10, Ubuntu 20.04

Attachments: File MDEV-25205.sql     File MDEV_25205.dump.sql.gz.00     File MDEV_25205.dump.sql.gz.01     File MDEV_25205.dump.sql.gz.02     Text File msql-10.4.18-analyzed.txt     Text File msql-10.4.18.txt    

 Description   

We have a query that was running for about 0.7 sec on avarage on mariadb server 10.4.17.
After upgrade to 10.4.18 the query still takes about 0.7.
However after running analyze on the tables the query starts running for 2.2 sec.

If I downgrade the server binary to 10.4.17 the query still takes 2.2 sec, but after running analyze again it goes back to 0.7 sec

I'm attaching here explain and analyze of the query before and after running the analyze.
Unfortunatelly I cannot provide a dump of the tables, since the data is private.

I'll be happy to run more tests for you or provide you with more information to fix that regression.

 Comments   
Comment by Anton Avramov [ 2021-03-25 ]

I've stripped down the relative tables and manage to produce steps to reproduce:
1. Download the attached files MDEV*
2. In 10.4.17 create database MDEV_25205 charset utf8;
and import: zcat MDEV_25205.dump.sql.gz.00 MDEV_25205.dump.sql.gz.01 MDEV_25205.dump.sql.gz.02 | gzip -dc | mysql MDEV_25205
3. Execute the query in MDEV-25205.sql and mesure the time several times.
time mysql MDEV-25205 < MDEV-25205.sql
4. Upgrade the binary to 10.4.18 or 10.5.9 and run again.
5. Run mariadb-analyze MDEV_25205 and then execute the query several times again
time mysql MDEV-25205 < MDEV-25205.sql

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