SQLite small blob storage: 35% Faster Than the Filesystem
sqlite.org
sqlite.org
The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how write intensive the workload is," but keep in mind this is spinning media from 2006. Modern SSDs change the equation quite a bit, as they are much more friendly to random IO and benefit less from database write-ahead log and buffer pool behavior.
Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency. The DB is self contained, and all blobs are contained in it. A restore of the DB on a different system yields a consistent system, it won't have links to missing files, and there won't be orphaned files left over (files not referenced by records in DB).
Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. Blobs bring additional overhead, require bigger DB storage (more expensive usually, think AWS RDS) and the increased size cascades in operational overhead (bigger backups, slower restore etc).
[0] https://www.microsoft.com/en-us/research/publication/to-blob...
Interesting. I would have thought the other way around, at least for crash-resistance: the I/O stack (including disk hardware) has a tendency to reorder writes and so updates that live within a single file will corrupt fairly easily. Separate files not so much. I vaguely remember a paper on that (Usenix?) and sqlite generally did very well except for that point.
- rollbacks in the DB can lead to orphaned files on disk. One can try to add logic in the app (eg. a catch block that removes the file if the DB rolled back) but that is not gonna help on a crash
- it is impossible to obtain a consistent backup of both the DB and the filesystem. You can backup the filesystem and the DB, but the two will not be consistent between them unless you froze the app during the backup. When you restore the two backups (filesystem, DB) you may encounter any anomaly: orphaned files (exists on filesystem but no entry in DB), broken links (entry in DB referencing a non-existent file) etc. This is because the moment at which the backup 'views' the file and the DB record referencing it are distinct in time.
As for write reordering: write-ahead log systems relies on correct write order. All DBs worth their name enforce this one way or another (via special API, via config requirements etc etc)
Something which can abstract away the storage on-disk of large blobs and manage/maintain them over time to prevent a lot of the issues you talk about, but still give the ability for raw file access if/when it's needed.
I've given it all of 10 seconds of thought, but even something like a DB type of a file handle would be useful. Do a query, get back a handle to a file that you can treat just like you opened it yourself.
Filestream https://docs.microsoft.com/en-us/sql/relational-databases/bl...
File Tables https://docs.microsoft.com/en-us/sql/relational-databases/bl...
Remote Blob Storage https://docs.microsoft.com/en-us/sql/relational-databases/bl...
BFILE http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_...
I'm sure there are more. But rest assured, they do cost, and usually a lot.
Things are a bit more complex. For one, the trivial problem of client vs. server host. The DB cannot return a handle (a FD) from the server, because it has no meaning on the host running the app. The second problem is that any file manipulation must conform to the DB semantics for transactions, locking, rollback and recovery.
What you describe does exists, is the FileStream feature that dates back to 2007 if I remember correctly. I'm describing the SQL Server feature since this is what I'm familiar with. The app queries the DB for a token, using GET_FILESTREAM_TRANSACTION_CONTEXT[0] and then uses this token to get a Win32 handle for the 'file' using OpenSqlFilestream[1]. The result handle is valid for usual file handle operations (read, write, seek etc). There were great expectations on this feature, but in real life it flopped. For one it caused all sort of operational headache from the increased DB files size (increased backups size etc) or from problems like having to investigate 'filestream thumbstone status'[2]. But more importantly, adoption required application rewrite (to use the OpenSqlFilestream), which of course never materialized.
File Tables is a newer stab at this problem and this one does allow to expose the DB files as a network share and apps can create and manipulate files on this share and everything is backed by the DB behind the scenes. But turns out a lot of apps do all sort of crazy things with the files, like copy-rename and swap as means to do failure safe saves, but many such operations are significantly more expensive in DB context. And when the DB content is manipulated directly by the apps that 'think' they interact with the filesystem, a lot of useful metadata is never collected in the DB, since the file API used never requires it (think file author, subject etc).
[0] https://docs.microsoft.com/en-us/sql/t-sql/functions/get-fil... [1] https://docs.microsoft.com/en-us/sql/relational-databases/bl... [2] https://www.sqlskills.com/blogs/paul/filestream-garbage-coll...
I'm hoping that PostgreSQL can try to tackle this use-case somehow because it's so damn common and 99% of the time the solution that is used completely throws out all the guarantees that the database gives you.
In some ways, the problem mirrors OR mappers. A lot of common cases can be handled, but there are always situations where you're confronted with the fact that relational logic just doesn't map cleanly to OO (or hierarchic storage).
Another pain point is backup; it's not possible to create a consistent snapshot of (rdbms state, other state on the file system). This is avoided entirely if you only need to snapshot (rdbms state,).
This is especially true if you want to deliver them back to the users in the context of a web application. If you place this kind of content in a database, you'll need to serve them with your application.
If you use files for this content, you get two interesting options. For one, you can use any stock web server like nginx to serve these files - and nginx will outperform your application in this context. On top of that, it's easy to push this content onto a CDN in order to further cut the latency to the user.
Well, not necessarily: https://github.com/FRiCKLE/ngx_postgres/
But I second that is an interesting nginx module
A filesystem is just an hierarchical database.
Loosely. Today's file systems aren't transactional (i.e. acid compliance), which is a basic property that most people consider necessary for a database.
> a basic property that most people consider necessary
I use non-ACID stores for some purposes, but don't consider them real databases.
Those give you these atomic ops and optimistic or cooperative locking: https://rcrowley.org/2010/01/06/things-unix-can-do-atomicall...
Multi-file transactions can be built by moving whole directories over symlinks to the previous version. The linux-specific RENAME_EXCHANGE flag can simplify this.
> or is there a new transactional API
CoW filesystems give you snapshots and reflink copies via ioctls. Under heavy concurrency this can provide cheaper isolation than locking.
> and makes the writes always durable (sync; not fsync)?
For durability fsync is sufficient if you do it on the file and the directory. To combine atomic and durable you can do the write, fsync, rename, fsync dir dance.
Right. This allows for the correct behaviour but I wouldn't conclude that POSIX is a transactional API. I would regard this as being able to build a transactional API on top of it; but not transactional in itself. I mean, if you had to do the same dance in SQL (insert to dummy pkey and then update the entry) you would rightly dismiss it.
> You can build multi-step transactions on top of them with various operations.
That's what I thought.
https://www.freebsd.org/cgi/man.cgi?query=sendfile&sektion=2
That's what I thought.
It has many optimizations, like not opening the file its serving (which reduces the latency of walking/interacting with the VFS layer); almost no runtime allocations on the heap (minimal stack frames and sizes as well) which can be completely disabled at the expense of logging; high performance logging which does not interfere with processing requests; the normal stuff in a fast webserver (sendfile, keep-alive, minimal code paths between reading the user request and delivering the first byte).
It delivers an ETag header that it computes the first time it opens a file and maintains as long as that file is open. If it has to re-open the file it will generate a new ETag value.
total sadness about having to deal with a 150GB SQL database, of which 148 GBs are blob storage for PDFs
"We have big data!! We need enterprise scale!"
Nope. You have big stupid.
There are several very valid business cases to store files as blobs in a DB.
What's the problem is the DB is 150GB? It's not like the working set (which for the files will just be the metadata) will be that big for storing file blobs.
10GB Database + 140GB of pdfs on the filesystem are not any different to a 150GB DB with everything in.
And you have other issues (consistency, transactional issues, backups, etc).
How about it?
>It seems there are better ways of managing lots of immutable small files (including for backups/replication) than shoving them all into a database.
Depends on the business case. A database gives certain guarantees you'll have to replicate (usually badly) in any other way.
Conceptually, it's absolutely cleaner.
As for from a scientific or engineering standpoint, there are again no laws that dictate saying whether this is bad or good.
Even the performance characteristics depend on the implementation of the particular DB storage engine. They might be totally on par with storing in the filesystem (or close enough not to matter).
Not to mention that some filesystems might even have worse overhead depending on the type, size, etc. of files. In fact this very FA speaks of "SQLite small blob storage" being "35% Faster Than the Filesystem".
Plus filesystem storage and DBs are not that different in most cases -- they share algorithms for storage and indexing (logstorage, btrees, etc), and the main difference is the access layer. The FreeBSD filesystem, for one, was more like a DB storage layer than a 70s style Unix filesystem.
Trivial example: it's easy to delete any file most recently accessed more than N months ago [+] with a trivial line in cron (the exact line escapes me -- it involves find), but doing that with a database requires that you roll your own access tracking logic. Incremental backups of a directory structure are easy (rsync or tarsnap); incremental backups of a database appeared to me to be highly non-trivial.
[+] Since we could re-generate PDFs or gifs from our source of truth at will (with 5~10 seconds of added latency), we deleted anything not accessed in N months to limit our hard disk usage.
Some user-level backups for example, will make the atime useless.
Store the files in a database but expose them via FUSE.
Sounds like they're OK with that.
In any case, couldn't you avoid the kernel trips with some dylib foolery (assuming the toolchain is dynamically linked)?
I keep running in to this as a common anti-pattern in software, even with software developers that should know better (having fallen afoul of it before).
I ran in to it with some semi-bespoke software once, used by a remarkable number of state departments across the country. It was one of many, many things wrong with that godawful thing. Worse, not only were they dumping files in a single directory, almost any time they went to do something with the directory they'd request a directory listing too. Like many of the bizarre things the app did, it never actually used the information it acquired that way.
If you want raw file access, your ideal filesystem is a giant key-value store that keys on the full path to the file. This choice means doing a directory listing will involve a lot of random access reads across the disk, and in the days before SSDs this would be a performance killer.
So instead, a directory would have a little section of disk to write its file entries to. These would be fairly small, because most directories are small, and if there were more files added than there was room, another block would be allocated. Doing a file listing would be reasonably quick as you're just iterating over a block and following the link to the next block, but random file access would get slower the more files are added.
It's probably a solvable problem in filesystem design, but because current filesystems fall down hard on the million-files-in-a-directory use case, nobody actually does that, so there's no drive to make a filesystem performant in that case.
Searching by filename on the other hand can be much faster with a prefix tree because directories only allow linear scans, not range-based ones.
f = open("/path/to/file");
// do something with file
d = opendir("path/to")
while((dent = readdir(d)) != NULL) {
if(!dent.name.startsWith("file"))
continue;
// do something with first matched entry
}
The latter is always O(n)
This makes any work in the shell painful.
If you store them on a filesystem, how do you deal with redundancy / failover / scaling ? At a previous job we did a bit of experimenting with using a clustered FS but they all introduced a lot of problems. However, this was a couple of years ago so the situation may be different now.
In most cases, using a solution like Amazon S3 will have all these three sorted out for you pretty good. And while it's not a full mountable filesystem, from a system development perspective it has a really good API for retrieving and storing files.
But I think for it to work well you need a better strategy than just large BLOBs.
GridFS for MongoDB - https://docs.mongodb.com/manual/core/gridfs/
ReGrid for RethinkDB - https://github.com/internalfx/rethinkdb-regrid
I'm sure the same concept could apply to other databases.
But as for how expensive `fopen` is really depends many factors.
Hmm... Does SQLite have some geo data or 2d coordinate lookup capabilities as well?
https://github.com/sqlitebrowser/sqlitebrowser/wiki/SpatiaLi...
(people had been asking previously, but we had no idea. ^\o/^)
Part of the concerns outlined in that article and of more importance for this company are ease of distribution/compatibility for customers of mapsets and mobile devices, rather than raw read/write performance, however.
Though the storage size claims might be relevant, and definitely a clear example that sqlite can be very workable and performant in the context of blob storage.
Original post: https://developmentseed.org/blog/2010/oct/08/portable-map-ti...
They then do not offer logical analysis as to why things are faster.
My understanding is that reads are probably faster due to operating system readahead being able to predict reads better when they are within a single file. Writes are faster because they do one bulk COMMIT instead of many individual fsyncs.
> 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.
One could, but then one would have great difficulty retrieving the individual files back when needed.
The point is not about what has the greatest raw speed, the point is that that for applications that read lots of small files from the filesystem, they'll possibly get better performance and almost certainly use less disk space by sticking the files in SQLite instead.
Not that great, and the point is its comparing apples to oranges, pointless.
You’d end up rewriting your own version of a database.
This is the hell we just crawled out of in one of our codebases.. it's not so much the access for a single process, but when you want to share the data in the file, that's when it seriously starts to get messy and breaking through otherwise clean abstraction layers and/or forcing recompile on compatibility breaking changes.
The idea came from previous generations of the product which generally ran on microcontrollers coupled with custom FPGA designs, so, not entirely unwarranted.. but completely incorrect for a newer product with much greater computing power than their previous devices.
Oh goodness have we come a long way if interacting with a file is harder than interacting with a SQL database.
(it really depends on what language and/or library(ies) you use and for what purpose)
Sure, it's completely trivial to read a file containing an array of C structs of metadata and offsets into a blob of concatenated PNGs: just mmap the thing, and your main data structure is sitting right there in memory.
But problems start arising when you need to update the structure of that metadata. Add some more fields. Some more structured, multi-value fields, referring to multiple entities in your increasingly complex data structure. Entities which must be guaranteed to exist. And perform modifications concurrently, from multiple processes. And have the file not get corrupted when something kills a writer process in the middle of writing...
Databases are slow and overkill for the simple case, but they certainly make adding unexpected requested features much easier. And it can be expected that you will get unexpected feature requests.
It's not interacting with a file, it's interacting with a file that contains other files of varying sizes that need to be accessed randomly with good performance.
If you want to do random writes then more consideration is needed.
And programmatically accessing them is significantly more complicated than using SQLite which involves dropping a single header in your project and about 10 lines of code.
> If you want to do random writes then more consideration is needed.
Which you almost certainly what you want to do for the sorts of use cases where SQLite is also under consideration.
10 lines of user code + the sqlite library. Using other formats like zip is hardly any more user code (+ some external library).
Btw I'm personally a big fan of SQLite, I just think the argument here is a bit of a straw man.
https://eigenstate.org/paste/d41e3b69d258e3b52f7cee7a9921f02...
Conclusion seems to be that filesystems on windows are not well optimized for the usecase of reading and writing from many small files at once.
Which could explain poor windows performance in the benchmark (writing to 100,000 individual files).
What they did is check an untuned Ext4, UBIFS, HPFS+ and NTFS vs a tuned database in a single thread scenario. Especially misleading in the mmap case where synchronization would just kill the performance dead.
This is under the 1.1 Caveats heading, which makes me feel the title is a little misleading (but only in the way most benchmark headings probably are, I guess).
Incidentally, can someone more experienced with filesystem and database I/O confirm or contest the assertion here? Specifically, I'm not sure it's fair to generalize these results (even if valid) to categorical relational databases. But this is not a special area of expertise for me.
For example, instead of a database, you could make a folder (instead of a table) and then a bunch of 4 byte files with ints in them. Doing a table scan would be like cat *.
Obviously this uses a lot more open/close/read calls, with more seeks and iops, because those files may or may not be clustered together on disk. If you packed it all into the same file, it would go faster because of the locality and less system calls. You can also be smarter about caching and other things that the file system does well, but probably isn't optimized for the small file use case.
An append-only transaction log is probably going to perform better on a spinning disk than random writes. An intelligent app sorting and committing writes in order is probably going to perform better than an fs on a disk with no queue ordering facility. As they said in the post, block-aligned writes in bulk are going to be more efficient than spreading out a bunch of writes not block-aligned. And latency for a db write is going to be lower if you don't close an fd, assuming that closing an fd would always trigger an fsync.
But a properly tuned filesystem, with the right filesystem, with the right kernel driver, with the right disk, with the right bus, with the right control plane, etc etc may work fantastically better than an unoptimized database, and a given workload may fit that fs perfectly.
It's important to remember that a benchmark is only meaningful to the person who is running the benchmark. If you want it to be meaningful to you, you have to try it yourself (and then presumably blog about it).
I don't get this. If scanning the content is important (as acknowledged by the author), then bypassing the scan via blob storage is a security issue and the application should go through some extra hoops to scan the content before saving it to blob, and this should be measured and part of the comparison.
Also, if the SQLite files are exempt from AV scan, then the level field should also exempt the uploaded files folder in test. I mean, knowing the dice are loaded and then claiming it as an advantage does not seem professional.
Word Docs with embedded content or can trigger code execution on parsing, data structures that break AV parsers, image or font data that causes system libraries to overflow/UAF/etc; There all could potentially be a security issue in a DB, the same as in a FS.
Credential: I was one of several people who vetted the article before it was published. I argued that Defender should be enabled, since it is a platform default. D. Richard Hipp chose to take the charitable path and not report those results alongside the others.
The test is easily replicated yourself. The source code for it is part of the SQLite source tree, and command lines for building and running the test are given in the article.
In any case, I really take issue with that approach. Nearly every windows database vendor, including Microsoft, recommends or requires that you disable most types of scanning in databases. Not only is it ineffective and performance impacting, it potentially undermines integrity of the data.
Once you start getting into optimizations, much of this article starts to sway back and forth. Put enough effort into optimization, and you can outdo SQLite for a specific application, simply because SQLite isn't magic, it's just C. Well-written and highly-optimized C, but purpose-written code always has the potential to do better than general-purpose code.
The point of SQLite in general and this report in specific is that you get a lot of performance, power, and safety for free with SQLite as opposed to `fopen()` and such. Good performance while antimalware interferes is just one way this manifests.
SQLite gets an additional advantage with Defender enabled because Defender doesn't try to scan inside the SQLite DB file for the sub-files, whereas with separate files, Defender must at least look at each file's header to determine whether it's a potentially dangerous file type.
While I was vetting this article, I did some of my own tests, and I found that simply excluding the directory containing the test tree was sufficient to get the same benefit.
My point in other posts here is that you don't need to disable Defender to get this benefit when you use SQLite.
But let's be clear: the 35% claimed performance benefit has nothing to do with Windows Defender. That's a wholly separate issue. Defender pays more attention to a pile of individual files than to an amalgamation of that same data into a single file, and this magnifies SQLite's advantages, but it is not the source of the advantage the linked report talks about.
I always thought that the SQLite system was far quicker to search and retrieve files from the dataset than it was doing so in our previous version, using hierarchical file folders.
Interestingly, we canned the project because the ERP vendor themselves release a similar tool using - wait for it - individual files in a folder system... (Addendum - much later on they switched to - wait for it - CVS for version control.... in 2010!!)
For writing, the major drawback of SQLite is it doesn’t support concurrent writes.
All filesystems do (at least when writing different files), all full-fledged RDBMS-es do, even some embedded databases do (like ESENT). Yet, in SQLite only a single thread can write.
Even embedded chips are multicore these days…
https://www.sqlite.org/cgi/src/timeline?n=100&r=begin-concur...
Also mentioned here:
I'd be happy to be wrong (seriously that would make my day if someone could explain a clean way to do the above) but if I want to get "big boy database features" then I'd use a heavy database like SQL Server. I think it's great that there's an option like SQLLite out there for rapid prototyping and "store all your music metadata" type applications!
The hardware evolved in a way so even very small systems (phones, embedded, Raspberry Pi and other IoT devices) are now multi core. Multithreading is required to benefit from a multi-core CPU. For IO-heavy tasks, it’s sometimes a good idea to implement multi-threaded IO as well.
> you can't write to a file concurrently
I can concurrently write different files. Or to different streams/blobs/records for most other IO methods, except SQLite.
SQLite is a big code base, using a simple archive file format instead gives you most of the advantages, but without the bloat.
PS: This is mostly for read-only situations though. Using SQLite probably starts to make sense when the applications needs to write to and create new files in the archive.
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).
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.
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.
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.
Isn't this because writing to a SQLite database file bypasses most (or all) of the antivirus file scanning since it's can't see a complete file, and can only look at raw blocks of data?
So if you find value in having your antivirus scan all of your files, that's a disadvantage for using SQLite to store them?
For example I have a PHP app that is used by 10k users a day and it happily handles 100k tmp files in a single directory. On each request, it checks the file age via filemtime() and if new enough, includes the tmp file with a simple include(). (I write PHP arrays into the tmp files). If too old, it recalculates the data and writes it via fopen(), fputs() and fclose().
This migh be archaic but it has been working fine for years and never gave me any problems.
Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems.
But if you really need to change your design for some reason, sqlite should handle 10k users per day just fine with simple transactions.
Why remain in ignorance? There are several articles in the SQLite documentation that address concurrency:
https://www.sqlite.org/wal.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/whentouse.html
> Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems.
SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it?
That's not to say that there are no concurrency problems in SQLite, but that they're more in the way of potential bottlenecks than data corruption risks. For a reader-heavy application like yours, I suspect SQLite will perform just as well or better than your existing solution.
If you're solely after speed, I'm not sure it would be worth rewriting your app to use SQLite. This technique's value is simply in the benefit it gives when you were already going to use SQLite for some other reason. If you just want a 35% speed boost, wait a few months or buy a faster SSD. Both are going to be easier and cheaper than rewriting the data storage layer of your application.
That said, maybe there are other things in SQLite that you could use. Easy schema changes, full-text searching, more advanced indexing than the filesystem allows, etc. If you go for one of those, then the extra speed is a nice bonus.
SQLite wouldn't really be ACID-compliant if
multiple threads were enough to defeat the
Durability guarantee, would it?
No idea if that is a realistic scenario. I'm kind of surprised I don't have this kind of problem with my filebased solution. What happens if process A reads from a file while process B writes it? No clue.
If process B started first, the writer blocks access to the table being written to, so the reader waits for the writer to complete. There are timeout and retry behaviors, but within those configurable limits, that's what happens.
You can make SQLite behave as you worry about if you set the retries to 0 and timeout to 0, but that's not the default.
No, it's not. It's a file, but it's not flat.
Also, yes, there are networked databases with sqlite database drivers.
How about reading efficiently with unbuffered I/O directly on the file descriptor?
The source code to the test program is part of the SQLite source tree. It should be fairly easy to modify it to test the scheme you have in mind.
> How well does SQLite deal with fragmentation?
SQLite operates a lot like a modern filesystem: tree-based indexing structures, writes go to empty spaces, deletes leave holes that may later be filled by inserts, etc.
SQLite has active mechanisms to reduce fragmentation on the fly: b-tree page defragmentation (https://www.sqlite.org/fileformat.html) and auto-vaccuum (https://www.sqlite.org/pragma.html#pragma_auto_vacuum) being the main ones. If the DB file gets too fragmented, you can run a manual VACCUM (https://www.sqlite.org/lang_vacuum.html) on it.
The article is about writing 100000 files, which will not scale well (since there will be massive contention at the directory inode).
If you have a dual-boot Win10/7 box, it's easy to run the test yourself.
it's not sqlite vs filesystem it's memory storage vs disk storage
ReiserFS 4 was (I believe still is) the only FS that improves small files performance by bundling small files and stored them on disk as a single blob.
as for that person who downvoted my original comment, I'd love to hear from you why you think I'm wrong. you don't just downvote comments because you disagree.
"The size of the blobs in the test data affects performance. The filesystem will generally be faster for larger blobs, since the overhead of open() and close() is amortized over more bytes of I/O, whereas the database will be more efficient in both speed and space as the average blob size decreases."
Yes, of course SQLite is going to do some operations in-memory. Raw file writes using fwrite are also going to be initially written in-memory, unless O_DIRECT or some other mechanism is involved. And the article explicitly outlines that they made no effort to bypass file buffering, to the point of not even explicitly flushing to disk.
If both processes are writing the BLOBs to disk, then I don't see how your original claim applies. Writing to disk in a manner that is more efficient for small files is still writing to disk, and so the test is not in-memory vs on-disk as your original claim.
filesystem, works on disk most of time, in memory some time (some buffering), slower
which makes my original comment true,
Yet this benchmark is at the top of HN for some reason.
I for one am interested in the result and the reason.
Compare to the typical case of adding another row by appending to an already allocated disk block. No seek operation needed.
For example SQLlite vacuuming to free up deleted data can be slow, on large SQLite databases, we've found it much faster to rewrite the entire file than to vacuum it.
It also has some scalability limits, it uses locking to limit to a single concurrent writer (short duration locks), which only scales up to a point.
There're a lot of file system characteristics that differ from a database file format, SQLite could probably be more file-system like,but then it would diverge from being a fast and lightweight database format.
Microsoft tried the database as a filesystem once: https://en.wikipedia.org/wiki/WinFS (though this goes beyond just being a container)
Instead, think about it this way. With a filesystem, the database is managed by the kernel. Every time you want to read a file, you might do four system calls: open, fstat, read, close. Or you might do mmap instead of read, but you're still doing four context switches, at least in typical cases. Switching to the kernel and back has cost. Normally this cost is small, but if you make a lot of system calls you'll notice the costs piling up. The kernel also has to check permissions to make sure that you have permission to read the file.
With SQLite, the database is inside your application. When you read a row, there's a chance that the row is already in your application's memory. This means no context switches back and forth between application and kernel.
Additionally, when you read a row, the entire database page is read into memory, which includes other rows too. The kernel won't do anything like that with your application--it won't give you img1.png and img2.png if you just ask for img1.png. Maybe they'll both be in the kernel's page cache, but you still have to open and read the file.
http://www.sqlite.org/src/doc/trunk/src/test_onefile.c
Maybe this can be turned into a vfs driver (fuse?).
The major differences here are due to the different levels of isolation and authentication that the kernel and SQLite provide. Another difference is the fact that when you find a row in SQLite by primary key, you don't have to do a second lookup to get the data from the metadata. This is a good optimization to use when storing many small pieces of data, but a bad optimization for large pieces of data.
This was just about opening a file and appending a few bytes vs appending to an open file. Obviously the latter can't be slower (and is typically faster).
"Duh, I always knew that" is easy to say when you don't have to provide proof.
Also, are you sure that CSV will be faster as well? Do you have a benchmark for it?
Can you read the correct line in in CSV faster? Can you find the row with specific critiria like SQLite faster? Can you handle concurrency correctly like SQLite? Can you make sure your CSV is always in consistent state like SQLite?
The point of the article is that SQLite is still fast even with all the benefit of database.
The idea of SQLite is that you could default to using it, and move away when it hits its limit. Nobody should default to using CSV, you use CSV when you have to, despite all its limitation.
And why is the whole setup a farce anyway?
As for turning SQLite into a filesystem, that's not really going to work. SQL isn't designed to support things like cheap "file" appends or reading only portions of a value or seeking or anything like that, so you'd end up having to read the entire value for any read, and write a new copy of the entire value for any write, and your performance would be really really bad. So yeah, putting SQLite inside of SQLite isn't going to work, because SQLite isn't a filesystem and wasn't designed to behave like one. Not to mention this entire article is about small blob storage, and embedding a SQLite database inside of SQLite isn't a small blob.
As for turning SQLite into a filesystem, we could make it into a file system that would be fast in this particular benchmark. Right?
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 third sentence in particular is:
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.
The demo went well.
SQLite is a library, not a database server. There's no install step - it's a single .C file (and a header, if you're into that). Most of the major scripting languages have rock-solid bindings that don't require any additional system libraries or software installs.
Python (in the stdlib!): https://docs.python.org/2/library/sqlite3.html
Nodejs (prebuilt): https://www.npmjs.com/search?q=sqlite
Ruby (needs libs, which are preinstalled on macOS): https://rubygems.org/search?utf8=&query=sqlite3
eh? Sqlite was the quickest thing I ever setup in my life. Just add a .dll and use some form of adapter written in your language and off you go.