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

Query having many NOT-IN clauses running forever and causing available free memory to use completely

    XMLWordPrintable

Details

    Description

      One of the customer reported that running a huge query (25KB) having a NOT-IN clause with many values running forever, causing the server to use available free memory and invoking the oom-killer to kill the mysqld process. Same behavior when tried to get the `EXPLAIN PLAN`.

      Initially, checking the existing bugs, found following two:

      MDEV-9764 - MariaDB does not limit memory used for range optimization
      MDEV-9750 - extended_keys=on causing the memory exhaustion on queries having multiple IN/NOT IN using INNODB

      As per those two bugs, we have told customer to turn off the "extended_keys" and check, but it's not helping out.

      Further, locally on the MariaDB Server versions 10.2.21, 10.3.14 and 10.4.12(ES), I can reproduce the same behavior on an empty table. Here as well, neither setting "extended_keys=off" helped nor the "max_session_mem_used" system variable worked to avoid the exhaustion of available free memory.

      Repro steps, I will share it separately.

      Maybe, we need to understand how can we handle this case?

      Thank You.

      Attachments

        1. intervals-and.jpg
          99 kB
          Sergei Petrunia
        2. screenshot-1.png
          33 kB
          Sergei Petrunia

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              suresh.ramagiri@mariadb.com suresh ramagiri
              Votes:
              1 Vote for this issue
              Watchers:
              11 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.