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

in_predicate_conversion_threshold cannot be set in my.cnf

Details

    Description

      Currently testing 10.3.6 but looking at code this is still true in current 10.3.

      Any setting of in_predicate_conversion_threshold in my.cnf is overridden in init_common_variables() with this line:

        global_system_variables.in_subquery_conversion_threshold= IN_SUBQUERY_CONVERSION_THRESHOLD;

      Removing this line appears to fix the problem. A default is set in sys_vars.cc anyway I don't think this is needed.

      This affects ColumnStore because we need to set this high.

      As a side note, I couldn't see this variable documented in KB.

      Attachments

        Issue Links

          includes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20083 Predicate into subqueries is server KILLER, which can not even be disabled

          • Major - Major loss of function.
          • Closed
          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20105 Case for bringing in_subquery_conversion_threshold back in next possible release

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

          New Feature - A new feature of the product, which has yet to be developed. MCOL-1385 Merge MariaDB 10.3

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20482 MyISAM & Aria very slow when IN predicates containing more than 999 elements reference unindexed column.

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

          Activity

            Ascending order - Click to sort in descending order
            View 7 older comments
            igor Igor Babaev (Inactive) added a comment -

            Slawomir,
            In your comments you assume only one pattern of using IN predicates. For this pattern the conversion into IN subquery can not be beneficial especially when the hash join is turned off.
            At the same time you claim that " this feature is so broken, buggy and random it ". I need examples where it is:
            1. broken
            2. buggy
            3. random.
            It would be helpful.

            igor Igor Babaev (Inactive) added a comment - Slawomir, In your comments you assume only one pattern of using IN predicates. For this pattern the conversion into IN subquery can not be beneficial especially when the hash join is turned off. At the same time you claim that " this feature is so broken, buggy and random it ". I need examples where it is: 1. broken 2. buggy 3. random. It would be helpful.
            pslawek83 Slawomir Pryczek added a comment -

            Ok i oversimplified this looking from perspective of this pattern which i see a lot and for which we're using IN most of the time. It also happened that the second pattern we're using for optimization also was degraded, however that seems irrelevant because differences weren't significiant (<2x). Realized this was designed for different scenarios so shuldn't call for it being removed.

            For these 3 points mentioned i meant that even with highest join level enabled a very small differences in table structure or in query, could lead to totally different and unexpected execution times which are very counter intuitive (eg. when you duplicate same WHERE condition twice, query will get executed 40 times faster).

            Sent some analyze for these 2 patterns on email, hopefully there's some usable info

            pslawek83 Slawomir Pryczek added a comment - Ok i oversimplified this looking from perspective of this pattern which i see a lot and for which we're using IN most of the time. It also happened that the second pattern we're using for optimization also was degraded, however that seems irrelevant because differences weren't significiant (<2x). Realized this was designed for different scenarios so shuldn't call for it being removed. For these 3 points mentioned i meant that even with highest join level enabled a very small differences in table structure or in query, could lead to totally different and unexpected execution times which are very counter intuitive (eg. when you duplicate same WHERE condition twice, query will get executed 40 times faster). Sent some analyze for these 2 patterns on email, hopefully there's some usable info
            serg Sergei Golubchik added a comment -

            May be we should add an optimizer switch to disable this optimization? igor, what do you think?

            serg Sergei Golubchik added a comment - May be we should add an optimizer switch to disable this optimization? igor , what do you think?
            serg Sergei Golubchik added a comment -

            https://github.com/MariaDB/server/commit/eb0cf76f7dd

            serg Sergei Golubchik added a comment - https://github.com/MariaDB/server/commit/eb0cf76f7dd
            joce jocelyn fournier added a comment -

            Perhaps setting the switch to off until MDEV-20109 is fixed would be better?

            joce jocelyn fournier added a comment - Perhaps setting the switch to off until MDEV-20109 is fixed would be better?

            People

              serg Sergei Golubchik
              LinuxJedi Andrew Hutchings (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.