The memory limit defined by SET memory_limit is not maintained during huge CSV import

[parvizfarnia]

Hello everyone,

I would like to ask a question about the memory_limit configuration parameter. Our datalab environment is a shared environment with a lot of available disk space but with far less available RAM given the high number of users who are often connected at the same time. As a result, optimizing programs to consume the least possible amount of RAM on the server is an obvious rule reminded regularly by the administrators of the environment. As a matter of fact, one of the reasons I chose DuckDB was precisely this limit imposed by memory_limit that guarantees that a program either succeeds or fails with an error message due to the lack of enough memory. In the case of the latter, at least, the program would not conflict with other users' programs in terms of available memory and resources. And for a while it seemed to me that setting this parameter was the way to go in a shared datalab environment, in particular this approach helped me a lot with Parquet files creation which sometimes can be tricky in terms of resource management.

However, this week I had multiple bad experiences while importing huge CSV files in DuckDB that made me doubtful about memory_limit and to wonder somewhat its true capacity of memory usage control.

Description of my environment:

OS: Windows Server (64 bits)
DuckDB version : 0.8.1
Python version : 3.10 (64bits)
CSV file size : about 60 GB

Here is my program written in Python:

import duckdb

def main():
   
    db_conn = duckdb.connect("mydb.duckdb", read_only=False)
    db_conn.execute("set temp_directory = 'tmp_dir'")
    db_conn.execute("set memory_limit = '8GB'")
   
    db_conn.execute( " ... Here I write my CREATE TABLE statement ...")
   
    # And here I import my huge CSV file
    db_conn.execute(
        "".join(
            [
                "copy ",
                "mytable ",
                " from ",
                "'",
                "... here the filepath .../myfile.csv",
                "'",
                " with (",
                "format csv, ",
                "header FALSE, ",
                "delimiter '|', ",
                "quote '\"', ",
                "null '', ",
                "encoding UTF8",
                ")"
            ]
        )
    )
   
    db_conn.close()


if __name__ == "__main__":
    main()

As you can see in the above program, I defined both temp_directory and memory_limit configuration parameters to control the memory usage of the program. Once I started the program, I did CTRL + ALT + DELETE to view the evolution of memory usage by the program. It consumed more than 70 GB for a CSV file of 60 GB !! I run the program multiple times and the same strange result was produced. Also, it seems to me that this is not a regular and constant behaviour, for example, I can import this file 5 times. Let's say for the 1st and 2nd import, DuckDB could exceed tremendously the memory usage based on the value defined by memory_limit but for example it may respect this limit during the 3rd import and so on.

Today, I noticed that there is a new version of DuckDB: 0.9.0 (it seems that its R version is not yet available). Using this newer version I managed to reduce significantly the memory exceeds, but still it can happen (5 or 6 GB higher than the limit defined via memory_limit) or at least this is the result that I obtained after trying 3 times to import the same huge CSV file.

Consequently, I would like to ask

1. Is this a normal behaviour of DuckDB or is it rather a bug or may be simply it is me who doesn't know how to configure properly my database using temp_directory and memory_limit configuration parameters ?

2. I give a course to several students in which we are using DuckDB, and since the beginning of the course, I categorically encouraged them to add two lines at the beginning of their programs setting appropriate values for temp_directory and memory_limit configuration parameters. I presented this as the good way to go in a shared datalab environment where there are rather strict memory constraint and limitations. Does this mean that I gave them a wrong piece of advice and they should simply forget about these parameters in their programs?

3. Will memory control be rather more strict using memory_limit in the future versions of DuckDB?

Thanks in advance

[pdet]

Hi @parvizfarnia, indeed, the CSV Scanner is not yet fully memory managed, hence can't follow strict memory limits. As you realized, 0.9 is already much better than 0.8 because memory management was introduced into the sniffer. However, it still needs to be done in the parser. The plan is that this will come in the next major release.

[parvizfarnia]

Hello @pdet

Thank you very much for your clarification. Its a relief. I'm a big fan of DuckDB and I follow each version regularly. Of course as other members of the users' community I am so grateful for the valuable and tremendous amount of job done by DuckDB team.

The fact that memory management is not perfect yet for CSV files is actually not the main concern for me, because as you mentioned, it gets better and better after each new release and that's perfectly fine for me.

My true concern, as mentioned above was about the training program with my students, and as I said, I had told them to add two lines at the beginning of their programs and to set appropriate values for temp_directory and memory_limit configuration parameters. I presented this as the good way to go in a shared datalab environment where there are rather strict memory constraint and limitations. But after multiple memory exeecing experiences that I had during last week, I was worried that whether I had given them a rather wrong piece of advice.

But now, thanks to your clarification, I can tell them that this is indeed the good approach and they should continue programming like this, only that they should update regularly DuckDB to be able to benefit a better memory management after each new release.

Am I right ?