Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.0.24, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
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
Issue Links
- causes
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Stalled
-
- mentioned in
-
Page Loading...
Activity
A patch with more comments: http://lists.askmonty.org/pipermail/commits/2020-November/014355.html
Review input:
Fix the comment with the definition of the weight
Something like "verify_weight" which we could run after every operation in debug mode
- Cut of the level of the tree that has the biggest weight, independent of level!
- consider: if there is a keypart gap, and after the gap there is a heavy tree, remove that.
Make the MAX_WEIGHT user-visibleMove this part of code into and_all_keys():+ Do not combine the trees if their total weight is likely to exceed the+ MAX_WEIGHT.
Print the weight into the optimizer trace (perhaps also print if the cutting has happened)
Patch addressing almost all of the input:
https://github.com/MariaDB/server/commit/ef5fe786a2f6608a67a34b1bfe91603d9a9f04f7
Branch on github https://github.com/MariaDB/server/tree/bb-10.4-mdev9750-v2
.. well, the answer is that yes, it is possible. I don't think doing pruning in a shared sub-graph causes harm , but it makes the semantics of the operation complex.
igor Please find the variant of the patch that only does pruning for complete graphs:
http://lists.askmonty.org/pipermail/commits/2020-November/014351.html