[MDEV-21958] Query having many NOT-IN clauses running forever and causing available free memory to use completely - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.21, 10.4.12
Fix Version/s: 10.2.37, 10.3.28, 10.4.18, 10.5.10, 10.6.0
Component/s: Optimizer
Labels:
- range-optimizer
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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

intervals-and.jpg
99 kB
2020-10-28 21:27
screenshot-1.png
33 kB
2020-10-29 10:57

Issue Links

causes

MDEV-24444 ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN query

Closed

includes

MDEV-24711 Make "unique_key NOT IN (...)" conditions non-sargable

Closed

relates to

MDEV-26856 Queries with many NOT IN clauses or ORs use a lot of memory

Open

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

Closed

MDEV-23634 Select query hanged the server and leads to OOM in MariaDB 10.4.14

Closed

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(25 mentioned in)

Activity

Ascending order - Click to sort in descending order

View 10 older comments

Sergei Petrunia added a comment - 2020-12-15 11:44

Pushed the "conservative" fix.

Sergei Petrunia added a comment - 2020-12-15 11:44 Pushed the "conservative" fix.

Sergei Petrunia added a comment - 2021-01-27 17:31

ccalender "conservative" means this fix:
https://jira.mariadb.org/browse/MDEV-21958?focusedCommentId=171309&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-171309
It implements the "(Very basic) Make NOT-IN non-sargable" suggestion. It solves the issue when the column col in the "col NOT IN (...)" is the primary (or the unique secondary) key. This was the case in the customer's complaint.

Sergei Petrunia added a comment - 2021-01-27 17:31 ccalender "conservative" means this fix: https://jira.mariadb.org/browse/MDEV-21958?focusedCommentId=171309&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-171309 It implements the "(Very basic) Make NOT-IN non-sargable" suggestion. It solves the issue when the column col in the "col NOT IN (...)" is the primary (or the unique secondary) key. This was the case in the customer's complaint.

Sergei Petrunia added a comment - 2021-01-27 17:38

A question from yesterday's optimizer call: Will the fix for ~~MDEV-9750~~ help with this?
The answer is: "not much", unless one sets optimizer_max_sel_arg_weight really low.

The problem in this MDEV is N^2 memory usage. Let's look the example data from here:

https://jira.mariadb.org/browse/MDEV-21958?focusedCommentId=170462&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-170462

n_ranges SEL_ARGS SEL_ARGs per range
11 11 1.00
21 43 2.05
51 199 3.90
101 659 6.52
201 2329 11.59
401 8669 21.62
601 19009 31.63
801 33349 41.63
1001 51689 51.64
2001 203389 101.64
4001 806789 201.65

here n_ranges=4000 , SEL_ARGs=800K.

The default max_sel_arg_weight is 32K. Before we construct an OR-list of 32K elements (and hit the limit),we may allocate up to

32K ^ 2 = 1024 * KK = 1024M= 1G

SEL_ARG objects. Each is ~100 bytes, so we'll use up to ~100G memory.

Sergei Petrunia added a comment - 2021-01-27 17:38 A question from yesterday's optimizer call: Will the fix for MDEV-9750 help with this? The answer is: "not much", unless one sets optimizer_max_sel_arg_weight really low . The problem in this MDEV is N^2 memory usage. Let's look the example data from here: https://jira.mariadb.org/browse/MDEV-21958?focusedCommentId=170462&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-170462 n_ranges SEL_ARGS SEL_ARGs per range 11 11 1.00 21 43 2.05 51 199 3.90 101 659 6.52 201 2329 11.59 401 8669 21.62 601 19009 31.63 801 33349 41.63 1001 51689 51.64 2001 203389 101.64 4001 806789 201.65 here n_ranges=4000 , SEL_ARGs=800K. The default max_sel_arg_weight is 32K. Before we construct an OR-list of 32K elements (and hit the limit),we may allocate up to 32K ^ 2 = 1024 * KK = 1024M= 1G SEL_ARG objects. Each is ~100 bytes, so we'll use up to ~100G memory.

Sergei Petrunia added a comment - 2021-01-27 18:03

Ok, the "conservative" fix that was pushed now has its own MDEV: ~~MDEV-24711~~

Sergei Petrunia added a comment - 2021-01-27 18:03 Ok, the "conservative" fix that was pushed now has its own MDEV: MDEV-24711

Sergei Petrunia added a comment - 2021-10-19 09:25

Closing this one.
Followup task: MDEV-26856.

Sergei Petrunia added a comment - 2021-10-19 09:25 Closing this one. Followup task: MDEV-26856 .

People

Assignee:: Sergei Petrunia

Reporter:: suresh ramagiri

Votes:: 1 Vote for this issue

Watchers:: 11 Start watching this issue

Dates

Created:: 2020-03-17 07:17

Updated:: 2024-09-06 10:00

Resolved:: 2021-10-19 09:25

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.