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

Case for bringing in_subquery_conversion_threshold back in next possible release

    XMLWordPrintable

Details

    Description

      Changing this ticket to be more correct taking into account some new info.

      "Conversion of big predicated into subqueries" ( https://mariadb.com/kb/en/library/conversion-of-big-in-predicates-into-subqueries/ ) is making many queries using *specific pattern* especially on default configuration to degrade in performance over 100 fold in specific cases. Most of these can't be fixed by enabling hash joins using join_cache_limit = 8

      While:

      • These issues are catastrophic even on simplest queries and tiny tables
      • Some issues can be partially fixed by reconfiguring the server or completely rewriting queries, leading to "bearable" performance degradation (<10 times slower)
      • This "optimization" is very specific, only for IN sets witch >1000 items
      • Degradation feels totally "random", simple data conversion between string and int can change 50x degradation into over 100x.
      • All tests are still on very small tables, with very small IN (..) sets to keep this ticket as short as possible
      • CPU overload caused by this on production machines can be huge

      So adding a switch to turn this off, so it can be disabled for when it's causing issues seem necessary

      https://jira.mariadb.org/browse/MDEV-12176
      https://jira.mariadb.org/browse/MDEV-16871
      https://jira.mariadb.org/browse/MDEV-17795
      https://jira.mariadb.org/browse/MDEV-20083

      Some of many cases will follow as separated comments as this is too large to add at once.

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              pslawek83 Slawomir Pryczek
              Votes:
              1 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.