Duckdb: DuckDB 0.2.1 consumes all available memory; app won't run

[fire-eggs]

Windows 7 x64. 32G memory. Python 3.7.4.

I cloned my sqlite database and python app to duckdb (0.2.1). [The .db.wal file is 1.9G, which compares nicely to the sqlite version at 5.6G].

The database consists of three tables:

1. images : 3,701,799 rows
2. tags: 392,049 rows
3. imageTags: the many-to-many relationship between images and tags: 107,763,315 rows.

I run my python app which uses the database. Merely connecting to the database:
self.conn = duckdb.connect("../danbooru2019.db"), the python process proceeds quickly to consume all available memory (31G) which isn't enough. The app doesn't get to run to the point of showing the GUI or making any database queries.

My intent was to compare duckdb against sqlite for performance etc, but that is not possible at this time.

To see the app source / details see the repo at Danbooru2019. If necessary, I can provide the script and CSV files to create the duckdb variant [as they are not in said repo].

[hannesmuehleisen]

I think this is related to #926

[fire-eggs]

For reference, the app running vs sqlite [database load only] is using 31M of memory. With one of my more complex queries, the process never breaks 42M of memory - but it takes 15 seconds and 220K I/O reads (which is why I'm looking at alternative dbs).

[Mytherin]

This is indeed related to the current checkpointing behavior, which does a complete checkpoint on start-up. As a work-around what you could do right now is load a subset of the data (e.g. 20%), then restart the database, and repeat until the full database is loaded. Not ideal, but it might be a while before this behavior is fixed (likely around a few months).

[cboettig]

@Mytherin Any updates on this? while using the R client, even when loading data in subsets and restarting the connection between each chunk, I'm still seeing memory continue to rise until the system crashes. Using DBI::dbDisconnect(db, shutdown = TRUE) and dbConnect() to try and restart the database, but not seeing the RAM released unless I restart the R session.

Currently this is a major barrier for us in trying to use duckdb in any context where data doesn't fit into RAM to begin with...

[Mytherin]

Are you running the latest release or the latest master branch? I think this should be fixed in #991, but there has not been a release since then. Could you verify that the behavior occurs even if you run the master branch?

[cboettig]

Thanks @Mytherin ! I'll try that, gotta remember how to build the R client from source. If I just clone and run make, does that build the R package as well?

[Mytherin]

cd tools/rpkg && R CMD INSTALL . should work on MacOS/Linux. I think there's also a way to install directly from github using devtools, but @hannesmuehleisen is the R expert :)

[cboettig]

excellent, trying that now! thanks so much for the speedy reply and the amazing work here.

[Mytherin]

No worries. If you still run into the issue please post a reproducible example, then I can have a look to see if I can fix it.

[cboettig]

This works beautifully now! Queuing up some more test cases now, but so far it is going swimmingly. The only thing limiting me now is the i/o wait of my spinning disks! This is a complete game-changer!

[hannesmuehleisen]

I guess we should release then soon.