Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5
-
None
-
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...
Attachments
Activity
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
?
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?
Yes, please. This sounds very unusual, that ALTER TABLE to aria and back changes myisam table that way.
ftp ftp.askmonty.org/private
Name or service not known
https://mariadb.com/kb/en/mariadb-community-bug-reporting/
It is more than 10M
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
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
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