Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25308

MyISAM converting to Aria and back becomes 2 times slower!

    XMLWordPrintable

    Details

      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

            People

            Assignee:
            Unassigned
            Reporter:
            oli Oli Sennhauser
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration