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

LP:637962 - Considerable performance regression on certain queries in maria-5.1-wl24

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.3.10
    • Component/s: None
    • Labels:

      Description

      The following query:

      SELECT `col_varchar_64_key` FROM `table100000_myisam_int_autoinc`
      WHERE ( `col_varchar_64_key` NOT IN ( 'now' , 'rsgxnnowvz' ) OR `col_varchar_64_key` LIKE CONCAT ('Utah' , '%' ) )
      AND ( `col_varchar_10_key` BETWEEN 'cr' AND 'really' OR `col_varchar_64_key` IN ( 'j' , 'rcrsgxnn' ) )
      AND ( ( `col_varchar_10_key` != 'it' ) OR `col_varchar_10_key` IS NULL )
      ORDER BY `col_varchar_64_key` LIMIT 7;

      Takes no time on maria-5.1 but takes over 1 second on maria-5.1-wl24 . Even if the filesort is avoided by removing the ORDER BY, considerable performance difference remains.

      EXPLAIN under maria-5.1:

      id: 1
      select_type: SIMPLE
      table: table100000_myisam_int_autoinc
      type: range
      possible_keys: col_varchar_10_key,col_varchar_64_key
      key: col_varchar_64_key
      key_len: 67
      ref: NULL
      rows: 99905
      Extra: Using where

      EXPLAIN under maria-5.1-wl24

      id: 1
      select_type: SIMPLE
      table: table100000_myisam_int_autoinc
      type: index_merge
      possible_keys: col_varchar_10_key,col_varchar_64_key
      key: col_varchar_10_key,col_varchar_64_key
      key_len: 13,67
      ref: NULL
      rows: 54298
      Extra: Using sort_union(col_varchar_10_key,col_varchar_64_key); Using where; Using filesort

      The table contains 100K rows and will be uploaded shortly.

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            ratzpo Rasmus Johansson added a comment -

            Launchpad bug id: 637962

            Show
            ratzpo Rasmus Johansson added a comment - Launchpad bug id: 637962
            Hide
            igor Igor Babaev added a comment -

            This is a serious performance degradation that can be observed for queries with
            ORDER BY a LIMIT n
            when there is an index on the field a and a range condition over field a,
            but at the same time optimizer chooses an index merge scan.

            The problem is that when making a cost-based choice between an index scan and an index-merge scan the optimizer does not take into account ORDER BY ...LIMIT n.

            Show
            igor Igor Babaev added a comment - This is a serious performance degradation that can be observed for queries with ORDER BY a LIMIT n when there is an index on the field a and a range condition over field a, but at the same time optimizer chooses an index merge scan. The problem is that when making a cost-based choice between an index scan and an index-merge scan the optimizer does not take into account ORDER BY ...LIMIT n.
            Hide
            igor Igor Babaev added a comment -

            I'm afraid we don't have enough time to fix the problem for 5.3.10. The fixing patch will require a good testing.
            I

            Show
            igor Igor Babaev added a comment - I'm afraid we don't have enough time to fix the problem for 5.3.10. The fixing patch will require a good testing. I
            Hide
            igor Igor Babaev added a comment -

            The problem is observed in mysql-5.6.7 (but not in prior releases).
            I've reported the bug http://bugs.mysql.com/67432.

            Show
            igor Igor Babaev added a comment - The problem is observed in mysql-5.6.7 (but not in prior releases). I've reported the bug http://bugs.mysql.com/67432 .
            Hide
            igor Igor Babaev added a comment -

            The bug was fixed and the fix was pushed into 5.3.

            Show
            igor Igor Babaev added a comment - The bug was fixed and the fix was pushed into 5.3.

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                philipstoev Philip Stoev
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 hour, 30 minutes
                  1h 30m