Duckdb: import large csv / db causes swapping

[KnutJaegersberg]

wanted to quickly import a large csv with read.csv function. the default behaviour seems to be to use ram beyond whats available. switched to monetdblight as its import function does not necessate to read in pieces.
when i did the import on a larger machine and want to use the db file on my laptop, it also swaps.
cant change pragm memory limit as swapping out on connecting to db before prevent me from doing so.

is a different default behavior feasibile?

[Mytherin]

Thanks for the report. This is indeed a known issue that stems from the fact that the intermediate buffers are not buffer managed yet, which causes swapping when loading too much data at once. The second issue is caused by the memory limit by default being set to infinite, which is causes swapping when the database checkpoints.

We should fix this as follows:

• Set the default memory limit of the buffer manager to the available memory on the machine
• Periodically flushing local buffers to the destination table to prevent the local non-buffer managed storage from becoming too large.
• Also buffer manage the transaction-local storage.

[KnutJaegersberg]

thank you helping me understand better!
wouldnt it be nice from user perspective to have this being partially or so covered by the R package etc?

[Mytherin]

The C++ database takes the memory limit as a startup parameter, this way the memory limit can be setup before startup. I think it would make a lot of sense to push that through to the R/Python/etc packages. That way you can set up the memory limit beforehand to ensure the checkpointing on startup does not lead to swapping.

The other issues however are a bit more internal to the system and require a more development of the storage engine. The current storage engine is still very much in pre-release mode and has a number of quirks that we have to fix before it is ready for prime-time.

[wizzard0]

Yes, would really appreciate memory limit in Python etc. Also I guess let's link #825 (memory limit on startup in shell), #661 (nodejs) and #321 (csv swap from Python) as related.

[Mytherin]

Working on this in the localstoragerework2 branch. The memory limit will default to 80% of system memory, and bulk loads will correctly use the buffer manager to avoid excessive swapping.