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
            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            Description The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            join_cache_level=2
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            join_cache_level=3 (or even 4)
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            		The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            join_cache_level=2
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            join_cache_level=4
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            psergei Sergei Petrunia made changes -
            Description The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            join_cache_level=2
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            join_cache_level=4
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            		The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            join_cache_level=2
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            join_cache_level=4
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            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.
            psergei Sergei Petrunia made changes -
            Description The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            join_cache_level=2
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            join_cache_level=4
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            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.             		The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            join_cache_level=2
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            join_cache_level=4
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            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]
            psergei Sergei Petrunia made changes -
            Assignee Varun Gupta [ varun ]
            varun Varun Gupta (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) made changes -
            Sprint 10.3.5-1 [ 229 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Blocker [ 1 ]
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            serg Sergei Golubchik made changes -
            alice Alice Sherepa made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.3 [ 22126 ]
            varun Varun Gupta (Inactive) made changes -
            Priority Blocker [ 1 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Sprint 10.3.5-1 [ 229 ]
            serg Sergei Golubchik made changes -
            Summary Default optimizer setting changes for MariaDB 10.3 Default optimizer setting changes for MariaDB 10.4
            varun Varun Gupta (Inactive) made changes -
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.4.0-1 [ 254 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked lower
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            Description The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            join_cache_level=2
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            join_cache_level=4
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            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]             		The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            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]
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            serg Sergei Golubchik made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Epic Link PT-76 [ 68557 ]
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            psergei Sergei Petrunia made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Rank Ranked higher
            alice Alice Sherepa made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels defaults
            varun Varun Gupta (Inactive) made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            psergei Sergei Petrunia made changes -
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            Fix Version/s 10.4.1 [ 23228 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Description The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            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]             		The current default settings are:

            {noformat}
            optimizer_use_condition_selectivity=1
            use_stat_tables=NEVER
            {noformat}

            This means many optimizations are not enabled.

            During discussion on the optimizer call, figured that we should use defaults like so:
            {noformat}
            optimizer_use_condition_selectivity=4
            use_stat_tables=PREFERABLY
            {noformat}

            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]
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            kevg Eugene Kosov (Inactive) made changes -
            Rank Ranked lower
            kevg Eugene Kosov (Inactive) made changes -
            Rank Ranked higher
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 85497 ] MariaDB v4 [ 133470 ]
            Richard Richard Stracke made changes -
            rob.schwyzer@mariadb.com Rob Schwyzer (Inactive) made changes -

            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.