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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 10.0.24, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.5.9
    • Optimizer
    • Debian Wheezy and Jessie amd64
    • 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

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25020 SELECT if there is IN clause with binary UUID in binary form is extremely slow since MariaDB 10.5.9

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-9764 MariaDB does not limit memory used for range optimization

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10046 InnoDB Range Optimizer Regression

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21958 Query having many NOT-IN clauses running forever and causing available free memory to use completely

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24725 Assertion failure: prev_range_last_block_records < stats.block_size

          • Major - Major loss of function.
          • Stalled
          mentioned in

          Page Loading...

          Activity

            Transition Time In Source Status Execution Times
            Elena Stepanova made transition -
            Open Confirmed
            		13h 1
            Sergei Petrunia made transition -
            Confirmed In Progress
            		1666d 20h 1
            Sergei Petrunia made transition -
            In Progress In Review
            		27d 10h 52m 1
            Julien Fritsch made transition -
            In Review Stalled
            		38d 13h 49m 1
            Sergei Petrunia made transition -
            Stalled Closed
            		46d 3h 20m 1

            People

              psergei Sergei Petrunia
              jb-boin Jean Weisbuch
              Votes:
              0 Vote for this issue
              Watchers:
              10 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.