[HN Gopher] An unscientific benchmark of SQLite vs. the file sys...
___________________________________________________________________
An unscientific benchmark of SQLite vs. the file system (btrfs)
Author : leeoniya
Score : 75 points
Date : 2022-02-12 17:09 UTC (5 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| CJefferson wrote:
| I couldn't see any fsync (or similar) in your filesystem code
| (sorry if I missed them) -- it doesn't seem like a completely
| fair comparison, as sqlite has various promises about commits
| being completely made, etc, which you won't get from just
| chucking files on a filesystem.
| bogota wrote:
| 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.
| baq wrote:
| SQLite makes seeking in a file using a lookup table look like
| a complex operation from the developer's perspective, which
| is absolutely a good thing unless you have a strong
| alternative rationale for your use case.
| kevingadd wrote:
| 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.
| samwillis wrote:
| 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
| electroly wrote:
| In one application I use sqlite to store JPEG thumbnails.
| That's it; there's nothing else in there. It's super handy; I
| specifically needed to reduce the number of files that I open
| and close because that's slow on Windows/NTFS. SQLite made
| this trivial. I could have managed a binary pack format on my
| own but I didn't have to.
| asperous wrote:
| Another alternative that I've seen used is a zip or tar
| with no compression if you are just appending files and
| reading but only rarely updating or deleting.
|
| But sqlite is still better, it is more reliable, a bad
| write on that end of zip index destroys the whole zip
| archive and sqlite also gives you a lot more potential
| flexibility later on if you need to add metadata or
| something else. It is better in terms of inserts and
| deletes, although you will still need to vacuum.
| hinkley wrote:
| 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.
| asperous wrote:
| Oh interesting! Good to know
| jbluepolarbear wrote:
| You could have easily done that with a single file with a
| tail header for look ups.
| electroly wrote:
| I'm aware; I mentioned this at the end of my post. This
| is still easier. I want to clearly express how absolutely
| trivial it was to use SQLite for this. I didn't really
| have to think about anything.
| baq wrote:
| 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.
| jbluepolarbear wrote:
| 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.
| KerrAvon wrote:
| Depends on the indexing needs, right? Something like IFF
| should be fine if you need a sequence of images to store
| that you don't need to actually query for individually.
| baq wrote:
| Always, but I find it really hard to see how not using
| SQLite in this use case is the better option. Maybe if
| you need every single percentile of performance? In 99%
| of other cases just go with SQLite.
| AceJohnny2 wrote:
| Indeed, "SQLite does not compete with client/server
| databases. SQLite competes with fopen()."
|
| https://www.sqlite.org/whentouse.html
| christophilus wrote:
| 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.
| electroly wrote:
| 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 comparison.
| HideousKojima wrote:
| Does /dev/null support sharding?
| pa7ch wrote:
| Yes.
| cafxx wrote:
| I am not aware of implicit fsyncs. Can you please link to
| what you are referring to?
| kevingadd wrote:
| If you're using the same file, it would make sense if the db
| 'warms up' by growing enough to comfortably hold all your data.
| christophilus wrote:
| Good point. That's the more probable explanation, actually. I'd
| imagine the execution plan would be cached within the first
| batch of requests. I hadn't considered the file resizing, but
| it's a good hunch.
| corbani wrote:
| Did you seee this? https://www.deconstructconf.com/2019/dan-luu-
| files
| rurban wrote:
| How about a fast filesystem? This is by far the slowest.
| christophilus wrote:
| That's fair. It's the file system I have easy access to. I
| should spin up a VPS and use ext4 or xfs. I may do that when I
| have some spare time. It looks like I may get double the perf
| by making that switch. SQLite also seems to get a bump on
| different file systems.
|
| Edit: just ran it on EXT4 on Linode.
|
| SQLite: 8.7s
|
| EXT4: 18.3s
| tmikaeld wrote:
| I'd bet that xfs is the fastest, it usually is
| nh2 wrote:
| > then rename it to overwrite the existing file ... This makes me
| think that probably my initial file tests weren't waiting for
| fsync, but the rename forces the application to wait. I'm not
| sure.
|
| I can answer this one.
|
| This is conflating atomicity (atomic rename) with durability
| (fsync; whether data is guaranteed to be on disk and will survive
| power failure). These are two orthogonal things.
|
| When you use sqlite with default settings, it will give you both
| atomicity and durability.
|
| If you want to get both in a file system like btrfs, you need to
| (1) fsync the written file, (2) do the atomic rename, (3) fsync
| the directory that contains the renamed file.
|
| Some file systems, like ext4, do automatic fsyncs "every couple
| seconds", but that is of course not something to rely on when
| benchmarking.
| pmarreck wrote:
| Now you got me intrigued by the concept of a big flat raw sqlite
| file AS a filesystem... I see there is an old FUSE implementation
| christophilus wrote:
| TL;DR: yes, probably for a real-world use case.
|
| Anyway, the perf is good enough for what I need, and the
| flexibility of querying makes it well worth it. There is
| something nice about plain text files, though.
| christophilus wrote:
| I'm the author. Out of curiosity, how did you find and post this
| before I did?
| leeoniya wrote:
| i follow you on github :)
|
| sorry!
| christophilus wrote:
| Ha! No worries.
| formerly_proven wrote:
| For SQLite in this test writes in particular means transactions,
| where each transaction contains exactly one write.
| christophilus wrote:
| Yeah. It's simulating the behavior of a CRUD server I'm
| thinking about writing. I wouldn't be able to batch writes very
| easily there, so it would be a transaction per write.
| CalmStorm wrote:
| The article doesn't mention whether SQLite WAL mode is on. That
| could make SQLite significantly faster:
| https://sqlite.org/wal.html
| bungle wrote:
| It mentions it at the end:
| ./tmp.db?_timeout=5000&_journal=WAL&_sync=1
|
| Not sure if it was updated after your comment.
| ape4 wrote:
| A quick google shows there are few FUSE SQLite implementations.
| Then you can use grep, ls, etc
| spockz wrote:
| Has anyone tried whether this will give you build performance
| benefits if you put your application source and/or dependencies
| on this fs?
|
| I've played with ramdisk before but didn't notice any
| difference, probably due the fs cache.
| pmarreck wrote:
| How would you defrag the sqlite file? Or would VACUUM or what
| have you automagically accomplish that?
|
| Actually, having a sqlite filesystem is intriguing because you
| could in theory add any kind of metadata or filesystem feature
| you wanted to (such as a forward-error-correcting checksum
| field, auto-compressed/decompressed data, dedup, encryption,
| etc.) It would make it nearly trivial for anyone to experiment
| with new filesystems.
| ape4 wrote:
| I don't know, maybe: `touch /mnt/sqlite/.vacuum` could be
| setup to vacuum?
| BenjiWiebe wrote:
| Why not hook into fstrim for that?
| tyho wrote:
| Go is perhaps not the best tool to use for this as calling sqlite
| via Cgo will incur a penalty. Might be significant in a very hot
| loop.
| christophilus wrote:
| Go's the tool I'm using to build a side project, so I was
| really only curious about Go + SQLite vs Go + file system.
| jchw wrote:
| You might find these projects interesting:
|
| - https://pkg.go.dev/modernc.org/ql
|
| - https://pkg.go.dev/modernc.org/sqlite
|
| It's hard to find much information about them, but the first
| one seems to be a database similar to sqlite, and the latter
| seems to be some kind of automated translation of sqlite to
| Go. I'm not sure either of these will outperform sqlite on
| CGo, but both of them should eliminate the CGo overhead and
| might give you some more room.
|
| P.S.: The latter is a bit unorthodox in that it contains
| separate code for each architecture supported by Go, most
| likely a reflection of the method of the automated
| translation.
| christophilus wrote:
| I hadn't heard of ql, but I tried the latter project, and
| it was much slower for inserts. Something like 500/second.
| chakkepolja wrote:
| depending on your data, there's also Keyvalue stores like
| boltdb & badger.
| derefr wrote:
| I believe https://github.com/erthink/libmdbx is the
| highest-throughput transactional KV store with a Golang
| binding right now. (It's the current storage backend for
| https://github.com/ledgerwatch/erigon, which switches
| quickly to whatever wacky library is optimally performant
| at the moment.)
___________________________________________________________________
(page generated 2022-02-12 23:00 UTC)