[MDEV-25308] MyISAM converting to Aria and back becomes 2 times slower! Created: 2021-03-31  Updated: 2021-04-20

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - MyISAM
Affects Version/s: 10.3, 10.4, 10.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Oli Sennhauser Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: MyISAM, regression-10.3, regression-10.4, regression-10.5
Environment:

Ubuntun 18.04, n.a.


 Description   

We had some significant query slow downs after upgrading from MariaDB 10.2 to 10.5. After some testing around we found at least one issue:

If we convert MyISAM tables to Aria and the back to MySAM queries on those tables are significantly slower (about factor 2) as they were before! This behaviour can be reverted by dumping the MyISAM tables and restore them.

The MyISAM file size dit not chang significantly.

This phenomena did NOT happen in 10.2 but started in 10.3 and continued in 10.4 and 10.5

We can provide a complete test case with results:

refer to: wf/test_dump.sql.gz

EXPLAIN
 
SELECT sql_no_cache DATE_FORMAT(d.`F-Datum`, '%Y/%m') AS MONTH, SUM(`Warenwert`) AS 'Umsatztotal'
 
  FROM `lieferscheine` AS d
 
  LEFT JOIN `kundenstamm` AS k ON (d.KdNr=k.k_KdNr)
 
 WHERE d.`F-Datum` BETWEEN '2019-01-01' AND '2020-12-31'
 
GROUP BY MONTH
 
;


+------+-------------+-------+-------+-----------------+---------+---------+-------------+---------+-----------------------------------------------------------+                                                                                                          
| id   | select_type | table | type  | possible_keys   | key     | key_len | ref         | rows    | Extra                                                     |                                                                                                          
+------+-------------+-------+-------+-----------------+---------+---------+-------------+---------+-----------------------------------------------------------+                                                                                                          
|    1 | SIMPLE      | d     | index | PartitioningKey | KdNr    | 16      | NULL        | 5354359 | Using where; Using index; Using temporary; Using filesort |                                                                                                          
|    1 | SIMPLE      | k     | ref   | PRIMARY         | PRIMARY | 3       | test.d.KdNr |   10485 | Using index                                               |                                                                                                          
+------+-------------+-------+-------+-----------------+---------+---------+-------------+---------+-----------------------------------------------------------+

Query execution plan was always the same in all tests

10.2.29

7.32 s MyISAM/MyISAM
7.37 s
7.38 s

10.3.20

7.38 s MyISAM/MyISAM
7.36 s
7.38 s

10.4.13

7.88 s MyISAM/MyISAM
8.01 s
8.02 s

10.5.9

7.38 s MyISAM/MyISAM
7.76 s
7.71 s

converting to aria and back to myisam

10.2

7.87 s
7.52 s
7.33 s

10.3

13.25 s
14.14 s
14.28 s

10.4

14.01 s
15.31 s
15.27 s

10.5

13.71 s
15.19 s
15.10 s

I do NOT believe that it is related to the partitions we used. But further investigation has to be done in this field...

 Comments   
Comment by Oli Sennhauser [ 2021-03-31 ]

non partitioned MyISAM tables did not make any difference:

10.2

8.07 s
7.73 s
7.69 s

10.3

14.60 s
14.22 s
14.42 s

10.4

15.09 s
14.97 s
15.17 s

10.5

14.36 s
15.02 s
15.17 s

Comment by Sergei Golubchik [ 2021-03-31 ]

Just to clarify:

  • you create a partitioned MyISAM table (from a dump) — run some SELECT as above
  • then you ALTER TABLE it to Aria and back to MyISAM — same SELECT runs twice as slow
    ?
Comment by Oli Sennhauser [ 2021-03-31 ]

Exactly. But last test shows that it is NOT related to partitions but MyISAM only. I tested it twice because I first was not sure that this can be true.
Shall I upload the test set?

Comment by Sergei Golubchik [ 2021-04-01 ]

Yes, please. This sounds very unusual, that ALTER TABLE to aria and back changes myisam table that way.

Comment by Oli Sennhauser [ 2021-04-01 ]

ftp ftp.askmonty.org/private
Name or service not known

https://mariadb.com/kb/en/mariadb-community-bug-reporting/

It is more than 10M

Comment by Sergei Golubchik [ 2021-04-05 ]

worked for me. May be your ftp client only expects a hostname, and ftp.askmonty.org/private is not a valid hostname? Try simply
ftp ftp.askmonty.org and then cd /private

Comment by Oli Sennhauser [ 2021-04-06 ]

Hmmm. Either it is fixed now or I was too tired... Now it works.
the files are called test_dump.sql.gz / test_dump_binary.sql.gz / test_dump_binary_25308.sql.gz

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