Comment by mpweiher | Hacker News Reader

Comment by mpweiher | Hacker News Reader

Parent Full thread

Not surprised by the results, very surprised by the explanation given, the overhead of open/close calls.

As far as I know (from many measurements and talking to kernel people and researching the mechanism involved), the difference is due to the fact that buffers are shared between all pages of a single file, and not between files.

So for reads, the filesystem will do read-ahead of significantly more data than requested and keep that in buffers, and future reads will profit. Similar for buffers shared when writing.

The same effect will be reproducible with any format storing multiple objects in a single file, it has virtually nothing to do with "SQLite" or "Databases".

One tradeoff is the greater potential for inconsistencies, which despite all the measures taken is much greater when you modify a file rather than writing only completely new files. Another is the inconvenience and duplication of effort, because all your default file-system management tools aren't available.

It would be interesting to see if a pseudo-filesystem that is mapped to a single underlying file would show the same effects (preferably implemented in user space to avoid overheads of multiple kernel round trips).

> The same effect will be reproducible with any format storing multiple objects in a single file

Given unbounded developer time, any advantage SQLite has here can of course be matched or beaten by custom code. It's just another C library, not magic.

The real issue is, how much work will it take you to do that?

SQLite is billed as competing with `fopen()`. That's the proper way to compare it: given equal development time spent talking to the C runtime library vs. integrating SQLite, how much speed and robustness can you achieve?

> the greater potential for inconsistencies

How? In this application, SQLite is filling a role more like a filesystem than a DBMS or `fopen()` call. You can corrupt a filesystem just as easily as a DBMS. SQLite protects itself in much the same ways that good filesystems do, and it can be defeated in much the same sort of ways.

> Another is the inconvenience and duplication of effort, because all your default file-system management tools aren't available.

It's a classic tradeoff: do you need the speed this technique buys or not?

A better reason to avoid this technique is when the files you're considering storing as BLOBs need to be served by a web server that uses the `sendfile(2)` system call. There, the additional syscalls caused by the DBMS layer will probably eat up the speed advantage.

Software development is all about tradeoffs. No technique or technology is perfect for everything.

Now you know one more technique. Maybe it will be of some use to you someday.

> a pseudo-filesystem that is mapped to a single underlying file

That pretty much describes SQLite. Both SQLite and a good filesystem use tree-based structures to index data, both have ways to deal with fragmentation, both have ways to ensure consistency, both strive for durability, etc., etc.

> preferably implemented in user space to avoid overheads of multiple kernel round trips

How are you going to avoid kernel round trips when I/O is involved?

If you think user-space filesystems are fast, go try FUSE.

> how much work will it take you to do that?

Dunno, use a zip library?

> [inconsistencies]

No, SQLite doesn't have the same tools available as filesystems. It is very good, but not quite as good.

> [Pseudo FS mapped onto a single file] That pretty much describes SQLite.

Not really. For one, it doesn't provide a filesystem-like interface.

>> avoid overheads of multiple kernel round trips

> How are you going to avoid kernel round trips when I/O is involved?

Added emphasis.

> If you think user-space filesystems are fast, go try FUSE.

Exactly, because you tend to make multiple round trips. If you can flatten that to just one, you avoid those overheads.

linking to a post in another article wrt tagging fs:

https://news.ycombinator.com/item?id=14552224

that thread began: "Now we just need a redesign of Unix tools and principles/practice"

This is perhaps one advantage of a sq-lite fs; the flexibility.

You could design concepts into the db structure, and have the driver translate this into the usual language of folders/files/read-write.

But as well as requiring new fsck tools etc, you might also need new ways to interact with the fs as it would have new capabilities, e.g. storing data non-hierarchically.

If the OS can be pushed forward wrt some feature, I care less about whether it would perform.

Reply on news.ycombinator.com