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

Quick memory exhaustion with 'extended_keys=on' on queries having multiple 'IN'/'NOT IN' using InnoDB

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 10.0.24, 10.2, 10.1, 10.0, 5.5
    • Fix Version/s: 10.5.9
    • Component/s: Optimizer
    • Labels:
    • Environment:
      Debian Wheezy and Jessie amd64
    • Sprint:
      5.5.50

      Description

      Running the attached query (either with EXPLAIN or by simply doing the SELECT) on the following empty table will exhaust system memory in seconds(uses more than 10G of memory in less than 2 minutes), killing the query even less than a second after having executed it can take up to 30 seconds to finish (the query will be in "Killed" command and "statistics" state on the Processlist) while still keep eating up more memory.

      Tested on both Wheezy and Jessie packaged versions of 10.0.24 with the same result, it didnt seemed to happen on 5.5.48 as i hit the bug only since i upgraded.

      The bug doesnt happens when :

      • Switching the engine to Aria or MyISAM
      • When the optimizer switch "extended_keys" = "off" (which was the default on 5.5 but not since 10.0)
      • When both columns are "tinyint"
      • With only two values on each IN()/NOT IN()

      Using "smallint" for both columns result in the query to be faster to kill but still not ending while eating memory.

      –

      CREATE TABLE `memoryexhaust` (
        `id` int NOT NULL,
        `secondary_id` int NOT NULL,
        PRIMARY KEY (`id`),
          KEY `secondary_id` (`secondary_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        Attachments

        1. memoryexhaust.sql
          49 kB
        2. memoryexhaust-order2.sql
          49 kB
        3. pic1.png
          pic1.png
          28 kB
        4. pic2.png
          pic2.png
          43 kB
        5. pic3.png
          pic3.png
          68 kB

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              jb-boin Jean Weisbuch
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration