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

An unscientific benchmark of SQLite vs. the file system (btrfs)

github.com

Post reply on HN
Print view View original

51–60 of 68 posts

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#51

Post by le-mark »

kevingadd wrote: post #6
bogota wrote: post #4

Earlier quoted context omitted.

If you only care about storing data and not doing any complex operations or query against it why are you using something like sqlite as well? It’s not a comparison as being in sqlite makes the ability to access this data significantly easier. This is comparing apples and dogs and i don’t see the merits.

It's not uncommon to see people advocate for using sqlite as an alternative to flat files or json blobs. In particular, transactions provide nice properties vs a web of separate flat files, and the stronger schema can be a good alternative to json. There's a lot of great existing sqlite tooling, too.

The history around mongrel2 using SQLite as a config file format may be instructive. Seems like there was a lot of resistance in that case.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#52

Post by prussian »

cafxx wrote: post #49
christophilus wrote: post #9

Earlier quoted context omitted.

Yeah. I mentioned that in the final test where I write to a temporary file, then rename. This is much slower, probably due to an implicit fsync.

I am not aware of implicit fsyncs. Can you please link to what you are referring to?

Probably in reference to Ext4's[0] configurable rename-replace behavior under auto_da_alloc

[0]: https://www.kernel.org/doc/Documentation/filesystems/ext4.tx...

Edit: note this isn't a universal property either, so it's still wrong: https://btrfs.wiki.kernel.org/index.php/FAQ#What_are_the_cra...

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#53

Post by grogers »

HideousKojima wrote: post #42
electroly wrote: post #10

Earlier quoted context omitted.

You can disable the fsync calls in sqlite if you wanted to do a little better with this benchmark. You're also explicitly choosing the WAL in your go-sqlite3 configuration which is not at all replicated by your filesystem test. I think, honestly, that you're just going to mislead and confuse people who don't know any better with this writeup. I can write faster to /dev/null, too, but that isn't a very interesting com…

Does /dev/null support sharding?

Even better, it's embarrassingly parallel!

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#54

Post by ehvatum »

jbluepolarbear wrote: post #46
baq wrote: post #28

Earlier quoted context omitted.

Easier than SQLite? With less bugs and better tests? SQLite advertises itself as an fopen replacement. Sounds like a perfect match for parent’s use case.

Yes easier, as in time to integrate and get working correctly. I’ve used SQLite extensively over the last 10 years and yes it’s a good solution, but not a replacement for fopen. A flat file would be easier to integrate, test, and harden over SQLite. Would take about the same time as integrating SQLite into a system for the same purpose and would be easily extendable to support features as the system grows.

> Would take about the same time as integrating SQLite into a system for the same purpose and would be easily extendable to support features as the system grows.

I find it much easier to add features to my post-2007 projects (when I started using SQLite) for the specific reason that I can open the SQLite file in a GUI and pretty quickly see what’s going on with data organization (schema) and how the customer uses the software I wrote (ie by what columns they actually use/misuse).

Prior to that, there’s various versions of my b-tree library and lots of zips, or linear text indexes, or any combination of whatever fit the need. Data storage implementation needs to be reasoned about in detail for each pre-2007 revisit in ways that don’t happen with SQLite projects.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#55

Post by contingencies »

I would use the term 'unrealistic' vs. unscientific. Points not discussed: Filesystems are not equal, thus portability may be a concern. For example, filesystems often have path or filename length limits, number of files or directory entries per directory limits, and cannot always store all characters as part of path or file names. Losses may not be evident when they occur or exhibit well-defined behavior, particularly if arbitrary filesystem-related tooling is in use. Filesystems do not permit complex indexing structures, rather typically use only an hierarchical indexing paradigm plus OS-level caching. Efficiency in real-world use is dependent on the approximate ratio of disparate reads, writes, and read-and-write access paths you will actually use (typically not just a contrived direct FS-hierarchical single-record write-or-retrieval) also noting the OS cache settings and backend block storage specified, and whether there is any chance of contention from other processes. Finally, persistence expectations are critical. If you want all writes to last you are in a very different boat to if you just need raw performance. In the latter case, never touching the disk during normal operations and only periodically flushing memory state to disk is a clear win. With SQLite3 you can do this easily via https://sqlite.org/inmemorydb.html or slightly less efficiently at the OS level by providing a RAM disk for it to work on. OP could try this and add to the benchmarks. Finally, filesystems do not permit the additional features of SQLite such as triggers and well defined error and write persist behavior, which are well advised to prevent entire categories of issues on datastores anticipating a degree of longevity and criticality. Oh, and SQLite3/FS performance via a bunch of go libraries isn't necessarily the same as raw.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#56

Post by hinkley »

asperous wrote: post #37
hinkley wrote: post #30

Earlier quoted context omitted.

A bad write on a zip file destroys the O(1) seek time, but it doesn't destroy the zip. That goes back to PKZip trying to work on floppies and over modems. You can still do an O(n) seek on a particular file, or expand and recompress the file to recover whatever isn't truncated. For this situation it does matter, but it is recoverable.

Oh interesting! Good to know

That property came back into use when we crossed the 2GB and then 4GB threshold for archives, because the TOC uses a 32 bit integer to reference backward into the file. A lot of implementations used a signed int, and even when they fixed it, organic growth in bandwidth and thus content size ran you into the wall in another couple of years. We had collectively mostly supported UTF-8 filenames around that same time period, so it was going from one problem to the next (or choosing between implementations that had fixed one but not the other).

For the 32 bit address problem, you could read forward from the front, and as long as no entry was over 4GB, you could still read the file. If the file count was low enough you could cache all of the entry objects, and if reads dominated opens, then you were functionally back to O(1) access (but O(n) startup).

There was a 64 bit extension going around, but when I stopped working with zip files every day I stopped tracking the progress.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#57

Post by heinrich5991 »

ehvatum wrote: post #54
jbluepolarbear wrote: post #46

Earlier quoted context omitted.

Yes easier, as in time to integrate and get working correctly. I’ve used SQLite extensively over the last 10 years and yes it’s a good solution, but not a replacement for fopen. A flat file would be easier to integrate, test, and harden over SQLite. Would take about the same time as integrating SQLite into a system for the same purpose and would be easily extendable to support features as the system grows.

> Would take about the same time as integrating SQLite into a system for the same purpose and would be easily extendable to support features as the system grows. I find it much easier to add features to my post-2007 projects (when I started using SQLite) for the specific reason that I can open the SQLite file in a GUI and pretty quickly see what’s going on with data organization (schema) and how the customer uses the…

What's the UI you use for viewing SQLite files?

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#58

Post by strainer »

It is stated regarding Btrfs -

> we're writing the entire file each time

This is a super-common misunderstanding of Copy-On-Write. Entire files are not re-written on each write to a file. New blocks are written and then the (btree) metadata is updated to include the updated block in the file. [1] This causes greater fragmentation than writing new blocks over old ones in the files existing structure as non-COW systems can do. Another thing which somewhat slows btrfs is all blocks have checksums calculated, stored in metadata and checked on read. If entire files really did need copied on each edit - it would be a great deal slower !

[1] https://btrfs.wiki.kernel.org/index.php/Btrfs_design#Files

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#59

Post by tyingq »

samwillis wrote: post #7
kevingadd wrote: post #6

Earlier quoted context omitted.

It's not uncommon to see people advocate for using sqlite as an alternative to flat files or json blobs. In particular, transactions provide nice properties vs a web of separate flat files, and the stronger schema can be a good alternative to json. There's a lot of great existing sqlite tooling, too.

Not only other people, the creators also advocate for it as a file format: https://www.sqlite.org/aff_short.html https://www.sqlite.org/appfileformat.html

Sqlite archive files are somewhat interesting too: https://www.sqlite.org/sqlar.html
Post reply on HN