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

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

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.