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

Default optimizer setting changes for MariaDB 10.4

Details

    • 10.4.0-1

    Description

      The current default settings are:

      optimizer_use_condition_selectivity=1
      		 
      use_stat_tables=NEVER

      This means many optimizations are not enabled.

      During discussion on the optimizer call, figured that we should use defaults like so:

      optimizer_use_condition_selectivity=4
      		 
      use_stat_tables=PREFERABLY

      The task is:

      • change the defaults
      • This will cause some MTR test result differences
      • Go through the failing test and see:
        • some tests specifically require old settings. Set the variables for these tests.
        • some tests dont' update test results for these.

      cc igor, cvicentiu, sanja, varun_raiko, shagalla

      Attachments

        Issue Links

          duplicates

          Task - A task that needs to be done. MDEV-9999 Change the default value of optimizer_use_condition_selectivity

          • Major - Major loss of function.
          • Closed
          includes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16374 filtered shows 0 for materilization scan for a semi join, which makes optimizer always pick materialization scan over materialization lookup

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16695 Estimate for rows of derived tables is very high when we are using index_merge union

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16921 Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16995 ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with optimizer_use_condition_selectivity>=3

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17023 Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17032 Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17039 Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 and use_stat_tables= PREFERABLY

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17255 New optimizer defaults and ANALYZE TABLE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17297 stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17752 Plan changes from hash_index_merge to index_merge with new optimizer defaults

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17778 Alter table leads to a truncation warning with ANALYZE command

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17784 Analyze table returns warnings due to rounding error or overflow while collection of EITS

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16307 Incorrect results when using BNLH join instead of BNL join with views

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13628 ORed condition in pushed index condition is not removed from the WHERE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13905 condition in pushed index condition is not removed from the WHERE in BNL joins

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15321 different results when using value of optimizer_use_condition_selectivity=4 and =1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17734 AddressSanitizer: use-after-poison in create_key_parts_for_pseudo_indexes

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22537 optimizer_use_cond_selectivity > 1 can cause slow plans

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15306 Wrong/Unexpected result with the value optimizer_use_condition_selectivity set to 4

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15368 Unnecessary condition used in Index Condition Pushdown

          • Major - Major loss of function.
          • Stalled

          Task - A task that needs to be done. MDEV-17903 New optimizer defaults: change optimize_join_buffer_size to be ON

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-18355 Switching the results for plans when there was query plan change with the new optimizer defaults

          • Major - Major loss of function.
          • Stalled

          Task - A task that needs to be done. MDEV-18551 New defaults for eq_range_index_dive_limit

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18608 Defaults for 10.4: histogram size should be set

          • Major - Major loss of function.
          • Closed
          mentioned in

          Page Loading...

          Page Loading...

          Activity

            Ascending order - Click to sort in descending order
            View 5 older comments
            danblack Daniel Black added a comment -

            varun de7a3b23ba66e0946adde0906f1972e80b0e700a had the wrong MDEV ref.

            danblack Daniel Black added a comment - varun de7a3b23ba66e0946adde0906f1972e80b0e700a had the wrong MDEV ref.
            varun Varun Gupta (Inactive) added a comment -

            Well we are not going to change the value for join_cache_level. It will still remain to be 2. We are not having any improved performance gains here because there is no cost based approach for hash join and we unconditionally always prefer hash join.

            varun Varun Gupta (Inactive) added a comment - Well we are not going to change the value for join_cache_level. It will still remain to be 2. We are not having any improved performance gains here because there is no cost based approach for hash join and we unconditionally always prefer hash join.
            varun Varun Gupta (Inactive) added a comment -

            The branch where all the changes is 10.4-selectivity4

            varun Varun Gupta (Inactive) added a comment - The branch where all the changes is 10.4-selectivity4
            varun Varun Gupta (Inactive) added a comment -

            One issue we came across while changing the defaults that ANALYZE on a table would become quite slow , because it will also calculate the EITS at the same time.

            According to the docs:

            With engine-independent statistics:
            		 
            If @@use_stat_tables='never' and PERSISTENT FOR isn't used as part of the ALTER TABLE statement, then only storage engine statistics will be updated and not engine-independent statistics.
            		 
            For other values of @@use_stat_tables, both storage engine statistics and engine-independent statistics will be updated.

            varun Varun Gupta (Inactive) added a comment - One issue we came across while changing the defaults that ANALYZE on a table would become quite slow , because it will also calculate the EITS at the same time. According to the docs: With engine-independent statistics: If @@use_stat_tables='never' and PERSISTENT FOR isn't used as part of the ALTER TABLE statement, then only storage engine statistics will be updated and not engine-independent statistics. For other values of @@use_stat_tables, both storage engine statistics and engine-independent statistics will be updated.
            varun Varun Gupta (Inactive) added a comment -

            Pushed to 10.4

            varun Varun Gupta (Inactive) added a comment - Pushed to 10.4

            People

              varun Varun Gupta (Inactive)
              psergei Sergei Petrunia
              Votes:
              1 Vote for this issue
              Watchers:
              9 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.