Comment by eatonphil | Hacker News Reader

Comment by eatonphil | Hacker News Reader

> Despite these apparent success stories, many other DBMSs have tried—and failed—to replace a traditional buffer pool with mmapbased file I/O. In the following, we recount some cautionary tales to illustrate how using mmap in your DBMS can go horribly wrong

Saying "many other DBMSs have tried — and failed" is a little weirdly put because above that they show a list of databases that use or used mmap and the number that are still using mmap (MonetDB, LevelDB, LMDB, SQLite, QuestDB, RavenDB, and WiredTiger) are greater than the number they list as having once used mmap and moved off it (Mongo, SingleStore, and InfluxDB). Maybe they just omitted some others that moved off it or ?

True they list a few more databases that considered mmap and decided not to implement it (TileDB, Scylla, VictoriaMetrics, etc.). And true they list RocksDB as a fork of LevelDB to avoid mmap.

My point being this paper seems to downplay the number of systems they introduce as still using mmap. And it didn't go too much into the potential benefits that, say, SQLite or LMDB sees keeping mmap an option other than the introduction when they mentioned perceived benefits. Or maybe I missed it.

SQLite does not use mmap as a main I/O mechanism by default, it provides it as an optional feature [1].

mmap has one large advantage, which is that it allows you to share buffers with the operating system. This allows you to share buffers between different processes, and allows you to re-use caches between restarts of your process. This can be powerful in some situations, especially as an in-process database, and cannot be done without using mmap.

There are many problems with using mmap as your only I/O and buffer management system (as listed in the paper and the SQLite docs). One of the main problems from a system design perspective is that mmap does not enforce a boundary on when I/O occurs and what memory to manage. This makes it very hard to switch away from mmap towards a dedicated buffer pool, as this will require significant re-engineering of the entire system.

By contrast, adding optional support for mmap in a system with a dedicated buffer pool is straightforward.

[1] https://www.sqlite.org/mmap.html

I was heavily involved in the push to switch Neo4j off of memory mapping and on to an in house page cache.

In retrospect, I’d say it was something like 50% driven by Java memory mapping having insane issues on Windows, 20% Java memory mapping having insane issues on every platform, 25% me being a noob and 5% actual issues with memory mapping on Linux.

I think if could say “this DB is posix only” I would try memory mapping for the next DB I build

> Java memory mapping having insane issues on every platform

I'm very interested, what issues? I've occasionally considered using mmap for a few things, but never had justification to. I'm curious what issues I might have run into.

Did you use JNI/JNA to mmap or via RandomAccessFile?

2GB limit. Cost of indirect addressing. Inability to address RAM and disk using same method calls without overhead.

I've implemented a modest database in both C and Java with memory mapping. It's hard to explain the feeling of programming both but in general: with java I found myself trying to add intelligence at the cost of complexity to make things run faster. In C I could get speed by removing overheads and doing less work.

Thanks! Insightful. I wonder if you could get what you needed via unsafe but I agree: that's fighting the system in Java.

> I think if could say “this DB is posix only” I would try memory mapping for the next DB I build

Great! Please reach out to us after you learn (again) that this is a bad idea!

I worked at a company that built a custom database on top of mmap, back in the early 2000's. It was single threaded, intended for very limited OLTP use cases, not a generic DBMS, but worked wonderfully for those use cases. A couple gigabytes was more than enough address space. During this time, a machine with 4 gigabytes RAM was considered "high end"! It only ran on a couple of posix platforms.

At startup, it would read everything into memory so everything was hot, ready to go. The "database" was intended to run on a dedicated node with no other applications running. It also kept a write ahead log for transaction recovery.

There’s was a previous discussion on HN a couple months ago [1]. The tl;dr seemed to be that mmap() is a good first step and eventually you can swap it out for a custom-made buffer pool if you need to. A lot of new databases are trying to figure out product/market fit and spending time on a buffer pool initially is usually not worth it.

[1] https://news.ycombinator.com/item?id=29936104

Thanks! I missed that thread.

How custom are these “custom buffer pools”, anyway? Could DBMS use-cases be similar enough that a single buffer-pool library — if such a thing were to be created — could suit all their needs? A Jemalloc equivalent specifically tuned for managing large disk-backed allocations?

There’s enough commonality that you could make some kind of generic buffer pool library. However, writing databases is still quite niche so I’m not sure it’d be worth it. There’s also a lot of details with regards to transactional semantics that differs per database.

That is essentially what the kernel cache is.

Caches are pretty heavily customized for the database design because they control so much of the runtime behavior of the entire system. The implementations are different based on the software architecture (e.g. thread-per-core versus multi-threaded), storage model, target workload, scale-up versus scale-out, etc. The "custom buffer pool" isn't just a cache, it is also a high-performance concurrent I/O scheduler since it is responsible for cache replacement.

Even if you were only targeting a single software architecture and storage model, it would require a very elaborate C++ metaprogramming library to come close to generating an optimized-for-purpose cache implementation. Not worth the effort. The internals are pretty modular and easy to hack on even in sophisticated implementations. In practice, it is often simpler to take components from existing implementations and do some custom assembly and tweaking to match the design objective.

In either case, you still have to understand how and why the internals do what they do to know how to effect the desired code behavior. For people that have a lot of experience doing it, the process of writing yet another one from scratch is pretty mechanical.

The buffer pool, concurrency control, and recovery algorithm all need to dovetail into each other. In theory you could have a generic pool library that offers a Pin and Unpin based API, but as you start talking about stuff like garbage collection that has to play nice with incremental checkpointing and the WAL rollover and... it gets hard to make a reusable library vs just each database writing something narrowly tailored to their needs.

Also, a lot of the interesting research stuff going on right now is on storage engines that look fairly different from a traditional RDBMS buffer pool centric storage engine.

The point is mmap is good for read heavy workload and they do go into a test setup where they compare performance between mmap and a file based io system. Bandwidth seems to fall after the page cache fills up.

Anyways it's not like OS will do some magic one a page needs to be flushed to disc. It all depends on how quickly and how nicely scheduling is done. Paper also goes into software aspects of using an mmap system. 3.2 and 3.4 seem quite relevant software problems.

Reply on news.ycombinator.com