Just as context here, I was playing around with duckdb using a schema that had a bunch of log files in it. I used ZSTD compression to generate the database. I noticed that system CPU time was very high. Using perf it seems that jemalloc was constantly mmaping and unmmaping memory regions.

$  MALLOC_CONF="" time  ./duckdb -s "select count(*) ...
99.99user 188.51system 0:10.61elapsed 2718%CPU (0avgtext+0avgdata 5426204maxresident)k
0inputs+0outputs (0major+11344194minor)pagefaults 0swaps

In Linux, it's a long-documented issue that multiple threads doing mmaps at the same time can cause lock contention. I was able to somewhat mitigate this issue by setting a large oversize_threshold getting a 3x (!!) improvement in performance. However, system CPU time was still high and there were still a number of mmap operations.

$  MALLOC_CONF="oversize_threshold:67108864" time  ./duckdb -s "select count(*) from 'all.parquet' where contains(msg, 'Fetched');"
...
99.61user 33.98system 0:02.73elapsed 4893%CPU (0avgtext+0avgdata 9615140maxresident)k
0inputs+0outputs (0major+3715382minor)pagefaults 0swaps

There are some recent(ish) improvements to jemalloc on this topic (jemalloc/jemalloc#2466)

I'm sure @interwq and the rest of the jemalloc team would be happy to see what they can do to address the portability issues you mention and make it easier for you to use an updateable version of jemalloc, if that's something that'd be of interest.

That's an interesting observation! I was unaware that MMAP is still used despite not calling it explicitly. I'm curious: how would I be able to reproduce this? Is it just ZSTD compressed Parquet files? On which OS/hardware was this done?

The improvement you linked, jemalloc/jemalloc#2466, is interesting but not applicable to DuckDB since we do not enable background threads.

Something I also wonder is if we have configured jemalloc optimally for our workload. We mostly allocate blocks of 256k. Is there anything we can do to improve that?

Thanks for the explanation! I will enable opt.retain in that case.

We looked into the background thread, but we decided not to enable it because DuckDB is an in-process database system, not a database server. Therefore, we do not want to use resources when idle. Furthermore, our users expect to be able to close() a DB connection, which should free up all resources (memory and threads).

I'm using the following config string:

"narenas:%llu,dirty_decay_ms:1000,muzzy_decay_ms:1000"

Where %llu is the number of CPUs. I've tried using percpu_arena, but this didn't work on all architectures for some reason, so I've used this instead. I don't know if this is the best option. I've done this because we do not want to over-allocate, as this causes the process to be killed in Docker containers for some of our users. Having one arena per thread allows each thread to purge its arena after finishing a task (ensuring we minimize resource utilization when idle).

Note that we also do not use jemalloc as our global allocator, just for allocations that go through our Allocator, so essentially only for malloc calls. Again, this decision stems from DuckDB being an in-process database system. DuckDB may be active as a library within Python in the same process as another library that uses jemalloc, which would cause conflicts if both use jemalloc as a global allocator.

I'll look into the malloc_stats when I find the time. Again, thanks for your input!

Hi @interwq, enabling/disabling the background thread is great! This allows us to make it configurable for our users. I will look into this, as well as upgrading jemalloc.

Using jemalloc's dev branch for CI is tricky, as it is not straightforward to integrate jemalloc for us. However, since we first integrated jemalloc, we have made changes to how DuckDB's extensions work, so it should be easier now.

I am quite busy at the moment, so I can't get this going right now, but I hope to get back to you soon.

Again, thank you for reaching out, this is of great help.

Glad to help @lnkuiper ! Feel free to let us know if anything.

I'll leave this thread open in case you want to track it.

Thanks for re-running the query with the new settings! Great to see the improvements, and thanks for the UTF8 suggestion!

Thanks for the suggestions. I re-ran the workload without metadata_tph and without JEMALLOC_CACHE_OBLIVIOUS, and the performance was the same, so it seems that these can be safely disabled without affecting performance.

Lowering narenas to half did affect performance of the first query (in thejemalloc_stats_15.txt file), so I've left this setting unchanged.

Thanks! This setting could indeed be safely enabled without affecting performance.

DuckDB's primary use case is as a shared library, as we're an in-process database system. I would love to statically link jemalloc, or let jemalloc override C++ new/delete, but this creates conflict when DuckDB is used as a Python package.

Leaving the responsibility of choosing an allocator to the user would be best, but most of our users simply do pip install duckdb and then run queries, without knowing that different allocators exist. We want to be able to offer the best performance we can to our average user, that's why we've chosen to implement jemalloc in DuckDB in this way.

At some point in the future I will try to mess around with our shared library builds to find a way to override new/delete without conflicting with Python's allocator.

At some point in the future I will try to mess around with our shared library builds to find a way to override new/delete without conflicting with Python's allocator.

The only way to overload new/malloc/etc in a shared library is if you can be assured that the library is loaded with LD_PRELOAD. Otherwise, the application may already have allocated memory in the default allocator before you override it.

The "fast by default" principle makes sense. Given that I'd suggest:

1. When you build a standalone duckdb binary, have duckdb use the default allocator. Statically link jemalloc in the standard configuration
2. By default, when building duckdb as a library, bundle the prefixed jemalloc so that you are not subject to the default allocator in the program
3. As an option, and recommended for high performance use cases, have duckdb use the programs' default allocator, and ask the user to use a good one (like jemalloc)

Thanks for the reply! I haven't gotten around to this before my holiday starts (today!). I'm curious to see what the stats say (in ~2 weeks time)

I've attached the printed malloc stats with and without retain to this comment. Both ran the same workload. The before/after are in the same file. I've indicated this with comments, e.g., ### WITH RETAIN BEFORE PURGE ###. From what I can see, the resident field is almost exactly the same with and without retain. However, the RSS (reported by htop) is much higher (around ~2GB) when retain is enabled.

with_retain.txt
without_retain.txt

@lnkuiper The stats do look very similar, and don't seem to indicate the big RSS difference you observed. It also confirms that both the thread cache and page cache are flushed.

The only thing I can think of is, the retain case runs a bit faster / shorter (~2.95s vs ~3.1s w/o retain). Is it possible that htop is supposed to refresh at the 3 second mark, and happen to not report the last usage for the retain case (which missed the big drop in RSS near the end )? Can you please try reporting RSS within the binary, right after the purge mallctl?

Hi @interwq, Thanks for taking a look at the stats! I thought I may have messed something up in our configuration, so I've updated the jemalloc source code and compared our jemalloc_internal_defs.h with a generated version on MacOS and Linux. I found that I configured some settings wrongly, which I've now corrected. This seems to have solved the problem, and the RSS is now going down when closing a connection! 🎉

@lnkuiper Thanks for following up! Glad to hear it's resolved for you. I'll close this issue but feel free to ping us if any questions.