DuckDB needs more than 11GB memory to process INSERT OR UPDATE statements

[user8555]

Setup

I have attached 1024 database instances and I'm either using Appenders on them in parallel or running INSERT OR UPDATE on them non-concurrently.

What happens?

If I run in dry-run mode that comments out the conn.Query() line then the memory consumption of entire process stays flat.

The moment I disable dry-run mode, the memory consumption shoots up quickly to ~100% and OOMs most of the time.

When I dive deep, it is the OS file cache that seems to just grow all the way to the max possible. And the reclamation of the reclaimable memory in DuckDB is not fast enough to prevent OOMs.

I've set the memory_limit to 21GB and the total available memory is 55GB.

Zero SELECT queries. Just INSERT OR UPDATE statements being executed across the 1k connections non-concurrently.

Later when I set the memory_limit to 11GB, the process OOMS with error

'duckdb::OutOfMemoryException'
what(): {"exception_type":"Out of Memory","exception_message":"failed to pin block of size 256.0 KiB (11.1 GiB/11.1 GiB used)"}

    @ 000000000a46a68c duckdb::TempBufferPoolReservation duckdb::StandardBufferManager::EvictBlocksOrThrow<char const*, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > >(duckdb::MemoryTag, unsigned long, duckdb::unique_ptr<duckdb::FileBuffer, std::default_delete<duckdb::FileBuffer>, true>*, char const*, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >)
                       ./src/storage/standard_buffer_manager.cpp:81
    @ 000000000a467eee duckdb::StandardBufferManager::Pin(std::shared_ptr<duckdb::BlockHandle>&)
                       ./src/storage/standard_buffer_manager.cpp:169
    @ 000000000a48bd4e duckdb::FixedSizeInitScan(duckdb::ColumnSegment&)
                       ./src/storage/compression/fixed_size_uncompressed.cpp:132
    @ 000000000a4d7386 duckdb::ColumnSegment::InitializeScan(duckdb::ColumnScanState&)
                       ./src/storage/table/column_segment.cpp:103
    @ 000000000a4cbbce duckdb::ColumnData::ScanVector(duckdb::ColumnScanState&, duckdb::Vector&, unsigned long, bool)
                       ./src/storage/table/column_data.cpp:136
    @ 000000000a4cc0f3 unsigned long duckdb::ColumnData::ScanVector<true, true>(duckdb::TransactionData, unsigned long, duckdb::ColumnScanState&, duckdb::Vector&)
                       ./src/storage/table/column_data.cpp:190
    @ 000000000a4c8fa2 duckdb::ColumnData::ScanCommitted(unsigned long, duckdb::ColumnScanState&, duckdb::Vector&, bool)
                       ./src/storage/table/column_data.cpp:210
    @ 000000000a516181 duckdb::StandardColumnData::ScanCommitted(unsigned long, duckdb::ColumnScanState&, duckdb::Vector&, bool)
                       ./src/storage/table/standard_column_data.cpp:77
    @ 000000000a500da4 void duckdb::RowGroup::TemplatedScan<(duckdb::TableScanType)1>(duckdb::TransactionData, duckdb::CollectionScanState&, duckdb::DataChunk&)
                       ./src/storage/table/row_group.cpp:494
    @ 000000000a4fdf3f duckdb::RowGroup::ScanCommitted(duckdb::CollectionScanState&, duckdb::DataChunk&, duckdb::TableScanType)
                       ./src/storage/table/row_group.cpp:593
    @ 000000000a515dfd duckdb::CollectionScanState::ScanCommitted(duckdb::DataChunk&, duckdb::TableScanType)
                       ./src/storage/table/scan_state.cpp:128
    @ 000000000a41a13b duckdb::DataTable::ScanTableSegment(unsigned long, unsigned long, std::function<void (duckdb::DataChunk&)> const&)
                       ./src/storage/data_table.cpp:789
    @ 000000000a41a959 duckdb::DataTable::WriteToLog(duckdb::WriteAheadLog&, unsigned long, unsigned long)
                       ./src/storage/data_table.cpp:830
    @ 000000000a54691a void duckdb::CommitState::CommitEntry<true>(duckdb::UndoFlags, unsigned char*)
                       ./src/transaction/commit_state.cpp:300
    @ 000000000a5458cd duckdb::UndoBuffer::Commit(duckdb::UndoBuffer::IteratorState&, duckdb::optional_ptr<duckdb::WriteAheadLog>, unsigned long)
                       ./src/transaction/undo_buffer.cpp:156
    @ 000000000a5408f5 duckdb::DuckTransaction::Commit(duckdb::AttachedDatabase&, unsigned long, bool)
                       ./src/transaction/duck_transaction.cpp:135
    @ 000000000a540ef9 duckdb::DuckTransactionManager::CommitTransaction(duckdb::ClientContext&, duckdb::Transaction&)
                       ./src/transaction/duck_transaction_manager.cpp:202
    @ 000000000a543f16 duckdb::MetaTransaction::Commit()
                       ./src/transaction/meta_transaction.cpp:98
    @ 000000000a544d8c duckdb::TransactionContext::Commit()
                       ./src/transaction/transaction_context.cpp:46
    @ 000000000a14cadf duckdb::ClientContext::EndQueryInternal(duckdb::ClientContextLock&, bool, bool)
                       ./src/main/client_context.cpp:226
    @ 000000000a14c4b2 duckdb::ClientContext::CleanupInternal(duckdb::ClientContextLock&, duckdb::BaseQueryResult*, bool)
                       ./src/main/client_context.cpp:261
    @ 000000000a14d3a1 duckdb::ClientContext::FetchResultInternal(duckdb::ClientContextLock&, duckdb::PendingQueryResult&)
                       ./src/main/client_context.cpp:292
    @ 000000000a19ff7f duckdb::PendingQueryResult::ExecuteInternal(duckdb::ClientContextLock&)
                       ./src/main/pending_query_result.cpp:79
    @ 000000000a153855 duckdb::ClientContext::Query(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, bool)
                       ./src/main/client_context.cpp:1012
    @ 000000000a1612d6 duckdb::Connection::Query(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)

• Why is it unable to evict? There are no active queries?

To Reproduce

I'll send a reproduction once I know whether this is unexpected behavior or a expected behavior

OS:

Centos 9

DuckDB Version:

0.10.1

DuckDB Client:

C++

Full Name:

Ajay Gopalakrishnan

Affiliation:

Meta

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

No - I cannot share the data sets because they are confidential

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have