Visitar URL original
OOM when reading Parquet file · Issue #3969 · duckdb/duckdb · GitHub
Skip to content

OOM when reading Parquet file #3969

Closed
Closed
OOM when reading Parquet file#3969

Description

@alexey-milovidov
alexey-milovidov
opened on Jun 25, 2022

What happens?

It is using all available memory and is terminated by OOM.

To Reproduce

Allocate a machine with 32 GB RAM, like c6a.4xlarge on AWS, with Ubuntu 22.04.
ssh into that machine.
Run the following commands:

sudo apt-get update
sudo apt-get install python3-pip
pip install duckdb
wget 'https://datasets.clickhouse.com/hits_compatible/hits.parquet'

Create the following run.py file:

#!/usr/bin/env python3

import duckdb
import timeit

con = duckdb.connect(database='my-db.duckdb', read_only=False)

print("Will load the data")

start = timeit.timeit()
con.execute("CREATE TABLE hits AS SELECT * FROM parquet_scan('hits.parquet')")
end = timeit.timeit()
print(end - start)

Make it executable:

chmod +x run.py

Run it:

./run.py

Wait around 10 minutes...

Will load the data
Killed

Environment (please complete the following information):

  • OS: Ubuntu 22.04
  • DuckDB Version: 0.4.0
  • DuckDB Client: Python

Identity Disclosure:

  • Full Name: Alexey Milovidov
  • Affiliation: ClickHouse, Inc

With OOM it cannot qualify in the ClickHouse benchmark.

Before Submitting

  • Have you tried this on the latest master branch? No.
  • Python: pip install duckdb --upgrade --pre It installs the same version 0.4.0.
  • R: I don't use R.
  • Other Platforms: I don't use other platforms.
  • Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there? Yes.

Activity

  1. alexey-milovidov commented on Jun 25, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 25, 2022
    Author

    The table contains 99 997 497 rows.

  2. alexey-milovidov commented on Jun 26, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 26, 2022
    Author

    I tried to add the following lines to the Python script:

    con.execute("PRAGMA memory_limit='{}b'".format(psutil.virtual_memory().total / 2))
con.execute("PRAGMA threads={}".format(psutil.cpu_count(logical=False)))

    But it did not help.

  3. alexey-milovidov commented on Jun 26, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 26, 2022
    Author

    I tried to load from the CLI but it also fails with OOM:

    #!/usr/bin/expect -f

match_max 100000
set timeout 10000

spawn ./duckdb
expect "D "

send -- "PRAGMA threads = 8;\r"
expect "D "

send -- "PRAGMA temp_directory='duckdb.tmp';\r"
expect "D "

send -- ".timer on\r"
expect "D "

send -- "CREATE TABLE hits AS SELECT * FROM parquet_scan('hits.parquet');\r"
expect "D "

  4. alexey-milovidov commented on Jun 26, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 26, 2022
    Author

    Changing parquet_scan to read_parquet does not help, although the difference is unclear.

  5. Alex-Monahan commented on Jun 26, 2022

    @Alex-Monahan
    Alex-Monahan
    on Jun 26, 2022
    Contributor

    Hello!

    Just a random idea, but would it help to create the table first and then insert into it? Create table as select * from ... where 1=0, etc.

  6. alexey-milovidov commented on Jun 26, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 26, 2022
    Author

    I tried, but it did not help.
    Also tried to switch to .csv.

  7. Mytherin commented on Jun 26, 2022

    @Mytherin
    Mytherin
    on Jun 26, 2022
    Member

    Thanks for the report! I will investigate more after the weekend.

    As a work-around for now, perhaps try either (1) setting an even lower memory limit explicitly (e.g. SET memory_limit='8GB', instead of the 16GB/24GB you tried before) or (2) splitting up the file load into multiple calls using e.g. INSERT INTO tbl SELECT * FROM hits.parquet LIMIT ... OFFSET .... Neither will be particularly fast but at least it should work.

  8. Mytherin commented on Jun 26, 2022

    @Mytherin
    Mytherin
    on Jun 26, 2022
    Member

    On the topic of the clickhouse benchmarks, please make sure to use the approx_count_distinct function instead of COUNT(DISTINCT) for better performance on approx distinct counts :)

    Is the code that you are using to benchmark DuckDB available somewhere publicly, or could we perhaps have a look at it otherwise?

  9. hannes commented on Jun 26, 2022

    @hannes
    hannes
    on Jun 26, 2022
    Member

    (Hi, Alexey), I tried this, it went up to 117 GB on a machine with 126 GB RAM when not setting a memory limit, but it completed. When setting the memory limit to 10 GB, it went up to 24 GB but also completed successfully (albeit much slower).

    So indeed I would recommend setting a very conservative memory limit for the load (e.g. 10 GB on the machine you're on). Going forward, we need to have a look for the source of those additional allocations.

    Update: Here is the Parquet file metadata
    hits.metadata.tsv.gz

  10. alexey-milovidov commented on Jun 26, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 26, 2022
    Author

    Ok, I will set the memory limit to 8GB.

    I'm currently preparing the benchmark, it is in work in progress: ClickHouse/ClickHouse#38437
    The idea is to make it easy reproducible (like running a single small script) and also compatible with most SQL DBMS.
    So, I've removed all custom aggregate functions and data types.

  11. Mytherin commented on Jun 26, 2022

    @Mytherin
    Mytherin
    on Jun 26, 2022
    Member

    Sounds great! Let me know if the fix works and if you run into anything else.

  12. alexey-milovidov commented on Jun 26, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 26, 2022
    Author

    Strange, but it does not work. I've set memory limit to the psutil.virtual_memory().total / 4.

    If I load Parquet it does not succeed due to unsupported cast:

    Unimplemented type for cast (BIGINT -> TIMESTAMP)

    If I load CSV it still failed with OOM.

    I will try to use 4GB.

  13. alexey-milovidov commented on Jun 26, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 26, 2022
    Author

    I will try to split the file into parts.
    Update: lowering to 4GB helped.

  14. alexey-milovidov commented on Jun 26, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 26, 2022
    Author

    Now I have different error:

    ubuntu@ip-172-31-4-239:~$ ./run.py 
Will load the data
4216.5390389899985
SELECT COUNT(*) FROM hits;

0.05523904401343316
0.003909286999260075
0.0038267960044322535
SELECT COUNT(*) FROM hits WHERE AdvEngineID != 0;

0.1603328409983078
0.06337512300524395
0.05665142499492504
SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;

0.238524653002969
0.08392444600758608
0.07758511300198734
SELECT AVG(UserID) FROM hits;

1.1524075900088064
0.10763906499778386
0.09490979700058233
SELECT COUNT(DISTINCT UserID) FROM hits;

9.184395752003184
8.429843456004164
9.689035353003419
SELECT COUNT(DISTINCT SearchPhrase) FROM hits;

Segmentation fault (core dumped)

  15. alexey-milovidov commented on Jun 26, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 26, 2022
    Author

    If I restart Python script after every query (and reopen the database), it succeeded.
    Most likely previous queries have corrupted the memory.

  16. hannes commented on Jun 27, 2022

    @hannes
    hannes
    on Jun 27, 2022
    Member

    That's also something for us to look into. But to come back on a point Mark made above, I see you are running COUNT DISTINCT there, which are approximate in ClickHouse AFAIK but are not approximate (ie exact) in DuckDB. Could you please make sure DuckDB's approx_count_distinct for the comparison? Thanks!

  17. alexey-milovidov commented on Jun 27, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jun 27, 2022
    Author

    No, COUNT(DISTINCT ...) is exact in ClickHouse.
    ClickHouse also has an approximate count distinct, but under different names.

  18. hannes commented on Jun 27, 2022

    @hannes
    hannes
    on Jun 27, 2022
    Member

    Thanks for the clarification, great to hear the default is the exact one.

  19. Mytherin
    added a commit that references this issue on Jun 27, 2022

    Fix duckdb#3969: route allocations of the resizeable parquet buffers …

    1053efc
  20. Mytherin
    closed this as completedin 1053efcon Jul 5, 2022

  21. Mytherin commented on Jul 7, 2022

    @Mytherin
    Mytherin
    on Jul 7, 2022
    Member

    This issue was auto-closed, but is actually not fully resolved yet in the master. In #4035 I have pushed a number of fixes for allocation tracking and reducing the number of allocations, which alleviates some of the problems, but the core problem here is actually caused by glibc malloc.

    Attached is a graph of the memory usage of DuckDB running on the AWS c6a.4xlarge box using different memory allocators and a memory limit of 26GB. glibc malloc runs out of memory during the compression phase despite DuckDB freeing more buffers than it is allocating, likely caused by memory fragmentation. Switching to jemalloc fixes the problem and lets DuckDB finish.

    image

    We need to switch to jemalloc internally to fully resolve this issue and make DuckDB complete the query out-of-the-box without being killed by the OOM killer.

  22. Mytherin
    reopened this on Jul 7, 2022

  23. alexey-milovidov commented on Jul 9, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jul 9, 2022
    Author

    Thank you!

    ClickHouse is using jemalloc since Aug 2018.
    We have found and fixed multiple bugs in jemalloc, and continue to find them, but generally, it works ok.

    And glibc's malloc is something that no reasonable software should ever use.

  24. hannes
    added a commit that references this issue on Jul 11, 2022

    Fix duckdb#3969: route allocations of the resizeable parquet buffers …

    a9eac8f

  25. alexey-milovidov commented on Jul 13, 2022

    @alexey-milovidov
    alexey-milovidov
    on Jul 13, 2022
    Author

    @hannes I've included DuckDB into https://github.com/ClickHouse/ClickBench
    Hope it will be interesting.

  26. Mytherin commented on Nov 11, 2022

    @Mytherin
    Mytherin
    on Nov 11, 2022
    Member

    This should be fully resolved now that jemalloc is merged (#4971) and we stream data to disk optimistically (#4996). Feel free to re-open if you are still hitting the issue.

  27. Mytherin
    closed this as completedon Nov 11, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions