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
| Field | Original Value | New Value |
|---|---|---|
| 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... |
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 {code:sql} 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 ; {code}{noformat} +------+-------------+-------+-------+-----------------+---------+---------+-------------+---------+-----------------------------------------------------------+ | 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 | +------+-------------+-------+-------+-----------------+---------+---------+-------------+---------+-----------------------------------------------------------+ {noformat} 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... |
| Labels | MyISAM regression-10.3 regression-10.4 regression-10.5 | MyISAM need_feedback regression-10.3 regression-10.4 regression-10.5 |
| Labels | MyISAM need_feedback regression-10.3 regression-10.4 regression-10.5 | MyISAM regression-10.3 regression-10.4 regression-10.5 |
| Workflow | MariaDB v3 [ 120641 ] | MariaDB v4 [ 142739 ] |