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 added a comment -

            join_cache_level:

            1 – flat (Block Nested Loop) BNL
            		 
            2 – incremental BNL  -- *current default*
            		 
            3 – flat Block Nested Loop Hash (BNLH)
            		 
            4 – incremental BNLH  -- *new default*
            		 
            5 – flat Batch Key Access (BKA)
            		 
            6 – incremental BKA
            		 
            7 – flat Batch Key Access Hash (BKAH)
            		 
            8 – incremental BKAH

            psergei Sergei Petrunia added a comment - join_cache_level: 1 – flat (Block Nested Loop) BNL 2 – incremental BNL -- *current default* 3 – flat Block Nested Loop Hash (BNLH) 4 – incremental BNLH -- *new default* 5 – flat Batch Key Access (BKA) 6 – incremental BKA 7 – flat Batch Key Access Hash (BKAH) 8 – incremental BKAH
            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 added a comment -

            optimizer_use_condition_selectivity:

            1 Use selectivity of predicates as in MariaDB 5.5. -- *current default *
            		 
            2 Use selectivity of all range predicates supported by indexes.
            		 
            3 Use selectivity of all range predicates estimated without histogram.
            		 
            4 Use selectivity of all range predicates estimated with histogram. -- *new default *
            		 
            5 Additionally use selectivity of certain non-range predicates calculated on record sample.

            Note that histograms are not collected automatically, so the value of 4 will have the same effect as 3 for those who never collected histograms.

            psergei Sergei Petrunia added a comment - optimizer_use_condition_selectivity: 1 Use selectivity of predicates as in MariaDB 5.5. -- *current default * 2 Use selectivity of all range predicates supported by indexes. 3 Use selectivity of all range predicates estimated without histogram. 4 Use selectivity of all range predicates estimated with histogram. -- *new default * 5 Additionally use selectivity of certain non-range predicates calculated on record sample. Note that histograms are not collected automatically, so the value of 4 will have the same effect as 3 for those who never collected histograms.
            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 ]
            psergei Sergei Petrunia added a comment -

            TODO: As a second step, should histogram_size default be changed from 0 to 255 ?

            psergei Sergei Petrunia added a comment - TODO: As a second step, should histogram_size default be changed from 0 to 255 ?
            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 ]
            danblack Daniel Black added a comment -

            If optimizer_use_condition_selectivity=4 then it would seem a little odd to keep histogram_size at 0. 255 seems like a good default to cover a broad range of values in a column with little cost if this isn't the case.

            danblack Daniel Black added a comment - If optimizer_use_condition_selectivity =4 then it would seem a little odd to keep histogram_size at 0. 255 seems like a good default to cover a broad range of values in a column with little cost if this isn't the case.
            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) added a comment -

            Milestone1: Increase the value for join_cache_level from 2 to 4

            varun Varun Gupta (Inactive) added a comment - Milestone1: Increase the value for join_cache_level from 2 to 4
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            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) 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) 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) 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) 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) 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) added a comment -

            Pushed to 10.4

            varun Varun Gupta (Inactive) added a comment - Pushed to 10.4
            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.