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

Poor performance after upgrade from 10.1.14 to 10.1.16

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.16
    • 10.1.16
    • Optimizer
    • This problem was detected in a CentOS 7 and Fedora 23 environment

    Description

      After the upgrade from the 10.1.14 to 10.1.16, I have detected that some queries need a lot of time to be executed.

      When I have downgraded to the 10.1.14, I have checked that the problem has dissapeared. I have executed the mysql_upgrade after each upgrade.

      I attach some files that I expect that help you to understand the problem and fix it.

      • saltos-10.1.14.log => contains the log of all queries executed by SaltOS to get the list page - using a MariaDB 10.1.14.
      • saltos-10.1.16.log => contains the log of all queries executed by SaltOS to get the list page using a MariaDB 10.1.16.
      • query.sql => contains the conflictive query!!!
      • explain-10.1.14.log => contains the explain extended command for the previous query using a MariaDB 10.1.14.
      • explain-10.1.16.log => contains the explain extended command for the same query using a MariaDB 10.1.16.
      • schema.sql => contains the schema used by the SaltOS's database.
      • used-size.pdf => contains the real used size to understand the problem.

      Attachments

        1. explain-10.1.14.log
          2 kB
        2. explain-10.1.16.log
          2 kB
        3. explain-10.1.16-v2.log
          2 kB
        4. query.sql
          3 kB
        5. saltos-10.1.14.log
          14 kB
        6. saltos-10.1.16.log
          14 kB
        7. saltos-10.1.16-v2.log
          14 kB
        8. schema.sql
          66 kB
        9. used-size.pdf
          68 kB

        Activity

          Ok, the change in behavior that I'm observing was caused by the fix for MDEV-10006.
          It also matches the behavior that sanz is reporting:

          • In 10.1.14, the join order of {a, e}

            was the only possible.

            • This was not correct ( {e,a}

              should be possible also, this is what MDEV-10006 is about)

            • However, this mistake forced the choice of a good query plan.
          • In 10.1.16, the fix for MDEV-10006 made the {e, a}

            order possible

            • And the optimizer picked it
            • However, when doing so, it did not take into account the ORDER BY LIMIT clause (this is a long-known limitation of both MariaDB and MySQL)
            • And as a result, query plan changed for the worse.

          (now, looking if there is anything we could do here)

          psergei Sergei Petrunia added a comment - Ok, the change in behavior that I'm observing was caused by the fix for MDEV-10006 . It also matches the behavior that sanz is reporting: In 10.1.14, the join order of {a, e} was the only possible. This was not correct ( {e,a} should be possible also, this is what MDEV-10006 is about) However, this mistake forced the choice of a good query plan. In 10.1.16, the fix for MDEV-10006 made the {e, a} order possible And the optimizer picked it However, when doing so, it did not take into account the ORDER BY LIMIT clause (this is a long-known limitation of both MariaDB and MySQL) And as a result, query plan changed for the worse. (now, looking if there is anything we could do here)
          psergei Sergei Petrunia added a comment - - edited

          Actually, the query plan reported for 10.1.16 should be able to avoid doing "Using filesort" with improved ORDER BY optimizations.

          sanz, would it be possible for you to

          • take 10.1.16
          • Add this into my1.cnf:

            optimizer_switch='orderby_uses_equalities=on'
            

          • start the server
          • run the EXPLAIN for the problematic query
          • run the query itself and check if it is fast or slow
          • post the results here?
          psergei Sergei Petrunia added a comment - - edited Actually, the query plan reported for 10.1.16 should be able to avoid doing "Using filesort" with improved ORDER BY optimizations. sanz , would it be possible for you to take 10.1.16 Add this into my1.cnf: optimizer_switch='orderby_uses_equalities=on' start the server run the EXPLAIN for the problematic query run the query itself and check if it is fast or slow post the results here?
          sanz Josep Sanz added a comment -

          Hi Sergei.

          With the optimizer_switch option works as expected as you can see in the attached files.

          I explain-10.1.16-v2.log saltos-10.1.16-v2.log

          Thanks by your help.

          sanz Josep Sanz added a comment - Hi Sergei. With the optimizer_switch option works as expected as you can see in the attached files. I explain-10.1.16-v2.log saltos-10.1.16-v2.log Thanks by your help.

          sanz, you're welcome. It should be safe to just run with 'orderby_uses_equalities=on' while on MariaDB 10.1. In the next MariaDB release (10.2), it will be ON by default.

          psergei Sergei Petrunia added a comment - sanz , you're welcome. It should be safe to just run with 'orderby_uses_equalities=on' while on MariaDB 10.1. In the next MariaDB release (10.2), it will be ON by default.

          Closing as there is a workaround (MDEV-8989).

          psergei Sergei Petrunia added a comment - Closing as there is a workaround ( MDEV-8989 ).

          People

            psergei Sergei Petrunia
            sanz Josep Sanz
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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