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

              Assignee:
              igor Igor Babaev
              Reporter:
              pslawek83 Slawomir Pryczek
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: