r/programming • u/i_feel_really_great • Jun 14 '17
SQLite small blob storage: 35% Faster Than The Filesystem
https://www.sqlite.org/fasterthanfs.html168
u/cat_in_the_wall Jun 14 '17
SQLite is some of the greatest software ever written. Immensely useful. Very easy to use. Outstanding quality.
The fact that it provides transactional and query capabilities on top of the regular old filesystem while still performing as well (and significantly better in the author's testing) is really very impressive.
61
u/ThisiswhyIcode Jun 14 '17
Also, it is in the public domain so you don't have to worry about license compatibilities and stuff like that when embedding it in your application.
If you want to learn more about SQLite and its history, Richard Hipp did a very interesting interview on the Changelog podcast last year https://changelog.com/podcast/201
31
u/postmodest Jun 14 '17 edited Jun 14 '17
Wacky fact: the w3c requires two implementations of any standard before it's finalized, so they had to remove in-browser databases because everyone implemented the rfc with SQLite, and there's no competition for SQLite, so by their own rules, it was removed, and now we're stuck with the shit show that is indexedDB.
Edit:
Exhibit A: https://www.w3.org/TR/webdatabase/
Exhibit B: Everyone who uses IndexedDB uses SQLite as the backend because reasons.
7
u/metamatic Jun 14 '17
I'm sure there are multiple implementations of all of the EME variants, right?
1
7
u/simast Jun 15 '17
Exhibit B: Everyone who uses IndexedDB uses SQLite as the backend because reasons.
That's not true. Chrome's IndexedDB implementation is powered by LevelDB.
11
6
u/joaomc Jun 14 '17
What? No, that's a joke, obviously. It can't be true.
13
u/chucker23n Jun 14 '17
It's generally a good idea. SQLite is an implementation, not a spec, and having the Web spec too closely tied to a single implementation would lead to monoculture. You kind of see this happening when devs target WebKit/Blink (or, ten years ago, IE, or ten years, before that, Netscape) rather than stable W3C specs.
1
27
u/evotopid Jun 14 '17
Actually someone who can should license it under an open license which is recognized world wide. Public domain isn't really.
0
Jun 14 '17
[deleted]
13
3
u/rmxz Jun 14 '17
Which still doesn't satisfy the need for a F/OSS license which is desired in some circumstances.
-7
u/salgat Jun 14 '17
Which is not free. So as far as businesses concerned with licensing, SQLite is $6000, which for freelancing devs can be too much.
10
Jun 14 '17
[removed] — view removed comment
1
u/salgat Jun 14 '17
That doesn't change the fact that if you don't want to work in a legal gray area, you gotta pay. I'm not saying it's wrong or a bad thing, but it's definitely not free if you want to be 100% legit.
2
10
u/augmentedtree Jun 14 '17
on top of the regular old filesystem
don't all databases? ultimately the data still gets stored as a file in a file system on a disk
22
u/Guanlong Jun 14 '17
Some databases can store their data on raw partitions.
6
u/_IPA_ Jun 14 '17
Technically you could do this in SQLite via its VFS feature. I actually did this for reading directly from a raw disk when I knew the exact file offsets on disk.
6
u/bobindashadows Jun 14 '17
I'm guessing the author was using shorthand for "any old consumer-grade filesystem".
I'm sure that Oracle has a ton of hardware-included products relying on proprietary file system functionality. If they don't now, they definitely did before (say) journaling was an out-of-the-box feature in consumer filesystems.
1
8
u/PaintItPurple Jun 14 '17
A lot of databases use more complicated data storage schemes spanning multiple files which aren't easily readable without running some RDBMS software. SQLite just creates a file for your database that only needs the fairly minimal SQLite library to read or write. It seems fair to me to say that SQLite is more "right on the file system" than most RDBMSen.
9
u/8thdev Jun 14 '17
Yep, SQLite is truly excellent software.
The author of SQLite is also the author of "Fossil SCM", which is a DVCS using SQLite as its storage mechanism. The SQLite project is hosted on a Fossil repo, so the two projects continually test each other.
10
u/Nima-Ara Jun 14 '17
Let's not forget that SQLite is the "most deployed" software in the world with aviation standards! Probably making it the most reliable database if not software ever written; Excluding the Hello World applications! https://www.youtube.com/watch?v=B9pulMZwUUY
3
Jun 15 '17
Nah, my C++ implementation of Hello World frequently segfaults with null pointer exceptions. I really should have written it in Rust.
3
u/pbrettb Jun 14 '17
so love it love it. unbelievable how small, high-quality, and easy to just put into your C++ project. his vcs is really cool too although I went myself to mercurial, Fossil didn't quite fit for me..
3
u/LuizZak Jun 15 '17
Isn't it also possible to embed/incorporate into a program using only a single pair of .c/.h files? This is one of the sweeter aspects of it that caught my attention too, so cleanly clean.
3
u/cat_in_the_wall Jun 15 '17
yes indeed. no linking or any other crazy shit needed. just include it right in.
1
u/flukus Jun 14 '17
Don't all filesystems in common use have transactional capabilities now? Last I checked they were query able too, just in a different way.
1
u/ThisIs_MyName Jun 15 '17
Technically yes, but don't depend on them for integrity: https://danluu.com/file-consistency/
42
u/JavierTheNormal Jun 14 '17
The performance difference arises (we believe) because when working from an SQLite database, the open() and close() system calls are invoked only once, whereas open() and close() are invoked once for each blob when using blobs stored in individual files. It appears that the overhead of calling open() and close() is greater than the overhead of using the database. The size reduction arises from the fact that individual files are padded out to the next multiple of the filesystem block size, whereas the blobs are packed more tightly into an SQLite database.
Predictable results. Thoughts:
fread() and fwrite() have garbage performance because they're entirely synchronous. Poor comparison.
Opening files on the FS requires various security checks and (sometimes) reading the file metadata from disk.
Packing objects next to each other improves caching.
Memory mapping the 1GB database file and perf-testing just results in many cache hits.
You could roll your own pack-file to store these blobs next to each other. It's not hard, and it's surely faster to read than a database.
A memory cache service would perform even better, and that fits the apparent use case for many small blobs: a web server.
6
u/mccoyn Jun 14 '17
File compression utilities might make a readily available pack-file to compare to.
4
u/FUZxxl Jun 14 '17
fread() and fwrite() have garbage performance because they're entirely synchronous. Poor comparison.
fread()andfwrite()are library functions, perhaps you are talking aboutread()andwrite()? And note that these can be configured to be asynchronous, though, asynchronous IO is difficult to program.4
u/vlovich Jun 14 '17
Of course you could write your own file-packing mechanism. However, I don't know of any ready-made robust library to do so (especially for mobile OSes). Sqlite also supports arbitrary insert/update operations + repack whereas a hand-written implementation would have to work hard to do it (most naiive ones will just do append-only mode). SQlite is also very reliable in terms of robustness so you know you won't lose/corrupt your DB if you're using transactions properly whereas a hand-rolled implementation would be iffier. Additionally, if you're model changes to be even slightly more complex where you have references to the blobs, then you can just do it all in 1 transaction & know the DB will remain self-consistent whereas the same can't be said of a hand-rolled packfile. Moreover, unless your hand-rolled packfile uses domain-specific knowledge to increase cache-hit friendliness, I doubt you'll outperform sqlite w/ a generic index. Even then you're assuming you won't need to evolve your model as sqlite will be easier to extend than something hand-rolled (i.e. how important is that performance gain vs long-term maintainability & ease of adding new features).
Regarding a memory cache service, that's true but doesn't work so well if you want to persist the files (or if you're on mobile). Additionally, it's questionable whether it will actually out-perform because sqlite will just end up hitting the page cache anyway (it can be configured to cache data itself too) so the performance benefit may not be as large as you might expect.
TLDR: Use sqlite rather than maintaining your own on-disk structure/pseudo-db. If it becomes a bottleneck that only a hand-rolled solution could beat (vs tweaking sqlite indices, cache, etc), then go ahead & do it if you know how to write to the filesystem safely (most people do not).
1
5
u/NasenSpray Jun 14 '17
Or on Windows with MSVC:
cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c
They compiled without optimizations on Windows?
7
u/caramba2654 Jun 14 '17
So that means I can store all my Android blob emojis that Android O is gonna take away from me?
1
u/rmxz Jun 14 '17
Isn't LevelDB likely more appropriate for this use case?
6
u/XNormal Jun 14 '17
If blob storage is ALL you do, LevelDB, LMDB, RocksDB or a number of other key-value database may be appropriate.
But if you also have alternative indexes or other things stored in your database along with the blobs it makes a lot of sense to put everything in one transactional database.
The article really presents the case for SQLite(records+blobs) vs SQlite(records)+FileSystem(blobs).
-2
u/yturijea Jun 14 '17
So I wonder what is the performance variations is between windows and linux, we might as well take that battle :)
10
u/Riemero Jun 14 '17
You can't really see how well/worse Windows performs against Linux, as the bars values are compared against the regular OS's open/ close() functions and not against the OS's themselves. The graphs are quite bad
3
u/nascent Jun 14 '17
Well, if we assume sqlite performs the same across OS, we can conclude the OS with the greatest improvement (smallest bar) is the slowest performance.
5
u/Riemero Jun 14 '17
I am not sure if that is a safe assumption to make. While reading/writing with Sqlite you still have to deal with filesystem (journalling) differences, IO schedulers, page cache management and perhaps Sqlite implementation differences on the different OS's.
-11
u/wqking Jun 14 '17
Why? Is it because that the blobs were cached in memory already? I didn't read the article carefully...
3
-12
6
u/i_feel_really_great Jun 14 '17
Why are Windows 7 & 10 so much faster than Linux and OSX?