[MDEV-9531] GROUP_CONCAT with ORDER BY inside takes a lot of memory while it's executed - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.2.24, 10.1.39, 10.3.15, 10.4.5
Component/s: OTHER
Labels:
None

Sprint:
10.1.14, 10.1.18, 10.1.19

Description

A query like

SELECT GROUP_CONCAT( seq, seq, seq, seq, seq, seq, seq, seq ORDER BY 2,1,3,4,6,5,8,7 ) AS cfield1 FROM seq_1_to_50000000

easily uses up to 4-5 Gb memory while it's running.

<montywi> everything has limits
<montywi> even for filesort; For bigger things it should always hit disk, never memory
...
<montywi> the problem is that when doing a sort, we create a sorted tree in memory
<montywi> however there is no memory check for this
<montywi> so this can be any size
<montywi> Normally it would make sense to limit this to group_concat_max_length, but I fear that would break things, as we sort records there, which can have a notable overhead
<montywi> So I have limit it to max_heap_table_size

Attachments

Issue Links

is duplicated by

MDEV-30282 Uncontrollable memory consumption upon GROUP_CONCAT with ORDER BY

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2016-02-08 16:19

Elena Stepanova made changes - 2016-02-08 17:37

Field	Original Value	New Value
Priority	Major [ 3 ]	Minor [ 4 ]

Sergei Golubchik made changes - 2016-02-08 17:43

Assignee

Elena Stepanova [ elenst ]

Oleksandr Byelkin [ sanja ]

Sergei Golubchik made changes - 2016-02-08 17:43

Priority

Minor [ 4 ]

Major [ 3 ]

Sergei Golubchik added a comment - 2016-02-08 17:47 - edited

There are two possible approaches:

introduce some some limit for a max tree size. when the tree grows that large, a special "cleanup" procedure is executed, that keeps first group_concat_max_len bytes of values (or first group_concat_max_len values, if that's much simpler) and removes all larger values. then continue inserting into the tree
use priority queue, not a tree. never grows large and doesn't need cleanups. but higher up-front memory cost if group_concat_max_len limit is rarely reached

Sergei Golubchik added a comment - 2016-02-08 17:47 - edited There are two possible approaches: introduce some some limit for a max tree size. when the tree grows that large, a special "cleanup" procedure is executed, that keeps first group_concat_max_len bytes of values (or first group_concat_max_len values, if that's much simpler) and removes all larger values. then continue inserting into the tree use priority queue, not a tree. never grows large and doesn't need cleanups. but higher up-front memory cost if group_concat_max_len limit is rarely reached

Rasmus Johansson (Inactive) made changes - 2016-04-27 07:04

Sprint

10.1.14 [ 51 ]

Rasmus Johansson (Inactive) made changes - 2016-04-27 07:04

Rank

Ranked lower

Oleksandr Byelkin made changes - 2016-04-27 13:56

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2016-05-04 17:16

revision-id: eb9fab76fb4322a7821e6502e6303ed381624429 (mariadb-10.1.13-18-geb9fab7)
parent(s): 732adec0a4c75d99389230feeb0deca0ad668de7
committer: Oleksandr Byelkin
timestamp: 2016-05-04 19:15:31 +0200
message:

~~MDEV-9531~~: GROUP_CONCAT with ORDER BY inside takes a lot of memory while it's executed

Limitation added to Red-Black tree.

—

Oleksandr Byelkin added a comment - 2016-05-04 17:16 revision-id: eb9fab76fb4322a7821e6502e6303ed381624429 (mariadb-10.1.13-18-geb9fab7) parent(s): 732adec0a4c75d99389230feeb0deca0ad668de7 committer: Oleksandr Byelkin timestamp: 2016-05-04 19:15:31 +0200 message: MDEV-9531 : GROUP_CONCAT with ORDER BY inside takes a lot of memory while it's executed Limitation added to Red-Black tree. —

Oleksandr Byelkin made changes - 2016-05-04 17:16

Assignee	Oleksandr Byelkin [ sanja ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2016-06-27 11:07

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2016-06-28 06:19

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2016-06-28 07:22

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin added a comment - 2016-06-28 09:01

I remove variable and made automatic minimum print output calculation:

revision-id: 13b5098fcaa888173472d255e29aff22bcc5baae (mariadb-10.1.13-18-g13b5098)
parent(s): 732adec0a4c75d99389230feeb0deca0ad668de7
committer: Oleksandr Byelkin
timestamp: 2016-06-28 10:59:59 +0200
message:

~~MDEV-9531~~: GROUP_CONCAT with ORDER BY inside takes a lot of memory while it's executed

Limitation added to Red-Black tree.

—

Oleksandr Byelkin added a comment - 2016-06-28 09:01 I remove variable and made automatic minimum print output calculation: revision-id: 13b5098fcaa888173472d255e29aff22bcc5baae (mariadb-10.1.13-18-g13b5098) parent(s): 732adec0a4c75d99389230feeb0deca0ad668de7 committer: Oleksandr Byelkin timestamp: 2016-06-28 10:59:59 +0200 message: MDEV-9531 : GROUP_CONCAT with ORDER BY inside takes a lot of memory while it's executed Limitation added to Red-Black tree. —

Oleksandr Byelkin made changes - 2016-06-28 09:01

Assignee	Oleksandr Byelkin [ sanja ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2016-07-09 08:26

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2016-07-09 20:09

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2016-07-09 20:09

Status

Stalled [ 10000 ]

In Review [ 10002 ]

Sergei Golubchik made changes - 2016-09-21 11:59

Sprint

10.1.14 [ 51 ]

10.1.14, 10.1.18 [ 51, 98 ]

Sergei Golubchik made changes - 2016-09-22 15:11

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2016-09-22 16:10

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2016-09-26 14:23

revision-id: f5a5ac49b5ff7b04aa816d33ca44d9fe88a94498 (mariadb-10.1.17-23-gf5a5ac4)
parent(s): d30809a3cde823ad696304a941afe5a562bfa3ed
committer: Oleksandr Byelkin
timestamp: 2016-09-26 16:21:18 +0200
message:

~~MDEV-9531~~: GROUP_CONCAT with ORDER BY inside takes a lot of memory while it's executed

Limitation added to Red-Black tree.

—

Oleksandr Byelkin added a comment - 2016-09-26 14:23 revision-id: f5a5ac49b5ff7b04aa816d33ca44d9fe88a94498 (mariadb-10.1.17-23-gf5a5ac4) parent(s): d30809a3cde823ad696304a941afe5a562bfa3ed committer: Oleksandr Byelkin timestamp: 2016-09-26 16:21:18 +0200 message: MDEV-9531 : GROUP_CONCAT with ORDER BY inside takes a lot of memory while it's executed Limitation added to Red-Black tree. —

Oleksandr Byelkin made changes - 2016-09-26 14:23

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2016-09-27 13:12

Assignee

Sergei Golubchik [ serg ]

Oleksandr Byelkin [ sanja ]

Rasmus Johansson (Inactive) made changes - 2016-10-26 19:55

Sprint

10.1.14, 10.1.18 [ 51, 98 ]

10.1.14, 10.1.18, 10.1.19 [ 51, 98, 109 ]

Rasmus Johansson (Inactive) made changes - 2016-10-26 19:55

Rank

Ranked higher

Sergei Golubchik made changes - 2017-04-18 15:05

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2017-04-18 15:05

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2017-05-22 12:15

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2018-07-17 13:38

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik added a comment - 2019-04-20 17:36

please, review commit 73b12b0cebc in bb-10.1-serg

Sergei Golubchik added a comment - 2019-04-20 17:36 please, review commit 73b12b0cebc in bb-10.1-serg

Sergei Golubchik made changes - 2019-04-20 17:36

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2019-04-24 05:58

OK to push.

Oleksandr Byelkin added a comment - 2019-04-24 05:58 OK to push.

Oleksandr Byelkin made changes - 2019-04-24 05:58

Assignee	Oleksandr Byelkin [ sanja ]	Sergei Golubchik [ serg ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2019-04-25 07:28

Component/s		OTHER [ 10125 ]
Fix Version/s		10.1.39 [ 23305 ]
Fix Version/s		10.2.24 [ 23308 ]
Fix Version/s		10.3.15 [ 23309 ]
Fix Version/s		10.4.5 [ 23311 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 73902 ]

MariaDB v4 [ 150087 ]

Elena Stepanova made changes - 2023-06-17 19:02

Link

This issue is duplicated by ~~MDEV-30282~~ [ ~~MDEV-30282~~ ]

People

Assignee:: Sergei Golubchik

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-02-08 16:19

Updated:: 2023-06-17 19:02

Resolved:: 2019-04-25 07:28

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration