Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5187

OOM happening when querying large datasets and using distinct

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Won't Fix
    • 22.08.1, 6.4.6
    • N/A
    • N/A
    • ububts 20.04 LTS

    Description

      I set TotalUmMemory to 25%, but ExeMgr use far beyond usage memory.
      Finally ExeMgr was killed by oom-killer.
      I tried two sql on large datasets.

      1. select distinct columnA, columnB, columnC from table.
      2. select columnA, columnB, columnC from table group by columnA, columnB, columnC.

      1. is OOM,
      but 2. is MCS-2003: Aggregation/Distinct memory limit is exceeded.

      I think 1. and 2. are similar query.
      I think that 1. should return same message (MCS-2003: Aggregation/Distinct memory limit is exceeded.)

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MCOL-5431 Remove dataset sorting from WFS.

          • Major - Major loss of function.
          • Open
          is part of

          New Feature - A new feature of the product, which has yet to be developed. MCOL-5250 Disk-based DISTINCT

          • Major - Major loss of function.
          • Stalled
          relates to

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MCOL-4626 Columnstore cluster becomes non operational when running out of memory on a query

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

          Bug - A problem which impairs or prevents the functions of the product. MCOL-5430 Window Functions in projection with GROUP BY delivers wrong results

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            Atsushi Kugimiya created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Component/s Storage Engine - ColumnStore [ 17600 ]
            Key MDEV-29285 MCOL-5187
            Affects Version/s 10.6.5 [ 26034 ]
            Workflow MariaDB v4 [ 169026 ] MariaDB ColumnStore workflow [ 169087 ]
            Project MariaDB Server [ 10000 ] MariaDB ColumnStore [ 10902 ]
            drrtuy Roman added a comment - - edited

            Thx for your suggestion. We will look into implementing it.
            JFYI SELECT DISTINCT is not a simple rename for GROUP BY and it is completely different comparing to GROUP BY processing-wise. I suggest you to prefer GROUP BY than DISTINCT.
            Did you try to enable disk-based aggregation to manage with the error message?

            drrtuy Roman added a comment - - edited Thx for your suggestion. We will look into implementing it. JFYI SELECT DISTINCT is not a simple rename for GROUP BY and it is completely different comparing to GROUP BY processing-wise. I suggest you to prefer GROUP BY than DISTINCT. Did you try to enable disk-based aggregation to manage with the error message?
            drrtuy Roman made changes -
            Labels SQL performance query SQL query
            Atsushi Kugimiya added a comment -

            Thanks for your answer.

            Yes, I did.
            I already done disk-based aggregation. When I use disk-based aggregation, Both sqls can be processed.

            DISTINCT problem is using memory without getting an error.
            I set 25% to TotalUmMemory, but ExeMgr use far beyond usage memory.
            DISTINCT implementation ignores TotalUmMemory.

            I think that DISTINCT implementation should check TotalUmMemory.
            What do you think?

            Atsushi Kugimiya added a comment - Thanks for your answer. Yes, I did. I already done disk-based aggregation. When I use disk-based aggregation, Both sqls can be processed. DISTINCT problem is using memory without getting an error. I set 25% to TotalUmMemory, but ExeMgr use far beyond usage memory. DISTINCT implementation ignores TotalUmMemory. I think that DISTINCT implementation should check TotalUmMemory. What do you think?
            drrtuy Roman added a comment -

            It surely must obey the limitations of TotalUmMemory. I will look into the difference b/w distinct and group by in terms of memory consumption limitation.

            drrtuy Roman added a comment - It surely must obey the limitations of TotalUmMemory. I will look into the difference b/w distinct and group by in terms of memory consumption limitation.
            drrtuy Roman made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            drrtuy Roman made changes -
            Assignee Roman [ drrtuy ]
            drrtuy Roman made changes -
            Assignee Roman [ drrtuy ] Alexey Antipovsky [ JIRAUSER47594 ]
            drrtuy Roman made changes -
            drrtuy Roman made changes -
            Affects Version/s 6.4.6 [ 28452 ]
            drrtuy Roman made changes -
            Affects Version/s 22.08.1 [ 28206 ]
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            allen.herrera Allen Herrera made changes -
            Labels SQL query SQL query triage
            allen.herrera Allen Herrera made changes -
            Priority Major [ 3 ] Trivial [ 5 ]
            drrtuy Roman made changes -
            Assignee Alexey Antipovsky [ JIRAUSER47594 ] Roman [ drrtuy ]
            drrtuy Roman made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            drrtuy Roman made changes -
            Assignee Roman [ drrtuy ] Alexey Antipovsky [ JIRAUSER47594 ]
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked lower
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked lower
            toddstoffel Todd Stoffel (Inactive) made changes -
            Fix Version/s 23.02.1 [ 28210 ]
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            alexey.vorovich alexey vorovich (Inactive) made changes -
            Fix Version/s 23.02 [ 28209 ]
            Fix Version/s 23.02.1 [ 28210 ]
            ccalender Chris Calender (Inactive) made changes -
            Priority Trivial [ 5 ] Critical [ 2 ]
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            ccalender Chris Calender (Inactive) made changes -
            alexey.vorovich alexey vorovich (Inactive) made changes -
            Assignee Alexey Antipovsky [ JIRAUSER47594 ] Roman [ drrtuy ]
            drrtuy Roman made changes -
            drrtuy Roman made changes -
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked lower
            toddstoffel Todd Stoffel (Inactive) made changes -
            Fix Version/s 23.08 [ 28540 ]
            Fix Version/s 23.02 [ 28209 ]
            JIraAutomate JiraAutomate added a comment -

            Automated message:
            ----------------------------
            Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

            JIraAutomate JiraAutomate added a comment - Automated message: ---------------------------- Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
            julien.fritsch Julien Fritsch made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            AirFocus AirFocus made changes -
            Labels SQL query triage SQL query
            julien.fritsch Julien Fritsch made changes -
            Component/s N/A [ 15201 ]
            Fix Version/s N/A [ 24311 ]
            Fix Version/s 23.10 [ 28540 ]
            Resolution Won't Fix [ 2 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 202244

            People

              drrtuy Roman
              Atsushi Kugimiya
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.