Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

35% Faster Than The Filesystem (2017)

sqlite.org

Post reply on HN
Print view View original

81–90 of 166 posts

Re: 35% Faster Than The Filesystem (2017)

#81

Post by bob1029 »

pedrocr wrote: post #63
bob1029 wrote: post #53

Earlier quoted context omitted.

I would be curious about your particular implementation with SQLite. When enabling WAL, I am able to saturate a NVMe disk on which the the database file resides. In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implementation. I do agree that SQLite with 100% defaults will easily be overtaken by Postgres, and that if your use case requires that more than 1 node persist…

The code for my most recent case is here: https://github.com/pedrocr/syncer/ The db code itself is here: https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... WAL is being used but that's not enough to get a mixed INSERT/SELECT to not completely starve reads without first caching INSERT and batching it. > In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implem…

Is there a reason you are managing locking at the application layer on the SQLite connection object?

https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090...

This is going to absolutely destroy your throughput. I am not sure about go, but in .Net, the underlying SQLite connection instance is inherently thread-safe and can be shared across execution contexts. If you are unable to share a single SQLite connection instance between multiple logical parallel execution contexts in go, I would recommend just creating a new SQLite connection in each case where you need one (e.g. at the beginning of every method currently performing a lock on connection). The only thing you need to remember with this approach is that the synchronous=normal pragma needs to be set each time you new up a connection. This is an extremely fast operation (doesn't touch disk IIRC), so you shouldn't worry too much about any extra overhead here.

Re: 35% Faster Than The Filesystem (2017)

#82

Post by pixl97 »

Const-me wrote: post #62

The fact that SQLite is much faster than file systems is not surprising at all. But there’s one surprising result there. > All machines use SSD except Win7 which has a hard-drive. And yet Win10 is much slower than Win7 in all tests (note they have latency on the graphs). How’s that possible? For a drive spinning at 5400 RPM, average random read latency can’t be smaller than 2.2ms, this is how long it takes to rotate…

Maybe they have a bad benchmark setup and the 7 box is setup to cache writes to the disk.

Re: 35% Faster Than The Filesystem (2017)

#83

Post by petre »

mceachen wrote: post #76
wayneftw wrote: post #50

Wow, so maybe instead of a node_modules folder, npm should use a node_modules.db?

There's already support for accessing zip and tar archives as filesystems (also see ASAR), which doesn't incur the overhead of SQL evaluation. At least on Windows, this would result in a huge speedup (just because you're avoiding antivirus latency). I don't expect it to help Mac or Linux that much (as their filesystems are comparatively much more efficient with larger numbers of small files).

I had a problem with zips, they can be only 232 bytes = 4.3 GB. The Zip64 format fixes this, however few libraries support it.

Re: 35% Faster Than The Filesystem (2017)

#84

Post by pedrocr »

bob1029 wrote: post #81
pedrocr wrote: post #63

Earlier quoted context omitted.

The code for my most recent case is here: https://github.com/pedrocr/syncer/ The db code itself is here: https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... WAL is being used but that's not enough to get a mixed INSERT/SELECT to not completely starve reads without first caching INSERT and batching it. > In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implem…

Is there a reason you are managing locking at the application layer on the SQLite connection object? https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... This is going to absolutely destroy your throughput. I am not sure about go, but in .Net, the underlying SQLite connection instance is inherently thread-safe and can be shared across execution contexts. If you are unable to share a single SQLite connectio…

It's a simplification for the threading implementation. Spawning a new connection per thread would work but then I'd probably have to deal with failed transactions. The bottleneck wasn't there though, INSERT performance will just naturally have that behavior:

https://stackoverflow.com/questions/1711631/improve-insert-p...

Without batching several INSERTs into a single transaction you can't do very many of them which is why I ended up doing a caching layer and batching them.

Re: 35% Faster Than The Filesystem (2017)

#85

Post by Const-me »

pixl97 wrote: post #82
Const-me wrote: post #62

The fact that SQLite is much faster than file systems is not surprising at all. But there’s one surprising result there. > All machines use SSD except Win7 which has a hard-drive. And yet Win10 is much slower than Win7 in all tests (note they have latency on the graphs). How’s that possible? For a drive spinning at 5400 RPM, average random read latency can’t be smaller than 2.2ms, this is how long it takes to rotate…

Maybe they have a bad benchmark setup and the 7 box is setup to cache writes to the disk.

After thinking for a bit, I think their results are actually reasonable. Their graphs are relative to how SQLight performs on the same machine. When measuring absolute numbers i.e. CPU cycles, all modern versions of Windows have comparable overhead on opening files, it’s the same file system and the kernels are quite similar. When a drive is slow, that overhead is masked by IO cost which almost equally applies to SQLight and NTFS. For a fast SSD that overhead is much higher than the cost of doing IO, hence the results.

Re: 35% Faster Than The Filesystem (2017)

#86

Post by z3t4 »

mmis1000 wrote: post #60
zaroth wrote: post #6

For small- to mid-sized projects, I’ve always realized huge gains in simplicity by haves “Files” tables to store various assets. It means instances in a web-farm can pull the files down when they initialize easily, it means files are automatically versioned, it provides an obvious place to put the files when they are being uploaded on the Admin panel. It means all the files are getting backed up as part of the databa…

I though, one of the advantage here is, you are able to store/read metadata along the file content easy without worrying the metadata and file may desync in some way or corrupted. This makes serve something like Image (photo dimension) or video(length) metadata way easier. Some filesystem (like ntfs) do have place to save metadata along files, but most filesystem don't have the way to done that properly

Just store the metadata in the file name. Unless your file system besides metadata also have a 11 character filename limit.

Re: 35% Faster Than The Filesystem (2017)

#87

Post by gridlockd »

petre wrote: post #83
mceachen wrote: post #76

Earlier quoted context omitted.

There's already support for accessing zip and tar archives as filesystems (also see ASAR), which doesn't incur the overhead of SQL evaluation. At least on Windows, this would result in a huge speedup (just because you're avoiding antivirus latency). I don't expect it to help Mac or Linux that much (as their filesystems are comparatively much more efficient with larger numbers of small files).

I had a problem with zips, they can be only 2 32 bytes = 4.3 GB. The Zip64 format fixes this, however few libraries support it.

That sounds like an upside. Force people to keep their hypothetical node_modules.db "lean and mean"!

Re: 35% Faster Than The Filesystem (2017)

#88

Post by devnonymous »

OJFord wrote: post #15

Next week on Show HN: `sqlitefs`, the SQLite-backed FUSE filesystem?

I've taken a stab at this and made quite a bit of progress.

However, the idea was to use the sqlite fuse layer as a stacked layer over the underlying filesystem rather than a full scale file-system (which for example, is what libsqlfs provides).

The motivation was not to improve filesystem performance but improving the speed of querying (ie: lightning fast stat of the entire deeply nested directory tree) at the expense of slower writes. Additionally I could (in theory, never got around to it) store rich metadata about the files, build all sorts of indexes, apply a richer access-control mechanism etc. Unfortunately, it remains in pre-alpha state, because life.

Re: 35% Faster Than The Filesystem (2017)

#89

Post by gridlockd »

wolf550e wrote: post #79
sigzero wrote: post #59

Earlier quoted context omitted.

That's true for everything.

simpler file formats are less fraught, though they have fewer features.

What's simpler than SQLite that would serve a similar purpose? If it's not a "battle-tested" format, it's still an issue no matter how simple the format is. Remember, your average C programmer can easily fit 11 buffer overflows in 10 lines of code.

Re: 35% Faster Than The Filesystem (2017)

#90

Post by pdimitar »

notyourday wrote: post #18

As someone who has spent time consulting for different kinds of "we do stuff on the internet" companies, I can confidently say that this is a premature optimization for 99.999% of the companies/projects. The companies simply don't have enough IO traffic to need it. Is it cool? Sure. Is it sexy? Maybe. Is it needed ? Nope. Do boring stuff. Use files. If you send your small files over the internet via web and you need…

The main added value of sqlite3 when I work with it is mostly compartmentalisation of your data in one DB and one file.

You not only store files as blobs, you can also store a lot of metadata on top of them, and then, when your needs inevitably grow, start using other SQL tables for the rest of your application configuration or state. And it's all in one neat tidy package.

For the scenario you describe I'd generate an nginx container -- or an Ansible script -- by pulling data from a sqlite3 DB. Technically your approach and mine are not mutually exclusive at all.

And, as a sibling commenter said, the speedy nature of sqlite3 is just a bonus. It's not always the chased-after feature (although with WAL and a few other flags enabled, sqlite3 is damn fast and not many DB engines can compete with it).

Post reply on HN