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

    • Type: Bug
    • Status: In Progress (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2.21, 10.4.12
    • Fix Version/s: 10.2
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      CentOS/RHEL 7

      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

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            suresh.ramagiri@mariadb.com suresh ramagiri
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: