Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Query produces error: HEAPalloc: Insufficient space for HEAP of 1168033427456 bytes. #6134

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2016-12-01 13:30:30 +0100
From: Gatis Ozolins <<g.ozolins>>
To: GDK devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: ajdamico, @hannesmuehleisen, @PedroTadim, winney.deng

Last updated: 2019-11-28 10:00:06 +0100

Comment 24740

Date: 2016-12-01 13:30:30 +0100
From: Gatis Ozolins <<g.ozolins>>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:50.0) Gecko/20100101 Firefox/50.0
Build Identifier:

Query results in: HEAPalloc: Insufficient space for HEAP of 1168033427456 bytes.

Reproducible: Always

Steps to Reproduce:

table_data.tar.gz

CREATE TABLE all_dates (d_date date, month_code varchar(7));
CREATE TABLE book (code varchar(15));
CREATE TABLE category (id bigint, category_code varchar(30), subcategory_code varchar(30));
CREATE TABLE fact (s1_id varchar(25), s2_id varchar(25), s3_id varchar(25), s4_id varchar(25), book_code varchar(15), category_id bigint, location_id bigint, d_period date);
CREATE TABLE location (id bigint, code varchar(30));
CREATE TABLE segment1 (id varchar(150), code varchar(150));
CREATE TABLE segment2 (id varchar(150), code varchar(150));
CREATE TABLE segment3 (id varchar(150), code varchar(150));
CREATE TABLE segment4 (id varchar(150), code varchar(150));

COPY INTO category FROM 'category.csv' DELIMITERS '|' , '\n', '"';
COPY INTO all_dates FROM 'all_dates.csv' DELIMITERS '|' , '\n', '"';
COPY INTO book FROM 'book.csv' DELIMITERS '|' , '\n', '"';
COPY INTO fact FROM 'fact.csv' DELIMITERS '|' , '\n', '"';
COPY INTO location FROM 'location.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment1 FROM 'segment1.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment2 FROM 'segment2.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment3 FROM 'segment3.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment4 FROM 'segment4.csv' DELIMITERS '|' , '\n', '"';

SELECT loc.code
,cat.subcategory_code
,cat.category_code
,s1.code
,s2.code
,s3.code
,s4.code
,d.d_date
,d.month_code
FROM category cat
,book b
,location loc
,all_dates d
,segment4 s4
,segment3 s3
,segment2 s2
,segment1 s1
,fact f
WHERE b.code = f.book_code
AND d.d_date = f.d_period
AND s4.id = f.s4_id
AND s3.id = f.s3_id
AND s2.id = f.s2_id
AND s1.id = f.s1_id
AND loc.id = f.location_id
AND b.code = 'G2FIN'
AND cat.id = f.category_id
AND f.book_code = 'G2FIN'
AND ( cat.subcategory_code = 200 OR cat.subcategory_code = 301 OR cat.subcategory_code >= 221 )
AND ( cat.subcategory_code = 200 OR cat.subcategory_code = 301 OR cat.subcategory_code <= 290 )
AND ( s1.code = '03' OR cat.subcategory_code = 25 )
AND d.month_code BETWEEN '2016.01' AND '2016.10';

Actual Results:

GDK reported error.
GDKextendf: could not extend file.
!OS: No space left on device.
HEAPalloc: Insufficient space for HEAP of 1168033427456 bytes.

Expected Results:

Query returns results

It seems there is something wrong with "OR" parsing/processing.
As soon as I remove "( s1.code = '03' OR cat.subcategory_code = 25 )", query momentary returns results.

Also depending on how many tables/columns are in SQL, sometimes it just takes 100G of hdd space/virtual memory and never returns result.

Comment 24741

Date: 2016-12-01 13:31:10 +0100
From: Gatis Ozolins <<g.ozolins>>

Created attachment 520
Data to reproduce this issue

Attached file: table_data.tar.gz (application/gzip, 99993 bytes)
Description: Data to reproduce this issue

Comment 24842

Date: 2016-12-21 16:03:54 +0100
From: Gatis Ozolins <<g.ozolins>>

Tested with 11.25.3: still produces same error

Comment 24875

Date: 2017-01-10 15:26:59 +0100
From: MonetDB Mercurial Repository <>

Changeset d21bef4f8fdd made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=d21bef4f8fdd

Changeset description:

Added test for Bug #6134

Comment 24876

Date: 2017-01-10 15:36:39 +0100
From: MonetDB Mercurial Repository <>

Changeset f0eaa26022fe made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=f0eaa26022fe

Changeset description:

new files, Bug #6134

Comment 24877

Date: 2017-01-10 15:41:49 +0100
From: @hannesmuehleisen

From looking at plan, it seems like the joins deteriorate into cross products when adding that single condition.

Comment 24878

Date: 2017-01-10 15:55:08 +0100
From: @hannesmuehleisen

This also happens in default

Comment 25681

Date: 2017-10-06 18:05:10 +0200
From: Gatis Ozolins <<g.ozolins>>

Still hapens only latest version.
I found one workaround: if I change order of tables listed in FROM section, then query executes successfully in less than 1 second.

However we can't control order of tables listed as SQL generated by BI.

Comment 27233

Date: 2019-08-19 09:19:27 +0200
From: winney.deng

We see the same error with SQL select query in version: v11.33.3, v11.31.13 and current development version.

Test environment:
Memory: 4G
Swap: 1.8G
Free disk: 31G

Steps to reproduce:

  1. Load data into monetdb:
    We loaded about 760k tuples into tableA and 2846k tuples into tableB from csv files by sql COPY command.

  2. Do below SQL select command:
    select fdd.*
    from "tableA" fdd,
    "tableB" rp
    where fdd."me" = rp."me" and
    ( fdd."date_id" = '2019-06-26' or fdd."date_id" = '2019-06-27' or
    fdd."date_id" = '2019-06-28' or fdd."date_id" = '2019-06-27' or
    fdd."date_id" = '2019-06-28' or fdd."date_id" = '2019-06-27');

After doing the above select query, we found

  1. Monetdb data file increase very fast (before query it is 3.7G, after query 34G)
    When monetdb crashed, killed and restarted, the disk space was freed.

  2. Swap was exhausted by monetdb

  3. Below errors were seen in log (v11.33.13, dev version) or screen(v11.33.3):
    GDK reported error: GDKextendf: could not extend file
    OS: No space left on device
    HEAPalloc: Insufficient space for HEAP of 9093120 bytes.

    Below are logs from the current dev version (Aug. 2019)

    2019-08-19 06:26:15 ERR db[214]: DFLOWworker5:!OS: No space left on device
    2019-08-19 06:26:15 ERR db[214]: DFLOWworker5:!ERROR: HEAPalloc: Insufficient space for HEAP of 587759616 bytes.
    2019-08-19 06:26:15 ERR db[214]: DFLOWworker5:!ERROR:MALException:mat.pack:HY001!Could not allocate space
    2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!ERROR: GDKextendf: could not extend file
    2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!OS: No space left on device
    2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!ERROR: HEAPalloc: Insufficient space for HEAP of 587759616 bytes.
    2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!ERROR:MALException:mat.pack:HY001!Could not allocate space
    2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!ERROR: GDKextendf: could not extend file
    2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!OS: No space left on device
    2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!ERROR: HEAPalloc: Insufficient space for HEAP of 587792384 bytes.
    2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!ERROR:MALException:mat.pack:HY001!Could not allocate space

Comment 27254

Date: 2019-08-23 12:10:16 +0200
From: @PedroTadim

In the main development branch of MonetDB we optimized IN statements as well OR clauses inside WHERE statements to perform a single semi-join instead of multiple theta-select statements. The performance of these queries should improve in the next feature release of MonetDB.

Comment 27411

Date: 2019-11-15 13:47:05 +0100
From: MonetDB Mercurial Repository <>

Changeset 3cf4189df085 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=3cf4189df085

Changeset description:

Fixes for bug #6134, ie run rel_push_select_down optimizer after rel_join_order, so the later gets more optimizing options to push down joins.

Also some identitation fixes and limit the depth of rel_find_joins to 20 relations bellow in the relation tree.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant